MySQL Bugs: #8436: Multiple "stacked" SQL statements cause replication to stop

Bug #8436	Multiple "stacked" SQL statements cause replication to stop
Submitted:	11 Feb 2005 5:33	Modified:	21 Mar 2005 14:33
Reporter:	Bryan Berg
Status:	Closed
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Linux (Fedora Core 2)
Assigned to:	Guilhem Bichot	Target Version:

Description:
Many apologies if this has been already reported, but I've spent about half an hour
searching the bug database and mailing list archives to see if anyone else has reported
this, and only one person has - it was sent to the java mailing list, though, and it's
really a replication bug, I think.

Multiple queries stacked and submitted by the .NET connector are accepted as valid
queries by the master but the slave SQL thread dies upon reading them out of the binary
logs.  The query below, when submitted to the master, completes; it makes it into the
binary logs as (this is redacted, sorry...):

#050210 19:27:02 server id 4  log_pos 1734425   Query   thread_id=0     exec_time=0    
error_code=0
use dbname;
SET TIMESTAMP=1108092422;
INSERT INTO table2 (field1, field2, field3, field4)
VALUES('totally', 'valid', 1, 'query');
INSERT INTO table (field1, field2, field3, field4, field5)
VALUES('another', 'totally', 'valid', 'query', 'yup');

...but when the slave reads that in, it stops the SQL thread complaining thusly:

050211 19:27:02 [ERROR] Slave: Error 'You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near ';
INSERT INTO table (field1, field2, field3, field4, field5)
V' at line 2' on query. Default database: 'imeem'. Query: 'INSERT INTO table2 (field1,
field2, field3, field4)
VALUES('totally', 'valid', 1, 'query');
INSERT INTO table (field1, field2, field3, field4, field5)
VALUES('another', 'totally', 'valid', 'query', 'yup')', Error_code: 1065

Manually pasting both queries into the MySQL causes them to succeed.

Also, upon further inspection, the binary log repeats the second query again immediately
following the first concatenated group of queries, like so:

#050210 19:27:02 server id 4  log_pos 1734425   Query   thread_id=0     exec_time=0    
error_code=0
use dbname;
SET TIMESTAMP=1108092422;
INSERT INTO table2 (field1, field2, field3, field4)
VALUES('totally', 'valid', 1, 'query');
INSERT INTO table (field1, field2, field3, field4, field5)
VALUES('another', 'totally', 'valid', 'query', 'yup');
# at 1734770
#050210 19:27:02 server id 4  log_pos 1734770   Query   thread_id=0     exec_time=0    
error_code=0
SET TIMESTAMP=1108092422;
INSERT INTO table (field1, field2, field3, field4, field5)
VALUES('another', 'totally', 'valid', 'query', 'yup');

Any insight would be appreciated; if you need more information, please let me know.

Thanks much!

How to repeat:
Given a one-way M -> S replication setup, one can reliably cause replication to abort by
submitting the following query (which runs correctly on the master, no problem.):

INSERT INTO table2 (field1, field2, field3, field4)
VALUES('totally', 'valid', 1, 'query');
INSERT INTO table (field1, field2, field3, field4, field5)
VALUES('another', 'totally', 'valid', 'query', 'yup')

(We're using the .NET connector to submit the query; I have not tested pasting it into
the mysql command line client.)

Suggested fix:
Separate these queries on their way into the binlog?  Or be more liberal about what to
accept upon getting them out?  Not really sure the best way to go.

Er, that should read:
Manually pasting directly from the binary log on the master to the MySQL command-line
client on the slave causes the query to succeed.

Can you please provide part of your client code that causes to put 2 INSERT statements one
after another?

Nevermind ..

We were able to reproduce this bug.

Thank you for the report!

Hi,
until the bug gets fixed, a workaround is to tell Connector/.NET to send one network
packet per query (and not one network packet for several queries packed together), by
specifying
"allow batch=no" in the connect string.
Note that the bug is in the MySQL server, but the workaround is in configuration of
Connector/.NET, to avoid hitting the bug.

Guilhem:

Thanks for pointing that out.  We just tested that workaround and it seems to work fine. 
Just wanted to document that with that workaround in place, the query makes it into the
binary log correctly.

Ok, it confirms what we thought. Thanks for letting us know this.
I'm working on a fix which will very very likely appear in 4.1.10.

fixed in 4.1.10 and 5.0.3 (when 4.1.10 is merged into 5.0.3)
ChangeSet@1.2159.10.1, 2005-02-14 23:47:17+01:00, guilhem@mysql.com

Mentioned in 4.1.10 and 5.0.3 change notes.

Fixed in 4.1.11, not 4.1.10 (missed 4.1.10 by two days :( )