MySQL Blogs

Cold Case "DISABLE/ENABLE or REPLACE=UPDATE on Triggers"

The fact that MySQL is not supporting at his best triggers and Store Procedure, is something we all know quite well.

Because that, we normally do use them with caution, but the other day I was working on a simple action on a customer site, and I face something that I did not recognize immediately, because I was starting from a wrong assumption.

Let me describe the case. A customer needs to perform regular export of his data, and reload of it on a different site. To do that we put up an external procedure using "select into outfile" and "Load DATA in FILE".

Nothing special here.
Given we know what we were doing we also set the REPLACE or IGNORE keyword as manual: "The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key";

We disable UNIQUE and FOREIGN KEY checks as well, finally no replication or binary logging is in place, so no concern that side.

Then we run the import and got an error message :"ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'".
This time the message error was quite clear, and I should not have problem in recognize the issue, right? It is a primary key duplication, BUT and here there is the need of a BIG BUT, I am using the REPLACE keyword because I want my inserts from file overwrite whatever is in the table.

Given I have being use that for years without any issue, I (me fool) did not recognize at the first shot the rot reason of the error.

This is because in my mind the keyword "REPLACE" had a special meaning, that for obvious reasons it does not have in MySQL.

To simplify my life here I have replicate the issue using the test dataset Sakila, so if you want to have fun try it downloading the schema from MySQL site.


So what I have done:

  • Load schema/data
  • Take an export in CSV
  • take info about triggers constrains and so on:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

DATABASE changed
mysql> SELECT T.`TRIGGER_SCHEMA`, T.`TRIGGER_NAME`, T.`EVENT_OBJECT_SCHEMA`, T.`EVENT_OBJECT_TABLE` FROM TRIGGERS T  WHERE T.`TRIGGER_SCHEMA` ='sakila';
+----------------+----------------------+---------------------+--------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME         | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE |
+----------------+----------------------+---------------------+--------------------+
| sakila         | customer_create_date | sakila              | customer           |
| sakila         | ins_film             | sakila              | film               |
| sakila         | upd_film             | sakila              | film               |
| sakila         | del_film             | sakila              | film               |
| sakila         | payment_date         | sakila              | payment            |
| sakila         | rental_date          | sakila              | rental             |
+----------------+----------------------+---------------------+--------------------+
6 rows IN SET (0.00 sec)
 
mysql> SELECT R.`ROUTINE_SCHEMA`, R.`ROUTINE_NAME`, R.`ROUTINE_TYPE`, R.`ROUTINE_BODY` FROM ROUTINES R 
    -> WHERE R.`ROUTINE_SCHEMA`= 'sakila';
+----------------+----------------------------+--------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME               | ROUTINE_TYPE | ROUTINE_BODY |
+----------------+----------------------------+--------------+--------------+
| sakila         | film_in_stock              | PROCEDURE    | SQL          |
| sakila         | film_not_in_stock          | PROCEDURE    | SQL          |
| sakila         | get_customer_balance       | FUNCTION     | SQL          |
| sakila         | inventory_held_by_customer | FUNCTION     | SQL          |
| sakila         | inventory_in_stock         | FUNCTION     | SQL          |
| sakila         | rewards_report             | PROCEDURE    | SQL          |
+----------------+----------------------------+--------------+--------------+
6 rows IN SET (0.00 sec)
 
mysql> SELECT R.`CONSTRAINT_SCHEMA`, R.`CONSTRAINT_NAME`, R.`UNIQUE_CONSTRAINT_SCHEMA`, R.`UNIQUE_CONSTRAINT_NAME`, R.`TABLE_NAME`, R.`REFERENCED_TABLE_NAME` FROM REFERENTIAL_CONSTRAINTS R 
    -> WHERE R.`CONSTRAINT_SCHEMA`='sakila' ORDER BY R.`REFERENCED_TABLE_NAME`;
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME           | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | TABLE_NAME    | REFERENCED_TABLE_NAME |
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
| sakila            | fk_film_actor_actor       | sakila                   | PRIMARY                | film_actor    | actor                 |
| sakila            | fk_store_address          | sakila                   | PRIMARY                | store         | address               |
| sakila            | fk_staff_address          | sakila                   | PRIMARY                | staff         | address               |
| sakila            | fk_customer_address       | sakila                   | PRIMARY                | customer      | address               |
| sakila            | fk_film_category_category | sakila                   | PRIMARY                | film_category | category              |
| sakila            | fk_address_city           | sakila                   | PRIMARY                | address       | city                  |
| sakila            | fk_city_country           | sakila                   | PRIMARY                | city          | country               |
| sakila            | fk_payment_customer       | sakila                   | PRIMARY                | payment       | customer              |
| sakila            | fk_rental_customer        | sakila                   | PRIMARY                | rental        | customer              |
| sakila            | fk_film_category_film     | sakila                   | PRIMARY                | film_category | film                  |
| sakila            | fk_film_actor_film        | sakila                   | PRIMARY                | film_actor    | film                  |
| sakila            | fk_inventory_film         | sakila                   | PRIMARY                | inventory     | film                  |
| sakila            | fk_rental_inventory       | sakila                   | PRIMARY                | rental        | inventory             |
| sakila            | fk_film_language_original | sakila                   | PRIMARY                | film          | LANGUAGE              |
| sakila            | fk_film_language          | sakila                   | PRIMARY                | film          | LANGUAGE              |
| sakila            | fk_payment_rental         | sakila                   | PRIMARY                | payment       | rental                |
| sakila            | fk_rental_staff           | sakila                   | PRIMARY                | rental        | staff                 |
| sakila            | fk_store_staff            | sakila                   | PRIMARY                | store         | staff                 |
| sakila            | fk_payment_staff          | sakila                   | PRIMARY                | payment       | staff                 |
| sakila            | fk_inventory_store        | sakila                   | PRIMARY                | inventory     | store                 |
| sakila            | fk_customer_store         | sakila                   | PRIMARY                | customer      | store                 |
| sakila            | fk_staff_store            | sakila                   | PRIMARY                | staff         | store                 |
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
22 rows IN SET (0.00 sec)
 

 


Now I have to reload the table FILM which has relation (constrains) and it has Triggers, ok let us take a look on them :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

 
mysql> SELECT T.`TRIGGER_SCHEMA`, T.`TRIGGER_NAME`, T.`EVENT_OBJECT_SCHEMA`, T.`EVENT_OBJECT_TABLE`, T.`ACTION_STATEMENT` FROM INFORMATION_SCHEMA.TRIGGERS T  WHERE T.`TRIGGER_SCHEMA` ='sakila' AND T.`EVENT_OBJECT_TABLE` ='film';
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_STATEMENT                                                                                                                                                                                                                                                                       |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sakila         | ins_film     | sakila              | film               | BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END 
                                                                                                                                                           |
| sakila         | upd_film     | sakila              | film               | BEGIN
    IF (old.title != new.title) OR (old.description != new.description)
    THEN
        UPDATE film_text
            SET title=new.title,
                description=new.description,
                film_id=new.film_id
        WHERE film_id=old.film_id;
    END IF;
  END |
 
| sakila         | del_film     | sakila              | film               | BEGIN
    DELETE FROM film_text WHERE film_id = old.film_id;
  END                                                                                                                                                                                                                     |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows IN SET (0.00 sec)
 

 

My table inserts/update/delete records on the table FILM_TEXT, at any insert/update/delete using PRYMARY KEY on both involve table.

The table itself is quite simple:

 

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> SHOW CREATE TABLE film_text;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE     | CREATE TABLE                                                                                                                                                                                                                                       |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| film_text | CREATE TABLE `film_text` (
  `film_id` smallint(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

 

 


It Is MyISAM because the full text (next time we will use Innodb full text ;) ), it use "film_id", as primary key.


At this point I think you should have already get why I was a fool, and where was my misunderstanding.

I was thinking that GIVEN I have REPLACE in primary key in the LOAD DATA IN FILE statement, MySQL was able to propagate this condition also to relate tables.

 

1
2
3
4

mysql> LOAD DATA INFILE '/tmp/sakila_film.csv' REPLACE INTO TABLE sakila.film  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';
ERROR 1062 (23000): Duplicate entry '1' FOR KEY 'PRIMARY'
mysql> 
 

 


So when I saw the error I was a little bit ... annoyed.

I did a quick check on the FILM_TEXT table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> SELECT * FROM film_text LIMIT 10;
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| film_id | title            | description                                                                                                           |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
|       1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist AND a Mad Scientist who must Battle a Teacher IN The Canadian Rockies                      |
|       2 | ACE GOLDFINGER   | A Astounding Epistle of a DATABASE Administrator AND a Explorer who must Find a Car IN Ancient China                  |
|       3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack AND a Car who must Sink a Lumberjack IN A Baloon Factory                      |
|       4 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee AND a Lumberjack who must Chase a Monkey IN A Shark Tank                          |
|       5 | AFRICAN EGG      | A Fast-Paced Documentary of a Pastry Chef AND a Dentist who must Pursue a Forensic Psychologist IN The Gulf of Mexico |
|       6 | AGENT TRUMAN     | A Intrepid Panorama of a Robot AND a Boy who must Escape a Sumo Wrestler IN Ancient China                             |
|       7 | AIRPLANE SIERRA  | A Touching Saga of a Hunter AND a Butler who must Discover a Butler IN A Jet Boat                                     |
|       8 | AIRPORT POLLOCK  | A Epic Tale of a Moose AND a Girl who must Confront a Monkey IN Ancient India                                         |
|       9 | ALABAMA DEVIL    | A Thoughtful Panorama of a DATABASE Administrator AND a Mad Scientist who must Outgun a Mad Scientist IN A Jet Boat   |
|      10 | ALADDIN CALENDAR | A Action-Packed Tale of a Man AND a Lumberjack who must Reach a Feminist IN Ancient China                             |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
10 rows IN SET (0.00 sec)
 

 

And yes data is there and at this point I must assume that this is the cause of the error.

So Marco stop dreaming about REPLACE cascade action and do something.

Cool, let me DISABLE the trigger such that I can reload the table on FILM without worries.

But oops I CANNOT disable a trigger, and more... what if the data I am inserting is the same for Primary key but not for the text, and I need to replace data in the DESCRIPTION fields?

Well I cannot do it without modifying the trigger itself.

So at the end I have to do something in the triggers like so instead of using

 

1
2
3
4
5
6

So at the end I have TO do something IN the triggers LIKE so instead of USING
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
END;;

 

We need to use

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description)  ON DUPLICATE KEY UPDATE film_id=new.film_id;
  END;;  
 
  mysql> DROP TRIGGER sakila.ins_film ;
Query OK, 0 rows affected (0.07 sec)
 
mysql> DELIMITER ;;
mysql> CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    ->     INSERT INTO film_text (film_id, title, description)
    ->         VALUES (new.film_id, new.title, new.description)  ON DUPLICATE KEY UPDATE film_id=new.film_id;
    ->   END;;  
Query OK, 0 rows affected (0.11 sec)
 

 

Which obviously will work fine:

1
2
3
4

 mysql> LOAD DATA INFILE '/tmp/sakila_film.csv' REPLACE INTO TABLE sakila.film  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';
Query OK, 1000 rows affected (0.57 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0
 

 

Problem solve, and all fine.
Really? Not really!

I have few comments here and I met two annoying issue during my exercise.

 

The first one is that I am still not able to disable the triggers, this is a feature request pending from 2005 http://bugs.mysql.com/bug.php?id=14661, we can discuss the best way to implement it, but not the fact that it is a NEEDED feature.

 

Second if I use REPLACE on LOAD DATA INFILE, I could expect a "Cascade" behaviour of the REPLACE action, at least when I involve the primary key in the Trigger.
That actually should be quite simple, like FIRING the UPDATE trigger instead the INSERT, not rocket science, no need to check what you are doing in the trigger, REPLACE=UPDATE in Trigger action.

In term of suggestion to the others, I can only say ... "REVIEW the triggers code to include the possible case of duplicate inserts", but I don't like it.
I think that we should go for a more elegant way to run it, then modify the trigger code, which was a simple operation here, but that could not be always the case.


Is this a critical issue? No we can stay as we did so far.
Is this something easy to solve? I think so.

Would make any sense to change it as I describe? I think so.
More I would like to say that I don't understand how we can have the option to disable FOREIGN key in place and STILL not have it for Triggers.

That is for me another annoying Cold Case, also if takes me few minutes to fix it!

 

Reference:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

http://drdobbs.com/database/231902587?pgno=1

 

{joscommentenable}