BLACKHOLE Blues - binary logging broken for statements using INSERT_ID
MySQL recommends using the BLACKHOLE engine as a replication filter. Well it turns out there is a flaw in the implementation that makes it very dangerous to use it for that. Normally, when an INSERT is made to a table with an AUTO_INCREMENT column, MySQL writes a INSERT_ID_EVENT packet into the binary log. This is broken for INSERT statements executed against a BLACKHOLE table.

I've opened the bug here:
http://bugs.mysql.com/35178

Let me demonstrate:

mysql> show create table n1\G
*************************** 1. row ***************************
       Table: n1
Create Table: CREATE TABLE `n1` (
  `c1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table n2\G
*************************** 1. row ***************************
       Table: n2
Create Table: CREATE TABLE `n2` (
  `c1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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.

Luckily, there is an easy 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())) {
+      return error;
+    }
+  }
   DBUG_RETURN(0);
 }

tada:

mysql> set insert_id=1337;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into n2 values(null); 
Query OK, 1 row affected (0.09 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      216 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000004
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 98
       Info: Server ver: 5.0.51a-debug-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000004
        Pos: 98
 Event_type: Intvar
  Server_id: 1
End_log_pos: 126
       Info: INSERT_ID=1337
*************************** 3. row ***************************
   Log_name: mysql-bin.000004
        Pos: 126
 Event_type: Query
  Server_id: 1
End_log_pos: 216
       Info: use `test`; insert into n2 values(null)
3 rows in set (0.00 sec)