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

[29 Jan 2006 0:33] Gui Gros
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;
|
[29 Jan 2006 10:27] Jorge del Conde
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
[8 Feb 2006 14:06] Magnus Blåudd
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.
[9 Feb 2006 10:12] 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/2354
[24 Feb 2006 8:18] Magnus Blåudd
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
[27 Feb 2006 20:40] Mike Hillyer
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>