Description:
When a replica with blackhole tables and with log_slave_updates enabled is used as a relay replica, and INSERT...SELECT statements on the master generate auto-increment values, the replica does not log the INSERT_ID events to its own binary logs.
How to repeat:
=========== On the master ===================
master> show create table source\G
*************************** 1. row ***************************
Table: source
Create Table: CREATE TABLE `source` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
master> show create table target\G
*************************** 1. row ***************************
Table: target
Create Table: CREATE TABLE `target` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nonid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
master> select * from source;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
master> select * from target;
Empty set (0.00 sec)
============== On the replica ==================
replica> alter table target engine=blackhole;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
============= On the master =================
master> insert into target(nonid) select 1 from source;
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
============== On the replica ==================
replica> show binlog events\G
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 5918
End_log_pos: 106
Info: Server ver: 5.1.57-rel12.8-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 106
Event_type: Query
Server_id: 5150
End_log_pos: 165
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 165
Event_type: Intvar
Server_id: 5150
End_log_pos: 193
Info: INSERT_ID=1
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 193
Event_type: Query
Server_id: 5150
End_log_pos: 305
Info: use `percona`; insert into target(nonid) select 1 from source
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 305
Event_type: Query
Server_id: 5150
End_log_pos: 365
Info: COMMIT
============== On the replica ==================
replica> alter table source engine = blackhole;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
============== On the master ==================
master> insert into target(nonid) select 1 from source;
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
============== On the replica ==================
replica> show binlog events\G
... snip ....
*************************** 6. row ***************************
Log_name: mysql-bin.000001
Pos: 365
Event_type: Query
Server_id: 5918
End_log_pos: 468
Info: use `percona`; alter table source engine = blackhole
*************************** 7. row ***************************
Log_name: mysql-bin.000001
Pos: 468
Event_type: Query
Server_id: 5150
End_log_pos: 527
Info: BEGIN
*************************** 8. row ***************************
Log_name: mysql-bin.000001
Pos: 527
Event_type: Query
Server_id: 5150
End_log_pos: 639
Info: use `percona`; insert into target(nonid) select 1 from source
*************************** 9. row ***************************
Log_name: mysql-bin.000001
Pos: 639
Event_type: Query
Server_id: 5150
End_log_pos: 699
Info: COMMIT
9 rows in set (0.00 sec)
Notice that there is no INSERT_ID on the replica's binary logs now. This will cause problems with downstream replicas.
Suggested fix:
The replica should log the INSERT_ID to its binary log.
Description: When a replica with blackhole tables and with log_slave_updates enabled is used as a relay replica, and INSERT...SELECT statements on the master generate auto-increment values, the replica does not log the INSERT_ID events to its own binary logs. How to repeat: =========== On the master =================== master> show create table source\G *************************** 1. row *************************** Table: source Create Table: CREATE TABLE `source` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) master> show create table target\G *************************** 1. row *************************** Table: target Create Table: CREATE TABLE `target` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nonid` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) master> select * from source; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) master> select * from target; Empty set (0.00 sec) ============== On the replica ================== replica> alter table target engine=blackhole; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 ============= On the master ================= master> insert into target(nonid) select 1 from source; Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0 ============== On the replica ================== replica> show binlog events\G *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 5918 End_log_pos: 106 Info: Server ver: 5.1.57-rel12.8-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 106 Event_type: Query Server_id: 5150 End_log_pos: 165 Info: BEGIN *************************** 3. row *************************** Log_name: mysql-bin.000001 Pos: 165 Event_type: Intvar Server_id: 5150 End_log_pos: 193 Info: INSERT_ID=1 *************************** 4. row *************************** Log_name: mysql-bin.000001 Pos: 193 Event_type: Query Server_id: 5150 End_log_pos: 305 Info: use `percona`; insert into target(nonid) select 1 from source *************************** 5. row *************************** Log_name: mysql-bin.000001 Pos: 305 Event_type: Query Server_id: 5150 End_log_pos: 365 Info: COMMIT ============== On the replica ================== replica> alter table source engine = blackhole; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 ============== On the master ================== master> insert into target(nonid) select 1 from source; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 ============== On the replica ================== replica> show binlog events\G ... snip .... *************************** 6. row *************************** Log_name: mysql-bin.000001 Pos: 365 Event_type: Query Server_id: 5918 End_log_pos: 468 Info: use `percona`; alter table source engine = blackhole *************************** 7. row *************************** Log_name: mysql-bin.000001 Pos: 468 Event_type: Query Server_id: 5150 End_log_pos: 527 Info: BEGIN *************************** 8. row *************************** Log_name: mysql-bin.000001 Pos: 527 Event_type: Query Server_id: 5150 End_log_pos: 639 Info: use `percona`; insert into target(nonid) select 1 from source *************************** 9. row *************************** Log_name: mysql-bin.000001 Pos: 639 Event_type: Query Server_id: 5150 End_log_pos: 699 Info: COMMIT 9 rows in set (0.00 sec) Notice that there is no INSERT_ID on the replica's binary logs now. This will cause problems with downstream replicas. Suggested fix: The replica should log the INSERT_ID to its binary log.