Bug #15699 multi-table update does not work with replicate-wild-do-table
Submitted: 13 Dec 2005 4:38 Modified: 2 Feb 2006 4:51
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1.17, 5.0.17 OS:Linux (linux, any)
Assigned to: Andrei Elkin CPU Architecture:Any

[13 Dec 2005 4:38] Timothy Smith
Description:
A multi-table update statement is run on the slave, even when it does not match any replicate-wild-do-table=* rules.

I tested this on both MySQL 5.0 and 4.1.

How to repeat:
I used two fresh data directories.  I start the master:

./bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/data
--tmpdir=$PWD/tmp --log-error=$PWD/data/log.err --socket=mysql.sock --port=33000 --server-id=33000 --log-bin=binlog  &

I start the slave:

./bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/data
--tmpdir=$PWD/tmp --log-error=$PWD/data/log.err --socket=mysql.sock --port=33001 --server-id=33001 --skip-networking --report-host=slave0a --replicate-wild-do-table=a.% &

Note: the 'mm m' and 'mm s' commands just switch MYSQL_UNIX_PORT between the two directories; so after 'mm m', any mysql clients are talking to the master, for example.

17:26 ~/m/csc/7606$ mm m
Setting MySQL environment to '/home/tim/m/csc/7606/m'
mysqld is alive
17:26 ~/m/csc/7606$ mysql < bug1.sql 
17:26 ~/m/csc/7606$ echo $?
0
17:26 ~/m/csc/7606$ mm s
Setting MySQL environment to '/home/tim/m/csc/7606/s'
mysqld is alive
17:26 ~/m/csc/7606$ mysql < bug2.sql 
Database
a
mysql
test
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: tim
                Master_Port: 33000
              Connect_Retry: 60
            Master_Log_File: binlog.000001
        Read_Master_Log_Pos: 429
             Relay_Log_File: siva-relay-bin.000002
              Relay_Log_Pos: 469
      Relay_Master_Log_File: binlog.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: a.%
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 429
            Relay_Log_Space: 469
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
17:26 ~/m/csc/7606$ mm m
Setting MySQL environment to '/home/tim/m/csc/7606/m'
mysqld is alive
17:27 ~/m/csc/7606$ mysql < bug3.sql 
17:27 ~/m/csc/7606$ mm s
Setting MySQL environment to '/home/tim/m/csc/7606/s'
mysqld is alive
17:27 ~/m/csc/7606$ mysql < bug2.sql
Database
a
mysql
test
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: tim
                Master_Port: 33000
              Connect_Retry: 60
            Master_Log_File: binlog.000001
        Read_Master_Log_Pos: 500
             Relay_Log_File: siva-relay-bin.000002
              Relay_Log_Pos: 469
      Relay_Master_Log_File: binlog.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: a.%
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'b.x' doesn't exist' on query. Default database: 'b'. Query: 'update x join y using (id) set x.id = 0'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 429
            Relay_Log_Space: 540
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL
17:27 ~/m/csc/7606$ 

Suggested fix:

As a workaround, create a dummy 'b' database on the slave, with dummy tables.  It may work for some scenarios.
[13 Dec 2005 4:42] Timothy Smith
Sorry, I forgot to include the .sql files:

17:41 ~/m/csc/7606$ cat bug1.sql
create database a;
create database b;
use b;
create table x (id int);
create table y (id int);
insert into x values (1), (2), (3), (4), (5);
insert into y select id + 3 from x;

17:41 ~/m/csc/7606$ cat bug2.sql
show databases;
show slave status\G

17:41 ~/m/csc/7606$ cat bug3.sql
use b;
update x join y using (id) set x.id = 0;
[4 Jan 2006 9:32] Joakim Ahlen
Hi,

I am getting the same bug i think. Although using a slightly more complicated query involving spatial extensions and the use of the DATABASE()-function which maybe could cause the binlog to behave unexpectedly, i think the error has to do with the use of left join in an update query rather than any of these extra features.

Query: UPDATE item LEFT JOIN content_db.db ON mysql_db=DATABASE() SET boundingbox=GeomFromText(CONCAT('POLYGON((',item.y1 / 10,' ',item.x1 / 10,', ',item.y1 / 10,' ',item.x2 / 10,', ',item.y2 / 10,' ',item.x2 / 10,', ',item.y2 / 10,' ',item.x1 / 10,', ',item.y1 / 10,' ',item.x1 / 10,'))'), 2400) WHERE coordinate_system_id=1 AND type_id=2 or type_id=3;

The use of the DATABASE()-function is because this query is always run with the mysql command line client with:

mysql -uxx -pxx my_database < query.sql

after which the DATABASE()-function will return "my_database".

Anyways, a SHOW SLAVE STATUS on the slave returns the following in the Last_error column:

Error 'Table 'customer_82_823.item' doesn't exist' on query. Default database: 'customer_82_823'. Query: 'UPDATE item LEFT JOIN content_db.db ON mysql_db=DATABASE() SET boundingbox=GeomFromText(CONCAT('POLYGON((',item.y1 / 10,' ',item.x1 / 10,', ',item.y1 / 10,' ',item.x2 / 10,', ',item.y2 / 10,' ',item.x2 / 10,', ',item.y2 / 10,' ',item.x1 / 10,', ',item.y1 / 10,' ',item.x1 / 10,'))'), 2400) WHERE coordinate_system_id=1 AND type_id=2 or type_id=3'

The replicate-do-statements are:

Replicate_Do_Table: content_db.session
Replicate_Wild_Do_Table: partner_99.%,customer_99_%.%
Replicate_Wild_Ignore_Table: %.%

Regards

Joakim Ahlén
[4 Jan 2006 10:53] Joakim Ahlen
Sorry, i forgot to add that i am using 5.0.15 on the master and 5.0.16 on the slave. Both running on windows.

Is there any chance of getting a fix for this bug in the next release?
[5 Jan 2006 13:44] Andrei Elkin
Taking upon speaking to Lars.
[10 Jan 2006 21:34] Andrei Elkin
Reliably reproducible in my env with 5.0.19. To be continued.
[14 Jan 2006 20:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1088
[23 Jan 2006 16:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1509
[24 Jan 2006 13:51] Andrei Elkin
Fixed in mysql-5.0.18
[25 Jan 2006 15:29] Lars Thalmann
This is actually fixed in 5.0.19  (and not 5.0.18)!
[26 Jan 2006 0:42] Mike Hillyer
Documented in 5.0.19 changelog:

  <listitem>
        <para>
          The <literal>--replicate-do</literal> and
          <literal>--replicate-ignore</literal> options were not being
          enforced on multi-table statements. (Bug #15699, Bug #16487)
        </para>
      </listitem>
[26 Jan 2006 10:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1665
[26 Jan 2006 10:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1666
[26 Jan 2006 14:50] Andrei Elkin
Mats approved.
[26 Jan 2006 15:00] Andrei Elkin
Fixed in 4.1.18, 5.0.19
[2 Feb 2006 4:51] Mike Hillyer
Documented in 4.1.18 changelog:

<listitem>
        <para>
          The <literal>--replicate-do</literal> and
          <literal>--replicate-ignore</literal> options were not being
          enforced on multi-table statements. (Bug #15699, Bug #16487)
        </para>
      </listitem>