MySQL 5.5.30 cascaded triggers not working

Question

For some reason, on a MySQL 5.5.30 machine, a trigger which deletes a row from a second table does no longer fire the delete trigger on the second table.

This works perfectly on our local MySQL version 5.5.25

I did not find any documentation that would explain this behaviour, does somebody maybe have an equal problem?

This is either a bug which occurs in MySQL version greater than 5.5.25 or a "feature" which is enabled accidently.

UPDATE table1 => fires BEFORE UPDATE trigger ON table1
      table1 BEFORE UPDATE TRIGGER executes: DELETE FROM table2 => should fire BEFORE DELETE trigger on table2 ( but doesn't )
            table 2 BEFORE DELETE TRIGGER executes: DELETE FROM table3 (never happens)

OK here my reproduce steps:

Database

CREATE DATABASE "triggerTest" DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Tables

CREATE TABLE "table1" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "active" tinyint(1) NOT NULL DEFAULT '0',
  "sampleData" varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE "table2" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "table1_id" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("id"),
  CONSTRAINT "test2_fk_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id") ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE "table3" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "table1_id" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("id"),
  CONSTRAINT "test3_fk_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id") ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

Triggers

DELIMITER $$

CREATE TRIGGER "table1_rtrg_AI" AFTER INSERT ON "table1" FOR EACH ROW
BEGIN
    IF NEW."active" THEN
        INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id";
    END IF;
END$$

CREATE TRIGGER "table1_rtrg_BU" BEFORE UPDATE ON "table1" FOR EACH ROW
BEGIN
    IF NOT NEW."active" AND OLD."active" THEN
        DELETE FROM "table2" WHERE "table1_id" = OLD."id";
    END IF;

    IF NEW."active" AND NOT OLD."active" THEN
        INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id";
    END IF;
END$$

CREATE TRIGGER "table2_rtrg_AI" AFTER INSERT ON "table2" FOR EACH ROW
BEGIN
    INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id";
END$$

CREATE TRIGGER "table2_rtrg_BD" BEFORE DELETE ON "table2" FOR EACH ROW
BEGIN
    DELETE FROM "table3" WHERE "table1_id" = OLD."table1_id";
END$$

DELIMITER ;

Q: Why do you quote identifiers using double quotes? (instead of backticks)

Because I don't like "niche syntax"

    mysql> show variables LIKE 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,**ANSI_QUOTES**,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Testcase 1: Expected behaviour (database version 5.2.20)

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

mysql> SET GLOBAL general_log := ON;

testing insert trigger

mysql> INSERT INTO "table1" ( "active", "sampleData" ) SELECT 0, 'sample data row 1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

general_log: 
130423 12:51:27 78010 Query     INSERT INTO "table1" ( "active", "sampleData" ) SELECT 0, 'sample data row 1'


mysql> INSERT INTO "table1" ( "active", "sampleData" ) SELECT 1, 'sample data row 2';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

general_log:
130423 12:51:33 78010 Query     INSERT INTO "table1" ( "active", "sampleData" ) SELECT 1, 'sample data row 2'
                78010 Query     INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id"
                78010 Query     INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id"

expected table contents:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      0 | sample data row 1 |
|  2 |      1 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
+----+-----------+
1 row in set (0.00 sec)

testing update trigger, set active

mysql> UPDATE "table1" SET "active" = 1 WHERE "id" = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

query_log:
130423 12:52:15 78010 Query     UPDATE "table1" SET "active" = 1 WHERE "id" = 1
                78010 Query     INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id"
                78010 Query     INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id"

expected table contents:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      1 | sample data row 1 |
|  2 |      1 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
|  1 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
|  1 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

testing update trigger, set inactive

mysql> UPDATE "table1" SET "active" = 0 WHERE "id" = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

query_log:

130423 12:52:49 78010 Query     UPDATE "table1" SET "active" = 0 WHERE "id" = 2
                78010 Query     DELETE FROM "table2" WHERE "table1_id" = NEW."id"
                78010 Query     DELETE FROM "table3" WHERE "table1_id" = OLD."table1_id"

expected table contents:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      1 | sample data row 1 |
|  2 |      0 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
+----+-----------+
1 row in set (0.00 sec)

---

Testcase2: unexpected behaviour (MySQL Version 5.5.30)

Holy triggers grml - You know what? Shame that I didn't test the second case first - unfortunately I was not able to reproduce the error.. the test worked on 5.5.30 as well, will keep you updated :)

EDIT Trigger did not cascade because of an unknown definer which was remaining in the sql dump made for production. Removing DEFINER= in the trigger dumps (alternative solution would be to create the user or to change DEFINER= to an existing one) solved the problem, solved a part of he problem.

The unknown definer did not cause any log file output

Answer 1

Final conclusion: MySQL 5.5.30 is not buggy in this case, also there was no misconfiguration of the server itself.

Several self-made mistakes caused the problem:

Mistake I: DEFINER user did not exist

Instead of just generating the database on the production machine, I was lazy and dumped the testing database to the production machine. If you don't explicitly set a DEFINER in your CREATE TRIGGER statement, it is set to CURRENT_USER. Unfortunately this exact CURRENT_USER on my testing machine does not exist on the production server.

Mistake II: Being lazy

mysqldump dumps the trigger definition with DEFINER and creating the trigger should generate a warning but again, I was lazy and did something like this..

mysqldump --triggers --routines -h test -p database | gzip -3 | ssh production "gunzip -c | mysql -h production_database_host -p production_database"

This looks cool (omg geek) and saves you a lot of dump file pushing, but it surpresses the warnings you might see when you load the dump from within the console

MySQL writes the following about trigger definers:

If you specify the DEFINER clause, these rules determine the legal DEFINER user values:

If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.

If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

Although it is possible to create a trigger with a nonexistent DEFINER account, it is not a good idea for such triggers to be activated until the account actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.

Source: http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

Mistake III: Being lazy

I have a very cool mysqldump wrapper, which is able to generate clean, reusable dump files. While overwriting the triggers without DEFINER I had a console transaction (locking table2) open on the production server, so the triggers on table2 didn't update at all but again, because of my data sql pipeline over 5 servers I did not see the timeout error.

Conclusion:

There was no bug, just the triggers were not created correctly..

Sometimes you should stop to be lazy, giving important things a bit more time and attention can save you a lot of time!!

Answer 2

Triggers in MySQL (unlike stored procedures) are always run in the context of the DEFINER. Triggers can appear not to work because the DEFINER does not have permissions to execute some or all of the trigger. In particular, in MySQL 5.1 and later the DEFINER needs to have the TRIGGER privilege as well as the relevant SELECT and/or UPDATE privileges.

When triggers don't seem to work, check the privileges.