Q:

MySQL 5.5.30 cascaded triggers not working

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

A:

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!!

