Bug #15981 mysqldump dumps triggers in incorrect format
Submitted: 26 Dec 2005 10:36 Modified: 28 Jun 2006 21:45
Reporter: Alexander Nozdrin Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0.19-BK OS:Linux (Linux)
Assigned to: Chad MILLER CPU Architecture:Any

[26 Dec 2005 10:36] Alexander Nozdrin
Description:
mysqldump creates invalid output for triggers
which consist of the one "elementary" statement.

For example:
  - create trigger trg1 after insert on t1 for each row
      set @aaa = 1;       # --- an "elementary" statement

    mysqldump dumps such triggers in a wrong way.

  - create trigger trg1 after insert on t1 for each row
    BEGIN
       set @aaa = 1;
    END |                      # --- a "compound" statment

    This works well.

How to repeat:
(booka)[~/MySQL/devel/5.0-tree/client]> ./mysql -u root test
mysql> drop database test;
mysql> create database test;
mysql> use test;
mysql> create table t1(c int);
mysql> create trigger trg1 after insert on t1 for each row set @aaa = 1;
mysql> show triggers;
+---------+--------+-------+---------------+--------+---------+----------+----------------+
| Trigger | Event  | Table | Statement     | Timing | Created | sql_mode | Definer        |
+---------+--------+-------+---------------+--------+---------+----------+----------------+
| trg1    | INSERT | t1    |  set @aaa = 1 | AFTER  | NULL    |          | root@localhost |
+---------+--------+-------+---------------+--------+---------+----------+----------------+
mysql> Bye
(booka)[~/MySQL/devel/5.0-tree/client]> ./mysqldump --triggers test > /tmp/qqq.sql
(booka)[~/MySQL/devel/5.0-tree/client]> ./mysql -u root test < /tmp/qqq.sql 
ERROR 1064 (42000) at line 40: 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 1

********************************************************************

(booka)[~/MySQL/devel/5.0-tree/client]> ./mysql -u root test
mysql> drop database test;
mysql> create database test;
mysql> use test;
mysql> create table t1(c int);
mysql> delimiter |
mysql> create trigger trg1 after insert on t1 for each row begin set @aaa = 1; end|
mysql> show triggers|
+---------+--------+-------+--------------------------+--------+---------+----------+----------------+
| Trigger | Event  | Table | Statement                | Timing | Created | sql_mode | Definer        |
+---------+--------+-------+--------------------------+--------+---------+----------+----------------+
| trg1    | INSERT | t1    |  begin set @aaa = 1; end | AFTER  | NULL    |          | root@localhost |
+---------+--------+-------+--------------------------+--------+---------+----------+----------------+
mysql> Bye
(booka)[~/MySQL/devel/5.0-tree/client]> ./mysqldump --triggers test > /tmp/qqq.sql
(booka)[~/MySQL/devel/5.0-tree/client]> ./mysql -u root test < /tmp/qqq.sql 
(booka)[~/MySQL/devel/5.0-tree/client]>

********************************************************************

(booka)[~/MySQL/devel/5.0-tree/client]> ./mysql -u root test 
mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.19-valgrind-max-debug-log |
+-------------------------------+
[26 Dec 2005 11:57] Valeriy Kravchuk
Thank you for a problem report. Verified just as described on 5.0.19-BK (ChangeSet@1.2003, 2005-12-24 14:32:50+01:00)

Line 40 is the following:

/*!50003 CREATE TRIGGER `trg1` AFTER INSERT ON `t1` FOR EACH ROW set @aaa = 1 */;;
[8 Feb 2006 16:32] Konstantin Osipov
See also Bug#16878
[9 Mar 2006 23:58] Markus Popp
Since this bug makes it impossible to backup triggers with mysqldump, shouldn't this bug get a higher priority?
[11 Mar 2006 0:08] Torrey Hoffman
It's very frustrating that this wasn't fixed in 5.0.19, especially since the related bug #16876 was.  Come ON!!!  MySQLDump is BROKEN until this works!

I've had to put an awful, fragile, high-maintenance work-around for this bug into our own replication system to deal with this since mysqldump simply doesn't dump triggers reliably!

Please, make this a higher priority and get it done for the next release!  

We are paying customers, by the way...
[28 Jun 2006 21:45] Chad MILLER
This works for me, using the latest 5.0 source.  I don't know when it was fixed (if it was).
[28 Jun 2006 22:00] Chad MILLER
Ah, this was fixed with Bug#16878.