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 4:33	Modified:	21 Mar 2005 13:33
Reporter:	Bryan Berg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Linux (Fedora Core 2)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

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 :( )