Bug #35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables
Submitted: 10 Mar 2008 9:12 Modified: 5 Apr 2008 7:01
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.51a OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any
Tags: blackhole, Contribution, corruption, INSERT_ID, replication

[10 Mar 2008 9:12] Justin Swanhart
Description:
CREATE TABLE `n1` (
  `c1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `n2` (
  `c1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1

mysql> SET INSERT_ID=1337;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO n1 VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SET INSERT_ID=1337;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO n2 VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

*************************** 8. row ***************************
   Log_name: mysql-bin.000003
        Pos: 779
 Event_type: Intvar
  Server_id: 1
End_log_pos: 807
       Info: INSERT_ID=1337
*************************** 9. row ***************************
   Log_name: mysql-bin.000003
        Pos: 807
 Event_type: Query
  Server_id: 1
End_log_pos: 898
       Info: use `test`; INSERT INTO n1 VALUES (NULL)
*************************** 10. row ***************************
   Log_name: mysql-bin.000003
        Pos: 898
 Event_type: Query
  Server_id: 1
End_log_pos: 989
       Info: use `test`; INSERT INTO n2 VALUES (NULL)

Notice that there is no Intvar event to set the INSERT_ID for the insert into n2.

I've marked this a critical, as the bug results in replication slave corruption.

How to repeat:
SET INSERT_ID then INSERT into a BLACKHOLE table.  Examine the binary log.  You will notice the Invtar event is missing.

Suggested fix:

--- mysql-5.0.51a/libmysqld/ha_blackhole.cc     2008-01-11 06:43:29.000000000 -0800
+++ mysql-5.0.51a-patched/libmysqld/ha_blackhole.cc     2008-03-10 01:25:10.000000000 -0700
@@ -104,6 +104,12 @@
 int ha_blackhole::write_row(byte * buf)
 {
   DBUG_ENTER("ha_blackhole::write_row");
+  if (table->next_number_field) {
+    int error;
+    if ((error= update_auto_increment())) {
+      DBUG_RETURN(error);
+    }
+  }
   DBUG_RETURN(0);
 }
[10 Mar 2008 21:59] Arjen Lentz
Eek.... can this be fixed urgently?
Quite a few people not use blackhole for replication filtering.
Thanks
Good catch Justin!
[10 Mar 2008 22:09] Guilhem Bichot
If the proposed patch is correct (which I have not verified), then this one would be shorter:
DBUG_RETURN(table->next_number_field ? update_auto_increment() : 0);
[10 Mar 2008 23:16] Arjen Lentz
this is also a problem with 5.1.22:

CREATE TABLE `n1` (
 `c1` int(11) NOT NULL auto_increment,
 PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1/*!*/;
# at 4028046
#8311 8:42:9 server id 1  end_log_pos 4028239   Query   thread_id=947
exec_time=0     error_code=0
SET TIMESTAMP=1205188929/*!*/;
CREATE TABLE `n2` (
 `c1` int(11) NOT NULL auto_increment,
 PRIMARY KEY  (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1/*!*/;
# at 4028239
#8311 8:42:9 server id 1  end_log_pos 4028267   Intvar
SET INSERT_ID=1337/*!*/;
# at 4028267
#8311 8:42:9 server id 1  end_log_pos 4028365   Query   thread_id=947
exec_time=0     error_code=0
SET TIMESTAMP=1205188929/*!*/;
INSERT INTO n1 VALUES (NULL)/*!*/;
# at 4028365
#8311 8:42:9 server id 1  end_log_pos 4028463   Query   thread_id=947
exec_time=0     error_code=0
SET TIMESTAMP=1205188929/*!*/;
INSERT INTO n2 VALUES (NULL)/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
[12 Mar 2008 21:28] Sveta Smirnova
Thank you for the report.

Verified as described.

Can be related/duplicate of bug #28018
[19 Mar 2008 14:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44228

ChangeSet@1.2598, 2008-03-19 16:06:53+02:00, aelkin@mysql1000.(none) +3 -0
  Bug #35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables
  
  binlogging of insert into a autoincrement blackhole table ignored
  an explicit set insert_id.
  
  Fixed with refining of the blackhole's insert method to call
  update_auto_increment() that prepares binlogging the insert query 
  with the preceeding set insert_id.
  
  Note, as the engine does not store any actual data one has to explicitly
  provide to the server with the value of the autoincrement column via
  set insert_id. Otherwise binlogging will happend with the default 
  set insert_id=1.
[19 Mar 2008 14:18] Andrei Elkin
Bug #28018 is set to be a dup of the current.
[19 Mar 2008 16:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44240

ChangeSet@1.2598, 2008-03-19 18:19:28+02:00, aelkin@mysql1000.(none) +3 -0
  Bug #35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables
  
  binlogging of insert into a autoincrement blackhole table ignored
  an explicit set insert_id.
  
  Fixed with refining of the blackhole's insert method to call
  update_auto_increment() that prepares binlogging the insert query 
  with the preceeding set insert_id.
  
  Note, as the engine does not store any actual data one has to explicitly
  provide to the server with the value of the autoincrement column via
  set insert_id. Otherwise binlogging will happend with the default 
  set insert_id=1.
[19 Mar 2008 16:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44246

ChangeSet@1.2598, 2008-03-19 18:44:50+02:00, aelkin@mysql1000.(none) +3 -0
  Bug #35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables
  
  binlogging of insert into a autoincrement blackhole table ignored
  an explicit set insert_id.
  
  Fixed with refining of the blackhole's insert method to call
  update_auto_increment() that prepares binlogging the insert query 
  with the preceeding set insert_id.
  
  Note, as the engine does not store any actual data one has to explicitly
  provide to the server with the value of the autoincrement column via
  set insert_id. Otherwise binlogging will happend with the default 
  set insert_id=1.
[21 Mar 2008 20:09] Andrei Elkin
pushed to 5.0-rpl.
[31 Mar 2008 7:56] Bugs System
Pushed into 5.1.24-rc
[1 Apr 2008 14:11] Bugs System
Pushed into 5.0.60
[3 Apr 2008 13:02] Bugs System
Pushed into 6.0.5-alpha
[5 Apr 2008 7:01] Jon Stephens
Documented fix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        INSERT_ID was not written to the binary log for inserts into BLACKHOLE
        tables.
[20 Apr 2009 2:20] Trent Hornibrook
Hello,

This appears to have created an issue with using blackhole as a front proxy.

For example - now the binary log will contain:

SET INSERT_ID=1;
# at 101691210
#090420 12:06:22 server id -1105015014  end_log_pos 101691326   Query   thread_id=11234 exec_time=0     error_code=0
SET TIMESTAMP=1240193182;
insert into HitsQueue(Entered) values ('12345');

....

new session:

# at 101691470
#090420 12:06:23 server id -1105015014  end_log_pos 101691498   Intvar
SET INSERT_ID=1;
# at 101691498
#090420 12:06:23 server id -1105015014  end_log_pos 101691614   Query   thread_id=11234 exec_time=0     error_code=0
SET TIMESTAMP=1240193183;

We need a flag that states - 'push auto_increment insert_id into the binary log'