CODEWITHSUNDEEP
mysql
Hans
Hans

Reputation: 2910

mysqldump with multiple triggers

I am using mysqldump to create and restore a backup onto another server.

One of the features I am using a lot on this database is triggers. It appears that if there are more than one trigger for any one action on the database the restore is failing because of a dependency on an object (the second trigger) that is not yet created.

The reason seems to be that both trigger declarations contain a reference to each other. But as they are executed in sequence, the first of these fails. 

CREATE TRIGGER trigger_one
...
PRECEDES trigger_two
...;

[and then a bit further down]

CREATE TRIGGER trigger_two
...
FOLLOWS trigger_one
...;

I've separated data and structure and also separated structure as 'just triggers' and 'everything but' following this article on the Percona blog, yet the problem exists and I'd like to be able to automate backups and replication.

Upvotes: 2

Views: 995

Answers (1)

wchiquito
wchiquito

Reputation: 16569

I can't reproduce the problem.

I don't have very clear the MySQL version used in the article of Percona mentioned, but I doubt that is the 5.7 (or at least 5.7.2).

Test:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `mytable` (`mycol` BOOL);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER `trigger_one` BEFORE INSERT ON `mytable`
    -> FOR EACH ROW
    ->   SET NEW.`mycol` := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER `trigger_two` BEFORE INSERT ON `mytable`
    -> FOR EACH ROW PRECEDES `trigger_one`
    ->   SET NEW.`mycol` := 2;
Query OK, 0 rows affected (0.00 sec)

$ mysqldump mydatabase > dump.sql

--
-- Table structure for table `mytable`
--

DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
  `mycol` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

...

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`testuser`@`testmachine`*/ 
/*!50003 TRIGGER `trigger_two` BEFORE INSERT ON `mytable`
FOR EACH ROW SET NEW.`mycol` := 2 */;;
DELIMITER ;

...

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`testuser`@`testmachine`*/ 
/*!50003 TRIGGER `trigger_one` BEFORE INSERT ON `mytable`
FOR EACH ROW
  SET NEW.`mycol` := 1 */;;
DELIMITER ;

...

21.3.1 Trigger Syntax and Examples

...

... To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. ...

...

Upvotes: 1

Related Questions