MySQL Bugs: #16878: dump of trigger

Bug #16878	dump of trigger
Submitted:	29 Jan 2006 0:33	Modified:	27 Feb 2006 20:40
Reporter:	Gui Gros	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Any (All)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
When you create a trigger without specifying the BEGIN/END mysqldump will not dump correctly.

How to repeat:
Step 1
CREATE TRIGGER ins_trans BEFORE INSERT ON transactions FOR EACH ROW SET NEW.CREATED=NOW();

Step 2
mysqldump.exe -hlocalhost -uroot --hex-blob somedb >db_dump.sql

the dump will look like that
/*!40000 ALTER TABLE `transactions` ENABLE KEYS */;

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE TRIGGER `ins_trans` BEFORE INSERT ON `transactions` FOR EACH ROW SET NEW.CREATED=NOW() */;;

DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;

Step 3
mysql.exe -hlocalhost -uroot -Dsomedb < db_dump.sql

Suggested fix:
the workaround here is to create the trigger like this :
DELIMITER |
CREATE TRIGGER ins_trans BEFORE INSERT ON transactions
FOR EACH ROW
BEGIN
SET NEW.CREATED=NOW();
END;
|

Thanks for your bug report.  I was able to reproduce this under XP and FC4:

C:\my>mysql test < t.sql
ERROR 1064 (42000) at line 64: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near '/' at line 2

mysqldump will produce the following SQL when dumping the trigger:

mysql> DELIMITER ;;
mysql> /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW set
new.created=now() */;;
mysql> DELIMITER ;

When restoring this statement with "mysql" it seems like the server is confused with the last  "end of comment" (*/). And it will give 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 '/' at line 2

Even if a semicolon(;) is added after now(). It will bail out complainig about (*/)

mysql> DELIMITER ;;
mysql> /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW set
new.created=now(); */;;
mysql> DELIMITER ;
"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 'set
new.created=now(); */' at line 1"

But if the last /*!50003 ... */ is removed the command will be accepted.
 mysql> /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */  TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW set new.c=now() ;;
Query OK, 0 rows affected (0.00 sec)

So I assume the parser think that the eding comment belongs to a comment started within the trigger.

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

Pushed a fix to 5.0.19 and 5.1.8 that makes the parser handle "single statement"  triggers with embedded "not version" comments like /*!50001 <feature> */.

Ex of a trigger that couldn't be loaded without this fix.
/*!50003 CREATE TRIGGER `ins_trans` BEFORE INSERT ON `transactions` FOR EACH ROW
SET NEW.CREATED=NOW() */

Also fixes bug#14857

Documented in 5.0.19 and 5.1.8 changelogs:

    <listitem>
        <para>
          Triggers created without <literal>BEGIN</literal> and
          <literal>END</literal> clauses could not be properly restored
          from a <filename>mysqldump</filename> file. (Bug #16878)
        </para>
      </listitem>