| 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: | |
| 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: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>
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.