Description:
We have discovered that a multiple table delete does not get executed on slave hosts, even
though it is replicated through the binary logs.
Executed on Slave:
- delete from test_1;
- delete from test_2 where i = 1;
Not Execute on Slave:
- delete test_1, test_2 from test_1, test_2 where test_1.i = test_2.i and test_1.i = 1;
- delete test_1, test_2 from test_1 right join test_2 on test_1.i = test_2.i where
test_1.i = 1;
- delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where test_1.i
= 1;
This is a serious problem for us, as now we have to use much slower and more cumbersome
delete syntax to handle a 5 table joined delete. We have spent a long time trying to get
around the error.
We have found that replicate-do-db does work correctly, but suffers from the problems of
cross-database queries not being executed. We could not come up with a combination of
replicate-do-db and replicate-wild-do-table that would work (one always took precedence
over the other).
How to repeat:
--------- Slave Configuration ---------
[mysqld]
# replication setup...
# allow replication of test
replicate-wild-do-table=test.%
# Note: The alternative does NOT suffer this problem, but
# does not handle cross-database queries which we require.
#replicate-do-db=test
--------- Execute On Master ---------
mysql> create database test;
Query OK, 1 row affected (0.03 sec)
mysql> use test;
Database changed
mysql> create table test_1 (i int);
Query OK, 0 rows affected (0.06 sec)
mysql> create table test_2 (i int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test_2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_1, test_2;
+------+------+
| i | i |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
# Alternative deletes that suffer same problem:
# delete test_1, test_2 from test_1, test_2 where test_1.i = test_2.i and test_1.i = 1;
# delete test_1, test_2 from test_1 right join test_2 on test_1.i = test_2.i where
test_1.i = 1;
mysql> delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where
test_1.i = 1;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_1, test_2;
Empty set (0.00 sec)
--------- Slave Binary Log ---------
#040127 15:40:53 server id 2 log_pos 81025449 Query thread_id=77624 exec_time=0
error_code=0
SET TIMESTAMP=1075178453;
create database test;
# at 332473
#040127 15:41:00 server id 2 log_pos 81025518 Query thread_id=77624 exec_time=1
error_code=0
use test;
SET TIMESTAMP=1075178460;
create table test_1 (i int);
# at 332535
#040127 15:41:02 server id 2 log_pos 81025580 Query thread_id=77624 exec_time=0
error_code=0
SET TIMESTAMP=1075178462;
create table test_2 (i int);
# at 332597
#040127 15:41:06 server id 2 log_pos 81025642 Query thread_id=77624 exec_time=0
error_code=0
SET TIMESTAMP=1075178466;
insert into test_1 values(1);
# at 332660
#040127 15:41:08 server id 2 log_pos 81025705 Query thread_id=77624 exec_time=0
error_code=0
SET TIMESTAMP=1075178468;
insert into test_2 values(1);
# at 332723
#040127 15:41:20 server id 2 log_pos 81025768 Query thread_id=77624 exec_time=0
error_code=0
SET TIMESTAMP=1075178480;
delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where test_1.i =
1;
--------- Execute On Slave ---------
mysql> use test;
Database changed
mysql> select * from test_1, test_2;
+------+------+
| i | i |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
# Replication BUG:
# ^ These should have been deleted as per the binary log ^
# - If you execute the multi-table delete on the slave it successfully deletes from the
tables as you expect.
# - There are no replication errors in log or in 'SHOW SLAVE STATUS'
Suggested fix:
The replicate-wild-do-table parser does not appear to know how to parse a multi-table
delete so just assumes that it does not apply.
This should be modified to handle this format query.
Description: We have discovered that a multiple table delete does not get executed on slave hosts, even though it is replicated through the binary logs. Executed on Slave: - delete from test_1; - delete from test_2 where i = 1; Not Execute on Slave: - delete test_1, test_2 from test_1, test_2 where test_1.i = test_2.i and test_1.i = 1; - delete test_1, test_2 from test_1 right join test_2 on test_1.i = test_2.i where test_1.i = 1; - delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where test_1.i = 1; This is a serious problem for us, as now we have to use much slower and more cumbersome delete syntax to handle a 5 table joined delete. We have spent a long time trying to get around the error. We have found that replicate-do-db does work correctly, but suffers from the problems of cross-database queries not being executed. We could not come up with a combination of replicate-do-db and replicate-wild-do-table that would work (one always took precedence over the other). How to repeat: --------- Slave Configuration --------- [mysqld] # replication setup... # allow replication of test replicate-wild-do-table=test.% # Note: The alternative does NOT suffer this problem, but # does not handle cross-database queries which we require. #replicate-do-db=test --------- Execute On Master --------- mysql> create database test; Query OK, 1 row affected (0.03 sec) mysql> use test; Database changed mysql> create table test_1 (i int); Query OK, 0 rows affected (0.06 sec) mysql> create table test_2 (i int); Query OK, 0 rows affected (0.04 sec) mysql> insert into test_1 values(1); Query OK, 1 row affected (0.02 sec) mysql> insert into test_2 values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from test_1, test_2; +------+------+ | i | i | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) # Alternative deletes that suffer same problem: # delete test_1, test_2 from test_1, test_2 where test_1.i = test_2.i and test_1.i = 1; # delete test_1, test_2 from test_1 right join test_2 on test_1.i = test_2.i where test_1.i = 1; mysql> delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where test_1.i = 1; Query OK, 2 rows affected (0.00 sec) mysql> select * from test_1, test_2; Empty set (0.00 sec) --------- Slave Binary Log --------- #040127 15:40:53 server id 2 log_pos 81025449 Query thread_id=77624 exec_time=0 error_code=0 SET TIMESTAMP=1075178453; create database test; # at 332473 #040127 15:41:00 server id 2 log_pos 81025518 Query thread_id=77624 exec_time=1 error_code=0 use test; SET TIMESTAMP=1075178460; create table test_1 (i int); # at 332535 #040127 15:41:02 server id 2 log_pos 81025580 Query thread_id=77624 exec_time=0 error_code=0 SET TIMESTAMP=1075178462; create table test_2 (i int); # at 332597 #040127 15:41:06 server id 2 log_pos 81025642 Query thread_id=77624 exec_time=0 error_code=0 SET TIMESTAMP=1075178466; insert into test_1 values(1); # at 332660 #040127 15:41:08 server id 2 log_pos 81025705 Query thread_id=77624 exec_time=0 error_code=0 SET TIMESTAMP=1075178468; insert into test_2 values(1); # at 332723 #040127 15:41:20 server id 2 log_pos 81025768 Query thread_id=77624 exec_time=0 error_code=0 SET TIMESTAMP=1075178480; delete test_1, test_2 from test_1 left join test_2 on test_1.i = test_2.i where test_1.i = 1; --------- Execute On Slave --------- mysql> use test; Database changed mysql> select * from test_1, test_2; +------+------+ | i | i | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) # Replication BUG: # ^ These should have been deleted as per the binary log ^ # - If you execute the multi-table delete on the slave it successfully deletes from the tables as you expect. # - There are no replication errors in log or in 'SHOW SLAVE STATUS' Suggested fix: The replicate-wild-do-table parser does not appear to know how to parse a multi-table delete so just assumes that it does not apply. This should be modified to handle this format query.