Possible bug with event and delete...limit ?
Hey list, I noticed a table that was trying to fill the disk before the weekend, so I quickly set up an event to gradually clean it out. Yesterday, however, I returned to find 400+ jobs in state "updating". I disabled the event, but the jobs hadn't cleared up today, so I had to kill them. I noticed, however, that the LIMIT statement I specified in the event wasn't present in the actual queries... Could that be a parser bug, or does the limit simply not show up in the process lists? Has anyone seen this before ? This is 5.5.30-1.1-log on Debian 64-bit. Thanks, Johan mysql> show create event jdmsyslogcleaner\G *** 1. row *** Event: jdmsyslogcleaner sql_mode: time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `jdmsyslogcleaner` ON SCHEDULE EVERY 30 SECOND STARTS '2014-09-19 19:14:21' ON COMPLETION PRESERVE DISABLE COMMENT 'Cleanup to not kill the disk' DO delete from syslog where logtime < "2014-07-20" limit 1 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> select * from information_schema.processlist WHERE `INFO` LIKE 'DELETE FROM `cacti%' order by time; +---+---++---+-+---+--++ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +---+---++---+-+---+--++ | 149192515 | cacti_net | host:49225 | cacti_net | Query | 21 | init | DELETE FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-24 08:48:28' | [...] | 148845878 | cacti_net | host:50186 | cacti_net | Query | 47345 | updating | DELETE FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-23 17:13:51' | +---+---++---+-+---+--++ 411 rows in set (13.66 sec) -- What's tiny and yellow and very, very dangerous? A canary with the root password.
possible bug in mysql_tzinfo_to_sql
i used mysql_tzinfo_to_sql utility to create timezone_* database after upgrading zoneinfo in my host. after that i am seeing this mismatch. 'America/Sao_Paulo' (http://www.timeanddate.com/worldclock/city.html?n=233) and 'America/Buenos_Aires' (http://www.timeanddate.com/worldclock/city.html?n=51) are supposed to have same time information .. but mysql database shows differently .. is this a bug somewhere ? mysql> select * from time_zone_name where Time_zone_id in (59,185); ++--+ | Name | Time_zone_id | ++--+ | America/Argentina/Buenos_Aires | 59 | | America/Sao_Paulo | 185 | ++--+ 2 rows in set (0.00 sec) mysql> select * from time_zone_transition_type where Time_zone_id=185; +--++++--+ | Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation | +--++++--+ | 185 | 0 | -11188 | 0 | LMT | | 185 | 1 | -7200 | 1 | BRST | | 185 | 2 | -10800 | 0 | BRT | +--++++--+ 3 rows in set (0.00 sec) mysql> select * from time_zone_transition_type where Time_zone_id=59; +--++++--+ | Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation | +--++++--+ | 59 | 0 | -15408 | 0 | CMT | | 59 | 1 | -14400 | 0 | ART | | 59 | 2 | -10800 | 1 | ARST | | 59 | 3 | -7200 | 1 | ARST | | 59 | 4 | -10800 | 0 | ART | +--++++--+ 5 rows in set (0.00 sec) mysql> select convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo'); ++ | convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo') | ++ | 2010-03-08 17:48:16| ++ 1 row in set (0.00 sec) mysql> select convert_tz(now(), @@global.time_zone,'America/Buenos_Aires'); +--+ | convert_tz(now(), @@global.time_zone,'America/Buenos_Aires') | +--+ | 2010-03-08 18:48:28 | +--+ 1 row in set (0.00 sec) A.Alagarsamy Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Possible bug in mysqldump?
Thanks Rolando, I'm using InnoDB tables. According to the docs, the single-transaction option: Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables. That seems to contradict what you're saying. I think they key is that InnoDB supports multiversioning and that single-transaction creates a snapshot "version" of the db by briefly locking all tables. That has the same effect as locking MyISAM tables for the duration of the dump - as I understand it. Can anyone confirm this? So this still doesn't explain the different behaviour between pipe and redirect that I'm seeing. Regards, Mark. On Tue, Aug 5, 2008 at 11:55 AM, Rolando Edwards <[EMAIL PROTECTED]>wrote: > This is an excerpt from > http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data > > The --master-data option automatically turns off --lock-tables. It also > turns on --lock-all-tables, unless *--single-transaction* also is > specified, in which case, *a global read lock is acquired only for a short > time at the beginning of the dump* (see the description for > --single-transaction). *In all cases, any action on logs happens at the > exact moment of the dump*. (Bold Italics mine) > > According to preceding statement, the option *"--single-transaction"* WILL > NOT HOLD OFF ANY NEW TRANSACTIONS FOR THE DURATION OF THE mysqldump. > Consequently, somewhere in the middle of the dump process, table locks are > released prematurely by design. > > > > This is why I suggested locking all tables with FLUSH TABLES WITH READ LOCK > on the master so no new transactions would sneak in during the pipe-fed > mysql load from mysqldump. > > > > Locking the master with FLUSH TABLES WITH READ LOCK should be done even if > you are dumping to a text file in order to have a perfect snapshot of the > data. > > > > Additionally, the option *"--single-transaction"* WILL NOT PROTECT MyISAM > tables from live changes being written to the dump file since you cannot run > ACID compliant transactions against MyISAM, only InnoDB. > > > > Doing FLUSH TABLES WITH READ LOCK on the master prior to the mysqldump will > guarantee that no transactions, regardless of whether it is for MyISAM or > InnoDB, will come through during a mysqldump. > > > ------ > > *From:* Mark Maunder [mailto:[EMAIL PROTECTED] > *Sent:* Tuesday, August 05, 2008 12:17 PM > *To:* Rolando Edwards > *Cc:* mysql@lists.mysql.com > *Subject:* Re: Possible bug in mysqldump? > > > > Thanks for the reply Rolando. > > In both the examples I provided (pipe and text file) the CHANGE MASTER > command appears at the top of the data import and is uncommented and > therefore executes before the data is imported. I don't think this is a > problem because the slave only starts replicating from the master once I run > the "start slave" command. That command is only run after all data is > imported. > > Unless the slave does some kind of processing before I run "start slave" I > don't see this is the explanation. > > Thanks again - and please let me know your thoughts on this because I could > be wrong. > > Mark. > > On Tue, Aug 5, 2008 at 8:47 AM, Rolando Edwards <[EMAIL PROTECTED]> > wrote: > > When you use --master-data=1, it executes the CHANGE MASTER command first > before adding data. > > Do the following to verify this: > > Run 'mysqldump --single-transaction --master-data=1 -h... -u... -p... > > DataDump1.sql > Run 'mysqldump --single-transaction --master-data=2 -h... -u... -p... > > DataDump2.sql > > Run 'head -30 DataDump1.sql' > You will see the CHANGE MASTER command before all CREATE TABLEs and > INSERTs. > Therefore, it will execute. > > Run 'head -30 DataDump2.sql' > You will see the CHANGE MASTER command before all CREATE TABLEs and > INSERTs. > However, the command is commented Out !!! > Therefore, it will not execute. > > After loading DataDump2.sql, you can then use the replication coordinates > (log file name and log position) in the Commented Out CHANGE MASTER Command > After the data are loaded. > > In theory, it is a paradigm bug because the CHANGE MASTER command when > using --master-data=1 should appear on the bottom of the mysqldump and not > at the top. Yet, it i
RE: Possible bug in mysqldump?
This is an excerpt from http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump. (Bold Italics mine) According to preceding statement, the option "--single-transaction" WILL NOT HOLD OFF ANY NEW TRANSACTIONS FOR THE DURATION OF THE mysqldump. Consequently, somewhere in the middle of the dump process, table locks are released prematurely by design. This is why I suggested locking all tables with FLUSH TABLES WITH READ LOCK on the master so no new transactions would sneak in during the pipe-fed mysql load from mysqldump. Locking the master with FLUSH TABLES WITH READ LOCK should be done even if you are dumping to a text file in order to have a perfect snapshot of the data. Additionally, the option "--single-transaction" WILL NOT PROTECT MyISAM tables from live changes being written to the dump file since you cannot run ACID compliant transactions against MyISAM, only InnoDB. Doing FLUSH TABLES WITH READ LOCK on the master prior to the mysqldump will guarantee that no transactions, regardless of whether it is for MyISAM or InnoDB, will come through during a mysqldump. From: Mark Maunder [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2008 12:17 PM To: Rolando Edwards Cc: mysql@lists.mysql.com Subject: Re: Possible bug in mysqldump? Thanks for the reply Rolando. In both the examples I provided (pipe and text file) the CHANGE MASTER command appears at the top of the data import and is uncommented and therefore executes before the data is imported. I don't think this is a problem because the slave only starts replicating from the master once I run the "start slave" command. That command is only run after all data is imported. Unless the slave does some kind of processing before I run "start slave" I don't see this is the explanation. Thanks again - and please let me know your thoughts on this because I could be wrong. Mark. On Tue, Aug 5, 2008 at 8:47 AM, Rolando Edwards <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote: When you use --master-data=1, it executes the CHANGE MASTER command first before adding data. Do the following to verify this: Run 'mysqldump --single-transaction --master-data=1 -h... -u... -p... > DataDump1.sql Run 'mysqldump --single-transaction --master-data=2 -h... -u... -p... > DataDump2.sql Run 'head -30 DataDump1.sql' You will see the CHANGE MASTER command before all CREATE TABLEs and INSERTs. Therefore, it will execute. Run 'head -30 DataDump2.sql' You will see the CHANGE MASTER command before all CREATE TABLEs and INSERTs. However, the command is commented Out !!! Therefore, it will not execute. After loading DataDump2.sql, you can then use the replication coordinates (log file name and log position) in the Commented Out CHANGE MASTER Command After the data are loaded. In theory, it is a paradigm bug because the CHANGE MASTER command when using --master-data=1 should appear on the bottom of the mysqldump and not at the top. Yet, it is at the top and executes immediately and then tries to load your data and read from the master's binary logs at the same time, guaranteeing duplicate key collision. This is why importing mysqldump straight to mysql via a pipe produces the error you are experiencing. Try this: 1) In mysql session 1, Run FLUSH TABLES WITH READ LOCK on the master. 2) In mysql session 1, run SHOW MASTER STATUS. 3) Record the log file and position from mysql session 1. 4) In mysql seesion 2, run 'STOP SLAVE;' 5) Run 'mysqldump --single-transaction mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName | mysql -u root -pmypass -h slaveHost dbName'. Let it run to completion. Notice I did not use --master-data in the mysqldump 5) In mysql session 2, run "CHANGE MASTER TO MASTER_LOG_FILE=',MASTER_LOG_POS='';" 6) In mysql session 2,run 'START SLAVE'. 7) In mysql session 1, run 'UNLOCK TABLES' Give it a try !!! -Original Message- From: Mark Maunder [mailto:[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>] Sent: Tuesday, August 05, 2008 3:02 AM To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com> Subject: Possible bug in mysqldump? Hi all, I'm busy setting up replication and have encountered what looks like a bug in mysqldump. The following commands work perfectly: Running the following commands in the mysql client on the slave: stop slave; reset slave; create database dbNam
Re: Possible bug in mysqldump?
Thanks for the reply Rolando. In both the examples I provided (pipe and text file) the CHANGE MASTER command appears at the top of the data import and is uncommented and therefore executes before the data is imported. I don't think this is a problem because the slave only starts replicating from the master once I run the "start slave" command. That command is only run after all data is imported. Unless the slave does some kind of processing before I run "start slave" I don't see this is the explanation. Thanks again - and please let me know your thoughts on this because I could be wrong. Mark. On Tue, Aug 5, 2008 at 8:47 AM, Rolando Edwards <[EMAIL PROTECTED]>wrote: > When you use --master-data=1, it executes the CHANGE MASTER command first > before adding data. > > Do the following to verify this: > > Run 'mysqldump --single-transaction --master-data=1 -h... -u... -p... > > DataDump1.sql > Run 'mysqldump --single-transaction --master-data=2 -h... -u... -p... > > DataDump2.sql > > Run 'head -30 DataDump1.sql' > You will see the CHANGE MASTER command before all CREATE TABLEs and > INSERTs. > Therefore, it will execute. > > Run 'head -30 DataDump2.sql' > You will see the CHANGE MASTER command before all CREATE TABLEs and > INSERTs. > However, the command is commented Out !!! > Therefore, it will not execute. > > After loading DataDump2.sql, you can then use the replication coordinates > (log file name and log position) in the Commented Out CHANGE MASTER Command > After the data are loaded. > > In theory, it is a paradigm bug because the CHANGE MASTER command when > using --master-data=1 should appear on the bottom of the mysqldump and not > at the top. Yet, it is at the top and executes immediately and then tries to > load your data and read from the master's binary logs at the same time, > guaranteeing duplicate key collision. > > This is why importing mysqldump straight to mysql via a pipe produces the > error you are experiencing. > > Try this: > > 1) In mysql session 1, Run FLUSH TABLES WITH READ LOCK on the master. > > 2) In mysql session 1, run SHOW MASTER STATUS. > > 3) Record the log file and position from mysql session 1. > > 4) In mysql seesion 2, run 'STOP SLAVE;' > > 5) Run 'mysqldump --single-transaction mysqldump --single-transaction > --master-data=1 -u root -pmypass -h masterHost dbName | mysql -u root > -pmypass -h slaveHost dbName'. Let it run to completion. > > Notice I did not use --master-data in the mysqldump > > 5) In mysql session 2, run "CHANGE MASTER TO MASTER_LOG_FILE=' from SHOW MASTER STATUS>,MASTER_LOG_POS=' STATUS>';" > > 6) In mysql session 2,run 'START SLAVE'. > > 7) In mysql session 1, run 'UNLOCK TABLES' > > Give it a try !!! > > -Original Message- > From: Mark Maunder [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 05, 2008 3:02 AM > To: mysql@lists.mysql.com > Subject: Possible bug in mysqldump? > > Hi all, > > I'm busy setting up replication and have encountered what looks like a bug > in mysqldump. The following commands work perfectly: > > Running the following commands in the mysql client on the slave: > stop slave; > reset slave; > create database dbName; > CHANGE MASTER TO MASTER_HOST='masterHost', MASTER_USER='root', > MASTER_PASSWORD='mypass'; > > Then running the following on the command line on the slave: > > mysqldump --single-transaction --master-data=1 -u root -pmypass -h > masterHost dbName >masterDB.sql ; > > mysql -u root -pmypass -h slaveHost dbName< masterDB.sql > > Then running the following in the mysql client on the slave: > > start slave; > > At this point the slave comes up perfectly and is in sync with the master. > > However, if I do exactly the same thing, but import the data using a pipe > command: > > mysqldump --single-transaction --master-data=1 -u root -pmypass -h > masterHost dbName | mysql -u root -pmypass -h slaveHost dbName > > When i start the slave I get a duplicate key error. In other words, the > slave is trying to execute entries in the masters log that have already > been > run. > > I can't figure out why this is a problem and this has forced me to store > data on disk as a file as an intermediate step when setting up slaves. > > The only difference between the two methods is that in the first case the > data is stored on disk and then imported via the client and in the second > case it's piped directly to the client. In both cases the data that > mysqldump produces is the same. Both include the CHANGE MASTER command that > sets the log file and position. > > Is this a bug in mysqldump, or am I missing something? > > Thanks in advance, > > Mark. > -- Mark Maunder <[EMAIL PROTECTED]> http://markmaunder.com/ +1-206-6978723
RE: Possible bug in mysqldump?
When you use --master-data=1, it executes the CHANGE MASTER command first before adding data. Do the following to verify this: Run 'mysqldump --single-transaction --master-data=1 -h... -u... -p... > DataDump1.sql Run 'mysqldump --single-transaction --master-data=2 -h... -u... -p... > DataDump2.sql Run 'head -30 DataDump1.sql' You will see the CHANGE MASTER command before all CREATE TABLEs and INSERTs. Therefore, it will execute. Run 'head -30 DataDump2.sql' You will see the CHANGE MASTER command before all CREATE TABLEs and INSERTs. However, the command is commented Out !!! Therefore, it will not execute. After loading DataDump2.sql, you can then use the replication coordinates (log file name and log position) in the Commented Out CHANGE MASTER Command After the data are loaded. In theory, it is a paradigm bug because the CHANGE MASTER command when using --master-data=1 should appear on the bottom of the mysqldump and not at the top. Yet, it is at the top and executes immediately and then tries to load your data and read from the master's binary logs at the same time, guaranteeing duplicate key collision. This is why importing mysqldump straight to mysql via a pipe produces the error you are experiencing. Try this: 1) In mysql session 1, Run FLUSH TABLES WITH READ LOCK on the master. 2) In mysql session 1, run SHOW MASTER STATUS. 3) Record the log file and position from mysql session 1. 4) In mysql seesion 2, run 'STOP SLAVE;' 5) Run 'mysqldump --single-transaction mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName | mysql -u root -pmypass -h slaveHost dbName'. Let it run to completion. Notice I did not use --master-data in the mysqldump 5) In mysql session 2, run "CHANGE MASTER TO MASTER_LOG_FILE=',MASTER_LOG_POS='';" 6) In mysql session 2,run 'START SLAVE'. 7) In mysql session 1, run 'UNLOCK TABLES' Give it a try !!! -Original Message- From: Mark Maunder [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2008 3:02 AM To: mysql@lists.mysql.com Subject: Possible bug in mysqldump? Hi all, I'm busy setting up replication and have encountered what looks like a bug in mysqldump. The following commands work perfectly: Running the following commands in the mysql client on the slave: stop slave; reset slave; create database dbName; CHANGE MASTER TO MASTER_HOST='masterHost', MASTER_USER='root', MASTER_PASSWORD='mypass'; Then running the following on the command line on the slave: mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName >masterDB.sql ; mysql -u root -pmypass -h slaveHost dbName< masterDB.sql Then running the following in the mysql client on the slave: start slave; At this point the slave comes up perfectly and is in sync with the master. However, if I do exactly the same thing, but import the data using a pipe command: mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName | mysql -u root -pmypass -h slaveHost dbName When i start the slave I get a duplicate key error. In other words, the slave is trying to execute entries in the masters log that have already been run. I can't figure out why this is a problem and this has forced me to store data on disk as a file as an intermediate step when setting up slaves. The only difference between the two methods is that in the first case the data is stored on disk and then imported via the client and in the second case it's piped directly to the client. In both cases the data that mysqldump produces is the same. Both include the CHANGE MASTER command that sets the log file and position. Is this a bug in mysqldump, or am I missing something? Thanks in advance, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug in mysqldump?
Hi all, I'm busy setting up replication and have encountered what looks like a bug in mysqldump. The following commands work perfectly: Running the following commands in the mysql client on the slave: stop slave; reset slave; create database dbName; CHANGE MASTER TO MASTER_HOST='masterHost', MASTER_USER='root', MASTER_PASSWORD='mypass'; Then running the following on the command line on the slave: mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName >masterDB.sql ; mysql -u root -pmypass -h slaveHost dbName< masterDB.sql Then running the following in the mysql client on the slave: start slave; At this point the slave comes up perfectly and is in sync with the master. However, if I do exactly the same thing, but import the data using a pipe command: mysqldump --single-transaction --master-data=1 -u root -pmypass -h masterHost dbName | mysql -u root -pmypass -h slaveHost dbName When i start the slave I get a duplicate key error. In other words, the slave is trying to execute entries in the masters log that have already been run. I can't figure out why this is a problem and this has forced me to store data on disk as a file as an intermediate step when setting up slaves. The only difference between the two methods is that in the first case the data is stored on disk and then imported via the client and in the second case it's piped directly to the client. In both cases the data that mysqldump produces is the same. Both include the CHANGE MASTER command that sets the log file and position. Is this a bug in mysqldump, or am I missing something? Thanks in advance, Mark.
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
Mariella Petrini wrote: ...MySQL 6.0.2 with Falcon on Linux with Debian 4. I have compiled the source code for 64 bit executable. The system is an Intel 2 cpus 4 cores each, with 8 GB of RAM. After having created approximately 8,500 empty tables mysqld server was still alive, you could connect with the mysql client, but any command that you would type would freeze (never come back). It would be interesting to get a stack trace at that point. We do have tests that create that many tables, so the problem is going to take some thought. I will try to re-run the same job, enabling falcon_debug_mask and see if I can get more info and repeat the problem. Sounds good - that can produce a LOT of output... so be sure to redirect it to a file. Best regards, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
Mathieu Bruneau wrote: I never tried the falcon engine, but could it be that mysql is running out of file descriptor ? That's less likely with Falcon than with engines that put each table and index in its own file. By default, Falcon tables share a single tablespace. Regards, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
I don't think that is the problem. I have been able to create over a 1,000,000 of tables with MySQL 5.1.21 and different storage engines with the same environment (I had already ulimit set). Thanks, Mariella Mathieu Bruneau <[EMAIL PROTECTED]> wrote: I never tried the falcon engine, but could it be that mysql is running out of file descriptor ? I think debian is limited to something around 8k per user by default. Check your pam settings and your ulimit ... Database usually use a lot of file handle and it's generally safe to increase it. Don't know if falcon use file handle per table or not tough... -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Mariella Petrini a �crit : > I have re-run and job and I was able to repeat the > problem. > Find attached the mysql server error log with all the > tarces. > > > Could you please help ? > > Thanks, > > Mariella > > > > --- Mariella Petrini > wrote: > >> Hi All, >> >> >> I have started using MySQL 6.0.2 with Falcon on >> Linux >> with Debian 4. >> I have compiled the source code for 64 bit >> executable. >> The system is an Intel 2 cpus 4 cores each, with 8 >> GB >> of RAM. >> >> I have started creating tables (without >> partitioning) >> whit engine type Falcon. After having created >> approximately 8,500 empty tables mysqld server was >> still alive, you could connect with the mysql >> client, >> but any command that you would type would freeze >> (never come back). >> >> I will try to re-run the same job, enabling >> falcon_debug_mask and see if I can get more info and >> repeat the problem. >> >> Mariella >> >> >> >> Below are shown the Falcon settings used: >> >> > +--+-+ >> >> | Variable_name| Value >> | >> > +--+-+ >> | falcon_checkpoint_schedule | 7 * * * * * >> | >> | falcon_debug_mask| 0 >> | >> | falcon_debug_server | OFF >> | >> >> | falcon_disable_fsync | OFF >> | >> >> | falcon_index_chill_threshold | 4 >> | >> >> | falcon_initial_allocation| 1073741824 >> | >> >> | falcon_max_transaction_backlog | 150 >> | >> >> | falcon_page_cache_size | 4194304 >> | >> | falcon_page_size | 4096 >> | >> | falcon_record_chill_threshold| 5 >> | >> | falcon_record_memory_max | 262144000 >> | >> >> | falcon_record_scavenge_floor | 50 >> | >> >> | falcon_record_scavenge_threshold | 67 >> | >> >> | falcon_scavenge_schedule | 15,45 * * * * * >> | >> >> | falcon_serial_log_buffers| 10 >> | >> >> | falcon_serial_log_dir| >> | >> | have_falcon | YES >> | >> > +--+-+ >> >> >> >> >> >> > >> Need a vacation? Get great deals >> to amazing places on Yahoo! Travel. >> http://travel.yahoo.com/ >> >> -- >> MySQL Internals Mailing List >> For list archives: http://lists.mysql.com/internals >> To unsubscribe: >> > http://lists.mysql.com/[EMAIL PROTECTED] >> > > > > > Shape Yahoo! in your own image. Join our Network Research Panel today! > http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 > > > > > > > - Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
If Debian is limited to create files over 8k, it can be the reason of the problem. Falcon seems not create data file for tables, but still create ".frm" files for each tables. -- Jaemyung Kim 2007/9/9, Mathieu Bruneau <[EMAIL PROTECTED]>: > > I never tried the falcon engine, but could it be that mysql is running > out of file descriptor ? > > I think debian is limited to something around 8k per user by default. > Check your pam settings and your ulimit ... Database usually use a lot > of file handle and it's generally safe to increase it. Don't know if > falcon use file handle per table or not tough... > > -- > Mathieu Bruneau > aka ROunofF > > === > GPG keys available @ http://rounoff.darktech.org > > Mariella Petrini a écrit : > > I have re-run and job and I was able to repeat the > > problem. > > Find attached the mysql server error log with all the > > tarces. > > > > > > Could you please help ? > > > > Thanks, > > > > Mariella > > > > > > > > --- Mariella Petrini <[EMAIL PROTECTED]> > > wrote: > > > >> Hi All, > >> > >> > >> I have started using MySQL 6.0.2 with Falcon on > >> Linux > >> with Debian 4. > >> I have compiled the source code for 64 bit > >> executable. > >> The system is an Intel 2 cpus 4 cores each, with 8 > >> GB > >> of RAM. > >> > >> I have started creating tables (without > >> partitioning) > >> whit engine type Falcon. After having created > >> approximately 8,500 empty tables mysqld server was > >> still alive, you could connect with the mysql > >> client, > >> but any command that you would type would freeze > >> (never come back). > >> > >> I will try to re-run the same job, enabling > >> falcon_debug_mask and see if I can get more info and > >> repeat the problem. > >> > >> Mariella > >> > >> > >> > >> Below are shown the Falcon settings used: > >> > >> > > +--+-+ > >> > >> | Variable_name| Value > >> | > >> > > +--+-+ > >> | falcon_checkpoint_schedule | 7 * * * * * > >> | > >> | falcon_debug_mask| 0 > >> | > >> | falcon_debug_server | OFF > >> | > >> > >> | falcon_disable_fsync | OFF > >> | > >> > >> | falcon_index_chill_threshold | 4 > >> | > >> > >> | falcon_initial_allocation| 1073741824 > >> | > >> > >> | falcon_max_transaction_backlog | 150 > >> | > >> > >> | falcon_page_cache_size | 4194304 > >> | > >> | falcon_page_size | 4096 > >> | > >> | falcon_record_chill_threshold| 5 > >> | > >> | falcon_record_memory_max | 262144000 > >> | > >> > >> | falcon_record_scavenge_floor | 50 > >> | > >> > >> | falcon_record_scavenge_threshold | 67 > >> | > >> > >> | falcon_scavenge_schedule | 15,45 * * * * * > >> | > >> > >> | falcon_serial_log_buffers| 10 > >> | > >> > >> | falcon_serial_log_dir| > >> | > >> | have_falcon | YES > >> | > >> > > +--+-+ > >> > >> > >> > >> > >> > >> > > > > >> Need a vacation? Get great deals > >> to amazing places on Yahoo! Travel. > >> http://travel.yahoo.com/ > >> > >> -- > >> MySQL Internals Mailing List > >> For list archives: http://lists.mysql.com/internals > >> To unsubscribe: > >> > > http://lists.mysql.com/[EMAIL PROTECTED] > >> > > > > > > > > > Shape Yahoo! in your own image. Join our Network Research Panel > today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 > > > > > > > > > > > > > > > > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
I never tried the falcon engine, but could it be that mysql is running out of file descriptor ? I think debian is limited to something around 8k per user by default. Check your pam settings and your ulimit ... Database usually use a lot of file handle and it's generally safe to increase it. Don't know if falcon use file handle per table or not tough... -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Mariella Petrini a écrit : I have re-run and job and I was able to repeat the problem. Find attached the mysql server error log with all the tarces. Could you please help ? Thanks, Mariella --- Mariella Petrini <[EMAIL PROTECTED]> wrote: Hi All, I have started using MySQL 6.0.2 with Falcon on Linux with Debian 4. I have compiled the source code for 64 bit executable. The system is an Intel 2 cpus 4 cores each, with 8 GB of RAM. I have started creating tables (without partitioning) whit engine type Falcon. After having created approximately 8,500 empty tables mysqld server was still alive, you could connect with the mysql client, but any command that you would type would freeze (never come back). I will try to re-run the same job, enabling falcon_debug_mask and see if I can get more info and repeat the problem. Mariella Below are shown the Falcon settings used: +--+-+ | Variable_name| Value | +--+-+ | falcon_checkpoint_schedule | 7 * * * * * | | falcon_debug_mask| 0 | | falcon_debug_server | OFF | | falcon_disable_fsync | OFF | | falcon_index_chill_threshold | 4 | | falcon_initial_allocation| 1073741824 | | falcon_max_transaction_backlog | 150 | | falcon_page_cache_size | 4194304 | | falcon_page_size | 4096 | | falcon_record_chill_threshold| 5 | | falcon_record_memory_max | 262144000 | | falcon_record_scavenge_floor | 50 | | falcon_record_scavenge_threshold | 67 | | falcon_scavenge_schedule | 15,45 * * * * * | | falcon_serial_log_buffers| 10 | | falcon_serial_log_dir| | | have_falcon | YES | +--+-+ Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.2-alpha and Falcon and possible bug/problem
I have re-run and job and I was able to repeat the problem. Find attached the mysql server error log with all the tarces. Could you please help ? Thanks, Mariella --- Mariella Petrini <[EMAIL PROTECTED]> wrote: > Hi All, > > > I have started using MySQL 6.0.2 with Falcon on > Linux > with Debian 4. > I have compiled the source code for 64 bit > executable. > The system is an Intel 2 cpus 4 cores each, with 8 > GB > of RAM. > > I have started creating tables (without > partitioning) > whit engine type Falcon. After having created > approximately 8,500 empty tables mysqld server was > still alive, you could connect with the mysql > client, > but any command that you would type would freeze > (never come back). > > I will try to re-run the same job, enabling > falcon_debug_mask and see if I can get more info and > repeat the problem. > > Mariella > > > > Below are shown the Falcon settings used: > > +--+-+ > > | Variable_name| Value > | > +--+-+ > | falcon_checkpoint_schedule | 7 * * * * * > | > | falcon_debug_mask| 0 > | > | falcon_debug_server | OFF > | > > | falcon_disable_fsync | OFF > | > > | falcon_index_chill_threshold | 4 > | > > | falcon_initial_allocation| 1073741824 > | > > | falcon_max_transaction_backlog | 150 > | > > | falcon_page_cache_size | 4194304 > | > | falcon_page_size | 4096 > | > | falcon_record_chill_threshold| 5 > | > | falcon_record_memory_max | 262144000 > | > > | falcon_record_scavenge_floor | 50 > | > > | falcon_record_scavenge_threshold | 67 > | > > | falcon_scavenge_schedule | 15,45 * * * * * > | > > | falcon_serial_log_buffers| 10 > | > > | falcon_serial_log_dir| > | > | have_falcon | YES > | > +--+-+ > > > > > > > > Need a vacation? Get great deals > to amazing places on Yahoo! Travel. > http://travel.yahoo.com/ > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 6.0.2-alpha and Falcon and possible bug/problem
Hi All, I have started using MySQL 6.0.2 with Falcon on Linux with Debian 4. I have compiled the source code for 64 bit executable. The system is an Intel 2 cpus 4 cores each, with 8 GB of RAM. I have started creating tables (without partitioning) whit engine type Falcon. After having created approximately 8,500 empty tables mysqld server was still alive, you could connect with the mysql client, but any command that you would type would freeze (never come back). I will try to re-run the same job, enabling falcon_debug_mask and see if I can get more info and repeat the problem. Mariella Below are shown the Falcon settings used: +--+-+ | Variable_name| Value | +--+-+ | falcon_checkpoint_schedule | 7 * * * * * | | falcon_debug_mask| 0 | | falcon_debug_server | OFF | | falcon_disable_fsync | OFF | | falcon_index_chill_threshold | 4 | | falcon_initial_allocation| 1073741824 | | falcon_max_transaction_backlog | 150 | | falcon_page_cache_size | 4194304 | | falcon_page_size | 4096| | falcon_record_chill_threshold| 5 | | falcon_record_memory_max | 262144000 | | falcon_record_scavenge_floor | 50 | | falcon_record_scavenge_threshold | 67 | | falcon_scavenge_schedule | 15,45 * * * * * | | falcon_serial_log_buffers| 10 | | falcon_serial_log_dir| | | have_falcon | YES | +--+-+ Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible bug: general logging not working with 5.1.20-beta-log
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, We have general logging enabled on our mysql server via a my.cnf setting. The log records an entry when the server restarts, but does not record any queries. Is this a bug? or have I missed something. In my.cnf [mysqld] log=query.log Contents of /var/lib/mysql/query.log after several queries have been run is still: /usr/sbin/mysqld, Version: 5.1.20-beta-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument Any help would be appreciated! Regards, Paul Maunders -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGxIF4sYtjtAM5Dp4RAvwsAKC30LP6RBvGYQkwmyd1qwfbwcnU0wCeLZhZ ZkHs5Osxwa1LzARdumP+ILQ= =5fzk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
Peter Brawley <[EMAIL PROTECTED]> wrote on 10/12/2005 04:27:18
PM:
> James,
>
> Both ...
>
> SELECT ...
> FROM a, b LEFT JOIN c ON a.x=c.y
>
> and
>
> SELECT ...
> FROM a
> LEFT JOIN B USING (x )
> LEFT JOIN c ON a.x=c.y
>
> work up to and including version 5.0.10, not in 5.0.11, 12 or 13.
>
> http://bugs.mysql.com/bug.php?id=13832 reports...
>
> "The two statements below are quite different from one another:
> 1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c
> 2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c
>
> "Statement (1) above will likely continue to give an "Unknown
column't1.a' in
> 'on clause'" error, while statement (2) will likely function correctly
at some
> point in the future.
>
> "This bug report is in reference to statements like statement (2), and
no bug
> reports that use a statement like statement (1) are duplicates of this
bug."
>
> The behaviour of (1) above is also verified (http://bugs.mysql.
> com/bug.php?id=13551), and that page explains...
>
> "This is a change that was made in 5.0.15 [sic] to make MySQL more
> compliant with the standard. According to the SQL:2003
>
> ::= FROM
> ::=
> [ { }... ]
> ::=
>
> |
> ::=
>
> |
> |
> ...
>
> "Thus when you write
>
> ... FROM t1 , t2 LEFT JOIN t3 ON (expr)
>
> it is parsed as
>
> (1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))
>
> and not as
>
> (2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)
>
> so, from expr you can only refer to columns of t2 and t3 - operands
> of the join. Workaround - to put parentheses explicitly as in (2).
> Then you can refer to t1 columns from expr.
>
> Unfortunately, this change is not properly documented in the manual,
> it will be fixed."
>
> PB
> http://www.artfulsoftware.com
>
> -
>
> [EMAIL PROTECTED] wrote:
> James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 02:06:26 PM:
>
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Peter Brawley wrote:
>
> James,
>
> You can reproduce that error by writing ...
>
> SELECT ...
> FROM a, b INNER JOIN c ON a.x=c.y
>
> The error goes away if you instead write ...
>
> SELECT ...
> FROM b, a INNER JOIN c ON A.x=c.y
>
> so you might try swapping
>
> FROM items i
> , nams.netids n
>
> Tried that,now I get:
> Unknown column 'n.badge'in 'on clause'
>
> So, whichever order I put them in, I get one of two errors.
>
> It appears that this bug will continue to break for me until it is
> fixed in the next version, hopefully.
>
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black[EMAIL PROTECTED]
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9
> cd7w92nB8uhOH2Y1+jAe4MU=
> =FOcN
> -END PGP SIGNATURE-
>
>
>
> What if, instead of using a comma, you use an explicit INNER JOIN? It's
> perfectly valid to leave out the ON clause of an INNER JOIN (creating a
> Cartesian product). I mention this because you don't seem to have a
term
> to use in an ON clause, unless you want to move the term
n.netid='jblack'
> from the WHERE clause.
>
> SELECT...
> FROM items i
> INNER JOIN nams.netids n
> INNER JOIN ...
> ...
>
> Does the problem remain? If it goes away, this would be useful
information
> to include in your bug report.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date:
10/12/2005
>
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date:
10/12/2005
>
Fantastic response!! Someone give this man a coffe mug or something!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: possible bug in mysql 5.0.13
James,
Both ...
SELECT ...
FROM a, b LEFT JOIN c ON a.x=c.y
and
SELECT ...
FROM a
LEFT JOIN B USING (x )
LEFT JOIN c ON a.x=c.y
work up to and including version 5.0.10, not in 5.0.11, 12 or 13.
http://bugs.mysql.com/bug.php?id=13832 reports...
"The two statements below are quite different from one another:
1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c
2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c
"Statement (1) above will likely continue to give an "Unknown column
't1.a' in
'on clause'" error, while statement (2) will likely function correctly
at some
point in the future.
"This bug report is in reference to statements like statement (2), and
no bug
reports that use a statement like statement (1) are duplicates of this
bug."
The behaviour of (1) above is also verified
(http://bugs.mysql.com/bug.php?id=13551), and that page explains...
"This is a change that was made in 5.0.15 [sic] to make MySQL more
compliant with the standard. According to the SQL:2003
::= FROM
::=
[ { }... ]
::=
|
::=
|
|
...
"Thus when you write
... FROM t1 , t2 LEFT JOIN t3 ON (expr)
it is parsed as
(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))
and not as
(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)
so, from expr you can only refer to columns of t2 and t3 - operands of
the join. Workaround - to put parentheses explicitly as in (2). Then
you can refer to t1 columns from expr.
Unfortunately, this change is not properly documented in the manual, it
will be fixed."
PB
http://www.artfulsoftware.com
-
[EMAIL PROTECTED] wrote:
James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 02:06:26 PM:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Peter Brawley wrote:
James,
You can reproduce that error by writing ...
SELECT ...
FROM a, b INNER JOIN c ON a.x=c.y
The error goes away if you instead write ...
SELECT ...
FROM b, a INNER JOIN c ON A.x=c.y
so you might try swapping
FROM items i
, nams.netids n
Tried that,now I get:
Unknown column 'n.badge'in 'on clause'
So, whichever order I put them in, I get one of two errors.
It appears that this bug will continue to break for me until it is
fixed in the next version, hopefully.
- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9
cd7w92nB8uhOH2Y1+jAe4MU=
=FOcN
-END PGP SIGNATURE-
What if, instead of using a comma, you use an explicit INNER JOIN? It's
perfectly valid to leave out the ON clause of an INNER JOIN (creating a
Cartesian product). I mention this because you don't seem to have a term
to use in an ON clause, unless you want to move the term n.netid='jblack'
from the WHERE clause.
SELECT...
FROM items i
INNER JOIN nams.netids n
INNER JOIN ...
...
Does the problem remain? If it goes away, this would be useful information
to include in your bug report.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > SELECT... > FROM items i > INNER JOIN nams.netids n > INNER JOIN ... > ... > Does the problem remain? If it goes away, this would be useful information > to include in your bug report. Thank you for the suggestion, but it led to an error of: Unknown column r.rid in on clause. Guess I will start working on creating a test db where I can demonstrate this bug, to make it easy to reproduce. - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTWgMikQgpVn8xrARAnaTAKCHTQDzZtL2Iwh09iwFZhKX6kJ3PACgi8u5 mFNisss5Yc4k/WlicBTG5lM= =Worc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 02:06:26 PM: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Peter Brawley wrote: > > James, > > > > You can reproduce that error by writing ... > > > > SELECT ... > > FROM a, b INNER JOIN c ON a.x=c.y > > > > The error goes away if you instead write ... > > > > SELECT ... > > FROM b, a INNER JOIN c ON A.x=c.y > > > > so you might try swapping > > > > FROM items i > > , nams.netids n > > Tried that,now I get: > Unknown column 'n.badge'in 'on clause' > > So, whichever order I put them in, I get one of two errors. > > It appears that this bug will continue to break for me until it is > fixed in the next version, hopefully. > > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black[EMAIL PROTECTED] > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.1 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9 > cd7w92nB8uhOH2Y1+jAe4MU= > =FOcN > -END PGP SIGNATURE- What if, instead of using a comma, you use an explicit INNER JOIN? It's perfectly valid to leave out the ON clause of an INNER JOIN (creating a Cartesian product). I mention this because you don't seem to have a term to use in an ON clause, unless you want to move the term n.netid='jblack' from the WHERE clause. SELECT... FROM items i INNER JOIN nams.netids n INNER JOIN ... ... Does the problem remain? If it goes away, this would be useful information to include in your bug report. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Brawley wrote: > James, > > You can reproduce that error by writing ... > > SELECT ... > FROM a, b INNER JOIN c ON a.x=c.y > > The error goes away if you instead write ... > > SELECT ... > FROM b, a INNER JOIN c ON A.x=c.y > > so you might try swapping > > FROM items i > , nams.netids n Tried that,now I get: Unknown column 'n.badge'in 'on clause' So, whichever order I put them in, I get one of two errors. It appears that this bug will continue to break for me until it is fixed in the next version, hopefully. - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9 cd7w92nB8uhOH2Y1+jAe4MU= =FOcN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Brawley wrote: > James, > > You can reproduce that error by writing ... > > SELECT ... > FROM a, b INNER JOIN c ON a.x=c.y > > The error goes away if you instead write ... > > SELECT ... > FROM b, a INNER JOIN c ON A.x=c.y I will try it. Thank you. Would this be a bug, or just something that should be documented? - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTUz7ikQgpVn8xrARArGNAJ9MrOEjMay9N4VirSvt9Zv/fZYE0ACfSOYD wqshJve8wnUiZv0vWqVNrCc= =Nhd7 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
James, You can reproduce that error by writing ... SELECT ... FROM a, b INNER JOIN c ON a.x=c.y The error goes away if you instead write ... SELECT ... FROM b, a INNER JOIN c ON A.x=c.y so you might try swapping FROM items i , nams.netids n PB - [EMAIL PROTECTED] wrote: James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 09:57:51 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Due to the complexity of my query I don't know how to get this down to a simple test case to demonstrate the error. This works under mysql 4.1.8 btw, so it is failing due to a change introduced recently. It also worked under mysql 5.0.9, but I haven't tested any of the beta versions between 9 and 13. Any suggestions as to what I may look at to make this simpler? I will be working on it in the meantime, to see what I find. mysql> INSERT INTO curuse(rid, start, badge, ip, card_type, fullname, dept, college, campus) SELECT 12612,1129124442, n.badge, 0, (SELECT role FROM nams.names WHERE badge=n.badge), (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.netid='jblack') AS fullnameTable, (SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1; ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause' +--+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +--+--+--+-+-++ | rid | int(11) | NO | PRI | NULL | auto_increment | | lid | int(11) | NO | MUL | 0 | | | itemtype | char(4) | NO | MUL | | | | label | char(12) | NO | UNI | | | | status | char(1) | NO | | | | | layoutx | int(11) | YES | | NULL | | | layouty | int(11) | YES | | NULL | | | theta | int(11) | YES | | NULL | | +--+--+--+-+-++ 8 rows in set (0.02 sec) mysql> describe curuse; +---+-+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-+---+ | rid | int(11) | NO | PRI | 0 | | | start | int(11) | NO | | 0 | | | badge | int(11) | NO | MUL | 0 | | | card_type | char(2) | NO | | | | | dept | char(3) | NO | | | | | college | char(2) | NO | | | | | campus | char(1) | NO | || | | fullname | varchar(24) | NO | | | | | ip | varchar(40) | NO | PRI | || | alive | int(11) | NO | | 0 | | +---+-+--+-+-+---+ 10 rows in set (0.02 sec) - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH A4h+LzTgcZns66WLG2xOp9c= =YIgR -END PGP SIGNATURE- Just to help the rest of us, here is his query reformatted only (no changes except for spacing and tabbing). Some of the longer CONCAT() functions will wrap but the majority of it should be much easier to read: INSERT INTO curuse( rid , start , badge , ip , card_type , fullname , dept , college , campus ) SELECT 12612 , 1129124442 , n.badge , 0 , (SELECT role FROM nams.names WHERE badge=n.badge) , ( SELECT IF( p.fullname IS NULL , (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge ) , p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON (sne.badge=p.badge) WHERE sne.netid='jblack'
re: possible bug in mysql 5.0.13
James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 09:57:51 AM: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Due to the complexity of my query I don't know how to get this down to a > simple test case to demonstrate the error. > > This works under mysql 4.1.8 btw, so it is failing due to a change > introduced recently. It also worked under mysql 5.0.9, but I haven't > tested any of the beta versions between 9 and 13. > > Any suggestions as to what I may look at to make this simpler? > > I will be working on it in the meantime, to see what I find. > > mysql> INSERT INTO curuse(rid, start, badge, ip, card_type, fullname, > dept, college, campus) > SELECT 12612,1129124442, n.badge, 0, > (SELECT role FROM nams.names WHERE badge=n.badge), > (SELECT IF(p.fullname IS NULL, > (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) > FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS > fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p > ON(sne.badge=p.badge) WHERE sne.netid='jblack') AS fullnameTable, > (SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments > d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND > d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT > f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE > f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !='' > ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM > nams.affiliations f, nams.roles r, nams.departments d WHERE > f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' > ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) FROM items i, > nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN > nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON > a.role=r.code > > LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) > > WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND > n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1; > > ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause' > > +--+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +--+--+--+-+-++ > | rid | int(11) | NO | PRI | NULL | auto_increment | > | lid | int(11) | NO | MUL | 0 | | > | itemtype | char(4) | NO | MUL | | | > | label | char(12) | NO | UNI | | | > | status | char(1) | NO | | | | > | layoutx | int(11) | YES | | NULL | | > | layouty | int(11) | YES | | NULL | | > | theta | int(11) | YES | | NULL | | > +--+--+--+-+-++ > 8 rows in set (0.02 sec) mysql> describe curuse; > > +---+-+--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +---+-+--+-+-+---+ > | rid | int(11) | NO | PRI | 0 | | > | start | int(11) | NO | | 0 | | > | badge | int(11) | NO | MUL | 0 | | > | card_type | char(2) | NO | | | | > | dept | char(3) | NO | | | | > | college | char(2) | NO | | | | > | campus | char(1) | NO | || | > | fullname | varchar(24) | NO | | | | > | ip | varchar(40) | NO | PRI | || > | alive | int(11) | NO | | 0 | | > +---+-+--+-+-+---+ > 10 rows in set (0.02 sec) > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black[EMAIL PROTECTED] > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.1 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH > A4h+LzTgcZns66WLG2xOp9c= > =YIgR > -END PGP SIGNATURE- > Just to help the rest of us, here is his query reformatted only (no changes except for spacing and tabbing). Some of the longer CONCAT() functions will wrap but the majority of it should be much easier to read: INSERT INTO curuse( rid , start , badge , ip , card_type , fullname , dept , college , campus ) SELECT 12612 , 1129124442 , n.badge , 0 , (SELECT role FROM nams.names WHERE badge=n.badge) , ( SELECT IF( p.fullname IS NULL , (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge ) , p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON (sne.badge=p.badge) WHERE sne.netid='jblack' ) AS fullnameTable , ( SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments d
re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Due to the complexity of my query I don't know how to get this down to a simple test case to demonstrate the error. This works under mysql 4.1.8 btw, so it is failing due to a change introduced recently. It also worked under mysql 5.0.9, but I haven't tested any of the beta versions between 9 and 13. Any suggestions as to what I may look at to make this simpler? I will be working on it in the meantime, to see what I find. mysql> INSERT INTO curuse(rid, start, badge, ip, card_type, fullname, dept, college, campus) SELECT 12612,1129124442, n.badge, 0, (SELECT role FROM nams.names WHERE badge=n.badge), (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.netid='jblack') AS fullnameTable, (SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1; ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause' +--+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +--+--+--+-+-++ | rid | int(11) | NO | PRI | NULL | auto_increment | | lid | int(11) | NO | MUL | 0 | | | itemtype | char(4) | NO | MUL | | | | label | char(12) | NO | UNI | | | | status | char(1) | NO | | | | | layoutx | int(11) | YES | | NULL | | | layouty | int(11) | YES | | NULL | | | theta | int(11) | YES | | NULL | | +--+--+--+-+-++ 8 rows in set (0.02 sec) mysql> describe curuse; +---+-+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-+---+ | rid | int(11) | NO | PRI | 0 | | | start | int(11) | NO | | 0 | | | badge | int(11) | NO | MUL | 0 | | | card_type | char(2) | NO | | | | | dept | char(3) | NO | | | | | college | char(2) | NO | | | | | campus | char(1) | NO | || | | fullname | varchar(24) | NO | | | | | ip | varchar(40) | NO | PRI | || | alive | int(11) | NO | | 0 | | +---+-+--+-+-+---+ 10 rows in set (0.02 sec) - -- "Love is mutual self-giving that ends in self-recovery." Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH A4h+LzTgcZns66WLG2xOp9c= =YIgR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible Bug? Left Join With OR Clause Takes Minutes
Scott Klarenbach <[EMAIL PROTECTED]> writes: > I'm using Mysql 5.0.7 and I've noticed the following very strange > functionality, perhaps someone can shed some light on it for me. Try using the "EXPLAIN" statement to get some insight into what MySQL is thinking. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Bug? Left Join With OR Clause Takes Minutes
I'm using Mysql 5.0.7 and I've noticed the following very strange functionality, perhaps someone can shed some light on it for me. 2 Tables (Request and Inventory) Request id (int), partNumber varchar(60) Inventory id(int), MPN varchar(60), MPNClean varchar(60) I have about 1500 request records, and 20,000 inventory records. The following query takes over 1.5 minutes to execute. select r.id from request r LEFT JOIN inventory i ON (i.MPN=r.partNumber OR i.MPNClean=r.partNumber); [~1.5 minutes to execute] I have no Idea why it would take so long. So I started playing around with the joins, and noticed something. If there is only one join criteria, it returns quickly: select r.id from request r LEFT JOIN inventory i ON (i.MPN=r.partNumber) [~1 second to execute] Similarly, an INNER join, regardless of the number of parameters, also returns quickly select r.id from request r INNER JOIN inventory i ON (i.MPN=r.partNumber OR i.MPNClean=r.partNumber); [~1 second to execute] select r.id from request r INNER JOIN inventory i ON (i.MPN=r.partNumber); [~1 second to execute] If someone could enlighten me as to why this is happening, I'd really appreciate it. Thanks, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible BUG in 'between' comparisons
>Description:
There appears to be a type promotion problem involving sql statements which
include a 'between' comparison and a decimal type field is one of the operands.
>How-To-Repeat:
CREATE TABLE `foo1` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo1` (`test1`, `test2`) VALUES ('97.50','154.30');
CREATE TABLE `foo2` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo2` (`test1`, `test2`) VALUES ('154.30','154.30');
CREATE TABLE `foo3` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo3` (`test1`, `test2`) VALUES (97.50,154.30);
CREATE TABLE `foo4` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo4` (`test1`, `test2`) VALUES (154.30,154.30);
select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
select * from foo3 left join foo4 on foo4.test2 between foo3.test1 and
foo3.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
1 row in set (0.00 sec)
mysql> select * from foo1 where test1 between 97.50 and 154.30;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
mysql> select * from foo1 where 154.30 between test1 and test2;
Empty set (0.00 sec)
mysql> select * from foo1 where test2 between 154.30 and test2;
Empty set (0.00 sec)
>Fix:
A work around is to include OR conditions that are equal comparisons to the
boundary condition of the BETWEEN. This query demonstrates a work around.
mysql> select * from foo1 where test2 between 154.30 and test2 or 154.30=test2;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
>Originator:Tom Allen
>Organization:
10 East Corp
>MySQL support: none
>Synopsis: BETWEEN comparisons with one or more DECIMAL type fields as
>operands don't handle boundary conditions properly
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release: mysql-4.1.12-max (MySQL Community Edition - Experimental (GPL))
>C compiler:2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux docs2 2.6.12 #1 SMP Mon Jun 20 12:08:43 EDT 2005 i686 unknown
unknown GNU/Linux
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-slackware-linux/3.2.3/specs
Configured with: ../gcc-3.2.3/configure --prefix=/usr --enable-shared
--enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld
--verbose --target=i486-slackware-linux --host=i486-slackware-linux
Thread model: posix
gcc version 3.2.3
Compilation info: CC='ccache gcc' CFLAGS='-O2 -mpentiumpro' CXX='ccache gcc'
CXXFLAGS='-O2 -mpentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Apr 16 2004 /lib/libc.so.6 ->
libc-2.3.2.so
-rwxr-xr-x1 root root 1458907 May 18 2003 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2467548 May 18 2003 /usr/lib/libc.a
-rw-r--r--1 root root 204 May 18 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Edition - Experimental (GPL)'
'--with-extra-charsets=complex' '--with-server-suffix=-max'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-berkeley-db' '--with-big-tables' '--with-raid'
'--with-readline' '--with-embedded-server' '--with-archive-storage-engine'
'--with-blackhole-storage-engine' '--with-ndbcluster'
'--with-example-storage-engine' '--with-innodb' 'CC=ccache gcc' 'CFLAGS=-O2
-mpentiumpro' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors' 'CXX=ccache gcc'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible BUG in 'between' comparisons
I have confirmed that this problem exhibits itself on the Mysql AB
compiled binaries that are compiled with gcc, both Standard and Max, but
does _not_ exhibit itself on the Mysql AB binary built with the Intel C++
Compiler 8.1 (mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz).
I have also verifed that the problem does _not_ exhibit on the mysql-4.12
server in Debian stable (Sarge), nor in Gentoo's latest ebuild.
I believe this problem likely stems from a compiler problem with the version
(2.95.3 20010315 SuSE) of GCC that Mysql AB compiles with.
# GCC: mysql-standard-4.1.12-pc-linux-gnu-i686.tar.gz
[EMAIL PROTECTED]:/usr/local# ls -l mysql
lrwxrwxrwx 1 root root 9 Jul 8 21:25 /usr/local/mysql -> mysql-gcc/
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql &
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql> select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqladmin shutdown
STOPPING server from pid file /usr/local/mysql/data/ponybox.pid
050708 21:30:36 mysqld ended
# ICC: mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz
[EMAIL PROTECTED]:/usr/local# rm mysql && ln -s mysql-icc mysql
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql &
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql> select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
--
Lester Hightower <[EMAIL PROTECTED]>
10East Corp.
On Fri, 8 Jul 2005 [EMAIL PROTECTED] wrote:
Description:
There appears to be a type promotion problem involving sql statements which
include a 'between' comparison and a decimal type field is one of the operands.
How-To-Repeat:
CREATE TABLE `foo1` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo1` (`test1`, `test2`) VALUES ('97.50','154.30');
CREATE TABLE `foo2` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo2` (`test1`, `test2`) VALUES ('154.30','154.30');
CREATE TABLE `foo3` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo3` (`test1`, `test2`) VALUES (97.50,154.30);
CREATE TABLE `foo4` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo4` (`test1`, `test2`) VALUES (154.30,154.30);
select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
select * from foo3 left join foo4 on foo4.test2 between foo3.test1 and
foo3.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
1 row in set (0.00 sec)
mysql> select * from foo1 where test1 between 97.50 and 154.30;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
mysql> select * from foo1 where 154.30 between test1 and test2;
Empty set (0.00 sec)
mysql> select * from foo1 where test2 between 154.30 and test2;
Empty set (0.00 sec)
Fix:
A work around is to include OR conditions that are equal comparisons to the
boundary condition of the BETWEEN. This query demonstrates a work around.
mysql> select * from foo1 where test2 between 154.30 and test2 or 154.30=test2;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
Originator: Tom Allen
Organization:
10 East Corp
MySQL support: none
Synopsis: BETWEEN comparisons with one or more DECIMAL type fields as
operands don't handle boundary conditions properly
Severity: <[ non-critical | serious | critical ] (one line)>
Priority: <[ low | medium | high ] (one line)>
Category: mysql
Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
Release:mysql-4.1.12-max (MySQL Community Edition - Experimental (GPL))
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
System: Linux docs2 2.6.12 #1 SMP Mon Jun 20 12:08:43 EDT 2005 i686 unknown
unknown GNU/Linux
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-sl
Re: possible bug left join and null
Thanks everyone for such quick and thorough responses! Quoting [EMAIL PROTECTED]: James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005 10:44:07 AM: The problem is something fairly simple but yet MySQL seems to make this complicated. Essentially, find a list of customers who have not bought product X ever. (Customers have orders, orders have order line items). All 3 coworkers independently arrived at the same sql which failed to work. Then, we wrote it as a subquery which has performance issue and finally rewrote it with a temp table and a join. However, it seems like what we had should have worked. Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway down the page I set out to create an identical schema and query in MySQL. CREATE TABLE `Customer` ( `id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `Customer` VALUES (1, 'bob'); INSERT INTO `Customer` VALUES (2, 'nathan'); CREATE TABLE `Order` ( `id` int(11) NOT NULL auto_increment, `customer_id` int(11) NOT NULL default '0', `order_date` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00'); CREATE TABLE `OrderLines` ( `order_id` int(11) NOT NULL default '0', `product_id` int(11) NOT NULL default '0', `quantity` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `OrderLines` VALUES (1, 5, 1); INSERT INTO `OrderLines` VALUES (1, 9, 1); INSERT INTO `OrderLines` VALUES (2, 15, 1); INSERT INTO `OrderLines` VALUES (2, 25, 1); Then, I run the following query: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL I would expect this to return a single row with Customer.id 2. Is there something obvious my coworkers and I are missing? James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com It's hard to remember where I picked this up but I once read that it's generally bad form to start with an outer join (LEFT or RIGHT JOIN) and move into an INNER JOIN like you are doing. Because if the rows from the Order table are optional to the results of the query, the rows from the OrderLines are transitively optional as well (if an Order row doesn't exist then there can't be any OrderLine rows either). So an equivalent form of your query could have been: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL; But this won't help you to determine if a Customer had NEVER ordered that product because you are including Order rows regardless of whether that order had a product #9 on it or not. I then tried a nested JOIN using parentheses like this and got no names: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN (`Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id WHERE Order.customer_id IS NULL; The unfiltered results of that join look like this(sorry if it wraps): SELECT * FROM Customer LEFT JOIN ( `Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id; ++++-+-+--++--+ | id | name | id | customer_id | order_date | order_id | product_id | quantity | ++++-+-+--++--+ | 1 | bob| 1 | 1 | -00-00 00:00:00 |1 | 9 | 1 | | 2 | nathan | 1 | 1 | -00-00 00:00:00 | NULL | NULL | NULL | | 1 | bob| 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | | 2 | nathan | 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | ++++-+-+--++--+ 4 rows in set (0.00 sec) Each customer has at least one order so the nested JOIN didn't work to find your answer either (BTW- nested joins are not documented as a valid syntax so I wasn't sure if it was going to work or not). However, I thought, why not do exactly what the original question stated: count how many times product 9 appears as a line item on an order and return the names of the customers where that count is 0. SELECT Customer.id , Customer.name , COUNT(orderlines.product_id) as Line
Re: possible bug left join and null
There is nothing wrong with what MySQL is doing. Your query is incorrect for what you are looking for. Step through your query and you'll see your error. SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id You now have a list of the all your Customers with and without orders. INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 You now joined the Customer/Order list with OrderLines with a product id of 9. Here is where your logic fails. You now have a list of all customers who ordered product 9. The list does not contain ANY customers without an order for product 9. WHERE Order.customer_id IS NULL Since you only have a list of customers who ordered product 9, you now filter out the entire result set. Change your inner join to a left join and your query should work. MySQL will step through your query in the order you wrote, building or filtering as it goes along. You can somewhat alter this order with LEFT and/or RIGHT joins. On Apr 21, 2005, at 10:44 AM, James Nobis wrote: The problem is something fairly simple but yet MySQL seems to make this complicated. Essentially, find a list of customers who have not bought product X ever. (Customers have orders, orders have order line items). All 3 coworkers independently arrived at the same sql which failed to work. Then, we wrote it as a subquery which has performance issue and finally rewrote it with a temp table and a join. However, it seems like what we had should have worked. Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway down the page I set out to create an identical schema and query in MySQL. CREATE TABLE `Customer` ( `id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `Customer` VALUES (1, 'bob'); INSERT INTO `Customer` VALUES (2, 'nathan'); CREATE TABLE `Order` ( `id` int(11) NOT NULL auto_increment, `customer_id` int(11) NOT NULL default '0', `order_date` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00'); CREATE TABLE `OrderLines` ( `order_id` int(11) NOT NULL default '0', `product_id` int(11) NOT NULL default '0', `quantity` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `OrderLines` VALUES (1, 5, 1); INSERT INTO `OrderLines` VALUES (1, 9, 1); INSERT INTO `OrderLines` VALUES (2, 15, 1); INSERT INTO `OrderLines` VALUES (2, 25, 1); Then, I run the following query: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL I would expect this to return a single row with Customer.id 2. Is there something obvious my coworkers and I are missing? James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug left join and null
James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005 10:44:07 AM: > The problem is something fairly simple but yet MySQL seems to make this > complicated. Essentially, find a list of customers who have not > bought product > X ever. (Customers have orders, orders have order line items). All > 3 coworkers > independently arrived at the same sql which failed to work. Then, we wrote it > as a subquery which has performance issue and finally rewrote it with a temp > table and a join. However, it seems like what we had should have worked. > > Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway > down the page I set out to create an identical schema and query in MySQL. > > CREATE TABLE `Customer` ( > `id` int(11) NOT NULL default '0', > `name` varchar(255) NOT NULL default '' > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > INSERT INTO `Customer` VALUES (1, 'bob'); > INSERT INTO `Customer` VALUES (2, 'nathan'); > > CREATE TABLE `Order` ( > `id` int(11) NOT NULL auto_increment, > `customer_id` int(11) NOT NULL default '0', > `order_date` datetime NOT NULL default '-00-00 00:00:00', > PRIMARY KEY (`id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; > > INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); > INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00'); > > CREATE TABLE `OrderLines` ( > `order_id` int(11) NOT NULL default '0', > `product_id` int(11) NOT NULL default '0', > `quantity` int(11) NOT NULL default '0' > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > INSERT INTO `OrderLines` VALUES (1, 5, 1); > INSERT INTO `OrderLines` VALUES (1, 9, 1); > INSERT INTO `OrderLines` VALUES (2, 15, 1); > INSERT INTO `OrderLines` VALUES (2, 25, 1); > > Then, I run the following query: > SELECT DISTINCT Customer.id, Customer.name > FROM Customer > LEFT JOIN `Order` ON Customer.id = Order.customer_id > INNER JOIN OrderLines ON Order.id = OrderLines.order_id > AND OrderLines.product_id =9 > WHERE Order.customer_id IS NULL > > I would expect this to return a single row with Customer.id 2. > > Is there something obvious my coworkers and I are missing? > > James Nobis > Web Developer > Academic Superstore > 223 W. Anderson Ln. Suite A110, Austin, TX 78752 > Voice: (512) 450-1199 x453 Fax: (512) 450-0263 > http://www.academicsuperstore.com > It's hard to remember where I picked this up but I once read that it's generally bad form to start with an outer join (LEFT or RIGHT JOIN) and move into an INNER JOIN like you are doing. Because if the rows from the Order table are optional to the results of the query, the rows from the OrderLines are transitively optional as well (if an Order row doesn't exist then there can't be any OrderLine rows either). So an equivalent form of your query could have been: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL; But this won't help you to determine if a Customer had NEVER ordered that product because you are including Order rows regardless of whether that order had a product #9 on it or not. I then tried a nested JOIN using parentheses like this and got no names: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN (`Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id WHERE Order.customer_id IS NULL; The unfiltered results of that join look like this(sorry if it wraps): SELECT * FROM Customer LEFT JOIN ( `Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id; ++++-+-+--++--+ | id | name | id | customer_id | order_date | order_id | product_id | quantity | ++++-+-+--++--+ | 1 | bob| 1 | 1 | -00-00 00:00:00 |1 | 9 | 1 | | 2 | nathan | 1 | 1 | -00-00 00:00:00 | NULL | NULL | NULL | | 1 | bob| 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | | 2 | nathan | 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | ++++-+-+--++--+ 4 rows in set (0.00 sec) Each customer has at least one order so the nested JOIN didn't work to find your answer either (BTW- nested joins are not documented as a valid syntax so I wasn't sure if it was going to work or not). However, I thought, why not do exactly what the original question stated: count how many times product 9 appears as a line item on an order and return the names of the customers where tha
Re: possible bug left join and null
From: "James Nobis" > SELECT DISTINCT Customer.id, Customer.name > FROM Customer > LEFT JOIN `Order` ON Customer.id = Order.customer_id > INNER JOIN OrderLines ON Order.id = OrderLines.order_id > AND OrderLines.product_id =9 > WHERE Order.customer_id IS NULL I expect customers to have placed at least one order, or can one have customers which have not a single order? Do you want these "customers" included in the output? Anyway, I expect that you want the order of all customers checked; this can be done with a (inner) join: `Customer` JOIN `Order` ON `Customer`.`id` = `Order`.`customer_id` Then you left-join this with the order lines to find out all the products and check for an empty order id: SELECT DISTINCT Customer.id, Customer.name FROM Customer JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE OrderLines.order_id IS NULL This returns: +-+---+ | Customer.id | Customer.name | +-+---+ | 2 |nathan | +-+---+ Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible bug left join and null
The problem is something fairly simple but yet MySQL seems to make this complicated. Essentially, find a list of customers who have not bought product X ever. (Customers have orders, orders have order line items). All 3 coworkers independently arrived at the same sql which failed to work. Then, we wrote it as a subquery which has performance issue and finally rewrote it with a temp table and a join. However, it seems like what we had should have worked. Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway down the page I set out to create an identical schema and query in MySQL. CREATE TABLE `Customer` ( `id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `Customer` VALUES (1, 'bob'); INSERT INTO `Customer` VALUES (2, 'nathan'); CREATE TABLE `Order` ( `id` int(11) NOT NULL auto_increment, `customer_id` int(11) NOT NULL default '0', `order_date` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00'); CREATE TABLE `OrderLines` ( `order_id` int(11) NOT NULL default '0', `product_id` int(11) NOT NULL default '0', `quantity` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `OrderLines` VALUES (1, 5, 1); INSERT INTO `OrderLines` VALUES (1, 9, 1); INSERT INTO `OrderLines` VALUES (2, 15, 1); INSERT INTO `OrderLines` VALUES (2, 25, 1); Then, I run the following query: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL I would expect this to return a single row with Customer.id 2. Is there something obvious my coworkers and I are missing? James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug with some collations?
I have table with only one column set to latin1_general_ci collation, and I get following error: *Illegal mix of collations (latin1_general_cs,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='* Client (PHP5 with mysql functions) uses utf-8 character set. MySQL is 4.1.10a. When I change collation of that column everything works fine. MySQL server knows client's character set, it knows column's character set but refuses to do conversion. I tried using other latin1 collations and here's result. latin1_bin OK latin1_danish_ci BAD latin1_general_ci BAD latin1_general_cs BAD latin1_german1_ci BAD latin1_german2_ci OK latin1_spanish_ci BAD latin1_sweedish_ci OK All latin2 collations are OK. Possible cause of error is utf8 word containing characters that don't exist in latin1 character set. For example: "SELECT * FROM table WHERE word='abaÅur'". But shouldn't all latin1 collatins return same error? I also noticed that PHP's mysqli query function doesn't fail with error. It just returns empty set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible bug with wait_timeout
Hello. The value of wait_timeout is initialized from wait_timeout variable or from the global interactive_timeout variable, depending on the type of client. Put interactive_timeout=10 in your config file. See: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi all, > > In version 4.0.18 when setting the wait_timeout variable to 10 in > my.cnf, it seems to work when looking at 'mysqladmin variables' as it is > indeed showing up as 10. > > However, when in the mysql client and I do a 'show variables' it is > showing up with the default value of 28800. > > I'm certain that I've connected to the same server and was using all the > tools from /usr/bin/mysql and specifying paths like this > bin/safe_mysqld, bin/mysqladmin, bin/mysql etc... > > Anyone seen this before or am I going crazy? > > Cheers, > > Andrew > > query, sql > -- 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]
Possible bug with wait_timeout
Hi all, In version 4.0.18 when setting the wait_timeout variable to 10 in my.cnf, it seems to work when looking at 'mysqladmin variables' as it is indeed showing up as 10. However, when in the mysql client and I do a 'show variables' it is showing up with the default value of 28800. I'm certain that I've connected to the same server and was using all the tools from /usr/bin/mysql and specifying paths like this bin/safe_mysqld, bin/mysqladmin, bin/mysql etc... Anyone seen this before or am I going crazy? Cheers, Andrew query, sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug in 4.1.7
Hello, I have a query that works fine on 4.0.20 but doesn't work in 4.1.7. I want to search all the rows of table AULE that don't have a record in table OCCUPAZIONI so the query is: select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; After some tests I find that an index changes the results. This is a sql command file that reproduce the problem on my Linux Fedora core 1 with MySql 4.1.7 : mysql> select version(); ++ | version() | ++ | 4.1.7-standard | ++ 1 row in set (0.00 sec) ==CUT DROP DATABASE IF EXISTS bug; create database bug; use bug; CREATE TABLE AULE ( AUL_ID int(11) NOT NULL auto_increment, PRIMARY KEY (AUL_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE OCCUPAZIONI ( OCC_ID int(11) NOT NULL auto_increment, OCC_ID_AUL int(11) NOT NULL, OCC_DATA date, PRIMARY KEY (OCC_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO AULE VALUES (1); INSERT INTO AULE VALUES (2); INSERT INTO AULE VALUES (3); INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10'); select "Before index"; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; alter table OCCUPAZIONI add KEY OCC_ID_AUL (OCC_ID_AUL); select "After Index"; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; ==CUT Results: [EMAIL PROTECTED] bugs]# mysql < v.sql Before index Before index AUL_ID OCC_ID 2 NULL 3 NULL After Index After Index [EMAIL PROTECTED] bugs]# Is it a bug? Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A possible bug
thanks, but some of the tables have to be in MyIsam format, and i cannot see a workaround for them. (actually I discovered that the integrity of the data exported is just fine, but it seems that the server hangs trying to close the file handle. - Original Message - From: "Nickolai Nielsen" <[EMAIL PROTECTED]> To: "Leonardo Javier Belén" <[EMAIL PROTECTED]> Sent: Wednesday, July 28, 2004 9:34 AM Subject: SV: A possible bug Hi Leonardo i had a similar problem, it was with InnoDB tables, so i changed these values: # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=100M in the my.ini Nickolai -Oprindelig meddelelse- Fra: Leonardo Javier Belén Sendt: 27. juli 2004 22:04 Til: [EMAIL PROTECTED] Emne: A possible bug Hi all, I am using MySQL ver. 4.1.3-beta on Windows 2000 pro and I found that, whenever i try to export data using the "into outfile" clause of the select command the server hangs and i need to restart the service. Has anyone faced the same problem, and if it is, how have you resolve it? I think it is wrong because the same select string on MySQL ver. 4.0.20 works fine. Leonardo J. Belén. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A possible bug
Hi all, I am using MySQL ver. 4.1.3-beta on Windows 2000 pro and I found that, whenever i try to export data using the "into outfile" clause of the select command the server hangs and i need to restart the service. Has anyone faced the same problem, and if it is, how have you resolve it? I think it is wrong because the same select string on MySQL ver. 4.0.20 works fine. Leonardo J. Belén. query mysql select query mysql select query mysql select query mysql select query mysql select query mysql select query mysql select query mysql select query mysql select query mysql select -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug in mysqldump with 5.0.0/InnoDB & longblobs?
I have been running into problems with mysqldump. I have not been able to figure out why mysqldump isnt working as I hope it would. Anyway I thought I'll make the mysql-list aware of the problem and hopefully it will help someone. Or better yet someone knows what I am doing wrong and could point it out to me ;) Problem: After running mysqldump my longblob data gets corrupted. All other table data is dumped correctly. I have tried mysqldump on both a Linux and OpenBSD system with the same result. If I used "SELECT INTO OUTFILE" I was able to dump the longblod data and later use "LOAD DATA INFILE" to restore it correctly. How I use mysqldump: mysqldump --add-drop-table --force --single-transaction -u user -p password myDB > dumpfile My linux specs: MySQL 5.0.0-alpha InnoDB tables My OpenBSD specs: MySQL 5.0.0 debug binary release from mysql.com InnoDB tables /Jonas
Re: Possible Bug: Dropping Trailing White Space
On Thu 2003-03-20 at 14:01:52 -0500, [EMAIL PROTECTED] wrote: > I have a table with a column defined as the following. > > hash CHAR(16) BINARY NOT NULL > > Most data inserts fine. However, if data has trailing white space > (ASCII character 32), it seems to be getting truncated by MySQL during > the insert, such that subsequent queries to find the values fail. Full > example below. > > Any Ideas? That is the documented behaviour (http://www.mysql.com/doc/en/CHAR.html) and is an (implementation dependend) feature of SQL. If you don't want it, you had to use VARCHAR instead. But there is a known bug with MySQL (see above and http://www.mysql.com/doc/en/Open_bugs.html). As the bug description implies, use a TEXT type like TINYTEXT instead. HTH, Benjamin. PS: Btw, the BINARY keyword only influences sort behaviour, nothing else. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible Bug: Dropping Trailing White Space
I have a table with a column defined as the following.
hash CHAR(16) BINARY NOT NULL
Most data inserts fine. However, if data has trailing white space
(ASCII character 32), it seems to be getting truncated by MySQL during
the insert, such that subsequent queries to find the values fail. Full
example below.
Any Ideas?
Thank you,
Gabriel
_
Gabriel Weinberg
[EMAIL PROTECTED]
INFO ABOUT SYSTEM:
bash-2.05b$ uname -a
FreeBSD prd01.navacity.com 5.0-RELEASE FreeBSD 5.0-RELEASE #0: Thu Jan
16 22:16:53 GMT 2003
[EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386
bash-2.05b$ mysql --version
mysql Ver 11.18 Distrib 3.23.55, for portbld-freebsd5.0 (i386)
EXAMPLE:
mysql> CREATE TABLE test (hash CHAR(16) BINARY NOT NULL, UNIQUE INDEX
(hash));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO test (hash) VALUES ('abcdefghiklmnop ');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM test;
+-+
| hash|
+-+
| abcdefghiklmnop |
+-+
1 row in set (0.00 sec)
mysql>
mysql> SELECT hash FROM test WHERE hash = 'abcdefghiklmnop ';
Empty set (0.00 sec)
mysql>
mysql> SELECT hash FROM test WHERE hash = 'abcdefghiklmnop';
+-+
| hash|
+-+
| abcdefghiklmnop |
+-+
1 row in set (0.00 sec)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug in mysql 4.0.12 Install for Win32
To replicate: 1. Start with a machine running Pentium 4, Windows XP Pro. 2. Map a "New Network Place" to: \\somecomputer\C$ Where \\somecomputer contains an install, in the root directory, (i.e. C:\mysql,) of mysql already. The remote install in this case was version 3.23.56. 3. Attempt to install mysql in the C:\mysql directory of the local machine. Somehow the installer locates the install at \\somecomputer\C$\mysql and points the NT service at THAT binary, not the one installed locally. I woudn't mention this, except that it's SO odd... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug when compiling MySQL using libz.so on Solaris 2.6
>Description: We are running MySQL 3.23.53 on Solaris 2.6. There seems to be a problem with MySQL finding libz.so. A plain vanilla installation (using ./configure with no extra arguments) results in make carping and then exiting. We thought we had a sort of workaround in passing the following arguments to ./configure (using tcsh): env LDFLAGS="-L/usr/local/lib -R/usr/local/lib" ./configure --prefix=/usr/local/mysql-3.23.53 --with-client-ldflags="-R/usr/local/lib -L/usr/local/lib" --with-mysqld-ldflags="-R/usr/local/lib -L/usr/local/lib" Before running make, we set LDFLAGS again: env LDFLAGS="-L/usr/local/lib -R/usr/local/lib" make Ommitting this step resulted in MySQL compiling and installing; however, trying to connect to the database using DBI failed as libz.so could not be found. This was remedied by explicitly setting the environment as above. Running ldd in the mysql/lib/mysql directory produced the following output: ldd /usr/local/mysql/lib/mysql/libmysqlclient.so.10.0.0 libz.so => (file not found) libsocket.so.1 =>/usr/lib/libsocket.so.1 libnsl.so.1 => /usr/lib/libnsl.so.1 libm.so.1 => /usr/lib/libm.so.1 libc.so.1 => /usr/lib/libc.so.1 libdl.so.1 =>/usr/lib/libdl.so.1 libmp.so.2 =>/usr/lib/libmp.so.2 /usr/platform/SUNW,Ultra-Enterprise/lib/libc_psr.so.1 This behaviour was finally corrected by editing the Makefile in the libmysql installation subdirectory as follows: LINK = $(LIBTOOL) --mode=link $(CCLD) $(AM_CFLAGS) $(CFLAGS) $(LDFLAGS) -o $@ was changed to LINK = env LD_RUN_PATH=/usr/local/lib $(LIBTOOL) --mode=link $(CCLD) $(AM_CFLAGS) $(CFLAGS) $(LDFLAGS) -o $@ Adding LD_RUN_PATH seems to have solved the problem once and for all. The problem first occurred when trying to install MySQL 3.23.51; our previous version (3.23.41) was just fine. >How-To-Repeat: Attempt to install MySQL on Solaris 2.6 using libz.so without taking one or more of the aforementioned steps. >Fix: See above. >Submitter-Id: >Originator: Jillian-Beth Stamos-Kaschke >Organization: >MySQL support: [none| email support | extended email support ] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.53 (Source distribution) >Environment: System: SunOS segfault 5.6 Generic_105181-33 sun4u sparc SUNW,Ultra-Enterprise Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc GCC: Reading specs from /usr/local/gcc-2.95.3/lib/gcc-lib/sparc-sun-solaris2.6/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='-L/usr/local/lib -R/usr/local/lib' LIBC: -rw-r--r-- 1 bin bin 1621560 Sep 10 00:37 /lib/libc.a lrwxrwxrwx 1 root root 11 Jun 9 2000 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 1025548 Sep 10 00:37 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1621560 Sep 10 00:37 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Jun 9 2000 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 1025548 Sep 10 00:37 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql-3.23.53 '--with-client-ldflags=-R/usr/local/lib -L/usr/local/lib' '--with-mysqld-ldflags=-R/usr/local/lib -L/usr/local/lib' 'LDFLAGS=-L/usr/local/lib -R/usr/local/lib' Perl: This is perl, version 5.005_02 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug with --localstatedir=/abcd configure option
>Description: When configuring with the --localstatedir option, any directory used that is longer than 3 characters has only those 3 characters used >How-To-Repeat: compile with the --localstatedir=/abcd or any 4 or more character directory name >Fix: workaround: use a symlink from $PREFIX/var to the directory you wish to use >Submitter-Id: [EMAIL PROTECTED] >Originator:MySQL Server >Organization: >MySQL support: none >Synopsis: possible bug with --localstatedir=/abcd >Severity: non-critical >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-3.23.51 Source distribution >Server: ../client/mysqladmin Ver 8.23 Distrib 3.23.51, for pc-linux-gnu on i686 Copyright C 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.51 Protocol version10 Connection Localhost via UNIX socket UNIX socket /dbdata/billing/mysql.sock Uptime: 12 min 32 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 8 Flush tables: 1 Open tables: 2 Queries per second avg: 0.007 >Environment: ibm x342, redhat 7.2, gcc-2.96-98, automake-1.4p5-2 System: Linux billdb3 2.4.18 #2 SMP Fri Aug 23 14:26:08 GMT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 Red Hat Linux 7.1 2.96-98 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 23 14:15 /lib/libc.so.6 -> libc-2.2.4.so -rwxr-xr-x1 root root 1285788 Apr 2 16:58 /lib/libc-2.2.4.so -rw-r--r--1 root root 27332668 Apr 2 16:42 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 2 16:42 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 23 14:15 /usr/lib/libc-client.a -> c-client.a Configure command: ./configure --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-extra-charsets=none --enable-thread-safe-client --with-server-suffix=-max --with-innodb --prefix=/opt/mysql --localstatedir=/dblogs Michael Komitee VONAGE 2147 Route 27 Edison, NJ 08817 T: 732.528.2674 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug?
Rich, Friday, July 26, 2002, 5:59:37 PM, you wrote: RA> Looks as if the MySQL DB crashed and recovered. RA> Linux 2.4.18-5smp #1 SMP RA> MySQL version 3.23.51 >>From the MySQL server while running mtop 26JUL02 ~6:45AM PST: RA> Unable to execute show procs [Lost connection to MySQL server during query] RA> Stack Trace: RA> at main::__ANON__(/usr/local/bin/mtop:446) RA> at main(/usr/local/bin/mtop:943) Have you installed MySQL from source or from binary distribution? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug?
Pada Fri, 26 Jul 2002 07:59:37 -0700 "Rich Amick" <[EMAIL PROTECTED]> menulis : > Searched G for mysqld_list_processes: > "The above happens if a new user logs in at the same time you do > mysql_list_processes(). This is fixed in the newest MySQL 3.22 > version!" > > --We are using version 3.23.51 - shouldn't be a problem? Still !!! :D Don't do show processlist TOO OFTEN !!! It can caused the crashes ... Before this I run : show processlist every one minutes ---> my server every 2 week restart :)) Now, I'm doing show processlist only once per hour ---> see my signature ... still holding on ;-) -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 35 days, Queries : 355.724 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug?
Looks as if the MySQL DB crashed and recovered. Linux 2.4.18-5smp #1 SMP MySQL version 3.23.51 >From the MySQL server while running mtop 26JUL02 ~6:45AM PST: Unable to execute show procs [Lost connection to MySQL server during query] Stack Trace: at main::__ANON__(/usr/local/bin/mtop:446) at main(/usr/local/bin/mtop:943) >From the error log (/data/mysql/var/*.err): mysqld got signal 11; Stack trace: 0x80b980e 0x40027f75 0x400290c6 0x400261cc 0x81146cb 0x80c19e1 0x80c3117 0x80bf55a 0x80bea14 Built symbol file: nm -n /data/mysql/libexec/mysqld > /tmp/mysqld.sym Created stack file: /data/mysql/var/errors/mysqld.stack.200207260645 Tried to find reason for crash: /data/mysql/bin/resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack Got: 0x80b980e handle_segfault__Fi + 406 0x40027f75 _end + 937971053 0x400290c6 _end + 937975486 0x400261cc _end + 937963460 0x81146cb mysqld_list_processes__FP3THDPCcb + 1915 0x80c19e1 mysql_execute_command__Fv + 6805 0x80c3117 mysql_parse__FP3THDPcUi + 211 0x80bf55a do_command__FP3THD + 1374 0x80bea14 handle_one_connection__FPv + 592 Searched G for handle_segfault: Only results for Mac OS and errors on compilation of MySQL Searched G for mysqld_list_processes: "The above happens if a new user logs in at the same time you do mysql_list_processes(). This is fixed in the newest MySQL 3.22 version!" --We are using version 3.23.51 - shouldn't be a problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: possible bug: alter table trashed foreign key constraints in innodb
Chuck, Tuesday, July 09, 2002, 4:26:31 AM, you wrote: CS> In version 3.23.49a when using an innodb table, "alter table" appears to CS> corrupt foreign key constraints. Try the following test case: It's described in the MySQL manual: http://www.mysql.com/doc/S/E/SEC446.html and fixed since 3.23.50 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: possible bug: alter table trashed foreign key constraints in innodb
Chuck, - Original Message - From: "Chuck Simmons" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, July 09, 2002 4:29 AM Subject: possible bug: alter table trashed foreign key constraints in innodb > sql query > > In version 3.23.49a when using an innodb table, "alter table" appears to > corrupt foreign key constraints. Try the following test case: > > " > create table test_base ( > base_id int not null, > primary key (base_id) > ) type = innodb; > > create table test_ref ( > base_id int not null, > ref_id int not null, > primary key (base_id, ref_id), > foreign key (base_id) references test_base (base_id) > ) type = innodb; > > insert test_base (base_id) values (1); > insert test_ref (base_id, ref_id) values (1, 1); > > alter table test_base add column value int not null; > > insert test_ref (base_id, ref_id) values (1, 2); > " > > The final insert fails with > " > mysql> insert test_ref (base_id, ref_id) values (1, 2); > ERROR 1216: Cannot add a child row: a foreign key constraint fails > " > > which suggests that the foreign key constraint has become hosed? the fact that ALTER TABLE spoiled foreign key definitions was a documented bug. It was fixed in 3.23.50. > Chuck Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
possible bug: alter table trashed foreign key constraints in innodbtables
sql query In version 3.23.49a when using an innodb table, "alter table" appears to corrupt foreign key constraints. Try the following test case: " create table test_base ( base_id int not null, primary key (base_id) ) type = innodb; create table test_ref ( base_id int not null, ref_id int not null, primary key (base_id, ref_id), foreign key (base_id) references test_base (base_id) ) type = innodb; insert test_base (base_id) values (1); insert test_ref (base_id, ref_id) values (1, 1); alter table test_base add column value int not null; insert test_ref (base_id, ref_id) values (1, 2); " The final insert fails with " mysql> insert test_ref (base_id, ref_id) values (1, 2); ERROR 1216: Cannot add a child row: a foreign key constraint fails " which suggests that the foreign key constraint has become hosed? Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug -- enum element is single space, but this is invalid as
David,
Tuesday, June 11, 2002, 3:28:52 PM, you wrote:
DH> I have encountered a strange problem with the enum type in version
DH> 3.23.49. It may be a bug, but I'd be grateful to hear comments.
DH> Here's a short example which illustrates the problem. First, I create
DH> a table with an enum column whose allowed elements are single letters,
DH> including a single space:
DH> CREATE TABLE cats (
DH> name VARCHAR(12) NOT NULL,
DH> gender ENUM(' ', 'F', 'M') NOT NULL,
DH> PRIMARY KEY(name)
DH> );
[skip]
DH> Notice that the table definition statement now explicitly states that
DH> the default value for the "gender" column is ' '. I didn't specify
DH> this explicitly when I defined the table originally.
DH> Now, when I try to read the backup file back into MySQL, I get this
DH> error:
DH> ERROR 1067 at line 12: Invalid default value for 'gender'
DH> But *why* should this happen? MySQL seems to be happy enough to
DH> let me include a single space as an element of the enumeration,
DH> but it won't let me specify it explicitly as the default value.
DH> This behaviour can be replicated at will in versions 3.23.38 and
DH> 3.23.49 of MySQL, running on both Compaq Alpha OSF5.1 and Red
DH> Hat Linux 7.2 for i386.
It was a bug that was fixed. In 4.0.2 and 3.23.51. ' ' is a valid
value, too.
DH> Thanks in advance
DH> David Harper
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug -- enum element is single space, but this is invalid as default
I have encountered a strange problem with the enum type in version
3.23.49. It may be a bug, but I'd be grateful to hear comments.
Here's a short example which illustrates the problem. First, I create
a table with an enum column whose allowed elements are single letters,
including a single space:
CREATE TABLE cats (
name VARCHAR(12) NOT NULL,
gender ENUM(' ', 'F', 'M') NOT NULL,
PRIMARY KEY(name)
);
Then I populate the table with a couple of records, specifying only the
name fields:
INSERT INTO cats(name) VALUES('Dinah'),('Molly');
When I check the contents of the table, everything looks fine:
mysql> select * from cats;
+---++
| name | gender |
+---++
| Dinah ||
| Molly ||
+---++
2 rows in set (0.00 sec)
The "gender" column has been set to the default value, a single space,
but that's impossible to see in the query above. This query shows
that the "gender" column is *not* the empty string:
mysql> select name,length(gender) from cats;
+---++
| name | length(gender) |
+---++
| Dinah | 1 |
| Molly | 1 |
+---++
2 rows in set (0.00 sec)
And for completeness, this query shows the index value of the "gender"
column, which is the default (1) in both cases:
mysql> select name,gender+0 from cats;
+---+--+
| name | gender+0 |
+---+--+
| Dinah |1 |
| Molly |1 |
+---+--+
2 rows in set (0.00 sec)
Now I run mysqldump to save that table to a file, using the --opt
option:
-- MySQL dump 8.21
--
-- Host: pcs3Database: test
-
-- Server version 3.23.49-log
--
-- Table structure for table 'cats'
--
DROP TABLE IF EXISTS cats;
CREATE TABLE cats (
name varchar(12) NOT NULL default '',
gender enum(' ','F','M') NOT NULL default ' ',
PRIMARY KEY (name)
) TYPE=MyISAM;
/*!4 ALTER TABLE cats DISABLE KEYS */;
--
-- Dumping data for table 'cats'
--
LOCK TABLES cats WRITE;
INSERT INTO cats VALUES ('Dinah',' '),('Molly',' ');
/*!4 ALTER TABLE cats ENABLE KEYS */;
UNLOCK TABLES;
Notice that the table definition statement now explicitly states that
the default value for the "gender" column is ' '. I didn't specify
this explicitly when I defined the table originally.
Now, when I try to read the backup file back into MySQL, I get this
error:
ERROR 1067 at line 12: Invalid default value for 'gender'
But *why* should this happen? MySQL seems to be happy enough to
let me include a single space as an element of the enumeration,
but it won't let me specify it explicitly as the default value.
This behaviour can be replicated at will in versions 3.23.38 and
3.23.49 of MySQL, running on both Compaq Alpha OSF5.1 and Red
Hat Linux 7.2 for i386.
Thanks in advance
David Harper
Wellcome Trust Sanger Institute, Cambridge, England
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug? MySQL table names
Hi, first of all: I'm currently not reading this mailing list, so if you answer I would be happy to receive a Cc: I'm currently trying to switch a web site on a complete new machine. The old machine was runnig MySQL 3.22.32, the new machine runs on 3.23.49 The application used Phorum for generating a web based forum for each article. Table name was a md5() string in order to get a unique table name for each forum. I dumped the whole database and tried to read it via mysql command line tool on the new machine. Some tables were created correctly: | 002ace3219ab50ab4d277e7357355f25| | 002ace3219ab50ab4d277e7357355f25_bodies | | 00bcd73fc818f7b57c48ce8d140dab70| | 00bcd73fc818f7b57c48ce8d140dab70_bodies | | 00bcd73fc818f7b57c48ce8d140dab70_seq| But with one table (table structure the same as the above tables) MySQL reported error 1064: table name: 021e0a5d20e6bf8211cfe55bd003a7c2 First of all I've seen no difference to the above table names which were created correctly. Then I tried to use ph_021e0a5d20e6bf8211cfe55bd003a7c2 as table name, and everything was okay. But this is only a workaround, so I tried to look up into the docs regarding legal naming conventions. It seemed to be correct as the documentation said that everything with digits and chars is okay. But then I saw: > It is recommended that you do not use names like 1e, because an expression > like 1e+1 is ambiguous. It may be interpreted as the expression 1e + 1 or as > the number 1e+1. and saw that the table name which was not working has had an "e" as the 4th character: 021e0a5d20e6bf8211cfe55bd003a7c2 ^ I changed the "e" to an a: 021a0a5d20e6bf8211cfe55bd003a7c2 and everything worked fine. So, my questions: 1.) is this a bug? Regarding the passage in the documentation I thought only table names like "1e" or "2e" are not correct 2.) why does MySQL think in 1e dimensions when having 021e0a5d20e6bf8211cfe55bd003a7c2 as a table name? 3.) if it is not a bug, should the documentation be extended? 4.) where am I wrong with my thoughts? 5.) if I'm not wrong, what can I do besides changing the table name to prefix_md5? TIA, Björn. -- PHP-Support * realitätsnahe Performance-Messungen mit Code-Analyse Webapplikationsentwicklung * PHP-Schulungen * Consulting 0700-THINKPHP -*- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB, possible bug?
Hi! >>>>> "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes: Heikki> Andrei, Heikki> this is probably not a bug in InnoDB. In theory, 4000 random disk seeks Heikki> would use more time than scanning the whole table of 700 000 rows. Heikki> The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over Heikki> table scans. That may solve the problem here. Heikki> On the other hand, the fact that MySQL refuses to use the index specified in Heikki> the USE INDEX clause may be a bug. I have forwarded this email to MySQL Heikki> developers. USE INDEX ... only tells MySQL that it should only consider using one of the named index to resolve the query. MySQL is however still free to use a table scan if finds the given index not suitable for resolving the query. Heikki> Best regards, Heikki> Heikki Tuuri Heikki> Innobase Oy Heikki> --- Heikki> Order technical MySQL/InnoDB support at https://order.mysql.com/ Heikki> See http://www.innodb.com for the online manual and latest news on InnoDB Heikki> - Original Message - Heikki> From: "Andrei Cojocaru" <[EMAIL PROTECTED]> Heikki> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mysql List" Heikki> <[EMAIL PROTECTED]> Heikki> Sent: Wednesday, May 22, 2002 3:16 AM Heikki> Subject: Re: InnoDB, possible bug? >> I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: >> select count(*) from newsentries10 where playerid=28575 and type=2; >> +--+ >> | count(*) | >> +--+ >> | 4218 | >> +--+ >> 1 row in set (13.81 sec) >> mysql> select count(*) from newsentries10 where playerid=28575 and type=2; >> +--+ >> | count(*) | >> +--+ >> | 3705 | >> +--+ Do you know why the result differs in this case ? mysql> explain select straight_join >> pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp Heikki> from >> newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and >> pn.newsid=ne.newsid; Heikki> +---++---+-+-+---+-- >> --++ >> | table | type | possible_keys | key | key_len | ref | >> rows | Extra | >> Heikki> +---++---+-+-+---+-- >> --++ >> | ne| ALL| list_news,delete_news | NULL|NULL | NULL | >> 774878 | where used | >> | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | >> 1 || >> Heikki> +---++---+-+-+---+-- > show index from newsentries10; >> > > | newsentries10 | 1 | list_news |1 | playerid >> | >> > A >> > > | 0 | NULL | NULL | | >> > > | newsentries10 | 1 | list_news |2 | type Heikki, something is a bit strange here. In this case MySQL will ask the InnoDB table handler of how many rows matches the key range (ne.playerid,ne.type) [28575, 2] In this case, InnoDB should return about 4000 rows, but it appears that it returns 77 rows. "Andrei", could you upload a copy of the tables to ftp://support.mysql.com/pub/mysql/secret so that Heikki could test this ? Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug?
Hi Egor, Thanks for your reply. > What table was deleted? report_YYYMMDD? Yes. > RV> MySQL Log file says nothing. > > Nothing about table delete or nothing about error? Nope. Robert Vetter Internet Application Developer Kontor23 GmbH Ottenser Hauptstrasse 56-62 22765 Hamburg Tel.: 040/380893-14 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug?
Robert, Wednesday, May 22, 2002, 12:18:22 PM, you wrote: RV> I have a problem in an Apache module which connects to MySQL using C RV> API for counting banner views and clicks. RV> This module creates a report table for each day. This table is called RV> report_MMDD, where MMDD is the current day. To keep thing RV> simple I just make following query first: RV> CREATE TABLE IF NOT EXISTS report_MMDD (PRIMARY KEY (banner_id)) RV> SELECT id as banner_id,0 as clicks, 0 as views from banners. [skip] RV> Well, funny things are happening here. After the first request the RV> table is created and everything is fine. But when the next one comes RV> the table is deleted! What table was deleted? report_YYYMMDD? RV> MySQL Log file says nothing. Nothing about table delete or nothing about error? RV> Can somone tell me what's going on? RV> MySQL Version is 3.23.49 and runs on a Linux system (compiled from RV> sources). RV> Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug?
Hello, I have a problem in an Apache module which connects to MySQL using C API for counting banner views and clicks. This module creates a report table for each day. This table is called report_MMDD, where MMDD is the current day. To keep thing simple I just make following query first: CREATE TABLE IF NOT EXISTS report_MMDD (PRIMARY KEY (banner_id)) SELECT id as banner_id,0 as clicks, 0 as views from banners. This means that a new table is created after the first HTTP request after midnight. In all other cases this query should be ignored by MySQL server. Right after that this table is updated: UPDATE report_MMDD set clicks=clicks+1 WHERE banner_id=XXX or: UPDATE report_MMDD set views=views+1 WHERE banner_id=XXX Well, funny things are happening here. After the first request the table is created and everything is fine. But when the next one comes the table is deleted! MySQL Log file says nothing. Can somone tell me what's going on? MySQL Version is 3.23.49 and runs on a Linux system (compiled from sources). Thanks -- Robert Vetter Internet Application Developer Kontor23 GmbH Ottenser Hauptstrasse 56-62 22765 Hamburg Tel.: 040/380893-14 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB, possible bug?
Andrei, this is probably not a bug in InnoDB. In theory, 4000 random disk seeks would use more time than scanning the whole table of 700 000 rows. The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over table scans. That may solve the problem here. On the other hand, the fact that MySQL refuses to use the index specified in the USE INDEX clause may be a bug. I have forwarded this email to MySQL developers. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: "Andrei Cojocaru" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mysql List" <[EMAIL PROTECTED]> Sent: Wednesday, May 22, 2002 3:16 AM Subject: Re: InnoDB, possible bug? > I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: > select count(*) from newsentries10 where playerid=28575 and type=2; > +--+ > | count(*) | > +--+ > | 4218 | > +--+ > 1 row in set (13.81 sec) > > mysql> select count(*) from newsentries10 where playerid=28575 and type=2; > +--+ > | count(*) | > +--+ > | 3705 | > +--+ > 1 row in set (9.72 sec) > > mysql> select count(*) from newsentries10 where type=2; > +--+ > | count(*) | > +--+ > | 611932 | > +--+ > 1 row in set (9.92 sec) > > mysql> explain select straight_join > pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from > newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and > pn.newsid=ne.newsid; > > +---++---+-+-+---+-- > --++ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +---++---+-+-+---+-- > --++ > | ne| ALL| list_news,delete_news | NULL|NULL | NULL | > 774878 | where used | > | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | > 1 || > +---++---+-+-+---+-- > --++ > 2 rows in set (0.01 sec) > > mysql> > mysql> explain select > pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from > newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and > ne.type=2 and pn.newsid=ne.newsid; > +---++---+-+-+---+-- > --++ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +---++---+-+-+---+-- > --++ > | ne| ALL| list_news,delete_news | NULL|NULL | NULL | > 774878 | where used | > | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | > 1 || > +---++---+-+-+---+-- > --++ > 2 rows in set (0.00 sec) > > mysql> > mysql> explain select straight_join > pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from > newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and > ne.type=2 and pn.newsid=ne.newsid; > +---++---+-+-+---+-- > --++ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +---++---+-+-+---+-- > --++ > | ne| ALL| list_news,delete_news | NULL|NULL | NULL | > 774878 | where used | > | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | > 1 || > +---++-------+-+-+---+-- > --++ > 2 rows in set (0.00 sec) > > Andrei Cojocaru > [EMAIL PROTECTED] > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, May 21, 2002 12:40 PM > Subject: Re: InnoDB, possible bug? > > > > Andrei, > > > > how many rows in ne satisfy > > > > (1) ne.playerid=28575, > > > > (2) ne.type=2? > > > > What version you are using? .48 was tuned to favor index searches over > table > > scans. > > > > What does EXPLAIN SELECT say if you force the index usage with USE INDEX > and > > STRAIGHT JOI
Re: InnoDB, possible bug?
I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 4218 | +--+ 1 row in set (13.81 sec) mysql> select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 3705 | +--+ 1 row in set (9.72 sec) mysql> select count(*) from newsentries10 where type=2; +--+ | count(*) | +--+ | 611932 | +--+ 1 row in set (9.92 sec) mysql> explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.01 sec) mysql> mysql> explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) mysql> mysql> explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 21, 2002 12:40 PM Subject: Re: InnoDB, possible bug? > Andrei, > > how many rows in ne satisfy > > (1) ne.playerid=28575, > > (2) ne.type=2? > > What version you are using? .48 was tuned to favor index searches over table > scans. > > What does EXPLAIN SELECT say if you force the index usage with USE INDEX and > STRAIGHT JOIN clauses? > > Best regards, > > Heikki Tuuri > Innobase Oy > --- > Order technical MySQL/InnoDB support at https://order.mysql.com/ > See http://www.innodb.com for the online manual and latest news on InnoDB > > > - Original Message - > From: ""Andrei Cojocaru"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.mysql > Sent: Monday, May 20, 2002 6:46 PM > Subject: InnoDB, possible bug? > > > > Hello, > > > > I've just switched to InnoDB table from myISAM and it's been running > pretty > > smoothly except on this SQL statement it doesn't use any indexes when > there > > are, and therefore is very slow. > > > > mysql> explain select > > pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp > from > > newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and > > pn.newsid=ne.newsid; > > > +---++---+-+-+---+-- > > --++ > > | table | type | possible_keys | key | key_len | ref | > > rows | Extra | > > > +---++---+-+-+---+-- > > --++ > > | ne| ALL| list_news,delete_news | NULL|NULL |
Re: Possible Bug in UPdATE in MySQL 4.0.1 alpha
On Tue, 21 May 2002, Michael Widenius wrote: > > Hi! > > >>>>> "Michael" == Michael B Venezia <[EMAIL PROTECTED]> writes: > > >> Description: > Michael> Possible Bug in UPDATE in MySQL 4.0.1 > > > > Michael> Attempting backtrace. You can use the following information to find out > Michael> where mysqld died. If you see no messages after this, something went > Michael> terribly wrong... > Michael> Stack range sanity check OK, backtrace follows: > Michael> 0x807db7f > Michael> 0x823d64a > Michael> 0x8204447 > Michael> 0x821bbd6 > Michael> 0x820bb41 > Michael> 0x80d082f > Michael> 0x80b0479 > Michael> 0x8086de7 > Michael> 0x808a262 > Michael> 0x8084e57 > Michael> 0x808a694 > Michael> 0x8084296 > Michael> Stack trace seems successful - bottom reached > Michael> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow > Michael> instructions on how to resolve the stack trace. Resolved > Michael> stack trace is much more helpful in diagnosing the problem, so please do > Michael> resolve it > > Michael, could you please read the above instructions and try to > resolve the backtrace for us? > > > > >> How-To-Repeat: > Michael> Did this query on a database called 'medical' below text > > Michael> UPDATE `Physical Examination Report` SET `History of Present > Michael> Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 > > Any chance you could ftp a copy of the 'Physical Examination Report' > table to ftp://support.mysql.com/pub/mysql/secret > so that we could try to repeat the problem ? > > Just having the table definition formats is not enough to repeat a > problem like this! > > > > Regards, > Monty > I've uploaded the table as PhysicalExaminationReport.tar.gz to the above location. It is very small (actually it only contains one or two records if I recall) The following is the stack trace resolved... 0x807db7f handle_segfault__Fi + 383 0x823d64a pthread_sighandler + 154 0x8204447 _mi_compare_text + 71 0x821bbd6 _mi_ft_cmp + 158 0x820bb41 mi_update + 721 0x80d082f update_row__9ha_myisamPCcPc + 67 0x80b0479 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemP8st_orderUl15enum_duplicates13thr_lock_type + 2473 0x8086de7 mysql_execute_command__Fv + 5723 0x808a262 mysql_parse__FP3THDPcUi + 270 0x8084e57 dispatch_command__F19enum_server_commandP3THDPcUi + 1319 0x808a694 do_command__FP3THD + 88 0x8084296 handle_one_connection__FPv + 546 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB, possible bug?
Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: ""Andrei Cojocaru"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? > Hello, > > I've just switched to InnoDB table from myISAM and it's been running pretty > smoothly except on this SQL statement it doesn't use any indexes when there > are, and therefore is very slow. > > mysql> explain select > pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from > newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and > pn.newsid=ne.newsid; > +---++---+-+-+---+-- > --++ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +---++---+-+-+---+-- > --++ > | ne| ALL| list_news,delete_news | NULL|NULL | NULL | > 734023 | where used | > | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | > 1 || > +---++---+-+-+---+-- > --++ > 2 rows in set (0.00 sec) > > (Notice that key for ne is NULL when there is obviously an index it could > use, but doesn't, why?) > > the table structures for the two tables are: > mysql> desc newsentries10; > +---+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +---+-+--+-+-+---+ > | newsid| int(10) unsigned| | MUL | 0 | | > | playerid | int(10) unsigned| | MUL | 0 | | > | hidestamp | int(10) unsigned| | | 0 | | > | viewpoint | tinyint(3) unsigned | | | 0 | | > | type | tinyint(3) unsigned | | | 0 | | > | delmarker | tinyint(3) unsigned | | MUL | 0 | | > +---+-+--+-+-+---+ > 6 rows in set (0.00 sec) > > mysql> desc pnews; > +---+-+--+-+-++ > | Field | Type| Null | Key | Default | Extra | > +---+-+--+-+-++ > | newsid| int(10) unsigned| | PRI | NULL| auto_increment | > | type | tinyint(3) unsigned | | | 0 | | > | id| int(10) unsigned| | MUL | 0 | | > | timestamp | int(10) unsigned| | | 0 | | > +---+-+--+-+-++ > 4 rows in set (0.00 sec) > > the indexs are: > mysql> show index from newsentries10; > +---++-+--+-+--- > +-+--++-+ > | Table | Non_unique | Key_name| Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Comment | > +---++-+--+-+--- > +-+--++-+ > | newsentries10 | 1 | delmarker |1 | delmarker | A > | 0 | NULL | NULL | | > | newsentries10 | 1 | list_news |1 | playerid| A > | 0 | NULL | NULL | | > | newsentries10 | 1 | list_news |2 | type| A > | 0 | NULL | NULL | | > | newsentries10 | 1 | delete_news |1 | newsid | A > | 76379 | NULL | NULL | | > +---++-+--+-+--- > +-+--++-+ > 4 rows in set (0.20 sec) > > mysql> show index from pnews; > +---++--+--+-+---+-- > ---+--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | > Cardinality | Sub_part | Packed | Comment | > +---++--+--+
Possible Bug in UPdATE in MySQL 4.0.1 alpha
Hi! >>>>> "Michael" == Michael B Venezia <[EMAIL PROTECTED]> writes: >> Description: Michael>Possible Bug in UPDATE in MySQL 4.0.1 Michael> Attempting backtrace. You can use the following information to find out Michael> where mysqld died. If you see no messages after this, something went Michael> terribly wrong... Michael> Stack range sanity check OK, backtrace follows: Michael> 0x807db7f Michael> 0x823d64a Michael> 0x8204447 Michael> 0x821bbd6 Michael> 0x820bb41 Michael> 0x80d082f Michael> 0x80b0479 Michael> 0x8086de7 Michael> 0x808a262 Michael> 0x8084e57 Michael> 0x808a694 Michael> 0x8084296 Michael> Stack trace seems successful - bottom reached Michael> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow Michael> instructions on how to resolve the stack trace. Resolved Michael> stack trace is much more helpful in diagnosing the problem, so please do Michael> resolve it Michael, could you please read the above instructions and try to resolve the backtrace for us? >> How-To-Repeat: Michael>Did this query on a database called 'medical' below text Michael>UPDATE `Physical Examination Report` SET `History of Present Michael> Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 Any chance you could ftp a copy of the 'Physical Examination Report' table to ftp://support.mysql.com/pub/mysql/secret so that we could try to repeat the problem ? Just having the table definition formats is not enough to repeat a problem like this! Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible Bug in UPdATE in MySQL 4.0.1 alpha
Michael, Sunday, May 19, 2002, 12:56:00 PM, you wrote: MBV> Description: MBV> Possible Bug in UPDATE in MySQL 4.0.1 MBV> The following is the message in the error log... MBV> Number of processes running now: 0 MBV> 020519 04:55:30 mysqld restarted MBV> 020519 4:55:30 InnoDB: Started MBV> /usr/local/mysql/bin/mysqld: ready for connections MBV> mysqld got signal 11; MBV> This could be because you hit a bug. It is also possible that this binary MBV> or one of the libraries it was linked against is corrupt, improperly MBV> built, MBV> or misconfigured. This error can also be caused by malfunctioning MBV> hardware. MBV> We will try our best to scrape up some info that will hopefully help MBV> diagnose MBV> the problem, but since we have already crashed, something is definitely MBV> wrong MBV> and this may fail. MBV> How-To-Repeat: MBV> Did this query on a database called 'medical' below text MBV> UPDATE `Physical Examination Report` SET `History of Present MBV> Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 MBV> DUMP of medical database schema: MBV> This was done multiple times and caused the server to restart MBV> every time. MBV> Fix: MBV> Moved Database to a machine with MySQL 3.x (3.23.49) and the query MBV> worked without issue. I even tried this by simply copying the data MBV> directory over (tar.gz-ed the data directory, untared it on the other MBV> machine, and restarted the server) I tested you SQL statement and it works fine on my v4.0.1 Probably, SIG11 is cause by broken hardware. Please check it. BTW, if you use compound names for your tables and columns use --quote-names option of mysqldump. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB, possible bug?
Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql> explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table structures for the two tables are: mysql> desc newsentries10; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | newsid| int(10) unsigned| | MUL | 0 | | | playerid | int(10) unsigned| | MUL | 0 | | | hidestamp | int(10) unsigned| | | 0 | | | viewpoint | tinyint(3) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | delmarker | tinyint(3) unsigned | | MUL | 0 | | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql> desc pnews; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | newsid| int(10) unsigned| | PRI | NULL| auto_increment | | type | tinyint(3) unsigned | | | 0 || | id| int(10) unsigned| | MUL | 0 || | timestamp | int(10) unsigned| | | 0 || +---+-+--+-+-++ 4 rows in set (0.00 sec) the indexs are: mysql> show index from newsentries10; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | newsentries10 | 1 | delmarker |1 | delmarker | A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |1 | playerid| A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type| A | 0 | NULL | NULL | | | newsentries10 | 1 | delete_news |1 | newsid | A | 76379 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ 4 rows in set (0.20 sec) mysql> show index from pnews; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | pnews | 0 | PRIMARY |1 | newsid | A | 139047 | NULL | NULL | | | pnews | 1 | id |1 | id | A | 139047 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 2 rows in set (0.07 sec) Now I have an index on the two columns that are used in the query on the newsentries10 table and it doesn't use them at all. Could someone please explain this to me and give me any advice on how to fix it? Thanks for your help in advance. words to bypass filter: sql queries Andrei Cojocaru [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscri
Possible Bug in UPdATE in MySQL 4.0.1 alpha
>Description:
Possible Bug in UPDATE in MySQL 4.0.1
The following is the message in the error log...
Number of processes running now: 0
020519 04:55:30 mysqld restarted
020519 4:55:30 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.
key_buffer_size=402649088
record_buffer=268431360
sort_buffer=268435448
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2489963 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x807db7f
0x823d64a
0x8204447
0x821bbd6
0x820bb41
0x80d082f
0x80b0479
0x8086de7
0x808a262
0x8084e57
0x808a694
0x8084296
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow
instr
uctions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8423db0 = UPDATE `Physical Examination Report` SET
`History of P
resent Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27
thd->thread_id=7
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 7 did to cause the crash. In some cases of really
bad corruption, the values shown above may be invalid.
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
020519 05:14:17 mysqld restarted
020519 5:14:17 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections
>How-To-Repeat:
Did this query on a database called 'medical' below text
UPDATE `Physical Examination Report` SET `History of Present
Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27
DUMP of medical database schema:
# phpMyAdmin MySQL-Dump
# version 2.2.6
# http://phpwizard.net/phpMyAdmin/
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: May 19, 2002 at 05:50 AM
# Server version: 4.00.01
# PHP Version: 4.0.5
# Database : `medical`
#
#
# Table structure for table `Appointment Types`
#
CREATE TABLE Appointment Types (
ID Code int(11) NOT NULL auto_increment,
Description mediumtext NOT NULL,
PRIMARY KEY (ID Code)
) TYPE=MyISAM COMMENT='Keeps Information unique to appointment type';
#
#
# Table structure for table `Appointments`
#
CREATE TABLE Appointments (
ID Code int(11) NOT NULL auto_increment,
Date of Appointment date default NULL,
ID Code of Patient int(11) default NULL,
ID Code of Doctor int(11) default NULL,
ID Code of Referer int(11) default NULL,
ID Code of Second Referer int(11) default NULL,
ID Code of Third Referer int(11) default NULL,
ID Code of Fourth Referer int(11) default NULL,
Type of Appointment int(11) default NULL,
Complete int(11) default NULL,
PRIMARY KEY (ID Code)
) TYPE=MyISAM COMMENT='Keeps track of what''s involved in the
appointment';
#
#
# Table structure for table `Doctors`
#
CREATE TABLE Doctors (
ID Code int(11) NOT NULL auto_increment,
First Name tinytext,
Middle Name tinytext,
Last Name tinytext,
Initials tinytext,
SSN tinytext,
Business Street tinytext,
Business City tinytext,
Business State tinytext,
Business Zip Code tinytext,
Business Phone Number tinytext,
Business Fax Number tinytext,
Business Email Address tinytext,
Home Street tinytext,
Home City tinytext,
Home State tinytext,
Home Zip Code tinytext,
Home Phone Number tinytext,
Home Fax Number tinytext,
Home Email Address tinytext,
PRIMARY KEY (ID Code)
) TYPE=MyISAM COMMENT='Keeps important data concerning the physicians that
work her';
#
#
# Table structure for table `Follow Up Report`
#
CREATE TABLE Follow Up Report (
Report Number int(11) NOT NULL auto_increment,
ID Code of Appointment int(11) default NULL,
Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
Hi! MySQL AB and Innobase Oy entry-level support contract costs 3770 euros annually (3250 USD). For corporations with requirements for high availability we have more expensive telephone support options. These are extremely low priced compared against your costs for proprietary database products, and are low even when compared to rates charged by other open-source vendors. So please consider investing in a MySQL/InnoDB support contract. You will be served not by a help desk, but directly by the developers who author MySQL/InnoDB's source code. Information: http://www.mysql.com/support/index.html To Purchase: https://order.mysql.com/ About the buffer pool error: a similar bug in recovery was fixed in 3.23.45: ... November 17, 2001: In recovery InnoDB may go into an infinite loop constantly printing a warning message that it cannot find free blocks from the buffer pool. Fixed in 3.23.45. ... I have also a faint recollection that I then also fixed some additional error in the buffer pool flush algorithm which could cause the error messages to be printed in normal operation, not just recovery. Regards, Heikki Tuuri Innobase Oy - Original Message - From: "JW" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, April 07, 2002 5:04 AM Subject: Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool.. > > I belive I have found a bug here, possibly. > The last known commands to be run on the DB that are "suspicious" were a join between 2 tables. > > > This is the info I got from the user who was workign on the DB: > > He was "joining between keyword and billing" > > select distinct(a.customerid) from cpcustomer.customerkeyword a left join cpbilling.billdetail b on a.customerid=b.customerid where b.customerid is null and active='y'; > > For some reason he hit ^c to exit (it didn't seem to be responding or somethign) > > He logged came back on to MySQL and ran: > > alter table add key bdetailcid (customerid); > show create billdetail; > > It crashed (locked up) durning the last query mentioned above. > > > I tried to shut down the server, but it wouldn't die so I ran the init-stop script a second time. > It seems that the init start/stop script gets forceful on the second try. > > Started it back up, it took a _very_ long time recovering (InnoDB), then started in with the error message I first posted. > > Any ideas? I'm currently restoring from backups we'll see how it goes. > > Thanks. > > JW >I forgot to point out that this is InnoDB > JW > > JW >I've searched Google and found the following disquiteing thing: > JW >http://www.innodb.com/oldbugs.html: > JW > > JW >"Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. " > JW > > JW >August 13, 2001: > JW >The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: > JW > > JW >Innobase: Warning: difficult to find free blocks from > JW >Innobase: the buffer pool! Consider increasing the > JW >Innobase: buffer pool size. > JW > > JW >If you encounter the above message, upgrade to 3.23.41." > JW > > JW >However, I'm already using a newer vresion than that: > JW > > JW >ccs012:~ # rpm -qa |grep mysql > JW >mysql-shared-3.23.44-5 > JW >mysql-Max-3.23.44-5 > JW >mysql-devel-3.23.44-5 > JW >mysql-navigator-1.2.3-106 > JW >mysql-client-3.23.44-5 > JW >mysql-3.23.44-5 > JW >mysql-bench-3.23.44-5 > JW >ccs012:~ # > JW > > JW >This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? > JW > > JW >JW >I'm getting this error messge constantly in my error log: > JW >JW > > JW >JW >020406 18:02:50 *** > JW >JW >InnoDB: Warning: difficult to find free blocks from > JW >JW >InnoDB: the buffer pool (200 search iterations)! Consider > JW >JW >InnoDB: increasing the buffer pool size. > JW >JW >InnoDB: It is also possible that in your Unix version > JW >JW >InnoDB: fsync is very slow, or completely frozen inside > JW >JW >InnoDB: the OS kernel. Then upgrading to a newer version > JW >JW >InnoDB: of your operating system may help. Look at the > JW >JW >InnoDB: number of fsyncs in diagnostic info below. > JW >JW
Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
I belive I have found a bug here, possibly. The last known commands to be run on the DB that are "suspicious" were a join between 2 tables. This is the info I got from the user who was workign on the DB: He was "joining between keyword and billing" select distinct(a.customerid) from cpcustomer.customerkeyword a left join cpbilling.billdetail b on a.customerid=b.customerid where b.customerid is null and active='y'; For some reason he hit ^c to exit (it didn't seem to be responding or somethign) He logged came back on to MySQL and ran: alter table add key bdetailcid (customerid); show create billdetail; It crashed (locked up) durning the last query mentioned above. I tried to shut down the server, but it wouldn't die so I ran the init-stop script a second time. It seems that the init start/stop script gets forceful on the second try. Started it back up, it took a _very_ long time recovering (InnoDB), then started in with the error message I first posted. Any ideas? I'm currently restoring from backups we'll see how it goes. Thanks. JW >I forgot to point out that this is InnoDB JW > JW >I've searched Google and found the following disquiteing thing: JW >http://www.innodb.com/oldbugs.html: JW > JW >"Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. " JW > JW >August 13, 2001: JW >The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: JW > JW >Innobase: Warning: difficult to find free blocks from JW >Innobase: the buffer pool! Consider increasing the JW >Innobase: buffer pool size. JW > JW >If you encounter the above message, upgrade to 3.23.41." JW > JW >However, I'm already using a newer vresion than that: JW > JW >ccs012:~ # rpm -qa |grep mysql JW >mysql-shared-3.23.44-5 JW >mysql-Max-3.23.44-5 JW >mysql-devel-3.23.44-5 JW >mysql-navigator-1.2.3-106 JW >mysql-client-3.23.44-5 JW >mysql-3.23.44-5 JW >mysql-bench-3.23.44-5 JW >ccs012:~ # JW > JW >This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW > JW >JW >I'm getting this error messge constantly in my error log: JW >JW > JW >JW >020406 18:02:50 *** JW >JW >InnoDB: Warning: difficult to find free blocks from JW >JW >InnoDB: the buffer pool (200 search iterations)! Consider JW >JW >InnoDB: increasing the buffer pool size. JW >JW >InnoDB: It is also possible that in your Unix version JW >JW >InnoDB: fsync is very slow, or completely frozen inside JW >JW >InnoDB: the OS kernel. Then upgrading to a newer version JW >JW >InnoDB: of your operating system may help. Look at the JW >JW >InnoDB: number of fsyncs in diagnostic info below. JW >JW >InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW >JW >InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW >JW >InnoDB: Starting InnoDB Monitor to print further JW >JW >InnoDB: diagnostics to the standard output. JW >JW > JW >JW >Running SuSE Linux 7.3: JW >JW > JW >JW >ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW >JW >Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW >JW >FilesystemSize Used Avail Use% Mounted on JW >JW >/dev/sda7 67G 59G 8.2G 88% / JW >JW >/dev/sda5 63M 36M 26M 58% /boot JW >JW >shmfs1007M 0 1006M 0% /dev/shm JW >JW > total used free sharedbuffers cached JW >JW >Mem: 2013 2008 4 0 10657 JW >JW >-/+ buffers/cache: 1340672 JW >JW >Swap: 1035 0 1035 JW >JW > JW >JW > JW >JW >TIA JW >JW > JW >JW >-- JW >JW > JW >JW > JW >JW >Jonathan Wilson JW >JW >System Administrator JW >JW >Clickpatrol.com JW >JW >Cedar Creek Software http://www.cedarcreeksoftware.com JW >JW > JW >JW > JW >JW > JW >JW >- JW >JW >Before posting, please check: JW >JW > http://www.mysql.com/manual.php (the manual) JW >JW > http://lists.mysql.com/ (the list archive) JW >JW > JW >JW >To request this thread, e-mail <[EMAIL PROTECTED]> JW >JW >To unsubscribe, e-mail <[EMAIL PROTECTED]> JW >JW >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php JW >JW > JW >JW > JW >JW > JW > JW >-- JW > JW > JW >Jonathan Wilson JW >System Administrator JW >Clickpatrol.com JW >Cedar Creek Software http://www.cedarcreeksoftware.com JW > JW > JW > JW >- JW >Before posting, please check: JW > http://www.mysql.com/manual.php (the manual)
VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
I forgot to point out that this is InnoDB I've searched Google and found the following disquiteing thing: http://www.innodb.com/oldbugs.html: "Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. " August 13, 2001: The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: Innobase: Warning: difficult to find free blocks from Innobase: the buffer pool! Consider increasing the Innobase: buffer pool size. If you encounter the above message, upgrade to 3.23.41." However, I'm already using a newer vresion than that: ccs012:~ # rpm -qa |grep mysql mysql-shared-3.23.44-5 mysql-Max-3.23.44-5 mysql-devel-3.23.44-5 mysql-navigator-1.2.3-106 mysql-client-3.23.44-5 mysql-3.23.44-5 mysql-bench-3.23.44-5 ccs012:~ # This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW >I'm getting this error messge constantly in my error log: JW > JW >020406 18:02:50 *** JW >InnoDB: Warning: difficult to find free blocks from JW >InnoDB: the buffer pool (200 search iterations)! Consider JW >InnoDB: increasing the buffer pool size. JW >InnoDB: It is also possible that in your Unix version JW >InnoDB: fsync is very slow, or completely frozen inside JW >InnoDB: the OS kernel. Then upgrading to a newer version JW >InnoDB: of your operating system may help. Look at the JW >InnoDB: number of fsyncs in diagnostic info below. JW >InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW >InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW >InnoDB: Starting InnoDB Monitor to print further JW >InnoDB: diagnostics to the standard output. JW > JW >Running SuSE Linux 7.3: JW > JW >ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW >Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW >FilesystemSize Used Avail Use% Mounted on JW >/dev/sda7 67G 59G 8.2G 88% / JW >/dev/sda5 63M 36M 26M 58% /boot JW >shmfs1007M 0 1006M 0% /dev/shm JW > total used free sharedbuffers cached JW >Mem: 2013 2008 4 0 10657 JW >-/+ buffers/cache: 1340672 JW >Swap: 1035 0 1035 JW > JW > JW >TIA JW > JW >-- JW > JW > JW >Jonathan Wilson JW >System Administrator JW >Clickpatrol.com JW >Cedar Creek Software http://www.cedarcreeksoftware.com JW > JW > JW > JW >- JW >Before posting, please check: JW > http://www.mysql.com/manual.php (the manual) JW > http://lists.mysql.com/ (the list archive) JW > JW >To request this thread, e-mail <[EMAIL PROTECTED]> JW >To unsubscribe, e-mail <[EMAIL PROTECTED]> JW >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php JW > JW > JW > -- Jonathan Wilson System Administrator Clickpatrol.com Cedar Creek Software http://www.cedarcreeksoftware.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MysqlDump (Possible bug?)
Hi I recently ran a mysqldump of a bunch of information. When the mysqldump extracted the dates, it extracted them in South African format (dd/mm/), I am guessing this was picked up from somewhere, so when I then ran the dump through mysql to reimport the information all the dates where import wrong, is this a bug or how can I fix it? regards Warren ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 Any views expressed in this message are the sender's own, and do not represent the views of RedTie Software except where the sender specifically states them to be the views of RedTie Software. This e-mail should only be read by those persons to whom it is addressed. Accordingly, we disclaim all responsibility and accept no liability (including in negligence) for the consequences of any person other than the intended recipients acting, or refraining from acting, on such information. If you have received this e-mail in error, please accept our apologies and we simply request that you delete this document. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and/or publication of this e-mail is strictly prohibited. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: mysqldump and foreign keys (possible Bug ??)
hi, I think u r right.. because after recovering from the dumped database, when insert a value in the foreign key column which is not present in the primary key, it accepts the row inserted. which is not true when I do the same think from the mysql command prompt,( before dumping) Anybody who has experience with the same problem and is it really the true.. if it is true then what's the use of mysqldump ..( which makes someone's database scattered.) Help me also !! it is urgent .. Thanks and regards, Chetan Lavti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 9:35 PM To: Chetan Lavti Subject: Re: mysqldump and foreign keys (possible Bug ??) No, not working, When I run the command SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'; shown in the manual (7.5.4.2 Foreign Key Constraints) I will see the foreign key (in the comments collumn). But if I dump and then reload the dump into mySQL the foreign key is gone. Experts, is this a BUG Adib. Chetan Lavti wrote: > hi, > I am also using the Innodb table type for my database. when I am using > the mysqldump for dumping the tables it really doesn't shows the > foreign key in the table structure. > but if after deleting my previous database I again create my database > using the same dumped file and executes the > > mysql>desc > > (at mysql command prompt) it shows the foreign keys.. > > am i right..?? Actually, I also wanted to know !! I am telling u what i > have done and seen. Please, u also try for the same and see if it works > fine. > please, tell me also regarding this... > > Thanks and regards, > Chetan Lavti > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- WIGE DATA GmbH Wiesenring 11, D-04159 Leipzig, Germany Adib Taraben Electronic Engineer PHONE +49 (0)341 - 46 21 100 FAX +49 (0)341 - 46 21 400 E-MAIL [EMAIL PROTECTED] INTERNETwww.wige-data.de A company of WIGE MEDIA AG - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Possible bug or corruption ? - MY ERROR PLEASE IGNORE
I have realized my error. Please disregard and ignore. > - Original Message -=20 > From: David Potter=20 > To: [EMAIL PROTECTED]=20 > Sent: Friday, March 01, 2002 1:53 PM > Subject: Possible bug or corruption ? > > > Hello, > I am trying to figure out if I=20 > sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug in mysqldump -d on mysql-max 4.0.1
I just noticed that mysqldump -d outputs /*!4 ALTER TABLE equity_ind1 DISABLE KEYS */; at the end of the dump of the table def... If I understand this correctly... this will be executed by any version of mysql version 4 and up... That would be fine, except that since it is not dumping data, if you use this verbatim, there is no ENABLE KEYS at the bottom, like when you dump the table with the data. Is that right? Or does the disable keys only work for the life of the connection or something like that? -- ___ __ ____ _ _ _ _ / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) \__ \ )(__)( /(__)\\ / )__)) ( )__) )( (___/(__)(__)(__)\/ ()()(_)\_)() (__) I am a mental tourist. My mind wanders. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug? Or ignorance on my part?
Is there any kind of UPDATE query limitation that would not allow me to make the following update? Basically my update is updating to the same text, only without the '[' and ']' from around the url. Only mysql is not updating the record. If I try updating to something else (like add an "asdf" in between "Why doesn't this work?" and "http://www.mysite.com"; while removing the "[" and "]" it works... I tried messing around, and noticed that this happens with \' character as well as quotes, a regular alpha character seems to work properly... Any ideas? When I run: "Check table wdCaseThread" this is what I receive mysql> check table wdCaseThread; ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | wirelessdeveloper.wdCaseThread | check | warning | Table is marked as crashed | | wirelessdeveloper.wdCaseThread | check | status | OK | ++---+--++ 2 rows in set (0.01 sec) What does table is marked as crashed mean? If I run check table wdCaseThread once more it shows up as OK. ? - Here is the structure of my table: wdCaseThread Table Structure mysql> describe wdCaseThread; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | threadID | int(10) unsigned | | PRI | NULL| auto_increment | | historyID | int(10) unsigned | YES | | NULL|| | body | text | YES | MUL | NULL|| | htmlBody | text | YES | | NULL|| +---+--+--+-+-++ 4 rows in set (0.22 sec) I also Have a fulltext index on body -- _ Jason Kushmaul, Software Engineer WirelessDeveloper.com Suite 200 2875 Northwind Drive East Lansing, MI 48823 [EMAIL PROTECTED] (517)337-2701 ext. 205 Become a Member Today!! http://www.WirelessDeveloper.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Full Text Search with and without index - possible bug?
Hi! On Jan 19, Gordan Bobic wrote: > Hi! > > The part that I thought could be a "bug" was the one you didn't quote. I still remeber that part, there's no need to explain it again :-) > > It's not a "known" bug as the code is rather new. > > Can you create a test case for this ? > > Possibly, but it could be difficult as my data set changes daily. It looks > like a problem that ought to be replicable with fairly generic data. You've said that SELECT with index returns ~20 rows, and w/o 7 rows. Create a table with those rows and send it to me... I hope that will be a test case. > Incidentally, could it be caused by the fact that I am using a multi-column > FTS? Could this be where the operation without the index is falling over? It could, of course. But it shouldn't. And the probability is low. > Another thing - is it possible to combine a FULLTEXT index with another > column/index to achieve a multi-column index with FTS capabilities? It just > seems wrong that FTS should take a fixed amount of time regardless of what > other indexable restrictions are made on the data set, and if there is a > limitation on 1 index per table per query, then the multi-column index is the > only way around it. In boolean FTS, fulltext index works almost like normal index, that is the list of documents is NOT computed in advance. as for combined index, it's possible to do, I think. But I have some plans of changing index structure. This will result in faster searches and smaller index files. With this index structure it would be impractical to introduce compined indexes. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Full Text Search with and without index - possible bug?
Hi! > > 1.1) Full Text Search can, according to the manual, be performed without > > the FTS index, but it is slower. > > 1.2) MySQL can only use 1 index per join per table. > > > > => This means that if I specify the USE INDEX (some_non_fulltext_index), > > the FTS will be performed without the index, and this will only work IN > > BOOLEAN MODE. Otherwise, MySQL returns an error, complaining about the > > lack of an index. > > Gordan, you're right. It's the way it was expected to behave :-) The part that I thought could be a "bug" was the one you didn't quote. The problem I am experiencing is that the boolean MATCH/AGAINST FTS WITH an FTS index doesn't match the results returned WITHOUT the FTS index when the same query/parameters are used. I don't know if this is the case when there is no index at all (it would be difficult to test, too). I just found this out by using the USE INDEX clause to switch to using different indices. Incidentally, I have set the fts minimum word length to 1, so the cause of this is not the fact that the index skips some values. In fact, it is the unindexed search that seems to get things wrong. The indexed FTS verifiably works on my data set. The un-indexed FTS demonstrably doesn't work as expected. The unindexed search returns much fewer values. > It's not a "known" bug as the code is rather new. > > Can you create a test case for this ? Possibly, but it could be difficult as my data set changes daily. It looks like a problem that ought to be replicable with fairly generic data. Incidentally, could it be caused by the fact that I am using a multi-column FTS? Could this be where the operation without the index is falling over? Anyway, from what I can see, the indexed FTS works great. The unindexed FTS doesn't (both in boolean mode). Another thing - is it possible to combine a FULLTEXT index with another column/index to achieve a multi-column index with FTS capabilities? It just seems wrong that FTS should take a fixed amount of time regardless of what other indexable restrictions are made on the data set, and if there is a limitation on 1 index per table per query, then the multi-column index is the only way around it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Full Text Search with and without index - possible bug?
Hi! On Jan 18, Gordan Bobic wrote: > Hi. > > I thought it would be useful to share my findings. They all relate to the > 4.0.1 release. It would be nice to have some clarification on whether this is > expected behaviour, whether this behaviour is wrong (i.e. bug, corrupted > index, etc), and what you guys think could be causing it. > > 1.1) Full Text Search can, according to the manual, be performed without the > FTS index, but it is slower. > 1.2) MySQL can only use 1 index per join per table. > > => This means that if I specify the USE INDEX (some_non_fulltext_index), the > FTS will be performed without the index, and this will only work IN BOOLEAN > MODE. Otherwise, MySQL returns an error, complaining about the lack of an > index. Gordan, you're right. It's the way it was expected to behave :-) It's not a "known" bug as the code is rather new. Can you create a test case for this ? Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Full Text Search with and without index - possible bug?
Hi.
I thought it would be useful to share my findings. They all relate to the
4.0.1 release. It would be nice to have some clarification on whether this is
expected behaviour, whether this behaviour is wrong (i.e. bug, corrupted
index, etc), and what you guys think could be causing it.
1.1) Full Text Search can, according to the manual, be performed without the
FTS index, but it is slower.
1.2) MySQL can only use 1 index per join per table.
=> This means that if I specify the USE INDEX (some_non_fulltext_index), the
FTS will be performed without the index, and this will only work IN BOOLEAN
MODE. Otherwise, MySQL returns an error, complaining about the lack of an
index.
HOWEVER, please someone explain why the following results are happening:
Two nearly identical queries, similar to:
SELECT Table1.ID,
Table1.Title,
Table1.Type,
Table1.Description,
DATE_FORMAT(Retrieved, '%d-%b-%Y %H:%i:%S') AS Retrieved
FROMTable1
WHERE Type = 'SomeType'
AND Retrieved > '2002011800'
MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE)
ORDER BY MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE) DESC;
and
SELECT Table1.ID,
Table1.Title,
Table1.Type,
Table1.Description,
DATE_FORMAT(Retrieved, '%d-%b-%Y %H:%i:%S') AS Retrieved
FROMTable1 USE INDEX (Table1_Retrieved_Index)
WHERE Type = 'SomeType'
AND Retrieved > '2002011800'
MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE)
ORDER BY Retrieved DESC;
These two queries return DIFFERENT numbers of records!
If my understanding of the documentation is correct, the second example
should be slower because the FTS index isn't used. But the results should be
the same right? Well, that definitely isn't the case in my database.
I have just done a REPAIR TABLE Table1, Table2... EXTENDED, so the tables
definitely aren't corrupted.
The FTS index search returns 24 records on my data set (~ 60K records), and
the non-fts search returns 7 records.
The reason I have been even trying this is because FTS is a bit slow for some
of the things I am doing. By limiting the data set through the "Retrieved"
date field, I can usually cut the data down to about 10% of the total size,
hoping that non-indexed FTS on that will be faster.
Well, it turned out to be faster for cases where the data set was cut down a
lot by the index, but the IN BOOLEAN MODE FTS doesn't seem to be reacting to
things like '-word' in the MATCH/AGAINST clause, as it should per the FTS
search. Sometimes, specifying a '-word' that should only remove a few results
returns 0 rows - which is clearly wrong in some cases.
Is there a know bug in the indexless FTS that causes this? The indexed FTS is
behaving well, but I was really hoping to gain some speed by using a
different index in some specific cases...
Regards.
Gordan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLd-MAX Crash - Possible Bug v3.23.47?
Rich, it was a bug. If inserts to several tables containing an auto-inc column are wrapped inside one LOCK TABLES, InnoDB will assert in lock0lock.c, line 2843. Workaround: remove the LOCK TABLES if you can, or LOCK just one table at a time. I have fixed the bug to 3.23.48. You will get a patch to ha_innobase.cpp if you need. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Rich wrote in message ... >v3.23.47 > >I have managed to Carsh the DB Server. > >I have a routine in my application which imports & exports a database. > >My Import does the following process, (all commands sent through >MyODBC). >1- Lock Table table1 write repeated for all tables in db. >2- Delete from table1 >3- insert data into table1 (using INSERT, SQL constructed from data in >an XML file) > >Repeat steps 2+3 for all tables. >But when it gets to the very first insert of the sceond table, the >server crashes with an assertion error, leaving my application to >complain theres no MySQL server. > >Console Monitor shows: >020117 14:27:57 InnoDB: Started >c:\mysql\bin\mysqld-max-nt: ready for connections >InnoDB: Assertion failure in thread 1136 in file >C:\get\innobase\lock\lock0lock.c line 2843 >InnoDB: We intentionally generate a memory trap. >InnoDB: Send a detailed bug report to [EMAIL PROTECTED] >InnoDB: Thread 1016 stopped in file C:\get\innobase\os\os0sync.c line >140 > >This procedure worked fine in MySQL v3.23.38 but since upgrading it >does not work. > >I have tried ammending the SQL commands to a file and sending the SQL >file through the server on the command line and there are no errors. >I have tried upgrading MyODBC still nothing. > >Has anyone had any similar errors? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: possible bug?
anti spam words: database,sql,query,table you can first put it here to be sure it's a bug be specific, showing table definitions, query that is not working please provide OS, mySQL version, any relevant information Etienne "Karl J. Stubsjoen" wrote: > > database,sql,query,table > > Hello, > Where is the correct place/procedure to report a possible bug? I think I > found one. > > Karl > > Karl Stubsjoen > excelbus.com/info-m > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Etienne Marcotte Specifications Management - Quality Control Imperial Tobacco Ltd. - Montreal (Qc) Canada 514.932.6161 x.4001 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
possible bug?
database,sql,query,table Hello, Where is the correct place/procedure to report a possible bug? I think I found one. Karl Karl Stubsjoen excelbus.com/info-m - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug - further refinement
> I tested this with 3.23.38 and was not able to reproduce the > problem. The UPDATE works as supposed for me. > > Did you compile the server yourself? If so, try an official binary. If > not, please post more information, like where you got your binary > from, which version it is and so on. Use "mysqlbug" utility if > possible. No - as I posted on the first report, the problem occurred on 4.0.0alpha-nt. I have now reverted to 3.23.45 and the problem has disapeared; it is a 4.0 effect only (something to do with the new fulltext, possibly). No problem - that is what you expect with alpha software - but I would like to know it is recognised and will be solved in a future 4.0 release. > PS: Could you get rid of that footer? It's annoying. Unfortunately not - it is added by the corporate mail server on the way out. A lot of UK corporate systems seem to be doing this - I think some legal PITA has said that you need it to protect yourself from his pack-mates. If you complain enough, I might have some ammo to shout at The Management, but at the moment I carry no weight compaired to the lawyers. Sorry. Alec Cawley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug - further refinement
Hi. I tested this with 3.23.38 and was not able to reproduce the problem. The UPDATE works as supposed for me. Did you compile the server yourself? If so, try an official binary. If not, please post more information, like where you got your binary from, which version it is and so on. Use "mysqlbug" utility if possible. Bye, Benjamin. PS: Could you get rid of that footer? It's annoying. On Thu, Nov 29, 2001 at 03:21:53PM +, [EMAIL PROTECTED] wrote: > I have pared down my bug scenario to a single script, at the end of which I > have > failed to update my table, and have reports that the MYD file cannot be > opened. > Here it is: > > #This script file for mysql shows a possible bug. > #The update statement near the bottom of the file (a) appears not to > #work (as shown by the fact that the two selects return the same result) > #(b) leaves the table in a state which myisamchk describes as > #"crashed", and (c) after a flush leaves clips.MYD unreadable > > #Drop and re-create the test table > DROP TABLE IF EXISTS Clips ; > CREATE TABLE Clips > ( > ClipID INTEGER NOT NULL, > Owner TINYTEXT DEFAULT NULL, > PRIMARY KEY (ClipID), > FULLTEXT (Owner) > ) ; > > #Create a single row > INSERT INTO clips SET clipID = 2 ; > > #Display it > SELECT ClipID, Owner FROM clips ; > #Owner should be NULL > > #Update it > #This is the line which causes the damage > UPDATE Clips SET Owner = "me" WHERE ClipID = 2 ; > > #Display it agaim > SELECT ClipID, Owner FROM clips ; > #Owner should be "me" but is NULL [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug - further refinement
I have pared down my bug scenario to a single script, at the end of which I have failed to update my table, and have reports that the MYD file cannot be opened. Here it is: #This script file for mysql shows a possible bug. #The update statement near the bottom of the file (a) appears not to #work (as shown by the fact that the two selects return the same result) #(b) leaves the table in a state which myisamchk describes as #"crashed", and (c) after a flush leaves clips.MYD unreadable #Drop and re-create the test table DROP TABLE IF EXISTS Clips ; CREATE TABLE Clips ( ClipID INTEGER NOT NULL, Owner TINYTEXT DEFAULT NULL, PRIMARY KEY (ClipID), FULLTEXT (Owner) ) ; #Create a single row INSERT INTO clips SET clipID = 2 ; #Display it SELECT ClipID, Owner FROM clips ; #Owner should be NULL #Update it #This is the line which causes the damage UPDATE Clips SET Owner = "me" WHERE ClipID = 2 ; #Display it agaim SELECT ClipID, Owner FROM clips ; #Owner should be "me" but is NULL #This works at this point SHOW COLUMNS FROM Clips ; FLUSH TABLE Clips ; #So myisamchk sees a clean file #This fails after the flush - errno 145 SHOW COLUMNS FROM Clips ; Alec Cawley <<-- This e-mail is intended for the named addressees only. Its contents may be privileged or confidential and should be treated as such. If you are not an intended recipient please notify the sender immediately; do not copy, distribute, or take any action based on this e-mail; and then delete it. In the pursuit of its legitimate business activities and its conformance with relevant legislation, Quantel Ltd. may access any e-mail (including attachments) it originates and receives, for potential scrutiny. Check out Quantel's new website, packed full of information, at : http://www.quantel.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql / possible bug?
> [EMAIL PROTECTED] wrote: > > > I changed the script and now all is well. However I have two concerns: > > 1) Paranoid about the password being in this script. Is there a way around this. > > chown root:root /etc/rc.d/init.d/mysqld > chmod go-rx /etc/rc.d/init.d/mysqld Just to be clear, if the permissions somehow already include 'write' for group or other, then this chmod won't remove that. I'd suggest this: % chmod 500 /etc/rc.d/init.d/mysqld % ls -l /etc/rc.d/init.d/mysqld -r-x-- 2 root root 4040 May 29 11:20 /etc/rc.d/init.d/mysqld --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql / possible bug?
[EMAIL PROTECTED] wrote: > I changed the script and now all is well. However I have two concerns: > 1) Paranoid about the password being in this script. Is there a way around this. chown root:root /etc/rc.d/init.d/mysqld chmod go-rx /etc/rc.d/init.d/mysqld > 2) Since I had to change the script to make it work, Is there a bug in 3.23.41?? It is a bug in the script NOT in MySQL its self. (Note that this script AFAIK is not a standard script that comes with MySQL, your distribution probably added it.) Killing safe_mysqld or the mysqld processes is dangerous and wrong. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql / possible bug?
I changed the script and now all is well. However I have two concerns:
1) Paranoid about the password being in this script. Is there a way around this.
2) Since I had to change the script to make it work, Is there a bug in 3.23.41??
Thanks for pointing me in the right direction.
Bill Adams <[EMAIL PROTECTED]> on 11/12/2001 01:58:18 PM
To: Lad Gaal/MarconiMedical@Marconi
cc: Mysql List <[EMAIL PROTECTED]>
Subject: Re: Can't stop mysql
[EMAIL PROTECTED] wrote:
> The only reference to mysqladmin is for the reload at the end. I think this
> whole thing started after doing the mysql_install_db and then creating the
root
> password - but it may be a coincidence.
>
> Any and all help is welcome as to why /etc/init.d/mysqld stop fails.
>
> The /etc/init.d/mysql is as follows:
I would update the script:
>
> stop(){
/path/to/mysqladmin -uroot -pyour?root.password shutdown >
/dev/null
2>&1
ret=$?
> if [ $ret -eq 0 ]; then
> action $"Stopping $prog: " /bin/true
> else
> action $"Stopping $prog: " /bin/false
> fi
> [ $ret -eq 0 ] && rm -f /var/lock/subsys/mysqld
> [ $ret -eq 0 ] && rm -f /var/lib/mysql/mysql.sock
> return $ret
> }
>
> restart(){
> stop
> start
> }
>
> condrestart(){
> [ -e /var/lock/subsys/mysqld ] && restart || :
> }
>
> reload(){
[ -e /var/lock/subsys/mysqld ] && mysqladmin -uroot -pyour?root.password
reload
> }
>
b.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
On Sat, 20 Oct 2001 12:48:36 -0500 Eric <[EMAIL PROTECTED]> wrote: I wouldn't be opposed to implementing this as a part of the join optimizer in MySQL, in fact, I've been reading through it for a few days now... However, it seems like it would be a large project as the join optimizer does not take WHERE conditions on the joins into account at all when estimating number of rows coming from a table. In addition, I would probably need to start storing some more metadata in order to facillitate the kind of optimization I need... has anyone thought We will definitely take a look at this optimizer glitch. -- Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
Well, I would definitely have to do the count for each query; not because my table sizes are changing (although they are at a fairly rapid rate), but because the number of rows I want to select is vastly different between queries. This is actually a self-join (refer to first emails from me to this list a few days ago), so I can't just count how many rows are in each table, because there is only one. The problem is that each self-join in my query has widely varying number of rows it returns based on what range I specify for the nvalue column. So I'm not really sure what to do...Wouldn't doing a COUNT on each self-join involved table take almost as much time as running the query on each table? I wouldn't be opposed to implementing this as a part of the join optimizer in MySQL, in fact, I've been reading through it for a few days now... However, it seems like it would be a large project as the join optimizer does not take WHERE conditions on the joins into account at all when estimating number of rows coming from a table. In addition, I would probably need to start storing some more metadata in order to facillitate the kind of optimization I need... has anyone thought about doing this? eric. On Sat, Oct 20, 2001 at 07:52:28PM +0300, Sinisa Milivojevic wrote: > Eric writes: > > I have no problem using STRAIGHT_JOIN, etc. My problem is really just > > figuring out the optimal join order. Is doing a "SELECT COUNT" on > > each of the tables I'm going to join the way to do it? Isn't there > > potential for the count to take as long as the full query processing > > would take (especially since the attribute I'm doing a range on is not > > indexed)? > > > > eric. > > > > -- > > _ _ > > | |(_) http://ir.iit.edu/~ej > > | _| | | Page me via ICQ at > > | |___ | | http://wwp.mirabilis.com/19022931 > > |__/ | or by mailing [EMAIL PROTECTED] > > |__/ > > > > Well , first of all this is recommandation only until we further > improve the optimiser. > > In most applications relative sizes of tables do not change > drastically. It is extremely rare that a tabla A that has 1000 times > less rows then table B would in short time have 1000 times more rows > then table B. > > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > -- _ _ | |(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |__/ | or by mailing [EMAIL PROTECTED] |__/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
Eric writes: > I have no problem using STRAIGHT_JOIN, etc. My problem is really just > figuring out the optimal join order. Is doing a "SELECT COUNT" on > each of the tables I'm going to join the way to do it? Isn't there > potential for the count to take as long as the full query processing > would take (especially since the attribute I'm doing a range on is not > indexed)? > > eric. > > -- > _ _ > | |(_) http://ir.iit.edu/~ej > | _| | | Page me via ICQ at > | |___ | | http://wwp.mirabilis.com/19022931 > |__/ | or by mailing [EMAIL PROTECTED] > |__/ > Well , first of all this is recommandation only until we further improve the optimiser. In most applications relative sizes of tables do not change drastically. It is extremely rare that a tabla A that has 1000 times less rows then table B would in short time have 1000 times more rows then table B. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
The information I require is the number of rows that will come from a SELECT which places a certain range restriction on an unindexed attribute...and I need this to not take much time relative to actually executing the query (constant time would be best). eric. On Sat, Oct 20, 2001 at 03:06:58PM +0300, Sinisa Milivojevic wrote: > Eric writes: > > Well, answering my own email, what I thought was a bug is not one at > > all. I was mistaken in thinking that MySQL paid any attention to the > > WHERE conditions when optimizing the join order beyond determining > > which keys are used for the join, correct? > > > > This is really terrible for queries like mine where the query could be > > sped up by orders of magnitude if the join optimizer would just > > determine which table in the join to scan and which to do the key > > lookup on based on a more intelligent estimation of the number of rows > > from each table. It would have to go beyond looking at what keys are > > used in the join (since each of the tables in my query can be looked > > up by the same key) and account for the WHERE conditions placed on the > > tables in the join. > > > > Is there sufficient metadata to estimate rows coming from a table > > based on conditions placed on the attributes of that table? Where is > > it? Has anyone ever thought of coding this? Can anyone give me a > > place to start? > > > > eric. > > > > > > -- > > _ _ > > | |(_) http://ir.iit.edu/~ej > > | _| | | Page me via ICQ at > > | |___ | | http://wwp.mirabilis.com/19022931 > > |__/ | or by mailing [EMAIL PROTECTED] > > |__/ > > > > > What information do you precisely require ?? > > The answer also depends on the API you are using and a method of > retrieval. > > For example, you can know how many rows you get if you use _store_ > instead of _use_ method, but that method is not applicable in the case > of larger result sets. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail [EMAIL PROTECTED] > To unsubscribe, e-mail <[EMAIL PROTECTED]> > -- _ _ | |(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |__/ | or by mailing [EMAIL PROTECTED] |__/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
I have no problem using STRAIGHT_JOIN, etc. My problem is really just figuring out the optimal join order. Is doing a "SELECT COUNT" on each of the tables I'm going to join the way to do it? Isn't there potential for the count to take as long as the full query processing would take (especially since the attribute I'm doing a range on is not indexed)? eric. On Sat, Oct 20, 2001 at 02:30:13PM +0300, Sinisa Milivojevic wrote: > On Fri, 19 Oct 2001 13:03:02 -0500 > Eric <[EMAIL PROTECTED]> wrote: > > > > Well, answering my own email, what I thought was a bug is not one at > > all. I was mistaken in thinking that MySQL paid any attention to the > > WHERE conditions when optimizing the join order beyond determining > > which keys are used for the join, correct? > > > > This is really terrible for queries like mine where the query could be > > sped up by orders of magnitude if the join optimizer would just > > determine which table in the join to scan and which to do the key > > lookup on based on a more intelligent estimation of the number of rows > > from each table. It would have to go beyond looking at what keys are > > used in the join (since each of the tables in my query can be looked > > up by the same key) and account for the WHERE conditions placed on the > > tables in the join. > > > > Is there sufficient metadata to estimate rows coming from a table > > based on conditions placed on the attributes of that table? Where is > > it? Has anyone ever thought of coding this? Can anyone give me a > > place to start? > > > > eric. > > HI! > > At least, with LEFT JOIN you can always specify precisely which table is to be >scanned and which to be searched by key. > > In other joins, you can use STRAIGHT_JOIN with correct order of tables in order to >achieve the same. > > Yes, the above is little bit harder to be done with dynamically created queries, but >it is still possible, as you can always get number of rows. > > -- > > Regards, > > -- > For technical support contracts, go to https://order.mysql.com/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com -- _ _ | |(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |__/ | or by mailing [EMAIL PROTECTED] |__/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
Eric writes: > Well, answering my own email, what I thought was a bug is not one at > all. I was mistaken in thinking that MySQL paid any attention to the > WHERE conditions when optimizing the join order beyond determining > which keys are used for the join, correct? > > This is really terrible for queries like mine where the query could be > sped up by orders of magnitude if the join optimizer would just > determine which table in the join to scan and which to do the key > lookup on based on a more intelligent estimation of the number of rows > from each table. It would have to go beyond looking at what keys are > used in the join (since each of the tables in my query can be looked > up by the same key) and account for the WHERE conditions placed on the > tables in the join. > > Is there sufficient metadata to estimate rows coming from a table > based on conditions placed on the attributes of that table? Where is > it? Has anyone ever thought of coding this? Can anyone give me a > place to start? > > eric. > > > -- > _ _ > | |(_) http://ir.iit.edu/~ej > | _| | | Page me via ICQ at > | |___ | | http://wwp.mirabilis.com/19022931 > |__/ | or by mailing [EMAIL PROTECTED] > |__/ > What information do you precisely require ?? The answer also depends on the API you are using and a method of retrieval. For example, you can know how many rows you get if you use _store_ instead of _use_ method, but that method is not applicable in the case of larger result sets. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
On Fri, 19 Oct 2001 13:03:02 -0500 Eric <[EMAIL PROTECTED]> wrote: > Well, answering my own email, what I thought was a bug is not one at > all. I was mistaken in thinking that MySQL paid any attention to the > WHERE conditions when optimizing the join order beyond determining > which keys are used for the join, correct? > > This is really terrible for queries like mine where the query could be > sped up by orders of magnitude if the join optimizer would just > determine which table in the join to scan and which to do the key > lookup on based on a more intelligent estimation of the number of rows > from each table. It would have to go beyond looking at what keys are > used in the join (since each of the tables in my query can be looked > up by the same key) and account for the WHERE conditions placed on the > tables in the join. > > Is there sufficient metadata to estimate rows coming from a table > based on conditions placed on the attributes of that table? Where is > it? Has anyone ever thought of coding this? Can anyone give me a > place to start? > > eric. HI! At least, with LEFT JOIN you can always specify precisely which table is to be scanned and which to be searched by key. In other joins, you can use STRAIGHT_JOIN with correct order of tables in order to achieve the same. Yes, the above is little bit harder to be done with dynamically created queries, but it is still possible, as you can always get number of rows. -- Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
Well, answering my own email, what I thought was a bug is not one at all. I was mistaken in thinking that MySQL paid any attention to the WHERE conditions when optimizing the join order beyond determining which keys are used for the join, correct? This is really terrible for queries like mine where the query could be sped up by orders of magnitude if the join optimizer would just determine which table in the join to scan and which to do the key lookup on based on a more intelligent estimation of the number of rows from each table. It would have to go beyond looking at what keys are used in the join (since each of the tables in my query can be looked up by the same key) and account for the WHERE conditions placed on the tables in the join. Is there sufficient metadata to estimate rows coming from a table based on conditions placed on the attributes of that table? Where is it? Has anyone ever thought of coding this? Can anyone give me a place to start? eric. On Thu, Oct 18, 2001 at 10:40:43AM -0500, Eric wrote: > I am sending this again as I am desperate for some help and believe > this to be a signifigant bug if it actually is one...which it seems to > be. See below for examples. > > What is quite puzzling is MySQL's estimation of the number of rows > from each of the self-joins. The conditions on alias queryTable0 > actually refer to 1582 rows, and the conditions on alias queryTable1 > refer to 39 rows. Notice in the EXPLAIN below that when I flip around > the join order, MySQL thinks that 1152 (which is its estimation for > 1582) rows are coming from queryTable1, whereas with the original join > order, it thought 1152 rows were coming from queryTable0...this seems > like a bug to me since the conditions on those two aliases are the > same between the two queries. Only the "FROM index queryTable0, index > queryTable1" is flipped to "FROM index queryTable1, index queryTable0". > > SELECT DISTINCT queryTable0.num, queryTable0.value, queryTable1.value > FROM > index queryTable0, index queryTable1 WHERE > queryTable0.path=24 AND queryTable0.type="E" AND > queryTable1.path=27 AND queryTable1.type="E" AND > queryTable0.num=queryTable1.num AND > queryTable0.nvalue > 0.0 AND queryTable0.nvalue <= 90.0 AND > queryTable1.nvalue > 140.0 AND queryTable1.nvalue <= 200.0; > > >+-+--+--++-+++--+-+ > | table | type | possible_keys| key| key_len | > ref| rows | Extra | > >+-+--+--++-+++--+-+ > | queryTable0 | ref | pathndx,numndx | pathndx | 4 | > const | 1152 | where used; Using temporary | > | queryTable1 | ref | pathndx,numndx | numndx | 4 | > queryTable0.num | 53 | where used | > >+-+--+--++-+++--+-+ > 2 rows in set (0.01 sec) > > On Wed, Oct 17, 2001 at 04:04:21PM +0300, Michael Widenius wrote: > > We have done some modifications to optimizer in 4.0, but nothing that > > should affect this. > > > > What is the output from EXPLAIN if you swap the tables ? > > EXPLAIN of query with "FROM index queryTable1, index queryTable0": > > >+-+--+--++-++--+-+ > | table | type | possible_keys| key| key_len | > ref| rows | Extra | > >+-+--+--++-++--+-+ > | queryTable1 | ref | pathndx,numndx | pathndx | 4 | > const | 1152 | where used; Using temporary | > | queryTable0 | ref | pathndx,numndx | numndx | 4 | > queryTable1.num | 53 | where used | > >+-+--+--++-++--+-+ > 2 rows in set (0.01 sec) > > > What is the output from "show create table 'index'" > > CREATE TABLE is: > > CREATE TABLE `index` ( > `indexnum` int(10) unsigned NOT NULL auto_increment, > `parent` int(10) unsigned NOT NULL default '0', > `path` int(10) unsigned NOT NULL default '0', > `type` char(1) NOT NULL default '', > `tagname` int(10) unsigned NOT NULL default '0', > `atrname` int(10) unsigned NOT NULL default '0', > `num` int(10) unsigned NOT NULL default '0', > `nvalue` double default NULL, > `value` mediumtext, > PRIMARY KEY (`indexnum`), > KEY `parentndx`(`parent`), > KEY `pathndx`(`path`), > KEY `tagnamendx`(`tagname`), > KEY `atrnamendx`(`atrname`), > KEY `numndx`(`num`), > ) TYPE=MyISAM MAX_ROWS=315360 PACK_KEYS=1 > > -- > _ _ > | ___
Possible bug in self-join order optimization
I am sending this again as I am desperate for some help and believe this to be a signifigant bug if it actually is one...which it seems to be. See below for examples. What is quite puzzling is MySQL's estimation of the number of rows from each of the self-joins. The conditions on alias queryTable0 actually refer to 1582 rows, and the conditions on alias queryTable1 refer to 39 rows. Notice in the EXPLAIN below that when I flip around the join order, MySQL thinks that 1152 (which is its estimation for 1582) rows are coming from queryTable1, whereas with the original join order, it thought 1152 rows were coming from queryTable0...this seems like a bug to me since the conditions on those two aliases are the same between the two queries. Only the "FROM index queryTable0, index queryTable1" is flipped to "FROM index queryTable1, index queryTable0". SELECT DISTINCT queryTable0.num, queryTable0.value, queryTable1.value FROM index queryTable0, index queryTable1 WHERE queryTable0.path=24 AND queryTable0.type="E" AND queryTable1.path=27 AND queryTable1.type="E" AND queryTable0.num=queryTable1.num AND queryTable0.nvalue > 0.0 AND queryTable0.nvalue <= 90.0 AND queryTable1.nvalue > 140.0 AND queryTable1.nvalue <= 200.0; +-+--+--++-+++--+-+ | table | type | possible_keys| key| key_len | ref| rows | Extra | +-+--+--++-+++--+-+ | queryTable0 | ref | pathndx,numndx | pathndx | 4 | const | 1152 | where used; Using temporary | | queryTable1 | ref | pathndx,numndx | numndx | 4 | queryTable0.num | 53 | where used | +-+--+--++-+++--+-+ 2 rows in set (0.01 sec) On Wed, Oct 17, 2001 at 04:04:21PM +0300, Michael Widenius wrote: > We have done some modifications to optimizer in 4.0, but nothing that > should affect this. > > What is the output from EXPLAIN if you swap the tables ? EXPLAIN of query with "FROM index queryTable1, index queryTable0": +-+--+--++-++--+-+ | table | type | possible_keys| key| key_len | ref| rows | Extra | +-+--+--++-++--+-+ | queryTable1 | ref | pathndx,numndx | pathndx | 4 | const | 1152 | where used; Using temporary | | queryTable0 | ref | pathndx,numndx | numndx | 4 | queryTable1.num | 53 | where used | +-+--+--++-++--+-+ 2 rows in set (0.01 sec) > What is the output from "show create table 'index'" CREATE TABLE is: CREATE TABLE `index` ( `indexnum` int(10) unsigned NOT NULL auto_increment, `parent` int(10) unsigned NOT NULL default '0', `path` int(10) unsigned NOT NULL default '0', `type` char(1) NOT NULL default '', `tagname` int(10) unsigned NOT NULL default '0', `atrname` int(10) unsigned NOT NULL default '0', `num` int(10) unsigned NOT NULL default '0', `nvalue` double default NULL, `value` mediumtext, PRIMARY KEY (`indexnum`), KEY `parentndx`(`parent`), KEY `pathndx`(`path`), KEY `tagnamendx`(`tagname`), KEY `atrnamendx`(`atrname`), KEY `numndx`(`num`), ) TYPE=MyISAM MAX_ROWS=315360 PACK_KEYS=1 -- _ _ | |(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |__/ | or by mailing [EMAIL PROTECTED] |__/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug?
Getting an error message in the log saying... fp=(nil), etc, etc, possible bug from ibuf0ibuf.c, line 2339 What're the possible reasons I could be getting this bug? Oh, forgot... MySQL-max 3.23.42 innodb Red Hat 6.2(2.2.14-6.1.1.smp) Intel 866Mhz, 512MB Ram, 20+ GB SCSI HDD Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug with Solaris Intel?
Hi,
I'm getting this error with Mysql when trying to connect using a TCP
connection (not localhost connection):
"ERROR 2013: Lost connection to MySQL server during query"
This only happens on our Solaris Intel boxes. Our installations on
Solaris Sparc work fine. Also, if we downgrade to MySQL 3.22, this error
goes away. Connections to localhost always work fine, this only happens
on TCP connections. We are also compiling with Sun CC compiler.
Is this a known issue?
We are able to reproduce the problem on Intel Solaris 7 and 8, and
tested both MySQL 3.23.32 and 3.23.48.
configuration parameters:
CC=cc \
CXX=CC \
CFLAGS="-O -I${PREFIX}/include -I/usr/local/include"\
CXXFLAGS="-O -I${PREFIX}/include -I/usr/local/include" \
CPPFLAGS="-I${PREFIX}/include -I/usr/local/include
-I/usr/openwin/include/X11" \
LDFLAGS="-s -L${PREFIX}/lib -L/usr/local/lib
-R${PREFIX}etc/mysql/lib:${PREFIX}/lib:/usr/local/lib" \
./configure \
--prefix=${PREFIX}/etc/mysql\
--without-debug \
--without-bench \
--with-mysqld-user=mysql\
--with-libwrap \
--enable-thread-safe-client \
--enable-large-files\
--with-berkely-db
Monte
--
Monte Ohrt <[EMAIL PROTECTED]>
http://www.ispi.net/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql deadlock situation - possible bug
Hi! > "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: Sinisa> Aaron Brick writes: >> good day - >> >> i have found cases in which the daemon blocks waiting on a select() and >> just plain refuses to process certain queries. since i am reading in a 60 >> meg file, meaning about 3.5 million queries, this consistently happens >> somewhere in the middle. i am using 3.23.41. What does 'mysqladmin proc' say when this happens ? If it says 'system lock' then this is a bug in the lockd daemon and you should start mysqld with '--skip-locking' or remove 'enable-locking' from your my.cnf file. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Optimization question and possible bug
No, they do not differ from the rest. The only thing is that they need longer time to process (between 1 and 4 seconds, normally the SAME query is processed in a few miliseconds). The only correlation i found is that all queries in that log query the merge table. How does mysql handle locks on merge-tables? Maybe it has do to with some locking problembut it's just another suspicion. -Ursprungliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Adams, Bill TQO Gesendet: Freitag, 7. September 2001 17:37 An: Stefan Pinkert Cc: [EMAIL PROTECTED] Betreff: Re: Optimization question and possible bug . > In the database i have a merge-table that merges 10 myisam tables. > Sometimes the loadaverage of the system raise above 50 and the > long-query-log is > filled with some query accessing the merge table. This happens 2-3 times a > day. > Only a minute later everthing is okay again without doing anything. > I can't believe that it is a performance problem because there is a > summary of only 10,000 entries in the merge table and 50,000 entries in > other > tables. Does anybody experienced this strange problem, too? Is the long query different than the other queries? Have you done an expain on it? . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
