Bug #15981 mysqldump dumps triggers in incorrect format

• Submitted: 26 Dec 2005 10:36
• Modified: 28 Jun 2006 21:45
• Reporter: Alexander Nozdrin
• Status: Can't repeat
• 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.