RE: How to Version Control a database
-Original Message- From: Will Merrell [mailto:[EMAIL PROTECTED] Sent: 20 February 2005 14:00 To: Mysql Mailing list Subject: How to Version Control a database I have a project that involves several developers working on their own machines. Each has a local copy of the database on their own machine. Since we have some developers who develop while not connected to the network, we cannot use a common database. How can I version control the database so that changes are not lost or stepped on. Right now, we use mysqldump to dump the database and version the dump file, but this still has problems. Is there a better way? -- Will We treat the database as any other source code, i.e. we store three sql scripts in our repository and have a ant target that builds the database from these scripts. If anyone need to change the database layout, the default values or the test data then he or she just checkout the corresponding file and make the change. If it isn't stored in the repository, then it doesn't exist. /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: How remove the closed connections
It was my own mistake , I'm sorry.
Inside the program I leave a bug wich skip the call to RemoveMyConnection;
Now when I call it the connection closed is removed properly also from the
database connection list.
Just to answer to Philippe:
The thread didn't reopen itself, I explained myself badly, it just die. It
is recreated by it master when required.
thanks
-Messaggio originale-
Da: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
Inviato: venerdi 18 febbraio 2005 18.11
A: AESYS S.p.A. [Enzo Arlati]
Cc: [EMAIL PROTECTED] Mysql. Com (E-mail)
Oggetto: Re: How remove the closed connections
AESYS S.p.A. [Enzo Arlati] wrote:
I get an application with several thread , with each thread using it's own
connection.
What I mean is that the thread create a connection at the beginning and
close anddetroyitbeforeitclose.
Under some circustances some of these threads close itself and reopen
after
few minutes ( for example 60 secs. ).
I don't really see the meaning of a thread that close itself and
reopen... If the thread return or die, you should explicitely close the
connection, if the thread is just sleeping, there is nothing to do, just
check the connection when the thread wake up.
When this appened also if the connection is closed , it is still
registered
as active by mysql.
Then the connection is not properly close, and the thread still active
somewhere (in sleep ?) ;)
So I got that each time a new thread is started a new connection is added
to
the list of mysql connection.
When the thread died , mysql keep a reference to an idle connection until
the wait_timeout expiration time.
So I must keep the value of wait_timeout lower , ie. = 120, while the
preferred value should higher that this value.
There are some way to shorten the timeout for removing the connections
without interfere with the timeout used for still active connection ?
I'm using mdac with c++buider 6 and the way I create and destroy the
connection are showed belowe:
is mdac you database layer ?
//
void __fastcall TDataModule_PMV::RemoveMyConnection(TMyConnection *
dbConn)
{
if( dbConn )
{
dbConn-Connected = false;
delete dbConn;
dbConn = 0;
}
}
Does this mean that it will close the connection ?
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with LOAD DATA INFILE
John, please print a detailed description of the latest foreign key error with SHOW INNODB STATUS and post it here. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: John Swartzentruber [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 20, 2005 11:54 PM Subject: Problems with LOAD DATA INFILE I am new to SQL and to MySQL, but am working with it on a project for a graduate Database system course. I'm running MySQL 4.1.10 under Windows 2000. I am trying to load a table from a text file. The table in question has a foreign key. The table that it references contains data. The problem is that when I load the data using LOAD DATA LOCAL INFILE, I get this error: ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails That seems clear enough except that the foreign key constraint should *not* fail. I've verified that the value exists in the other table. What is really strange is that when I tried inserting the data directly using INSERT INTO, it works. In this particular case, I only need to load a handful of records, so using INSERT INTO is an option, but I would really like to figure out what isn't working. Here is my table definition: create table Subscriber ( UserIDint auto_increment, Name varchar(50) not null, Password varchar(8) not null, EmailAddress varchar(50), SGroupNamevarchar(50), primary key (UserId), foreign key (SGroupName) references AccessGroup(GroupName) on update cascade ) ENGINE=INNODB; Here is how I am attempting to load it: LOAD DATA LOCAL INFILE 'c:/Documents and Settings/john/My Documents/Grad School/Project/LoadData/Subscriber.csv' REPLACE INTO TABLE Subscriber FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Name, Password, EmailAddress, SGroupName); Here are the first two lines of my data file: Name, Password, EmailAddress, SGroupName John Swartzentruber, 8490JTTT, [EMAIL PROTECTED], Administrator And here is what *does* work: insert into Subscriber (Name, Password, EmailAddress, SGroupName) values (John Swartzentruber, 8490JTTT, [EMAIL PROTECTED], Administrator); Can anyone see what my problem is? I really appreciate any assistance you can provide. I hope this is the appropriate group for newbie questions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need older version of mysql (current version seeminly corrupts FTS tables)
Hello. I suggest you to use 4.1.10 (in 4.1.8 was fixed a bug #6784, I haven't seen it in a changelist of 4.0.x branch). If myisamchk -r *.MYI, myisamchk -o *.MYI doesn't solve the problem, as the last chance, use USE_FRM mode of REPAIR command. But you may lose some information about the table's metadata, make a backup. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Steven Roussey [EMAIL PROTECTED] wrote: See: http://downloads.mysql.com/archives.php Thank you. Nice link to have around. Key 3 is the FTS key. The others are a UNIQUE KEY (#1) and a KEY(#2). Do you have the same values for full-text parameters (ft_mit_word_len for example)? Not at first. I had noticed that not long after I sent my message, and I rebuilt all the tables with mysamchk -rf *MYI. That eliminated the Duplicate Key errors, but not the Incorrect key file... Try to repair it error. Did it again with 4.0.18 and had the same error. :( Next I did mysamchk -o *MYI to really be sure, and I even used the 4.0.18 binary. Then I tried running 4.0.23 with debug turned on (somewhat) and still get the error. I'll try again with 4.0.18 (or 17) with debug and see if it has the same error at the same place. Here is what I had in the trace file (note that the query is a replace cmd that often is used to update an entry): Do I need more fine grain debug info to find the error? mysql_change_db: info: Use database: search do_command: info: Command on TCP/IP (9) = 3 (Query) dispatch_command: query: replace into forums_posts_1239959 (forumid,messageid,parent,rootmessageid,deleted,deleted_marked,approved,auto respond,loginid,ip,user_id,author,email,title,message,search_forumid ) values (2255626,1108700026,0,1108700026,'no','no','yes','no',1524436,1100232325,'1c c48d0a485629a91e2b5634c122a339', '[EMAIL PROTECTED]','[EMAIL PROTECTED]','Message text deleted for privacy, but I can sent if needed','fid2255626') thr_lock: info: write_wait.data: 0x0 mi_get_status: info: key_file: 28418048 data_file: 39236852 mi_write: error: Got error: 121 on write _mi_writeinfo: info: operation: 1 tot_locks: 1 _mi_ck_delete: info: root_page: 19973120 _mi_prefix_search: info: key: '004' _mi_prefix_search: info: key: '004' _mi_prefix_search: info: key: '004' _mi_prefix_search: info: key: '004' d_search: error: Didn't find key mi_update: error: key: 2 errno: 126 _mi_writeinfo: info: operation: 1 tot_locks: 1 my_message_sql: error: Message: 'Incorrect key file for table: 'forums_posts_1239959'. Try to repair it' thr_unlock: info: updating status: key_file: 28418048 data_file: 39236852 mi_lock_database: info: changed: 1 w_locks: 0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 25 (HY000): Can't create symlink
Hello. May be comments at: http://bugs.mysql.com/bug.php?id=1649 would be helpful for you. Does the altering successful if you executed a FLUSH TABLES before? See: http://dev.mysql.com/doc/mysql/en/merge-table-problems.html Mark Uhrmacher [EMAIL PROTECTED] wrote: Hi Andy, I see that. Unfortunately, I have no idea why mysql is trying to create a symlink to a symlink it just created. Does anyone have any idea why mysql would do that? Thanks, Mark Ady Wicaksono wrote: Hi Mark, There's perror to check what happen, so when you have Error 17 you check from your bash shell like this # perror 17 Error code 17: File exists Gotcha ! The reason is file exists :) Good luck Mark Uhrmacher wrote: Hi all, I've been getting a strange error when attempting to add an index to a set tables that make up a merge table. Here is a transcript of the session: mysql alter table he_access_offline_3 add index status (status); ERROR 25 (HY000): Can't create symlink './Logs/#sql-156e_48d8.MYI' pointing at '/var/lib/mysql/Logs/#sql-156e_48d8.MYI' (Error 17) mysql alter table he_access_offline_2 add index status (status); ERROR 25 (HY000): Can't create symlink './Logs/#sql-156e_48d8.MYI' pointing at '/var/lib/mysql/Logs/#sql-156e_48d8.MYI' (Error 17) mysql alter table he_access_offline_1 add index status (status); ERROR 25 (HY000): Can't create symlink './Logs/#sql-156e_48d8.MYI' pointing at '/var/lib/mysql/Logs/#sql-156e_48d8.MYI' (Error 17) What is interesting is that I could create that index on he_access_offline_4 which is also a member of the merge table (called he_access_offline). Doing some research with Google I found someone had a similar problem when they were using symlinks from files in mysql's datadir to the actual location of their data files. In my case the contents of /var/lib/mysql/Logs are actual files and not symlinks. The permissions appear to be set correctly and, in fact, I've created other indexes on these same tables using the same scheme. I'm not sure what has changed. Also, restarting the server doesn't eliminate the problem. System Info: MySQL 4.1.9 on Fedora Core 2. I downloaded the binary from mysql.com. Any ideas? Thanks, Mark -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: download mysql 4 in rpm for Redhat 9.0
Hello. See: http://dev.mysql.com/downloads/mysql/4.1.html sam wun [EMAIL PROTECTED] wrote: Hi, I realised that install mysql 4 thru rpm is the best way for a smooth installation. Can anyone please point me to a site where I can download mysql 4 rpm for Redhat9? Thanks Sam -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index key on yearweek(date)
Hello. No, you can't. But you can make some workarounds. For example you may use triggers in 5.0.2 version (but it is still not production ready) or use a separate field for storing the value of the week. Hany Nagaty [EMAIL PROTECTED] wrote: Hi all, I'm a bit new to MySQL, it's my first email to the list. Well, my question is: Can I have a key on a function of the column. I have some table with a date column in it. I wish to have a key on yearweek(datecolumn). I don't want to allow having duplicate weeks in the table. Thanks for your support. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log-warnings
Hello. There is no direct way to load warnings into log files. But you may use a small value for max_error_count and launch mysql in a batch mode saving results in the file. Marcus Bointon [EMAIL PROTECTED] wrote: I'm migrating a MySQL 3.23 db to 4.1.10 (on Linux x86) and I'm getting some infrequent warnings when importing data dumps created with mysqldump, but I can't seem to find out what the warnings are. I'm typically importing around 100,000 records at a time, so using 'SHOW WARNINGS' manually is no use - and I can't yet nail down a query that actually generates a warning anyway. I can start the mysql client using -v, but as even at the lowest level or verbosity it always displays the query, and since my inserts are often 30k per record, it's just not practical to scan the output, plus it makes it go very slowly. I've got log-warnings in my my.cnf file, and mysqld is definitely picking it up (adding it made log-warnings appear as set to 2, though I can't find any reference in the docs for what the values mean), but mysql is not logging any warnings at all. I have log-error=/var/lib/mysql/mysql.err set in my.cnf, and again it's definitely being picked up by the server. If it makes a difference, I'm using all InnoDB tables. All I need is for warnings to be logged along with the query that caused it without having to log everything and trawl through it - what have I missed?! Marcus -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems
Hello. I think, you don't have a server part of MySQL distribution. You should install something like mysql-server-xxx.rpm when i run whereis mysql Use 'l' query option for the rpm command to see all files from the package: rpm -ql mysql-3.23.58-1 ayion [EMAIL PROTECTED] wrote: dear sir good wishes. i am using enterprise redhat linux 3. mine rpm version of mysql is -3.23.58-1 and it was installed by the time of linux installation. when i use if the rpm is installed or not by using the comman rpm -qa | grep mysql it shows mysql-3.23.58-1 libdbi-dbd-mysql-0.6.5-6 mysql-devel-3.23.58-1 now i am sure that mysql is installed. but when i run whereis mysql result is : /usr/lib/mysql usr/bin/mysql /usr/include/mysql /usr/share/mysql /usr/share/mysql/man/mnn1/mysql.1.gz my .cnf file is under /etc and its contents are look like this [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysql.log pid-file=/var/run/mysqld/mysqld.pid when i run: service mysqld start it shows unrecongnise service how can i run my sql in my machine? if you describe step by step procedure to run mysql i will be greatful. thanks [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: one long lived connection or one connection per query
Hello. queries will likely be spaced out by hours or days (idle disconnect?) How long does it take to establish a new connection? If the time is large enough, may be you should make a temporary connection and close it after timeout. If you deside to use a persistent connection, don't forget about xxx_timeout variables. Tommy McNeely [EMAIL PROTECTED] wrote: (REPOST: I never got my original post, or any answers, so I am reposting, assuming it was lost in the mail) Hi, I apologize in advance, I am sure this question has been asked dozens of times, but my searches came up empty. I am building an IRC based application bot (using libmysql) .. that will take commands from users (!mybugs, !mybugs KEY !newbugs, !bug ID, etc), do SQL queries and of course dump formatted results back to the channel. Currently its setup to open a single DB connection at initialization and use that connection over and over... would it be better or worse to have it open/close a connection for each command in the way a web app would? Some things to keep in mind :) - IRC server/services bot is half way across the US from the mySQL server (connection reliability?) - queries will likely be spaced out by hours or days (idle disconnect?) - when one person does a query, its almost assured that there will be 10 more within 10 seconds, its contagious or something :) - I would *like* to do SSL (still studying how to do that), so that will most certainly increase the mysql_real_connect() time? Does the client library maintain a pool of connections or something like that? Thanks in advance, Tommy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with the stopword file in 4.1.9-Max
Hello.
Full-text search successfully works on my 4.1.9-max instance:
mysql select version();
+---+
| version() |
+---+
| 4.1.9-max-log |
+---+
CREATE TABLE `ft` (
`a` text,
FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
$cat ftopfile.txt
gleb
clear
mysql select * from ft;
+--+
| a|
+--+
| gleb manual |
| clear manual |
| cooler manual|
| af01e9f7ab799a2050c709ad68c3beee |
| 3aa4c2ebf3996f2754de213b527c4275 |
| 30f09732b77f417ae743a127c172a1ad |
| 30f09732b77f417ae743a127c172a1ad |
| 30f09732b77f417ae743a127c172a1ad |
| 02bb9a3b6b5c27263270116d6700ed3b |
| 02bb9a3b6b5c27263270116d6700ed3b |
| 02bb9a3b6b5c27263270116d6700ed3b |
| 02bb9a3b6b5c27263270116d6700ed3b |
| 8ca42418d6c5902249c15e24c3ca2830 |
mysql select a from ft where match(a) against('cooler');
+---+
| a |
+---+
| cooler manual |
+---+
mysql select a from ft where match(a) against('manual');
+-+
| a |
+-+
| gleb manual |
| clear manual|
| cooler manual |
+-+
mysql select a from ft where match(a) against('clear');
Empty set (0.00 sec)
( 'clear' - is in the stop file).
mysql show variables like 'ft_stop%'\G ;
*** 1. row ***
Variable_name: ft_stopword_file
Value:
/home/gleb/mysqls/mysql-debug-4.1.10-pc-linux-gnu-i686/ftopfile.txt
It works also with cyrillic words. My system (Fedora Core 3) uses UTF-8 locale.
Have you tried my format of the stopword file?
N S [EMAIL PROTECTED] wrote:
Hi
I set ft_min_len_word = 3 and ft_stopword_file =
/etc/stopword.cnf in my.cnf file under the [mysqld]
section
I then restart the server and rebuild the indexes with
REPAIR TABLE ... QUICK
The ft_min_len_word=3 seems to work: I get the
expected results when quering for 3-letter words
Unfortunately, that is not the same with the stopwords
The default character set of the server is utf8. All
data in the tables are utf8 as well. So, I thought
that the stopword file should be in utf8 as well. If
that is wrong please say so. Now the syntax of that
file: I've tried one word each row, I've tried
double-quotes around words, I've tried comma-separated
as well as double-quotes and commas together, also
tried space-separated. I've tried anything I could
think of. None of those seem to work. When I query for
one of those stopwords, I get results :(
After each change to the stopword file, I always
restart server, rebuild indexes etc.
I've run out of ideas. Any help would be appreciated.
Thx in advance, Nick
__
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SUM and DECIMAL field
Hell. I think you should read comments at: http://bugs.mysql.com/bug.php?id=1388 They are related to the ODBC connector, but may be it will help you to solve problems with Connector/J. LAFONTAINE Julien - LYO [EMAIL PROTECTED] wrote: Hi everyone, I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9. Now I have a problem when using the SUM operator on DECIMAL field. The value returned by the SUM operator when used with DECIMAL field has a coma (,) as decimal separator while it used to be have a dot (.) . If I query my table to display the DECIMAL fields (SELECT * FROM ...) I get a dot as decimal separator as expected. This doesn't look like a big issue but it prevents Connector/J from retieving the data properly. Connector/J can't parse the value of the field as it's expecting a dot as decimal separator. Here is the stack trace : java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( . () . SUM(AMFTPF)()). at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493) I'm using Connector/J 3.0.16. One last thing : this seems to happen only on AIX. I have tried on Linux and Windows XP and everything works as expected. Is there something wrong with some of my database parameters or is this a bug ? Please let me know if someone is interested by a testcase. Reagrds, Julien LAFONTAINE -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hello. Sergei Golubchik said that we can't change the value of the blocksize of a key (it is chosen in mi_create.c) and there is no workaround with this LOAD INDEX problem. HMax [EMAIL PROTECTED] wrote: Hello there. OK I'll paste the results of commands you asked right after my reply, because we found out where the problem comes from. The myisamchk command showed that the index on the VarChar has a block size of 2048 instead of 1024. However, when I turn this index to a FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO CACHE works. Now this is a problem because our huge table needs both our FULLTEXT indexes and some on VARCHAR fields too. At least we know where it comes from. Now, is there a fast solution ? We were waiting for this bug correction to study a release date for our application :/ Thank you, and here is the results : SHOW CREATE TABLE=20 `tbltest`; CREATE TABLE `tbltest` ( `testid` int(10) unsigned NOT NULL auto_increment, `testvalue` varchar(100) NOT NULL default '', PRIMARY KEY (`testid`), KEY `BOB` (`testvalue`) ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 SHOW VARIABLES; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | D:\mysql\4.1\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| utf8| | character_set_connection| utf8| | character_set_database | utf8| | character_set_results | utf8| | character_set_server| utf8| | character_set_system| utf8| | character_sets_dir | D:\mysql\4.1\share\charsets/| | collation_connection| utf8_general_ci | | collation_database | utf8_general_ci | | collation_server| utf8_general_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | D:\mysql\4.1\Data\ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024| | have_archive| NO | | have_bdb| NO | | have_compress | YES | | have_crypt | NO | | have_csv| NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED| | have_isam | NO | | have_ndbcluster | NO | | have_openssl| NO | | have_query_cache| YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink| YES | | init_connect| | | init_file |
Merging / Moving InnoDB Databases
Hi, Server A - Multiple InnoDB databases Server B - Replication of Server A Server C - Other InnoDB databases I need to be able to move Server C's databases onto Server A and continue to replicate (with new databases) to Server B. Can I: 1. Take down Server C, use ibbackup to back up the innodb data files and frm data 2. Taken down Servers A B 3. Put on Servers A B the data files from Server C, but under different InnoDB names (ibdata3 for example) 4. Start Servers A B and watch all the databases fly happily? Is this the correct procedure? Thanks, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Approximately when is a beta with useable stored procedures
Hello. I have downloaded both 5.0.0 and latest 5.0.1 binary snapshot but I You may use 5.0.2 - a lot of bugs were fixed during a half year development period between the releases. Bereczki Gabor [EMAIL PROTECTED] wrote: I have downloaded both 5.0.0 and latest 5.0.1 binary snapshot but I found that the stored procedures are very unstable. E.g. when I fetch empty string into variables, the server crashes. It seems like 5.x development has been ongoing for more than a year. Can somebody give me a rough estimate about when a beta is available with useable stored procedures? If not what other options do I have for fast cursor processing? (I dont want to code in C.) Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory table questions
Hello. You may use 'CREATE TABLE ... SELECT' statement: create table t3 type=heap select * from t2; But be careful, as it doesn't create indexes automatically, unless you specify them manually in your statement. See: http://dev.mysql.com/doc/mysql/en/create-table.html You may put your statements into the file, and tell mysqld to read it at every startup with --init-file command line option. See: http://dev.mysql.com/doc/mysql/en/server-options.html We are building two copies of our commonly used tables. When we have to write something we write it Both tables: TABLE A TABLE B MEMORY table When we read to run our script we only read from he MEMORY TABLE. My question is how do I tell TABLE B to clone TABLE A after a reboot automatically. That is actually part of the table build itself right? what does that SQL look like? Thanks Donny Lairson [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with LOAD DATA INFILE
On 2/21/2005 4:21 AM Heikki Tuuri wrote: John, please print a detailed description of the latest foreign key error with SHOW INNODB STATUS and post it here. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ Thank you! I won't bother to post the results because the command showed me the problem. The problem appears to be that the parser doesn't skip whitespace after separators as I assumed it did. The result is that my key was Administrator, but it was looking for Administrator. Removing all whitespace allowed it to work correctly. To me, this parsing is either a bug or a feature that should be documented in red letters. But it isn't a InnoDB issue. Thanks again for your help by pointing out this very useful command. - Original Message - From: John Swartzentruber [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 20, 2005 11:54 PM Subject: Problems with LOAD DATA INFILE I am new to SQL and to MySQL, but am working with it on a project for a graduate Database system course. I'm running MySQL 4.1.10 under Windows 2000. I am trying to load a table from a text file. The table in question has a foreign key. The table that it references contains data. The problem is that when I load the data using LOAD DATA LOCAL INFILE, I get this error: ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails That seems clear enough except that the foreign key constraint should *not* fail. I've verified that the value exists in the other table. What is really strange is that when I tried inserting the data directly using INSERT INTO, it works. In this particular case, I only need to load a handful of records, so using INSERT INTO is an option, but I would really like to figure out what isn't working. Here is my table definition: create table Subscriber ( UserIDint auto_increment, Name varchar(50) not null, Password varchar(8) not null, EmailAddress varchar(50), SGroupNamevarchar(50), primary key (UserId), foreign key (SGroupName) references AccessGroup(GroupName) on update cascade ) ENGINE=INNODB; Here is how I am attempting to load it: LOAD DATA LOCAL INFILE 'c:/Documents and Settings/john/My Documents/Grad School/Project/LoadData/Subscriber.csv' REPLACE INTO TABLE Subscriber FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Name, Password, EmailAddress, SGroupName); Here are the first two lines of my data file: Name, Password, EmailAddress, SGroupName John Swartzentruber, 8490JTTT, [EMAIL PROTECTED], Administrator And here is what *does* work: insert into Subscriber (Name, Password, EmailAddress, SGroupName) values (John Swartzentruber, 8490JTTT, [EMAIL PROTECTED], Administrator); Can anyone see what my problem is? I really appreciate any assistance you can provide. I hope this is the appropriate group for newbie questions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a query please
Hi, I am having trouble with the following query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID OR U.User_ID = 101) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... Any help here would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: can 5.0.2 be built for Solaris 8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have tried to compile mysql, and got some linking errors. Any chance a binary for Solaris 8 can be made available? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGeAIikQgpVn8xrARAnzUAJ4hAQDnl5uE1O6b2rq6ejRkWS3DgwCePXxV trqHThwInfJg8BuPQC+uk2g= =7eMe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
- Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 21, 2005 7:28 AM Subject: Help with a query please Hi, I am having trouble with the following query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID OR U.User_ID = 101) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... What's the problem then? It seems to be doing exactly what you want to do. Can you clarify how the actual result differs from the expected result? So far, they sound the same Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
From: Rhino [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 08:22:29 -0500 - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 21, 2005 7:28 AM Subject: Help with a query please Hi, I am having trouble with the following query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID OR U.User_ID = 101) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... What's the problem then? It seems to be doing exactly what you want to do. Can you clarify how the actual result differs from the expected result? So far, they sound the same Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 Hi Rhino, Thanks for your reply, I would like the query to retun one instance of user 101 rather than 15! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
what about your query returning all the users UNION your query returning user 101? SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname UNION SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U WHERE (U.User_ID = 101) not sure to understand what you want to do with the join if you anyway want to retrieve user 101 and select only the parameters from the first table From: shaun thornburgh [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 13:24:55 + From: Rhino [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 08:22:29 -0500 - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 21, 2005 7:28 AM Subject: Help with a query please Hi, I am having trouble with the following query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID OR U.User_ID = 101) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... What's the problem then? It seems to be doing exactly what you want to do. Can you clarify how the actual result differs from the expected result? So far, they sound the same Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 Hi Rhino, Thanks for your reply, I would like the query to retun one instance of user 101 rather than 15! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
shaun thornburgh schrieb: Thanks for your reply, I would like the query to retun one instance of user 101 rather than 15! SELECT DISTINCT ... Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to make so I only need to specify the id once..
Hello, I have a small problem that is probably easy to fix but it is to advanced for me as a newbe. In my sql-query (below) have I the ID specified twice, I wonder if there is a easy way to solve it so I only need to write my ID once in the query? SELECT (SELECT COUNT(*) FROM subs WHERE id=1) + (SELECT COUNT(*) FROM subs_erased WHERE id=1); Thanks in advance! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
It's not precisely correct.
When time difference is less than 7, the time is calcualted wrong
end_time 2005-01-10 17:53:33
end_time 2005-01-04 16:44:57
Result: days 6
Result: bussiness_days 6
On Sat, 19 Feb 2005 09:50:06 -0500, Mike Rains [EMAIL PROTECTED] wrote:
On Sat, 19 Feb 2005 14:01:05 +, Jerry Swanson [EMAIL PROTECTED] wrote:
I have two dates (start_date, end_date). Datediff() function returns
difference in days.
I need the difference but not including Satuday and Sunday.
Any ideas?
C:\Program Files\MySQL\MySQL Server 4.1\binmysql -utest -ptest test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.8-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql CREATE TABLE `DateDiffs` (
- start_date DATETIME,
- end_date DATETIME
- );
Query OK, 0 rows affected (0.15 sec)
mysql INSERT INTO DateDiffs
- (start_date, end_date)
- VALUES
- ('2005-02-14 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-07 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-04 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-31 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-24 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-21 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-17 00:00:00', '2005-02-18 00:00:00');
Query OK, 9 rows affected (0.06 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql SELECT
-start_date,
-end_date,
-datediff(end_date, start_date)
-AS dd1,
-datediff(end_date, start_date) -
floor(datediff(end_date, start_date) / 7) * 2
-AS dd2
- FROM DateDiffs
- ORDER BY start_date;
+-+-+--+--+
| start_date | end_date| dd1 | dd2 |
+-+-+--+--+
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 |9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 |4 |4 |
+-+-+--+--+
9 rows in set (0.00 sec)
mysql DROP TABLE DateDiffs;
Query OK, 0 rows affected (0.19 sec)
mysql exit
The column dd1 contains the unaltered DATEDIFF() which includes the
Saturdays and Sundays, while the dd2 column contains the number of
business days omitting the weekend days.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make so I only need to specify the id once..
Hi, why not try: SELECT COUNT(s.Id)+COUNT(se.Id) FROM subs s INNER JOIN subs_erased se ON s.Id=se.Id WHERE s.Id=1; /Johan Joppe A wrote: Hello, I have a small problem that is probably easy to fix but it is to advanced for me as a newbe. In my sql-query (below) have I the ID specified twice, I wonder if there is a easy way to solve it so I only need to write my ID once in the query? SELECT (SELECT COUNT(*) FROM subs WHERE id=1) + (SELECT COUNT(*) FROM subs_erased WHERE id=1); Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make so I only need to specify the id once..
Hi, why not try: SELECT COUNT(s.Id)+COUNT(se.Id) FROM subs s INNER JOIN subs_erased se ON s.Id=se.Id WHERE s.Id=1; /Johan This won't return the same result if there's no entries in subs_erased for the ID = 1. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Joppe A wrote: Hello, I have a small problem that is probably easy to fix but it is to advanced for me as a newbe. In my sql-query (below) have I the ID specified twice, I wonder if there is a easy way to solve it so I only need to write my ID once in the query? SELECT (SELECT COUNT(*) FROM subs WHERE id=1) + (SELECT COUNT(*) FROM subs_erased WHERE id=1); Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ? How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! Thx for your advices HMax On Fri, 18 Feb 2005 18:01:29 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Sergei Golubchik said that we can't change the value of the blocksize of a key (it is chosen in mi_create.c) and there is no workaround with this LOAD INDEX problem. HMax [EMAIL PROTECTED] wrote: Hello there. OK I'll paste the results of commands you asked right after my reply, because we found out where the problem comes from. The myisamchk command showed that the index on the VarChar has a block size of 2048 instead of 1024. However, when I turn this index to a FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO CACHE works. Now this is a problem because our huge table needs both our FULLTEXT indexes and some on VARCHAR fields too. At least we know where it comes from. Now, is there a fast solution ? We were waiting for this bug correction to study a release date for our application :/ Thank you, and here is the results : SHOW CREATE TABLE=20 `tbltest`; CREATE TABLE `tbltest` ( `testid` int(10) unsigned NOT NULL auto_increment, `testvalue` varchar(100) NOT NULL default '', PRIMARY KEY (`testid`), KEY `BOB` (`testvalue`) ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 SHOW VARIABLES; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | D:\mysql\4.1\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| utf8| | character_set_connection| utf8| | character_set_database | utf8| | character_set_results | utf8| | character_set_server| utf8| | character_set_system| utf8| | character_sets_dir | D:\mysql\4.1\share\charsets/| | collation_connection| utf8_general_ci | | collation_database | utf8_general_ci | | collation_server| utf8_general_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | D:\mysql\4.1\Data\ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024| | have_archive| NO | | have_bdb| NO | | have_compress | YES | |
Re: Problem with SUM and DECIMAL field
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 LAFONTAINE Julien - LYO wrote: Hi everyone, I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9. Now I have a problem when using the SUM operator on DECIMAL field. The value returned by the SUM operator when used with DECIMAL field has a coma (,) as decimal separator while it used to be have a dot (.) . If I query my table to display the DECIMAL fields (SELECT * FROM ...) I get a dot as decimal separator as expected. This doesn't look like a big issue but it prevents Connector/J from retieving the data properly. Connector/J can't parse the value of the field as it's expecting a dot as decimal separator. Here is the stack trace : java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( . () . SUM(AMFTPF)()). at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493) I'm using Connector/J 3.0.16. One last thing : this seems to happen only on AIX. I have tried on Linux and Windows XP and everything works as expected. Is there something wrong with some of my database parameters or is this a bug ? Please let me know if someone is interested by a testcase. Reagrds, Julien LAFONTAINE Julien, Connector/J uses the locale of the _client_ computer to parse numbers. It seems your AIX box and your MySQL compile are 'sensitive' to the locale, and thus returning numbers formatted different than your client expects them. If you use the 'mysql' client, does it show decimal numbers with comma separators as well? Since MySQL doesn't actually have configurable locale, and doesn't expose this information in any status variable, you will have to set your client to the same locale as your server to get these numbers to parse, see: http://java.sun.com/j2se/1.5.0/docs/guide/intl/locale.doc.html and http://java.sun.com/j2se/corejava/intl/reference/faqs/index.html#set-default-locale -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: http://www.mysqluc.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGf0ctvXNTca6JD8RAvBCAKCedPifB6OwMK0KWmqtDSo71dLmKwCgi99W 1NQrbWDzt3BrP4YcySewcFI= =xry/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with an SQL query
Hi, I need a bit of help with a mySQL query. I have a list of users in a text file called 'users': u655354 u687994 u696974 u728141 .. .. and I need to check the last date each user logged on to the proxy in the last 3 months. In my database, there is a table for the users: mysql desc user_table; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | MUL | NULL| auto_increment | | uid | varchar(10) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.00 sec) and a table (tYYMMDD) for each days log: mysql desc t20041209; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | MUL | 0 | | | urlid| int(10) unsigned | | MUL | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | +--+--+--+-+-+---+ 11 rows in set (0.00 sec) The time column here gives the actual time the user logged on, but I would be happy just to know the date (which I could get from the table name if the user's uid was present in there). Could anyone help me to write an SQL query to do this please? Many thanks. Please let me know if you need anymore information. JS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +-+-+---+ | start_date | end_date| business_days | +-+-+---+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make so I only need to specify the id once..
Hi Martijn, yes of course you're right but SELECT COUNT(s.Id)+ (SELECT COUNT(se.Id) FROM subs_erased se WHERE s.Id=se.Id) FROM subs s WHERE s.Id=1 might work, at least in 4.1.x. I did test it with 4.1.9. /Johan Martijn Tonies wrote: Hi, why not try: SELECT COUNT(s.Id)+COUNT(se.Id) FROM subs s INNER JOIN subs_erased se ON s.Id=se.Id WHERE s.Id=1; /Johan This won't return the same result if there's no entries in subs_erased for the ID = 1. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Joppe A wrote: Hello, I have a small problem that is probably easy to fix but it is to advanced for me as a newbe. In my sql-query (below) have I the ID specified twice, I wonder if there is a easy way to solve it so I only need to write my ID once in the query? SELECT (SELECT COUNT(*) FROM subs WHERE id=1) + (SELECT COUNT(*) FROM subs_erased WHERE id=1); Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Postcode Proximity Search?
Hi I'm just starting out with PHP and MySQL My database has a postcode column and I want to be able to run a search (using a part of full postcode) to return results in order of proximity within a given range. If anyone could point to a decent tutorial / resource, or give me any advice or hints I would greatly appreciate it. Pete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with an SQL query
J S wrote: | Hi, | | I need a bit of help with a mySQL query. I have a list of users in a | text file called 'users': | | u655354 | u687994 | u696974 | u728141 | .. | .. | | and I need to check the last date each user logged on to the proxy in | the last 3 months. | | In my database, there is a table for the users: | | mysql desc user_table; | +---+--+--+-+-++ | | Field | Type | Null | Key | Default | Extra | | +---+--+--+-+-++ | | ID| int(10) unsigned | | MUL | NULL| auto_increment | | | uid | varchar(10) | | PRI | || | +---+--+--+-+-++ | 2 rows in set (0.00 sec) | | and a table (tYYMMDD) for each days log: | | mysql desc t20041209; | +--+--+--+-+-+---+ | | Field| Type | Null | Key | Default | Extra | | +--+--+--+-+-+---+ | | uid | int(10) unsigned | | MUL | 0 | | | | time | timestamp(14)| YES | | NULL| | | | ip | int(10) unsigned | | MUL | 0 | | | | urlid| int(10) unsigned | | MUL | 0 | | | | timetaken| smallint(5) unsigned | YES | | 0 | | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | | method_ID| tinyint(3) unsigned | | | 0 | | | | action_ID| tinyint(3) unsigned | | | 0 | | | | virus_ID | tinyint(3) unsigned | | | 0 | | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | | +--+--+--+-+-+---+ | 11 rows in set (0.00 sec) | | The time column here gives the actual time the user logged on, but I | would be happy just to know the date (which I could get from the table | name if the user's uid was present in there). | | Could anyone help me to write an SQL query to do this please? | | Many thanks. Please let me know if you need anymore information. Select u.uid, max(l.time) as lastLog ~ From user_table u join t20041209 l on u.uid = l.uid ~ Group by uid; Thanks Mike. I need to run this query over 3 months though. Is there a quick way to write: t20041101 union t20041102 union t20041103 union ... t20050125 union t20050126 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hi! On Feb 21, HMax wrote: So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ? No. FULLTEXT indexes now have small block size (1024) so they should load ok. Only long varchar indexes will be a problem (but not 'ever', see below :) How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. Right, it's in the TODO. Here's the problem: LOAD INDEX reads the complete MYI file sequentially, block after a block, and loads them in cache. If blocks would have different sizes it would be not possible, because block header does not store block size. Loading only a selected index does not work either, because block header does not store what index it belongs to. The only solution would be to traverse the index tree from the root - but it'd be slow, because it implies random reads from the index file :( Instead, we plan to store index number in every block, but it means incompatible change in MYI file format, so it's not for 4.1 (and not even for 5.0 which is almost frozen now). What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. See above, regular btree traversal is not a problem. Sequential MYI file access is. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! It's fixed in 4.1.8. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to make question that check...
The following statement helped Roger Backlund me with a few days ago, It works great but now I need to make it more advance and I don#t get any where to find the right solution.. SELECT COUNT(created) from SUBSCRIBER where date_format( date_sub(now(), interval 1 day), '%Y%m%d%H%i%s') = created; the db-table (table name is SUBSCRIBER) have the following columns as follows: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 076 [EMAIL PROTECTED] 20050215134500 20050215134556 The other table is called SUBSCRIPTIONS and look like following ID sys_id 001 1 063 2 076 3 The data type of the columns are: ID =VARCHAR(14) email =VARCHAR(255) created =VARCHAR(14) updated =VARCHAR(14) sys_id =VARCHAR(14) What I want to do is to be able to check the last hours new rows for a specific sys_id , (not list) only to get out how many rows that have been added... Thanks! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Thank you for your answer Sergei, It's all clear now, and I'm glad to know where the problem comes from. Now if I understand correctly, my only solution is to manage to create indexes in my tables that ALL have the same block size (1024). This would mean reducing the size of the indexes on my Varchar fields, which I think I can. What is the max characters I should use when indexing my Varchar so that block size are 1024 ? (if possible of course). I have no idea how to calculate this. And is there absolutly no way to force 1024 block size even for varchar ? Thx again for your enlightment On Mon, 21 Feb 2005 17:41:40 +0100, Sergei Golubchik [EMAIL PROTECTED] wrote: Hi! On Feb 21, HMax wrote: So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ? No. FULLTEXT indexes now have small block size (1024) so they should load ok. Only long varchar indexes will be a problem (but not 'ever', see below :) How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. Right, it's in the TODO. Here's the problem: LOAD INDEX reads the complete MYI file sequentially, block after a block, and loads them in cache. If blocks would have different sizes it would be not possible, because block header does not store block size. Loading only a selected index does not work either, because block header does not store what index it belongs to. The only solution would be to traverse the index tree from the root - but it'd be slow, because it implies random reads from the index file :( Instead, we plan to store index number in every block, but it means incompatible change in MYI file format, so it's not for 4.1 (and not even for 5.0 which is almost frozen now). What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. See above, regular btree traversal is not a problem. Sequential MYI file access is. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! It's fixed in 4.1.8. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with an SQL query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 J S wrote: | | | J S wrote: | | Hi, | | | | I need a bit of help with a mySQL query. I have a list of users in a | | text file called 'users': | | | | u655354 | | u687994 | | u696974 | | u728141 | | .. | | .. | | | | and I need to check the last date each user logged on to the proxy in | | the last 3 months. | | | | In my database, there is a table for the users: | | | | mysql desc user_table; | | +---+--+--+-+-++ | | | Field | Type | Null | Key | Default | Extra | | | +---+--+--+-+-++ | | | ID| int(10) unsigned | | MUL | NULL| auto_increment | | | | uid | varchar(10) | | PRI | || | | +---+--+--+-+-++ | | 2 rows in set (0.00 sec) | | | | and a table (tYYMMDD) for each days log: | | | | mysql desc t20041209; | | +--+--+--+-+-+---+ | | | Field| Type | Null | Key | Default | Extra | | | +--+--+--+-+-+---+ | | | uid | int(10) unsigned | | MUL | 0 | | | | | time | timestamp(14)| YES | | NULL| | | | | ip | int(10) unsigned | | MUL | 0 | | | | | urlid| int(10) unsigned | | MUL | 0 | | | | | timetaken| smallint(5) unsigned | YES | | 0 | | | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | | | method_ID| tinyint(3) unsigned | | | 0 | | | | | action_ID| tinyint(3) unsigned | | | 0 | | | | | virus_ID | tinyint(3) unsigned | | | 0 | | | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | | | +--+--+--+-+-+---+ | | 11 rows in set (0.00 sec) | | | | The time column here gives the actual time the user logged on, but I | | would be happy just to know the date (which I could get from the table | | name if the user's uid was present in there). | | | | Could anyone help me to write an SQL query to do this please? | | | | Many thanks. Please let me know if you need anymore information. | | Select u.uid, max(l.time) as lastLog | ~ From user_table u join t20041209 l on u.uid = l.uid | ~ Group by uid; | | | Thanks Mike. I need to run this query over 3 months though. Is there a | quick way to write: | | t20041101 union t20041102 union t20041103 union ... t20050125 union | t20050126 Not that I know of. Why are you using a different table per day - as opposed to a single log table with a field containing the day? I'm sure there's a reason for the current structure, but having a logday field in a single log table would make this kind of query much easier. A simple BETWEEN clause would suffice if it was in a single table. As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL can handle the query - and that you can type all of them without error. Note that if you use a UNION query as you suggest above, you will get the last login FOR EACH DAY - not the overall last loging. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGhfOjeziQOokQnARAvSUAJ4zyHmYa95o+0eZ2zs//S24n0kyqQCeO6M2 UzELKfj6hZ14bp+NLLj+McQ= =NZBB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can 5.0.2 be built for Solaris 8
James Black wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have tried to compile mysql, and got some linking errors. Any chance a binary for Solaris 8 can be made available? I cannot answer your question, but I am currently building 5.0.2 on Solaris 8 sparc and the build works. It does require some dependencies to be installed from www.blastwave.org. I build sparcv8, sparcv8plus+vis and sparcv9 daemon and shared libraries. The sparcv8 portion has finished. Then, I need to finish sparcv8plus+vis and sparcv9 and package it and run it through the test suite. If you are interested, just say so, but note that it is not a supported blastwave.org package. Our official released package is 4.1.9, with 4.1.10 due this week. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unable to start mysql
dear sir good wishes. in my previous mail i explained mine mysql regarding problem. here is my mysqlbug file. i hope i will help you to solve my mysql regarding problem. help me to run mysql. thanks. ayion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GPL license issue
It appears that the requirement #2 on the MySQL license page (http://www.mysql.com/company/legal/licensing/commercial-license.html) is not consistent with the GPL, which does not impose restrictions on non-linked programs. However use of the MySQL client JDBC or ODBC driver would appear to require either GPL'ing your code or your customers buying the commercial license, so in essence it is the same. Comments? Would use of a JDBC type-3 intermediate server be restricted as well, if only the intermediate server was GPL'ed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to write this query?
This works if you don't care about holidays. If you do the only solution that I have seen that works is to create a business day table. Ours is shown below. You have to hand construct the calendar by removing weekends and holidays for the specific entity. This calendar forces a non business day DATE to the next business day. Date arithmetic then becomes simple including FIRST MONDAY OF THE MONTH in the US where many holidays fall on Monday. clnd_Day is the relative business day since 2000-01-01 clnd_Week is the relative business week since 2000-01-01 mysql show create table calendar; +--+- - | Table| Create Table +--+- - | calendar | CREATE TABLE `calendar` ( `clnd_Day` smallint(5) unsigned NOT NULL default '0', `clnd_Date` date NOT NULL default '-00-00', `clnd_Week_Day_Txt` char(9) default NULL, `clnd_Week_Day_Num` tinyint(3) unsigned default NULL, `clnd_Char_Date` char(12) default NULL, `clnd_Week` smallint(8) unsigned default NULL, `clnd_Real_Date` char(10) default NULL, PRIMARY KEY (`clnd_Date`), UNIQUE KEY `clnd_Real_Date_IDX` (`clnd_Real_Date`), KEY `clnd_Day_IDX` (`clnd_Day`), KEY `clnd_Char_Date` (`clnd_Char_Date`) ) TYPE=InnoDB | mysql select * from calendar limit 10; +--++---+---+--- -+---++ | clnd_Day | clnd_Date | clnd_Week_Day_Txt | clnd_Week_Day_Num | clnd_Char_Date | clnd_Week | clnd_Real_Date | +--++---+---+--- -+---++ |1 | 2000-01-01 | Monday| 2 | 01/03/2000 | 1 | 01/01/2000 | |1 | 2000-01-02 | Monday| 2 | 01/03/2000 | 1 | 01/02/2000 | |1 | 2000-01-03 | Monday| 2 | 01/03/2000 | 1 | 01/03/2000 | |2 | 2000-01-04 | Tuesday | 3 | 01/04/2000 | 1 | 01/04/2000 | |3 | 2000-01-05 | Wednesday | 4 | 01/05/2000 | 1 | 01/05/2000 | |4 | 2000-01-06 | Thursday | 5 | 01/06/2000 | 1 | 01/06/2000 | |5 | 2000-01-07 | Friday| 6 | 01/07/2000 | 1 | 01/07/2000 | |6 | 2000-01-08 | Monday| 2 | 01/10/2000 | 2 | 01/08/2000 | |6 | 2000-01-09 | Monday| 2 | 01/10/2000 | 2 | 01/09/2000 | |6 | 2000-01-10 | Monday| 2 | 01/10/2000 | 2 | 01/10/2000 | +--++---+---+--- -+---++ 10 rows in set (0.00 sec) -Original Message- From: Mike Rains [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 9:33 AM To: mysql@lists.mysql.com Subject: Re: how to write this query? SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +-+-+---+ | start_date | end_date| business_days | +-+-+---+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
AM Thomas wrote: Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): You've removed necessary conditions on the JOINs, so you are getting lots of extra rows. Furthermore, the logic isn't right, so I think this query will, in all likelihood, retrieve incorrect rows. I'll explain. For a given resource id, select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) (adding the WHERE clause below), this retrieves every row in copy 0 with the right subject and grade, *regardless of goal* join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) and pairs it with every row in copy 1 with GoalNumber=1, *regardless of Subject or Grade* join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) and pairs it with every row in copy 2 with GoalNumber=2, *regardless of Subject or Grade* join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) and pairs it with every row in copy 3 with GoalNumber=3, *regardless of Subject or Grade* join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) and pairs it with every row in copy 4 with GoalNumber=4, *regardless of Subject or Grade* where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; and finally, we pick one of those many rows to display (the effect of the GROUP BY r.id). Do you see why that is both more than and different from what you want? Consider a resource with the following rows in resource_goals: ++---+++--+ | ResourceID | Grade | Subject| GoalNumber | NumericGrade | ++---+++--+ | 14 | 4th | Social_Studies | 7 |4 | | 14 | 1st | English| 1 |1 | | 14 | 2nd | English| 2 |2 | | 14 | 3rd | English| 3 |3 | | 14 | 5th | History| 4 |5 | ++---+++--+ ResourceID 14 would be returned by your query, but isn't what you want. I'd guess you haven't come across a case like this because you have few, if any, cross-subject resources, but I assume they are a possibility, since you have Subject part of the resource_goals table, rather than part of the resources table. The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. If we join each row in resources to a single row in each copy of resource_goals using an index, this should be reasonably fast, but I expect you are getting multiple matching rows in each copy, as it stands now. The total resulting rows per id is the product of the matches in each copy. If just 3 rows match your current conditions per copy, that would be 3^5 = 243 rows per resource id, where we expect only 1! In other words, I expect some of the slow down is due to the overhead of retrieving many times the number of desired rows. The rest is probably lack of a suitable index. As I understand it, you are looking for a resource for 4th grade Social Studies which meets goals 1 through 4. In terms of your tables, that corresponds to having 4 rows in resources_goals, *all* of which have grade=4th and Subject='Social_Studies'. That is, we need to look in *4* copies of resources_goals (not 5). For a given resource id, we want exactly one row from each copy, namely, the row with the correct resource id, correct subject, correct grade, and desired goal number. I think this should do: SELECT r.id FROM resources as r JOIN resource_goals as g1 ON r.id = g1.ResourceID AND g1.Subject = 'Social_Studies' AND g1.Grade = '4th' AND g1.GoalNumber = 1 JOIN resource_goals as g2 ON r.id = g2.ResourceID AND g2.Subject = 'Social_Studies' AND g2.Grade = '4th' AND g2.GoalNumber = 2 JOIN resource_goals as g3 ON r.id = g3.ResourceID AND g3.Subject = 'Social_Studies' AND g3.Grade = '4th' AND g3.GoalNumber = 3 JOIN resource_goals as g4 ON r.id = g4.ResourceID AND g4.Subject = 'Social_Studies' AND g4.Grade = '4th' AND g4.GoalNumber = 4; That should return 1 row per resource, so long as there are no duplicates rows in
[ERROR] Got error 127 when reading table
I had a previously stable 4.1.8-standard MYSQL install on OS X 10.3.x Server using the binaries supplied by MySQL. Recently I have had multiple corruption issues with a database on multiple tables. I keep getting [ERROR] Got error 127 when reading table when trying to write to an effected table. I went so far as to delete entire tables and recreate them. I could reproduce the error from my default PHP and CLI attempts to write to the tables. The only thing that I recently changed on the server was the location of the data directory. The Mac OS X install / upgrade places the data in '/usr/local/mysql/data' where mysql is a symbolic link to the most recent version installed in /usr/local/. I got tired of manually moving the data directory each time I upgraded so I created a /etc/my.cnf file and specified the new data dir location in /var/mysql/data/. I moved the old data for the last time checked the permissions and ownership and restarted mysql. When I created the new my.cnf file I used the my-medium.cnf default example as detailed below: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock datadir = /var/mysql/data/ #skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout I have changed my my.cnf file to simply: [mysqld] datadir = /var/mysql/data/ to see is one of these new parameters caused the problem Because I had no my.cnf I suspect that this is the problem. I tried some Google and list searches with no meaningful results. Any tips / pointers / suggestions would be great. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hello. doesn't work. Is there really no workaround ? We have for about 1.5Go MySQL will store used blocks in memory and minimize a disk I/O, if your key_buffer_size variable has a sutable value and you have enough RAM. You may use CACHE INDEX to assign a separate cache for your table and get more performance. HMax [EMAIL PROTECTED] wrote: So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ? How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! Thx for your advices HMax On Fri, 18 Feb 2005 18:01:29 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Sergei Golubchik said that we can't change the value of the blocksize of a key (it is chosen in mi_create.c) and there is no workaround with this LOAD INDEX problem. HMax [EMAIL PROTECTED] wrote: Hello there. OK I'll paste the results of commands you asked right after my reply, because we found out where the problem comes from. The myisamchk command showed that the index on the VarChar has a block size of 2048 instead of 1024. However, when I turn this index to a FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO CACHE works. Now this is a problem because our huge table needs both our FULLTEXT indexes and some on VARCHAR fields too. At least we know where it comes from. Now, is there a fast solution ? We were waiting for this bug correction to study a release date for our application :/ Thank you, and here is the results : SHOW CREATE TABLE=20 `tbltest`; CREATE TABLE `tbltest` ( `testid` int(10) unsigned NOT NULL auto_increment, `testvalue` varchar(100) NOT NULL default '', PRIMARY KEY (`testid`), KEY `BOB` (`testvalue`) ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 SHOW VARIABLES; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | D:\mysql\4.1\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| utf8| | character_set_connection| utf8| | character_set_database | utf8| | character_set_results | utf8| | character_set_server| utf8| | character_set_system| utf8| | character_sets_dir | D:\mysql\4.1\share\charsets/| | collation_connection| utf8_general_ci | | collation_database | utf8_general_ci | | collation_server| utf8_general_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | D:\mysql\4.1\Data\ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84
re: can 5.0.2 be built for Solaris 8
Hi James! Am Mo, den 21.02.2005 schrieb James Black um 14:20: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have tried to compile mysql, and got some linking errors. Any chance a binary for Solaris 8 can be made available? Solaris 8 is a supported platform, and we (try to) generate binaries for it. However, with 5.0.2 (which is still an alpha release!) we must have had some problems on it, or else we would have published it. Then, work on other releases was more important. As we are currently working on 5.0.3-alpha (which is also being built on Solaris 8, and has significant improvements), we will not go back to 5.0.2. If compiling from source does not work for you, please wait for 5.0.3 (and then tell us your experiences with it). Regards, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
access to mysql in linux
Dear List members, Having relatively recently moved to GNU/Debian Linux from Windows, I'd like to move my MS Access databases to MySQL. I'm using the latest unstable MySQL version and am all setup with user, passwords, and privileges, so can create and modify databases in MySQL. Searching for tools to migrate my MS Access databases, I've come across mdbtools, with which I can see and export the tables to csv, but the process of getting them into MySQL is tedious and cumbersome. The script mdb-schema in mdbtools can export the tables to other DBMS formats, excluding MySQL unfortunately. Can somebody please suggest other, more convenient, tools (free preferably) to make this transition? Best wishes, -- Sebastian Luque -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hi! On Feb 21, HMax wrote: Thank you for your answer Sergei, It's all clear now, and I'm glad to know where the problem comes from. Now if I understand correctly, my only solution is to manage to create indexes in my tables that ALL have the same block size (1024). This would mean reducing the size of the indexes on my Varchar fields, which I think I can. What is the max characters I should use when indexing my Varchar so that block size are 1024 ? (if possible of course). I have no idea how to calculate this. You'd better try with trial-and-error. myisamchk -dvv shows block size. It's enough to create an empty table and run myisamchk -dvv on it. The formula is in mi_create.c but I would spend more time unrolling all the defines and deriving max varchar langth, that you would do with trial-and-error :) And is there absolutly no way to force 1024 block size even for varchar ? No, but you can make it 2048 for normal indexes. Block length is a multiple of myisam_block_size, so if you set it to 2048, all indexes will use it. (of course it'll be suboptimal for everything but the long varchar keys. And even if you have an index over VARCHAR(255), actual values are usually shorter, right ?) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can 5.0.2 be built for Solaris 8
When will the 5.0.3 binaries be available? Thanks, Fredrick - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: James Black [EMAIL PROTECTED] Sent: Monday, February 21, 2005 11:45 AM Subject: re: can 5.0.2 be built for Solaris 8 Hi James! Am Mo, den 21.02.2005 schrieb James Black um 14:20: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have tried to compile mysql, and got some linking errors. Any chance a binary for Solaris 8 can be made available? Solaris 8 is a supported platform, and we (try to) generate binaries for it. However, with 5.0.2 (which is still an alpha release!) we must have had some problems on it, or else we would have published it. Then, work on other releases was more important. As we are currently working on 5.0.3-alpha (which is also being built on Solaris 8, and has significant improvements), we will not go back to 5.0.2. If compiling from source does not work for you, please wait for 5.0.3 (and then tell us your experiences with it). Regards, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Effect of VARCHAR length?
Hi list, I've just been wondering if the length parameter of a VARCHAR column has any effect on storage efficiency or space requirements. Afaik, VARCHAR columns only store the amount of data actually written into them and require no significantly more memory. So to be especially flexible with a particular table column, could I just define it VARCHAR(255) and face no further disadvantage of it? Thanks for the info... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Unclassified NewsBoard Forum -- newsboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Such bounty of comments! Thank you, Michael Stassen. I see how my logic was faulty, and that a more correct solution would indeed be faster. Thanks for pointing that out and not making me feel like too much of an idiot :) I'll try the revised solution. I am clueful about join making more rows/record, but didn't realize that it would be *that* dramatic. As for the NumericGrade field, I'm basically getting the text grade (e.g. 8th) from someone's MS Access CSV export; I figured I'd leave it in place, and use the numeric grade for sorting. I'd never run into speed issues before, so I was just trying to save myself coding time by sticking with what I had already. I wouldn't need the special lookup tables for grade and subject; if I were to use numeric fields, I could just do a lookup in Perl. Next time, when I try to do this better from the start, I probably will. At the moment, though, I'm trying to avoid changing the Perl code as much as I can (it's much recycled from an earlier project). Will look into indexing - that's probably covered in my old O'Reilly MySQL/mSQL book. I am curious about how much faster numeric field comparisons would be to string field comparisons for the Grade field; Would it make enough of a difference to this problem for me to go mucking with this Perl code? This is a CGI Web app, so it's not lightning fast anyway, but then there are a lot of comparisons going on. Currently it looks like it will have about 300 resource records and about six thousand resource_goal records; I should test this myself... I do have a lot of multi-subject and multi-grade resources; my testing was just not very good, I think. Oh, and I think I see the error of my ways with regard to my TINYTEXT fields. Probably would do well to shrink those. Easy to change, too. Thanks! You've given excellent explanations here. I feel like I should buy your book now, if you have one! I'm wrestling with CSS issues on IE 4.0 for the Mac at the moment, but will return to SQL issues soon, I hope. More later probably, AM On Mon, 21 Feb 2005 14:30:59 -0500, Michael Stassen [EMAIL PROTECTED] wrote: As I understand it, you are looking for a resource for 4th grade Social Studies which meets goals 1 through 4. In terms of your tables, that corresponds to having 4 rows in resources_goals, *all* of which have grade=4th and Subject='Social_Studies'. That is, we need to look in *4* copies of resources_goals (not 5). For a given resource id, we want exactly one row from each copy, namely, the row with the correct resource id, correct subject, correct grade, and desired goal number. I think this should do: SELECT r.id FROM resources as r JOIN resource_goals as g1 ON r.id = g1.ResourceID AND g1.Subject = 'Social_Studies' AND g1.Grade = '4th' AND g1.GoalNumber = 1 JOIN resource_goals as g2 ON r.id = g2.ResourceID AND g2.Subject = 'Social_Studies' AND g2.Grade = '4th' AND g2.GoalNumber = 2 JOIN resource_goals as g3 ON r.id = g3.ResourceID AND g3.Subject = 'Social_Studies' AND g3.Grade = '4th' AND g3.GoalNumber = 3 JOIN resource_goals as g4 ON r.id = g4.ResourceID AND g4.Subject = 'Social_Studies' AND g4.Grade = '4th' AND g4.GoalNumber = 4; -- Virtue of the Small / (919) 929-8687 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbug
Hello, I've been using MySQL for a while on my computer to host a couple of things and since today I've got this error: 2003 - Can't connect to MySQL server on 'localhost' (10061) I have no clue why MySQL isn't even connecting any more, if you have any information why, please email me. Thanks. - Jerome -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbug
At 03:31 PM 2/21/2005, Jerome Campbell wrote: Hello, I've been using MySQL for a while on my computer to host a couple of things and since today I've got this error: 2003 - Can't connect to MySQL server on 'localhost' (10061) I have no clue why MySQL isn't even connecting any more, if you have any information why, please email me. Thanks. - Jerome Jerome, Perhaps the MySQL service is no longer running (in WIndows look at the Services Manager), or the password has changed. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing header file?
Hi all, I'm trying to modify and recompile some UDFs for MySQL 4.1. I'm using the header files from the version 4.1.10 Linux source archive and am getting the following error: /home/sskarupo/mysql-4.1.10/include/my_global.h:70: my_config.h: No such file or directory my_config.h isn't there. It is included in the Windows source archive, though. Can anyone explain this? Thanks in advance, Sergei
Re: help with an SQL query
In article [EMAIL PROTECTED], J S [EMAIL PROTECTED] writes: and I need to check the last date each user logged on to the proxy in the last 3 months. In my database, there is a table for the users: mysql desc user_table; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | MUL | NULL| auto_increment | | uid | varchar(10) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.00 sec) and a table (tYYMMDD) for each days log: mysql desc t20041209; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | MUL | 0 | | | urlid| int(10) unsigned | | MUL | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | +--+--+--+-+-+---+ 11 rows in set (0.00 sec) The time column here gives the actual time the user logged on, but I would be happy just to know the date (which I could get from the table name if the user's uid was present in there). Could anyone help me to write an SQL query to do this please? Create a MERGE table over all the logs for at least the last three months, and then proceed as described in the manual under The Rows Holding the Group-wise Maximum of a Certain Field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relevance with complex fulltext query
I want to have the query cited at the very bottom return a result sorted
by FullText Relevance. I'm thinking I would modify each SELECT
separately. For example maybe the first SELECT clause something like:
SELECT page.* FROM `page` LEFT JOIN `keywords` USING (`page_id`), MATCH
(`keywords`.`keyword_txt`)AGAINST ('$radio_keyword' IN BOOLEAN MODE) as
RELEVANCE FROM `page` WHERE MATCH (`keywords`.`keyword_txt`)AGAINST
('$radio_keyword' IN BOOLEAN MODE)
and then the same idea for the other two (2) SELECT clauses(?) I'm not
sure...here's the MYSQL (and php) query in question. Any help
appreciated:
$query =
SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`)AGAINST
('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` LEFT JOIN `url_pages` USING (`page_id`)
WHERE MATCH (`url_pages`.`page_url`) AGAINST ('$radio_keyword' IN
BOOLEAN MODE);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Mailing List
mySQL Mailing List test -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqlhotcopy on Windows
Is it possible to use mysqlhotcopy on Windows 2000 Server? If so, how? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date: Mon, 21 Feb 2005 16:14:05 +0000 To: mysql@lists.mysql.com mysql@lists.mysql.com mysql@lists.mysql.com From: Peter O'Brien mysql@surreyfilms.co.uk Subject: Postcode Proximity Search? Message-Id: c429bcca97d9448795dc0ff9a62ae801@surreyfi
Date: Mon, 21 Feb 2005 16:14:05 + To: mysql@lists.mysql.com mysql@lists.mysql.com mysql@lists.mysql.com From: Peter O'Brien [EMAIL PROTECTED] Subject: Postcode Proximity Search? Message-Id: [EMAIL PROTECTED] Hi I'm just starting out with PHP and MySQL My database has a postcode column and I want to be able to run a search (using a part of full postcode) to return results in order of proximity within a given range. If anyone could point to a decent tutorial / resource, or give me any advice or hints I would greatly appreciate it. Pete. Sure, you can do that. First, go to the US Census Bureau's website and downloadthe file 'places.zip' (Download places.zip from http://www.census.gov/tiger/tms/gazetteer/places2k.zip).. This file contains the zip codes, latitutude and longitudes of cities in the United States. One thing you should be aware of is that the zip codes in this file do not fully correspond to the US Postal Service codes, but they are darn close. If 'darn close' is not good enough, you can pay the USPS to give you their current zip codes. The USCB files are free to all, though. Next, study the formula at http://www.meridianworlddata.com/Distance-Calculation.asp; (this site is very well-written). The Great Cirlce forumla is the one you want, but read the whole thing. Now, set up a script that implements the formula and the information contained in places.zip. You said you wanted to return results in order of proximity within a given range. If, by that, you mean relative to a number you feed the query script, the above works great. If, however, by some off chance, you have a certain fixed point that you want all your ordering to be based around, say New Orleans, then you could simply pipe the entire places.zip file through a calculation and stick the results in the database. I'm guessing, though, that is not what you had in mind. One last note: The Great Circle formula assumes a smooth, perfectly round earth. So, if you go up Pike's Peak and down again, you add 5.3 miles to you trip, but the Great Circle formula will not reflect this because altitude is not used. Hope this helps! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd rounding errors with 4.1
Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. Shouldn't the SUM() remain with the precision of the DECIMAL type and not try to round to 2 decimal places? Anyway, any help is appreciated. Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd rounding errors with 4.1
Huh, you know. Now that I'm not at work and therefore don't have my numbers to check against, you're right. Man, I must need more coffee. Never mind me. :) May be back tomorrow, though, when I have the numbers in front of me. I know they didn't add up earlier... Martin Hassan Schroeder wrote: Martin wrote: My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 When I use a SUM() on these I get: 7.860 Sounds good to me... If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. Time for a hand upgrade, I think :-) 5 + 5 = 5??? I don't even want to think about how Excel would come up with this... Shouldn't the SUM() remain with the precision of the DECIMAL type and not try to round to 2 decimal places? My own, possibly suspect, hand calculations show that SUM() is right; and it's common knowledge that floating point isn't the right thing to use for situations like this -- that's why there *is* a DECIMAL type. FWIW! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connect /sellect to 2 dbs
Can I select from one db and insert into another? also...Can I join to a remote db? tia, .V -- Forums, Boards, Blogs and News in RiA http://www.boardVU.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using = in WHERE vs HAVING clause
Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21 running on Mac OS X 10.3.7 I'm trying to compare two identical tables and find the rows that are new/modified. I can't use a timestamp column because the new table is constantly regenerated. So I'm using a large WHERE clause and the = operator to detect changes. (BTW, it would be very nice if there was a NULL safe not equal operator) I get empty sets returned when I use = in the WHERE clause, which seems wrong to me. However if I switch to a HAVING clause, it works as expected. Is this a bug or am I doing something funky here? Note, the testC table is used because in full query, the testA and testB tables will have 100k+ rows and I need to effeciently narrow the scope down to the ~250 rows that I'm interested in. Thanks, Rene Test Script: create table testA (id int, a int, b int, c int); create table testB (id int, a int, b int, c int); create table testC (id int); insert into testB values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4); insert into testC values(2),(3),(4); WHERE query: select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c from testC left join testA on testC.id = testA.id left join testB on testC.id = testB.id where NOT ( testA.a = testB.a AND testA.b = testB.b AND testA.c = testB.c); Empty set (0.00 sec) HAVING query: select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c from testC left join testA on testC.id = testA.id left join testB on testC.id = testB.id having NOT ( testA.a = testB.a AND testA.b = testB.b AND testA.c = testB.c); +--+--+--+--+--+--+ | a| a| b| b| c| c| +--+--+--+--+--+--+ | NULL |2 | NULL |2 | NULL |2 | | NULL |3 | NULL |3 | NULL |3 | | NULL |4 | NULL |4 | NULL |4 | +--+--+--+--+--+--+ 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd rounding errors with 4.1
In the last episode (Feb 21), Martin said: Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. You hand calculations (and Excel) are wrong, apparently. Here's my hand-calculation: 1 11 1 2.495 5.090 + 2.595 + 2.770 === === 5.090 7.860 , which matches MySQL's results. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd rounding errors with 4.1 [Duh, me!]
Yeah, I checked the math after another responder. Either I was smoking crack at work today (a state likely induced by too much/too little caffeine) or I mis-noted my numbers. I'm going to check again when I get back there tomorrow. But thanks :) Martin Dan Nelson wrote: In the last episode (Feb 21), Martin said: Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. You hand calculations (and Excel) are wrong, apparently. Here's my hand-calculation: 1 11 1 2.495 5.090 + 2.595 + 2.770 === === 5.090 7.860 , which matches MySQL's results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Mailing List
Chuck Flowers wrote: mySQL Mailing List test Luckily there are only very few people who do this, otherwise I'd spend my whole day deleting people's test messages. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining on non unique index is irrationally slow, I can beat join performance with stored procedure 100 fold!!!!
Hi, I am working with 5.0.3 snapshot on x86_64. I am now experiencing with features of MySQL, so please ignore that in my example I use self join . I know that this particular example could be done much easier but eventually I am going to join multiple tables with similar structure (one preprocessing table and one final table). My query is: mysql insert into log select t1.uniprot_primary_ac from uniprot_attributes as t1 left join uniprot_attributes as t2 on (t1.attribute_name=t2.attribute_name and t1.attribute_value=t2.attribute_value) where t2.uniprot_primary_ac=P15017; Query OK, 748 rows affected (16.31 sec) Records: 748 Duplicates: 0 Warnings: 0 mysql explain select t1.uniprot_primary_ac from uniprot_attributes as t1 left join uniprot_attributes as t2 on (t1.attribute_name=t2.attribute_name and t1.attribute_value=t2.attribute_value) where t2.uniprot_primary_ac=P15017; - ? ++-+---+-- +--+--+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+-- +--+--+- ++---+-+ | 1 | SIMPLE | t2| ref | up_att_1,up_att_3,up_att_4,up_att_5,up_att_2 | up_att_3 | 23 | const | 9 | Using where | | 1 | SIMPLE | t1| ref | up_att_4,up_att_5,up_att_2 | up_att_4 | 8 | test.t2.attribute_name | 35655 | Using where | ++-+---+-- +--+--+- ++---+-+ Why does it take 16 seconds to join 9 rows to 35655 rows on a x86_64 machine with 2GB RAM nearly all processor capacity available? Why is it using where, when there are indexes for all possible combinations? I indexed the table almost all possible ways to find a way to optimize the query but no help: mysql show index from uniprot_attributes; - ? +++--+-- ++---+-+--+ +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+-- ++---+-+--+ +--++-+ | uniprot_attributes | 1 | up_att_1 |1 | uniprot_primary_ac | A | 170522 | NULL | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_1 |2 | attribute_name | A | 1961014 |4 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_3 |1 | uniprot_primary_ac | A | 170522 | NULL | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_3 |2 | attribute_name | A | 1961014 |5 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_3 |3 | attribute_value| A | 3922028 |7 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_4 |1 | attribute_name | A | 109 |5 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_4 |2 | attribute_value| A | 980507 |7 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_4 |3 | uniprot_primary_ac | A | 3922028 | NULL | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_5 |1 | attribute_value| A | 980507 |7 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_5 |2 | attribute_name | A | 980507 |5 | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_5 |3 | uniprot_primary_ac | A | 3922028 | NULL | NULL | YES | BTREE | | | uniprot_attributes | 1 | up_att_2 |1 | attribute_value| NULL | 1 | NULL | NULL | YES | FULLTEXT | | +++--+-- ++---+-+--+ +--++-+ 12 rows in set (0.01 sec) Moreover I created a stored procedure that does exactly the same as the select statement above: mysql create procedure test_performance(IN uniprot_id VARCHAR(20) ) - begin - declare v_attribute_name VARCHAR(20); - declare v_attribute_value TEXT; - declare done int default 0; - declare cur1 cursor for select
InnoDB Row Lock test (A query maybe?)
Hi, I'm writing an application that uses InnoDB tables to provide transactional integrity. The front-end is a web-based interface. I'd like to know - is there a way one can issue a query to test whether a particular set of rows (or row) has already been locked by another session - rather than the default action of waiting for quite long for the lock to be granted. The idea is so that a person using the front-end may know whether the information they are attempting to access is unavailable at the time. There will be situations where rows will be locked exclusively for prolonged times and I wouldn't want the web application users to be subjected to these lengthy delays but rather be told to try later. Regards, Gerald. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Postcode Proximity Search
I wrote to the list explaining about the Great Circle formula for doing the desired calculations. I also stated that places.zip was the file to be used for these calculations--I misspoke. The correct file is http://www.census.gov/tiger/tms/gazetteer/zcta5.zip;. Sorry for the error! --Brandon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timediff and subtime: when is the result negativ?
Hi listers, could you please explain: mysql select timediff(10:00:00, 12:13:14) - ; +--+ | timediff(10:00:00, 12:13:14) | +--+ | -02:13:14| +--+ 1 row in set (0.00 sec) mysql select subtime(10:00:00, 12:13:14); +-+ | subtime(10:00:00, 12:13:14) | +-+ | -02:13:14 | +-+ 1 row in set (0.00 sec) mysql select version(); ++ | version() | ++ | 4.1.9-standard | ++ 1 row in set (0.00 sec) mysql why is the result of timediff negativ? timediff in the doc (quote) returns the time between the start time expr and the end time expr2. expr and expr2 are time or date-and-time expressions, but both must be of the same type(unquote) the difference between start- and end-time, that is in our case start-time 10:00:00 and end_time 12:13:14. in common sense, a difference in time is considered postiv, if the end-time is later than the start time, otherwise negativ. is this not so in mysql? talking about subtime (quote)subtracts expr2 from expr and returns the result. expr is a time or datetime expression, and expr2 is a time expression.(unquote): the result is correct, i.e. negativ. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
