MySQL Bugs: #32205: Replaying statements from mysqlbinlog fails with a syntax error, replicates fine

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

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.

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.

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

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

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

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

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

Pushed into 5.0.56

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Also documented for 5.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.