MySQL Bugs: #32575: Parse error of stmt with extended comments on slave side

Bug #32575	Parse error of stmt with extended comments on slave side
Submitted:	21 Nov 2007 15:12	Modified:	16 May 2008 6:59
Reporter:	Alexey Stroganov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.50	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
Slave is unable to parse following stmt:

...
/*!50001 DROP VIEW IF EXISTS v12*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW v12 AS select t4.x AS x from t4 where t4.x < 3 */
/*!50002 WITH CHECK OPTION */;
...

It fails with following error:

show slave status:
...
                 Last_Errno: 1064
                 Last_Error: 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 '/
/*!50002 WITH CHECK OPTION' at line 1' on query. Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v12` AS select t4.x AS x from t4 where t4.x < 3 */
/*!50002 WITH CHECK OPTION'
...

Apparently this is 5.0 specific issue because the same stmt executes ok on 5.1.

How to repeat:
1. Download attached test case and put it to t/ directory
2. Run it:

./mysql-test-run.pl rpl_slave_parse_bug

Test case for the bug

Attachment: rpl_slave_parse_bug.test (application/octet-stream, text), 368 bytes.

Need information where the statements were created (mysqldump?  what version?), and make sure we don't produce output that can't be parsed in the right server

Problem was that mysql_make_view did not properly create the create view statement to the binary log. (the statement would look like the bug report when using mysqldump.) I will probably propose a patch somewhat like this:
===== sql/sql_view.cc 1.122 vs edited =====
--- 1.122/sql/sql_view.cc	2008-02-21 18:58:27 +01:00
+++ edited/sql/sql_view.cc	2008-05-05 15:27:09 +02:00
@@ -649,7 +649,17 @@
       buff.append(')');
     }
     buff.append(STRING_WITH_LEN(" AS "));
-    buff.append(views->source.str, views->source.length);
+    buff.append(views->query.str, views->query.length);
+    /* add with check option */
+    if (views->with_check != VIEW_CHECK_NONE)
+    {
+      buff.append(STRING_WITH_LEN(" WITH "));
+      if (views->with_check == VIEW_CHECK_LOCAL)
+        buff.append(STRING_WITH_LEN("LOCAL "));
+      else if (views->with_check == VIEW_CHECK_CASCADED)
+        buff.append(STRING_WITH_LEN("CASCADED "));
+      buff.append(STRING_WITH_LEN("CHECK OPTION"));
+    }

Suggested solution:
- Make sure all comments are stripped before the SQL statement is
  logged.

Future idea:
- In the future, we may consider generating !-version code in the
  binlog, so that one can replicate NEW->OLD even if one is using NEW
  features.  This is not for now though.

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/46385

ChangeSet@1.2616, 2008-05-06 15:07:25+02:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +4 -0
  Bug#32575 - Parse error of stmt with extended comments on slave side
  
  Problem was that mysql_create_view did not remove all comments characters
  when writing to binlog, resulting in parse error of stmt on slave side.
  
  Solution was to use the recreated select clause
  and add a generated CHECK OPTION clause if needed.

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/46495

ChangeSet@1.2616, 2008-05-08 09:41:22+02:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +4 -0
  Bug#32575 - Parse error of stmt with extended comments on slave side
  
  Problem was that mysql_create_view did not remove all comments characters
  when writing to binlog, resulting in parse error of stmt on slave side.
  
  Solution was to use the recreated select clause
  and add a generated CHECK OPTION clause if needed.

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/46506

ChangeSet@1.2616, 2008-05-08 14:02:09+02:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +2 -0
  Bug#32575: Parse error of stmt with extended comments on slave side
  
  test change for 5.1+ (show create view adds two columns in 5.1)
  
  Patch only for 5.1+

Pushed into mysql-6.0-bugteam, mysql-5.1-bugteam and mysql-5.0-bugteam

Pushed into 5.0.62

Pushed into 5.1.25-rc

Documented in the 5.0.62 changelog as follows:

        SQL statements containing extended comments were not written to the
        binary log correctly, causing parse errors on the slave. Now, all
        comments are stripped from SQL statements before being written to the
        binary log.

Did not document for 5.1+ and closed bug report because: 
  - Developer comments above state that bug affected 5.0 only
  - 5.1 commit appears to be change in test case only

Pushed into 6.0.6-alpha

Also documented fix in the 6.0.6 changelog.

Previous comment should read: Also documented fix in the 5.1.25 and 6.0.6 changelogs.