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

[11 Feb 2005 4:33] Bryan Berg
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.
[11 Feb 2005 4:33] Bryan Berg
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.
[11 Feb 2005 15:13] Victoria Reznichenko
Can you please provide part of your client code that causes to put 2 INSERT statements one after another?
[11 Feb 2005 15:37] Victoria Reznichenko
Nevermind ..

We were able to reproduce this bug.

Thank you for the report!
[11 Feb 2005 18:37] Guilhem Bichot
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.
[11 Feb 2005 19:57] Bryan Berg
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.
[11 Feb 2005 21:44] Guilhem Bichot
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.
[15 Feb 2005 14:25] Guilhem Bichot
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
[15 Feb 2005 17:06] Paul Dubois
Mentioned in 4.1.10 and 5.0.3 change notes.
[21 Mar 2005 13:33] Guilhem Bichot
Fixed in 4.1.11, not 4.1.10 (missed 4.1.10 by two days :( )