Bug #62829 INSERT/SELECT between Blackhole tables loses INSERT_ID
Submitted: 19 Oct 2011 18:58 Modified: 20 Oct 2011 13:13
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.57 OS:Any
Assigned to:
Tags: binlog, blackhole, qc, replication

[19 Oct 2011 18:58] Baron Schwartz
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.
[19 Oct 2011 19:01] Baron Schwartz
See also bug 35178.