Bug #15290 replicate-rewrite-db/replicate-do-table doesn't honour aliases #2

• Submitted: 28 Nov 2005 15:40
• Modified: 4 Dec 2005 15:29
• Reporter: Alexander Y. Fomichev
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Linux (gentoo 1.6.13)
• CPU Architecture: Any

[28 Nov 2005 15:40] Alexander Y. Fomichev

Description:
Replication slave doesn't execute queries where exists subquery aliase if
replicate-rewrite-db/replicate-do-table  options are in use.

How to repeat:
Slave my.cnf:
replicate-do-db                 = test
replicate-do-table              = test.a
replicate-do-table              = test.b

Master:
mysql> DROP TABLE IF EXISTS a;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS b;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `a` (   `i` int(11) default NULL ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `b` (   `i` int(11) default NULL ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO a VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO b VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE a LEFT JOIN (SELECT i FROM b) as alias ON a.i=alias.i SET a.i=0 WHERE alias.i=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM a;
+------+
| i    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Slave:
mysql> SELECT * FROM a;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

[28 Nov 2005 20:18] MySQL Verification Team

Hello,

Which exactly replicate-rewrite-db do you use? (you didn't specify it in the bug report)
What exactly version of MySQL do you use?

I was not able reproduce this behavior with 4.1.16-debug-log.

[29 Nov 2005 9:12] Alexander Y. Fomichev

G'day
i've caught and then reproduce this on separate hosts ( both 4.1.15 ), on one of them replicate-rewrite-db is present:
replicate-rewrite-db            = shop22pre->shop_ro
replicate-do-db                 = shop_ro
replicate-do-table              = shop_ro.account
[skip]
on the second i skip 'rewrite' but it makes no matter in my case.
Ok, i'll try 4.1.16-bk today. ( JFI: i couldn't reproduce it with 5.0.16 )

[29 Nov 2005 15:17] Alexander Y. Fomichev

Seems like this is duplicate of http://bugs.mysql.com/bug.php?id=13236 but symptoms rather different. In any cases patch http://lists.mysql.com/internals/31070 sovles, tnx.

[4 Dec 2005 15:29] Valeriy Kravchuk

Duplicate of bug #13236 (according to the reporter). The bug is fixed in 4.1.16.