Bug #5711 replication SQL thread does not abort on SQL syntax error

• Submitted: 23 Sep 2004 8:28
• Modified: 23 Sep 2004 16:03
• Reporter: Stefan Paletta
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 4.0.21
• CPU Architecture: Any

[23 Sep 2004 8:28] Stefan Paletta

Description:
I had replication set up between a 4.0.13 master and an 4.0.21 slave. 
The master accepted a syntactically wrong CREATE TABLE statement (it had a spurious 
comma before the right parenthesis) and wrote it to the binlog. 
The SQL thread on the master encountered a syntax error upon execution of this 
statement, logged the error, but it did not abort. It aborted shortly afterwards on a 
statement that tried to use the table that was supposed to be created. 
The expected behaviour is to abort the SQL thread on the (unlikely) case of an invalid 
statement being replicated. 

How to repeat:
Cause invalid SQL statement to be accepted by a buggy master and replicated to a 
non-buggy client.

[23 Sep 2004 16:03] Guilhem Bichot

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Hi,
well the proper solution would be that the master does not write to the binlog a syntactically wrong query. That would happen, I guess, when you upgrade your master to 4.0.13 (4.0.13 is getting old, there have been bugfixes since).
However, right it could be a corrupted binary log file and then slave needs to bail out. So I just fixed 4.0; in 4.0 from now on you'll get:

ERROR: 1064  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 ')' at line 1
040923 13:46:38 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 ')' at line 1' on query. Default database: 'test'. Query: 'create table ttt (a int,)', Error_code: 1064
040923 13:46:38 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'gbichot2-bin.001' position 79

As you see the first line is superfluous; in 4.1 this line won't appear.
Thank you for your bug report.
ChangeSet@1.2007.1.11, 2004-09-23 14:43:31+02:00, [e-mail]