Bug #32205 Replaying statements from mysqlbinlog fails with a syntax error, replicates fine
Submitted: 8 Nov 2007 23:43 Modified: 6 Feb 2008 16:54
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.50 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any

[8 Nov 2007 23:43] Morgan Tocker
Description:
SQL queries that use the comment '-- ' have the ability to break mysqlbinlog from replaying statements, yet work fine for replication.

How to repeat:
See testcase attached.
[13 Dec 2007 9:30] Zhenxing He
The reason of this bug is that when mysqlbinlog dumps a query, the query is written to output with a delimeter appended right after it, if the query string ends with a '--' comment, then the delimeter would be considered as part of the comment, if there are any statements after this query, then it will cause a syntax error.

for example, the following statement:
   create table t1 (a int) -- a comment
will be dumped as:
  create table t1 (a int) -- a comment/*!*/;

Here is the test case for this bug

--------8<--------------------------------------->8----------
# Test case for bug#32205 Replaying statements from mysqlbinlog fails
# with a syntax error, replicates fine

-- source include/master-slave.inc
connection master;

--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (a int) -- create table t1;
insert into t1 values (1);
flush logs;
sync_slave_with_master;
connection slave;
select * from t1;
--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000001 | $MYSQL
select * from t1;
--------8<--------------------------------------->8----------

The problem could be resolved if we add a newline before add the delimeter.
[13 Dec 2007 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39868

ChangeSet@1.2558, 2007-12-13 17:38:55+08:00, hezx@hezx.(none) +4 -0
  BUG#32205 Replaying statements from mysqlbinlog fails with a syntax error, replicates fine
  
  The reason of this bug is that when mysqlbinlog dumps a query, the query is written to
  output with a delimeter appended right after it, if the query string ends with a '--'
  comment, then the delimeter would be considered as part of the comment, if there are any
  statements after this query, then it will cause a syntax error.
  
  Output the delimeter on a newline after a query string
[15 Dec 2007 12:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40053

ChangeSet@1.2559, 2007-12-15 19:50:23+08:00, hezx@hezx.(none) +10 -0
  BUG#32205 Replaying statements from mysqlbinlog fails with a syntax error, replicates fine
  
  The reason of this bug is that when mysqlbinlog dumps a query, the query is written to
  output with a delimeter appended right after it, if the query string ends with a '--'
  comment, then the delimeter would be considered as part of the comment, if there are any
  statements after this query, then it will cause a syntax error.
  
  Start a newline before appending delimiter after a query string
[15 Dec 2007 12:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40054

ChangeSet@1.2654, 2007-12-15 20:19:35+08:00, hezx@hezx.(none) +9 -0
  BUG#32205 Replaying statements from mysqlbinlog fails with a syntax error, replicates fine
  
  The reason of this bug is that when mysqlbinlog dumps a query, the query is written to
  output with a delimeter appended right after it, if the query string ends with a '--'
  comment, then the delimeter would be considered as part of the comment, if there are any
  statements after this query, then it will cause a syntax error.
  
  Start a newline before appending delimiter after a query string
[17 Dec 2007 13:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40091

ChangeSet@1.2654, 2007-12-17 21:13:25+08:00, hezx@hezx.(none) +11 -0
  BUG#32205 Replaying statements from mysqlbinlog fails with a syntax error, replicates
  fine
  
  The reason of this bug is that when mysqlbinlog dumps a query, the query is written to
  output with a delimeter appended right after it, if the query string ends with a '--'
  comment, then the delimeter would be considered as part of the comment, if there are any
  statements after this query, then it will cause a syntax error.
  
  Start a newline before appending delimiter after a query string
[6 Jan 2008 7:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40606

ChangeSet@1.2561, 2008-01-06 15:27:42+08:00, hezx@mail.hezx.com +5 -0
  Fixed some test case after push of BUG#32205
[5 Feb 2008 12:45] Bugs System
Pushed into 5.0.56
[5 Feb 2008 13:03] Bugs System
Pushed into 5.1.24-rc
[5 Feb 2008 13:07] Bugs System
Pushed into 6.0.5-alpha
[6 Feb 2008 16:54] Jon Stephens
Documented bugfix in 5.0.56, 5.1.24, and 6.0.5 changelogs as follows:

        SQL statements containing comments using --
        syntax were not replayable by mysqlbinlog,
        even though such statements replicated correctly.
[6 Mar 2008 13:04] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[31 Mar 2008 14:35] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.