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