Rain City Consulting

Of course, it is not possible to simply flip a switch in MySQL and turn off all triggers (lame). There is an ACTION_CONDITION column in the information_schema.TRIGGERS table, so I would not be surprised to see something like this in the future. In any event, here's my work-around:

1) Dump all triggers to file
2) Drop all triggers
3) Do what you need to do without triggers
4) Add triggers back in using file created in step 1)

Step 1) is difficult because there is no MySQL tool or mysqldump option to just get the triggers. So we get to use diff and some other command line tools:

$ mysqldump --no-data $DB >schema-with-triggers.sql
$ mysqldump --no-data --skip-triggers $DB >schema-without-triggers.sql
$ diff schema-with-triggers.sql schema-without-triggers.sql | egrep '^<' | fgrep -v AUTO_INCREMENT | sed -e's/^< //' >triggers.sql

What I did above is get two schema files, one with triggers and one without. Then I diffed the files and pulled out only the triggers. I did some other stuff too, that's left as an exercise to the reader. In the end, we have a triggers.sql file that has our trigger definitions.

Now to drop all triggers. The MySQL command to drop a trigger is DROP TRIGGER trigger_name. We need to get all the trigger names. We can get all trigger names using the information_schema.TRIGGERS table.

$ echo "SELECT TRIGGER_NAME FROM TRIGGERS WHERE TRIGGER_SCHEMA = 'xxx'" | mysql information_schema | awk 'NR > 1 {print "DROP TRIGGER "$1";"}' | mysql xxx

The above command finds all triggers in the xxx schema, creates SQL statements to drop those triggers, then sends those commands back to MySQL.

Now do what you want to do without triggers.

Now load the triggers back in:

$ mysql xxx <triggers.sql

This is currently untested. I'm going to do this later today.

As I was writing this, I thought of another option: use the information_schema.TRIGGERS table exclusively. This could mean something like:

1) Dumping the table, data and all using mysqldump
2) Truncating the table
3) Now, there are no triggers
4) Load the table in using the data from step 1)

I would restart MySQL after truncating the TRIGGERS table. I'm not even sure if this would work. I'm a little hesitant to mess with MySQL internals.