Bug #16749 Replication: MySQL Master does not update Slave if [db].table syntax is used
Submitted: 24 Jan 2006 12:40 Modified: 24 Jan 2006 13:41
Reporter: Sebastian Nohn Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[24 Jan 2006 12:40] Sebastian Nohn
Description:
MySQL Master does not update Slave if [db].table syntax is used. MySQL 5.0.18-std. The problem is not only that the slave does not sync the changes on the master. The problem is, that the slave thinks, it is in sync.

How to repeat:
[ INNODB ]

drop table if exists repldb.x_repli_status;
create table repldb.x_repli_status ( id int, healthcheck timestamp, primary key (id) ) engine InnoDB;

replace into repldb.x_repli_status values ( 0, now() );

[ and same behaviour MyISAM ]

drop table if exists repldb.x_repli_status;
create table repldb.x_repli_status ( id int, healthcheck timestamp, primary key (id) ) engine MyISAM;

replace into repldb.x_repli_status values ( 0, now() );

case 1: schema update

mysql

root@localhost [(none)]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586199 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> create table repldb.x_repli_status ( id int, healthcheck timestamp, primary key (id) ) engine InnoDB;
Query OK, 0 rows affected (0.07 sec)

root@localhost [(none)]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586199 | repldb | mysql |
+--------------------+-----------+--------------+------------------+

RESULT: slave is not receiving shema update!

mysql repldb

root@localhost [repldb]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586199 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

root@localhost [repldb]> create table repldb.x_repli_status ( id int, healthcheck timestamp, primary key (id) ) engine InnoDB;
Query OK, 0 rows affected (0.07 sec)

root@localhost [repldb]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586371 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

RESULT: SLAVE is updated correct

case 2:

cli: mysql

root@localhost [(none)]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586854 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> replace into repldb.x_repli_status values ( 0, now() );
Query OK, 2 rows affected (0.04 sec)

root@localhost [(none)]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586854 | repldb | mysql |
+--------------------+-----------+--------------+------------------+

RESULT: slave is not receiving shema update!

cli: mysql repldb

root@localhost [repldb]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810586854 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

root@localhost [repldb]> replace into repldb.x_repli_status values ( 0, now() );
Query OK, 2 rows affected (0.06 sec)

root@localhost [repldb]> show master status;
+--------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| bladeA8-bin.000032 | 810587015 | repldb | mysql |
+--------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

RESULT: SLAVE is updated correct
[24 Jan 2006 13:41] Hartmut Holzgraefe
See the comments on binlog-do-db/binlog-ignore-db on 
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

What you are looking for can only be done using

  --replicate-wild-do-table=db_name.%  

on the slave side, see

  http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

for --replicate-* filter options