Bug #21035 Restoring a database from a dump containing triggers breaks the replication
Submitted: 13 Jul 2006 14:01 Modified: 19 Jul 2006 10:35
Reporter: Ralf Narozny Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22/5.0BK OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: mysqldump, replication, restoring, trigger

[13 Jul 2006 14:01] Ralf Narozny
Description:
If you restore a database that contains triggers from a dump, the replication fails with error 1064.

How to repeat:
mysql> create database rep_test;
Query OK, 1 row affected (0.00 sec)

mysql> use rep_test;
Database changed
mysql> create table t1 (val int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (val int);
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;;
mysql> CREATE TRIGGER tst AFTER UPDATE ON t1 FOR EACH ROW BEGIN insert into t2 values (9); END;;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

On the master do something like:

mysqldump --opt rep_test | mysql rep_test

Then the replication status will be something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: xxx.xxx.xxx.xxx
                Master_User: xxxxxx
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: master-bin.000407
        Read_Master_Log_Pos: 697710247
             Relay_Log_File: slave-relay-bin.001199
              Relay_Log_Pos: 696747662
      Relay_Master_Log_File: master-bin.000407
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,temp.%,test.%
                 Last_Errno: 1064
                 Last_Error: 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 1' on query. Default database: 'rep_test'. Query: 'CREATE DEFINER=`root`@`localhost` TRIGGER `tst` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN insert into t2 values (9); END */'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 696747525
            Relay_Log_Space: 697710384
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
[13 Jul 2006 14:51] MySQL Verification Team
Thank you for the bug report.

slave>SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: slaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000002
        Read_Master_Log_Pos: 1745
             Relay_Log_File: hegel-relay-bin.000002
              Relay_Log_Pos: 1233
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1064
                 Last_Error: 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 1' on query. Default database: 'rep_test'. Query: 'CREATE DEFINER=`root`@`localhost` TRIGGER `tst` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN insert into t2
values (9); END */'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1096
            Relay_Log_Space: 1882
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

slave>
[19 Jul 2006 10:35] Alexander Nozdrin
This bug is a duplicate of BUG#20438: CREATE statements for views, stored routines and triggers can be not replicable