Description:
I haven't tested extensively to find out all the possible scenarios, like with other engines or other binlog_formats. I bumped into this while doing a performance test for various binlog_formats under different conditions with different table types.
The problem occurs while using binlog_format=row.
A single column (single row as well) MyISAM table is updated at regular intervals of 1 second and it was getting replicated properly. In between this, another script started writing to an InnoDB table. This stopped the replication of MyISAM table, while InnoDB was replicated properly.
Also, after stopping both the scripts, even an update from the interactive client was not replicated on MyISAM table. Also, the master and slave status shows that
It should be noted that the same table (MyISAM one) replicated properly on being changed to InnoDB.
How to repeat:
Setup two machines with one master and one slave.
At Master:
CREATE TABLE `repl_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(255) DEFAULT NULL,
`comments` varchar(255) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`author` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
KEY `author` (`author`,`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `hb_ts` (
`ts` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Have a script that inserts one single row in hb_ts and updates that row with the incrementing number every second. Stop after some time, check replication.
At Master:
mysql> SELECT * FROM hb_ts;
+------------+
| ts |
+------------+
| 1208251792 |
+------------+
At Slave:
mysql> SELECT * FROM hb_ts;
+------------+
| ts |
+------------+
| 1208251792 |
+------------+
Restart the first script. Start another script for pumping rows continuously into repl_test table. Stop both the scripts after some time. Check replication.
At Master:
mysql> SELECT count(*) from repl_test;
+----------+
| count(*) |
+----------+
| 53050 |
+----------+
mysql> SELECT * FROM hb_ts;
+------------+
| ts |
+------------+
| 1208253475 |
+------------+
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysqld-bin.000003
Position: 18496196
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
At Slave:
mysql> SELECT count(*) from repl_test;
+----------+
| count(*) |
+----------+
| 53050 |
+----------+
mysql> SELECT * FROM hb_ts;
+------------+
| ts |
+------------+
| 1208252475 |
+------------+
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXXXXXXXXXXX
Master_User: XXXX
Master_Port: XXXX
Connect_Retry: 10
Master_Log_File: mysqld-bin.000003
Read_Master_Log_Pos: 18496196
Relay_Log_File: mysqld-relay-bin.000021
Relay_Log_Pos: 15516279
Relay_Master_Log_File: mysqld-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 18496196
Relay_Log_Space: 15516581
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
Master_SSL_Verify_Server_Cert:
Last_IO_Errno: 2013
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
Now try updating hb_ts table from interactive client.
At Master:
mysql> UPDATE hb_ts SET ts=0;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM hb_ts;
+----+
| ts |
+----+
| 0 |
+----+
At Slave:
mysql> SELECT * FROM hb_ts;
+------------+
| ts |
+------------+
| 1208252475 |
+------------+
Description: I haven't tested extensively to find out all the possible scenarios, like with other engines or other binlog_formats. I bumped into this while doing a performance test for various binlog_formats under different conditions with different table types. The problem occurs while using binlog_format=row. A single column (single row as well) MyISAM table is updated at regular intervals of 1 second and it was getting replicated properly. In between this, another script started writing to an InnoDB table. This stopped the replication of MyISAM table, while InnoDB was replicated properly. Also, after stopping both the scripts, even an update from the interactive client was not replicated on MyISAM table. Also, the master and slave status shows that It should be noted that the same table (MyISAM one) replicated properly on being changed to InnoDB. How to repeat: Setup two machines with one master and one slave. At Master: CREATE TABLE `repl_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(255) DEFAULT NULL, `comments` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `author` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`), KEY `author` (`author`,`ts`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `hb_ts` ( `ts` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Have a script that inserts one single row in hb_ts and updates that row with the incrementing number every second. Stop after some time, check replication. At Master: mysql> SELECT * FROM hb_ts; +------------+ | ts | +------------+ | 1208251792 | +------------+ At Slave: mysql> SELECT * FROM hb_ts; +------------+ | ts | +------------+ | 1208251792 | +------------+ Restart the first script. Start another script for pumping rows continuously into repl_test table. Stop both the scripts after some time. Check replication. At Master: mysql> SELECT count(*) from repl_test; +----------+ | count(*) | +----------+ | 53050 | +----------+ mysql> SELECT * FROM hb_ts; +------------+ | ts | +------------+ | 1208253475 | +------------+ mysql> SHOW MASTER STATUS \G *************************** 1. row *************************** File: mysqld-bin.000003 Position: 18496196 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) At Slave: mysql> SELECT count(*) from repl_test; +----------+ | count(*) | +----------+ | 53050 | +----------+ mysql> SELECT * FROM hb_ts; +------------+ | ts | +------------+ | 1208252475 | +------------+ mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXXXXXXXXX Master_User: XXXX Master_Port: XXXX Connect_Retry: 10 Master_Log_File: mysqld-bin.000003 Read_Master_Log_Pos: 18496196 Relay_Log_File: mysqld-relay-bin.000021 Relay_Log_Pos: 15516279 Relay_Master_Log_File: mysqld-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 18496196 Relay_Log_Space: 15516581 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 Master_SSL_Verify_Server_Cert: Last_IO_Errno: 2013 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) Now try updating hb_ts table from interactive client. At Master: mysql> UPDATE hb_ts SET ts=0; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hb_ts; +----+ | ts | +----+ | 0 | +----+ At Slave: mysql> SELECT * FROM hb_ts; +------------+ | ts | +------------+ | 1208252475 | +------------+