Bug #36570 Parse error of CREATE PROCEDURE stmt with comments on slave
Submitted: 7 May 2008 16:36 Modified: 24 May 2008 17:39
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.60 OS:Any
Assigned to: Chad MILLER CPU Architecture:Any
Tags: comments, create function, create procedure, parse error, replication, syntax error
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[7 May 2008 16:36] Sven Sandberg
Description:
CREATE PROCEDURE statements involving /* comments */ are written wrongly to the binlog, causing the slave to stop with a parse error.

For instance, the following statement:

   /*!50001 create procedure p1() */
   begin
   insert into t1 values (1);
   end

is written to the binlog as:

   CREATE DEFINER=`root`@`localhost` procedure p1() */
   begin
   insert into t1 values (1);
   end

Note the extra "*/" in the query. This causes a syntax error when the slave tries to execute it.

This is related to BUG#32575 (same symptoms for another statement), and the fix is probably similar.

The bug exists also for CREATE FUNCTION.

The bug does not appear to exist in 5.1 or higher.

How to repeat:
Run the following test case:

--source include/master-slave.inc

create table t1 (a int);

delimiter |;
/*!50001 create procedure p1() */
begin
insert into t1 values (1);
end|
delimiter ;|
sync_slave_with_master;

Suggested fix:
Probably similar to BUG#32575.
[7 May 2008 18:39] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.60:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> /*!50001 create procedure p1() */
    ->    begin
    ->    insert into t1 values (1);
    ->    end//
ERROR 1304 (42000): PROCEDURE p1 already exists
mysql> drop procedure p1//
Query OK, 0 rows affected (0.03 sec)

mysql> /*!50001 create procedure p1() */
    ->    begin
    ->    insert into t1 values (1);
    ->    end//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog.exe ..\data\toshiba-user
-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080507 21:34:33 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
60-enterprise-gpl-nt-log created 080507 21:34:33 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
ROLLBACK/*!*/;
# at 98
#080507 21:35:05 server id 1  end_log_pos 178   Query   thread_id=1     exec_tim
e=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1210185305/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
drop procedure p1
/*!*/;
# at 178
#080507 21:35:10 server id 1  end_log_pos 338   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1210185310/*!*/;
CREATE DEFINER=`root`@`localhost` procedure p1() */
   begin
   insert into t1 values (1);
   end
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[13 May 2008 12:38] 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/46650

ChangeSet@1.2624, 2008-05-13 08:37:27-04:00, cmiller@zippy.cornsilk.net +6 -0
  Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on \
  	slave
  
  The stored-routine code took the contents of the (lowest) parser
  and copied it directly to the binlog, which causes problems if there
  is a special case of interpretation at the parser level -- which 
  there is, in the "/*!VER */" comments.  The trailing "*/" caused
  errors on the slave, naturally.
  
  Now, since by that point we have /properly/ created parse-tree (as 
  the rest of the server should do!) for the stored-routine CREATE, we
  can construct a perfect statement from that information, instead of
  writing uncertain information from an unknown parser state.  
  Fortunately, there's already a function nearby that does exactly 
  that.
[14 May 2008 23:24] 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/46714

ChangeSet@1.2624, 2008-05-14 19:23:54-04:00, cmiller@zippy.cornsilk.net +6 -0
  Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on \
  	slave
  
  The stored-routine code took the contents of the (lowest) parser
  and copied it directly to the binlog, which causes problems if there
  is a special case of interpretation at the parser level -- which 
  there is, in the "/*!VER */" comments.  The trailing "*/" caused
  errors on the slave, naturally.
  
  Now, since by that point we have /properly/ created parse-tree (as 
  the rest of the server should do!) for the stored-routine CREATE, we
  can construct a perfect statement from that information, instead of
  writing uncertain information from an unknown parser state.  
  Fortunately, there's already a function nearby that does exactly 
  that.
[15 May 2008 20:48] 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/46749

ChangeSet@1.2625, 2008-05-15 16:48:29-04:00, cmiller@zippy.cornsilk.net +5 -0
  Update for Bug#36570.  Qualify routine names with db name when
  writing to the binlog.
[15 May 2008 23:09] 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/46754

ChangeSet@1.2625, 2008-05-15 19:09:37-04:00, cmiller@zippy.cornsilk.net +5 -0
  Update for Bug#36570.  Qualify routine names with db name when
  writing to the binlog ONLY if the source text is qualified.
[15 May 2008 23:13] 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/46755

ChangeSet@1.2624, 2008-05-15 19:13:24-04:00, cmiller@zippy.cornsilk.net +8 -0
  Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on \
  	slave
  
  The stored-routine code took the contents of the (lowest) parser
  and copied it directly to the binlog, which causes problems if there
  is a special case of interpretation at the parser level -- which 
  there is, in the "/*!VER */" comments.  The trailing "*/" caused
  errors on the slave, naturally.
  
  Now, since by that point we have /properly/ created parse-tree (as 
  the rest of the server should do!) for the stored-routine CREATE, we
  can construct a perfect statement from that information, instead of
  writing uncertain information from an unknown parser state.  
  Fortunately, there's already a function nearby that does exactly 
  that.
  ---
  Update for Bug#36570.  Qualify routine names with db name when
  writing to the binlog ONLY if the source text is qualified.
[19 May 2008 8:24] Bugs System
Pushed into 5.0.64
[19 May 2008 8:25] Bugs System
Pushed into 5.1.25-rc
[19 May 2008 12:51] Jon Stephens
How does this differ from Bug#32575, which appears to be the same issue?
[19 May 2008 13:14] Sven Sandberg
Jon, it's the same issue for another command. The present bug is about CREATE PROCEDURE and CREATE FUNCTION. BUG#32575 is about CREATE VIEW. You can probably merge them to the same changelog entry.
[22 May 2008 9:51] Bugs System
Pushed into 6.0.6-alpha
[24 May 2008 17:39] Jon Stephens
Documented in the 6.0.64, 5.1.25, and 6.0.6 changelogs as follows:

        CREATE PROCEDURE and CREATE FUNCTION statements containing extended 
        comments were not written to the binary log correctly, causing parse 
        errors on the slave.

Note: While this issue appears related to Bug#32575, it didn't have the same fix, and the fixes were applied in different versions, hence the separate changelog entries.
[28 Jul 2008 13:50] Bugs System
Pushed into 5.0.64  (revid:sp1r-cmiller@zippy.cornsilk.net-20080516214807-22967) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)
[28 Jul 2008 16:55] Bugs System
Pushed into 5.1.25-rc  (revid:sp1r-cmiller@zippy.cornsilk.net-20080516213737-27229) (version source revid:sp1r-cmiller@zippy.cornsilk.net-20080516213737-27229) (pib:3)