| 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: | |
| 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 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'
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); }