MySQL 5.5.30 cascaded triggers not working

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.

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