Bug #2527 Multi-Table Delete - Not Replication use replicate-wild-do-table

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.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in ChangeSet@1.1691, 2004-01-29 19:22:29+01:00, guilhem@mysql.com