Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Very interesting. Waiting for update. On Jun 15, 2011 4:51 AM, Hank hes...@gmail.com wrote: The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Re: optimization strategies based on file-level storage
At 11:45 AM 6/14/2011, Johan De Meersman wrote: - Original Message - From: Bennett Haselton benn...@peacefire.org modifications. (For example, the question I asked earlier about whether you can declare extra space at the end of each row that is reserved for future columns.) That question I can answer: you can't reserve space, but if you know what kind of rows you'll want to add later you can pre-add them (and incur the accompanying storage cost), and simply rename them appropriately later. Thanks. It would be more flexible if I could declare, say, 50 bytes, and decide later if I wanted to use them for a datetime, a char(n), or an int, but this is still helpful :) Do you happen to know the answer to my other problem -- if I have TEXT and BLOB columns but all my other columns are fixed-length, can I still get the benefit of faster lookups resulting from fixed-length rows, if each row just contains a fixed-length reference to the actual TEXT or BLOB data which is stored elsewhere? -Bennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query on wait_timeout
Hi, Small doubt for wait_timeout. If my wait_timeout is set for 180 seconds and if any deadlock occures and both query are waiting to execute. What wil happen in that case? 1. Do the connection will wait till deadlock is removed or 2. Connection will close after 180 seconds as both queries are ideal and waiting for each other. Thanks, Yogesh
Re: Query on wait_timeout
Good question Yogesh, I can say the best solution is Create a deadlock and test it, you will come to know more about it. On Wed, Jun 15, 2011 at 3:38 PM, Yogesh Kore yogeshk...@gmail.com wrote: Hi, Small doubt for wait_timeout. If my wait_timeout is set for 180 seconds and if any deadlock occures and both query are waiting to execute. What wil happen in that case? 1. Do the connection will wait till deadlock is removed or 2. Connection will close after 180 seconds as both queries are ideal and waiting for each other. Thanks, Yogesh -- Thanks Suresh Kuna MySQL DBA
Lock wait timeout
I'm getting a Lock wait timeout exceeded error and I'm not sure why. I've been trying to read this SHOW ENGINE INNODB STATUS output, but I don't understand what it's trying to tell me. Can someone give me a hand here? ---TRANSACTION 1942A27B, ACTIVE 124 sec, process no 4849, OS thread id 1311476032 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 992511, query id 903933959 192.168.100.41 ecash Updating UPDATE trans_item SET is_void=1,void_bus_date='2011-06-15 00:00:00',void_actual_dt='2011-06-15 19:46:57',void_store_id=0,void_user_id=1,void_drawer_id=0,void_hdr_new_status_id=12 WHERE company_id=30 AND id=47077 Trx read view will not see trx with id = 1942A27C, sees 19428B97 --- TRX HAS BEEN WAITING 124 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 233406 page no 2296 n bits 136 index `PRIMARY` of table `745cash_ecash`.`trans_item` trx id 1942A27B lock_mode X locks rec but not gap waiting Record lock, heap no 63 PHYSICAL RECORD: n_fields 45; compact format; info bits 0 This is the very first statement in a series of statements, so i'm not quite sure why it would fail. I could understand a later statement failing due to this one having the table locked or something. MySQL v5.5.9-log -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Oops... big typo in above steps... add the following line: replicate-ignore-table=db.log to the SLAVE my.cnf, and restart the SLAVE server. The master does not need to be restarted or changed. Just the SLAVE. Sorry about that. -Hank Eskin On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.comwrote: Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
No worries! I think I would have figured that out! I'll feedback you tomorrow. Thanks again Claudio 2011/6/15 Hank hes...@gmail.com Oops... big typo in above steps... add the following line: replicate-ignore-table=db.log to the SLAVE my.cnf, and restart the SLAVE server. The master does not need to be restarted or changed. Just the SLAVE. Sorry about that. -Hank Eskin On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.com wrote: Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio -- Claudio
Re: Query on wait_timeout
How we can create a deadlock manually to test this problem. Thanks Suresh Kuna wrote: Good question Yogesh, I can say the best solution is Create a deadlock and test it, you will come to know more about it. On Wed, Jun 15, 2011 at 3:38 PM, Yogesh Kore yogeshk...@gmail.com wrote: Hi, Small doubt for wait_timeout. If my wait_timeout is set for 180 seconds and if any deadlock occures and both query are waiting to execute. What wil happen in that case? 1. Do the connection will wait till deadlock is removed or 2. Connection will close after 180 seconds as both queries are ideal and waiting for each other. Thanks, Yogesh
