Description:
It's a race condition.'t' is a MyISAM table for example (not checked with other table types).
thread 1 does DELETE FROM t; (no WHERE clause)
thread 2 does INSERT INTO t values(10);
the INSERT is executed&binlogged after the DELETE is executed but before the DELETE is binlogged.
Consequence : in the master 't' finally contains 1 row, while in the binlog we have
INSERT
DELETE
which causes 't' to be empty on the slave.
Code in sql/sql_delete.cc seems different in 4.0 than in 3.23, so 4.0 may not be bugged; a person that knows this code well could tell.
How to repeat:
To provoke the race condition for sure, apply this patch to sql/sql_delete.cc :
===== sql_delete.cc 1.39 vs edited =====
*** /tmp/sql_delete.cc-1.39-7002 Fri Jul 19 20:57:52 2002
--- edited/sql_delete.cc Wed Mar 19 21:32:22 2003
***************
*** 106,111 ****
--- 106,113 ----
}
if (!error)
{
+ char s[10];
+ fscanf(stdin,"%s",s);
mysql_update_log.write(thd,thd->query,thd->query_length);
if (mysql_bin_log.is_open())
{
Do CREATE TABLE t (a int); INSERT INTO t values(10); DELETE FROM t; on the master. The master will DELETE all rows from t,
but will wait for you to type something on the keyboard before writing to the binlog. But before typing something, open another 'mysql' client and send
INSERT INTO t VALUES(10);
This INSERT will be executed immediately, binlogged, and will return.
Then type something, and the DELETE will be binlogged. You have managed to
write to the binlog in the reverse order of execution. Example : see how timestamps are in wrong order :
# at 131
#030319 22:07:35 server id 1 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1048108055;
insert into t values(10);
# at 189
#030319 22:07:32 server id 1 Query thread_id=1 exec_time=5 error_code=0
SET TIMESTAMP=1048108052;
delete from t;
Suggested fix:
Monty, I send this to you for instant fix :)
Description: It's a race condition.'t' is a MyISAM table for example (not checked with other table types). thread 1 does DELETE FROM t; (no WHERE clause) thread 2 does INSERT INTO t values(10); the INSERT is executed&binlogged after the DELETE is executed but before the DELETE is binlogged. Consequence : in the master 't' finally contains 1 row, while in the binlog we have INSERT DELETE which causes 't' to be empty on the slave. Code in sql/sql_delete.cc seems different in 4.0 than in 3.23, so 4.0 may not be bugged; a person that knows this code well could tell. How to repeat: To provoke the race condition for sure, apply this patch to sql/sql_delete.cc : ===== sql_delete.cc 1.39 vs edited ===== *** /tmp/sql_delete.cc-1.39-7002 Fri Jul 19 20:57:52 2002 --- edited/sql_delete.cc Wed Mar 19 21:32:22 2003 *************** *** 106,111 **** --- 106,113 ---- } if (!error) { + char s[10]; + fscanf(stdin,"%s",s); mysql_update_log.write(thd,thd->query,thd->query_length); if (mysql_bin_log.is_open()) { Do CREATE TABLE t (a int); INSERT INTO t values(10); DELETE FROM t; on the master. The master will DELETE all rows from t, but will wait for you to type something on the keyboard before writing to the binlog. But before typing something, open another 'mysql' client and send INSERT INTO t VALUES(10); This INSERT will be executed immediately, binlogged, and will return. Then type something, and the DELETE will be binlogged. You have managed to write to the binlog in the reverse order of execution. Example : see how timestamps are in wrong order : # at 131 #030319 22:07:35 server id 1 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1048108055; insert into t values(10); # at 189 #030319 22:07:32 server id 1 Query thread_id=1 exec_time=5 error_code=0 SET TIMESTAMP=1048108052; delete from t; Suggested fix: Monty, I send this to you for instant fix :)