Bug #44520 INSERT ignored silently if CREATE and INSERT are bundled in one BINLOG stmt
Submitted: 28 Apr 2009 14:29 Modified: 29 Apr 2009 5:48
Reporter: Jan Kneschke Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S4 (Feature request)
Version:5.1.33, 5.1, 6.0 bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[28 Apr 2009 14:29] Jan Kneschke
Description:
MySQL 5.1 supports the BINLOG statement to execute base64 encoded strings of binlog-events. The statement can handle multiple events in one packet.

If a CREATE and INSERT are bundle in one packet, the INSERT will be silently ignored.

If they are in 2 binlog stmts, both will be executed nicely.

How to repeat:
DROP TABLE IF EXISTS test.pump;
## FORMAT_DESC...
BINLOG '0wz3SQ8CAAAAZgAAAGYAAAAAAAQANS4xLjI2LW15c3FsLXByb3h5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADTDPdJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC';
## CREATE TABLE pump ( f1 VARCHAR(16) NOT NULL, f2 INT, f3 INT );
## INSERT INTO pump VALUES ( 'it works', 1, 2 );
BINLOG '0wz3SQICAAAAYgAAAAYBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AENSRUFURSBUQUJMRSBwdW1wICggZjEgVkFSQ0hBUigxNikgTk9UIE5VTEwsIGYyIElOVCwgZjMgSU5UICk=
0wz3SQICAAAAUQAAAFcBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AElOU0VSVCBJTlRPIHB1bXAgVkFMVUVTICggJ2l0IHdvcmtzJywgMSwgMiAp';
SELECT * FROM test.pump;
-- 0 rows

DROP TABLE IF EXISTS test.pump;
## FORMAT_DESC...
BINLOG '0wz3SQ8CAAAAZgAAAGYAAAAAAAQANS4xLjI2LW15c3FsLXByb3h5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADTDPdJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC';
## CREATE TABLE pump ( f1 VARCHAR(16) NOT NULL, f2 INT, f3 INT );
## INSERT INTO pump VALUES ( 'it works', 1, 2 );
BINLOG '0wz3SQICAAAAYgAAAAYBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AENSRUFURSBUQUJMRSBwdW1wICggZjEgVkFSQ0hBUigxNikgTk9UIE5VTEwsIGYyIElOVCwgZjMgSU5UICk=';
BINLOG '0wz3SQICAAAAUQAAAFcBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AElOU0VSVCBJTlRPIHB1bXAgVkFMVUVTICggJ2l0IHdvcmtzJywgMSwgMiAp';
SELECT * FROM test.pump;
-- 1 row

Suggested fix:
Either return a error, or execute the INSERT too.
[29 Apr 2009 5:48] Sveta Smirnova
Thank you for the report.

Verified as described with release version.

Debug version crashes:

Thread 1 (process 26166):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x085a6171 in my_write_core (sig=6) at stacktrace.c:310
#3  0x0824cf72 in handle_segfault (sig=6) at mysqld.cc:2536
#4  <signal handler called>
#5  0x002ce402 in __kernel_vsyscall ()
#6  0x00314f90 in raise () from /lib/libc.so.6
#7  0x00316678 in abort () from /lib/libc.so.6
#8  0x0030e269 in __assert_fail () from /lib/libc.so.6
#9  0x08233335 in Diagnostics_area::set_ok_status (this=0x95510bc, thd=0x9550308, affected_rows_arg=0, last_insert_id_arg=0, message_arg=0x0) at sql_class.cc:436
#10 0x08175185 in my_ok (thd=0x9550308, affected_rows=0, id=0, message=0x0) at sql_class.h:2261
#11 0x08440935 in mysql_client_binlog_statement (thd=0x9550308) at sql_binlog.cc:238
#12 0x0826af56 in mysql_execute_command (thd=0x9550308) at sql_parse.cc:4816
#13 0x0826bc10 in mysql_parse (thd=0x9550308, inBuf=0x95a80e0 '\217' <repeats 200 times>..., length=252, found_semicolon=0xb744d2fc) at sql_parse.cc:5902
#14 0x0826c84c in dispatch_command (command=COM_QUERY, thd=0x9550308, 
    packet=0x95951c1 "BINLOG\n'0wz3SQICAAAAYgAAAAYBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AENSRUFURSBUQUJMRSBwdW1wICggZjEgVkFSQ0hBU\nigxNikgTk9UIE5VTEwsIGYyIElOVCwgZjMgSU5UICk=\n0wz3SQICAAAAUQAAAFcBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AElOU0VSVC"..., packet_length=252) at sql_parse.cc:1216
#15 0x0826da6a in do_command (thd=0x9550308) at sql_parse.cc:857
#16 0x0825a411 in handle_one_connection (arg=0x9550308) at sql_connect.cc:1115
#17 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#18 0x003b74fe in clone () from /lib/libc.so.6

Looks like duplicate of bug #43509
[29 Apr 2009 6:56] Mats Kindahl
Each BINLOG statement is intended to represent exactly that: a single statement. In the event that multiple statements are encoded in a BINLOG statement, the behavior is undefined (i.e., we do not support it).

The BINLOG statement is only intended for row-based events, and statements should not be "hidden" inside such constructions; statements should be executed as plain statements.

If this needs fixing (note that there is no crash), we should not allow the BINLOG statement to be used for anything but Format_description, Table_map, Write_row, Update_row, or Delete_row and add checks to that effect.
[29 Apr 2009 7:09] Sveta Smirnova
Mats, why not to show error in case if more than one statement in single binlog entry?
[12 Jun 2009 0:10] Alfranio Tavares Correia Junior
Changing severity to S4 after Omer's comment.