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
Triage: D2 (Serious)

[21 Nov 2007 15:12] Alexey Stroganov
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
[21 Nov 2007 15:13] Alexey Stroganov
Test case for the bug

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

[24 Jan 2008 18:51] Lars Thalmann
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
[5 May 2008 13:32] Mattias Jonsson
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"));
+    }
[5 May 2008 16:35] Lars Thalmann
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.
[6 May 2008 13:08] 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/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.
[8 May 2008 7:41] 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/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.
[8 May 2008 12:02] 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/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+
[9 May 2008 7:15] Mattias Jonsson
Pushed into mysql-6.0-bugteam, mysql-5.1-bugteam and mysql-5.0-bugteam
[14 May 2008 15:19] Bugs System
Pushed into 5.0.62
[14 May 2008 15:20] Bugs System
Pushed into 5.1.25-rc
[16 May 2008 6:59] Jon Stephens
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
[22 May 2008 9:50] Bugs System
Pushed into 6.0.6-alpha
[24 May 2008 17:40] Jon Stephens
Also documented fix in the 6.0.6 changelog.
[24 May 2008 17:42] Jon Stephens
Previous comment should read: Also documented fix in the 5.1.25 and 6.0.6 changelogs.