My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Add to Memories Share Next Entry
BLACKHOLE Blues - binary logging broken for statements using INSERT_ID
swanhart
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)


Tell them to fix it ...

(Anonymous)

2008-03-10 08:55 am (UTC)

Did you file a bug report for this?
Regards

pabloj

Thanks!

I'll look at adding this to 5.0.

Cheers,
-Brian

you know it makes me hot when you get all techie-babble...

I'm surprised that whether or not it logs to the binary log is dependent on the storage engine. I guess it's because nothing's technically inserted, and writing happens after an insert?

The write to the binary log happens after write_record() is called to insert the data into the table. The binary log write checks the value of thd->insert_id_used to determine if it needs to write an INSERT_ID_EVENT into the binary log.

The underlying ha_blackhole::write_row was not calling update_auto_increment(), which is inherited and sets thd->insert_id_used among a few other things. As thd->insert_id_used was not getting set to TRUE the binary log writer omitted "the environment" for the INSERT, which in this case is the SET INSERT_ID statement.

well done

(Anonymous)

2008-04-05 08:52 pm (UTC)

thats for sure, dude

You are viewing swanhart