MySQL Bugs: #36570: Parse error of CREATE PROCEDURE stmt with comments on slave

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

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.

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*/;

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.

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.

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.

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.

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.

Pushed into 5.0.64

Pushed into 5.1.25-rc

How does this differ from Bug#32575, which appears to be the same issue?

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.

Pushed into 6.0.6-alpha

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.

Pushed into 5.0.64  (revid:sp1r-cmiller@zippy.cornsilk.net-20080516214807-22967) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

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)