safely editing MySQL triggers in a production database

MySQL does not provide an atomic CREATE OR REPLACE TRIGGER, or an ALTER TRIGGER statement that will safely modify a trigger on a database while it is in use. The only way to update a TRIGGER is with a DROP and then a CREATE.

Why is that a big deal? Say, for example, you are using triggers to keep row counts up-to-date in a summary table. You may miss some inserts while you are issuing the DROP and then the CREATE. To verify this, I used mysqlslap. Here is my schema script:


drop table if exists triggertest.record_count;
create table triggertest.record_count
(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
count_name VARCHAR(64) NOT NULL,
count_value INTEGER UNSIGNED NOT NULL DEFAULT 1,
UNIQUE (count_name)
) ENGINE=InnoDB;

drop table if exists triggertest.record_table;
create table triggertest.record_table
(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
some_value VARCHAR(64) NOT NULL
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS triggertest.sp_increment_record_count;

DELIMITER |

CREATE PROCEDURE triggertest.sp_increment_record_count(IN countname VARCHAR(64))
BEGIN
INSERT INTO triggertest.record_count(count_name, count_value) VALUES (countname,1) ON DUPLICATE KEY UPDATE count_value = count_value + 1;
END
|

DELIMITER ;

DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;

CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table
FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');


I used mysqlslap to run a lot of inserts against the record_table, and while that was running, I re-created the trigger by running this script a bunch of times:


DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;
CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table
FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');


I then verified that the count_value in record_count was smaller than the number of records in record_table:


mysql> select * from record_count;
+----+--------------+-------------+
| id | count_name | count_value |
+----+--------------+-------------+
| 1 | record_table | 9944 |
+----+--------------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from record_table;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)

mysql>


At first, I was not sure there would be a solution to this. I realize that you can lock tables, but my first guess was that since ddl (DROP, CREATE, etc.) statements cause an implicit commit, that my locks would be released.

Fortunately, as the MySQL documentation explains, if you use LOCK TABLES, implicit commits don't release your locks. From the docs:

...statements that implicitly cause transactions to be committed do not release existing locks.

So the safe way to recreate my trigger is like this:

set autocommit=0;
lock tables triggertest.record_table write;
DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;
CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');
unlock tables;


I had some trouble testing this with mysqlslap, even with only one thread running inserts, because of some locking issues (the inserts would error out with a 'Lock wait timeout'), but I did get a few tests to make it through, so I could verify that the record_count matched the number of rows in the record_table. So the worst-case seems to be that some of the inserts on the production database may hit a lock wait timeout, but no inserts will miss firing the triggers!

UPDATE: Soon after writing this post, I came across this: http://code.openark.org/blog/mysql/why-of-the-week, which may explain why I was having so many problems with deadlocks when I tried to run against a database that was in use. I'm not talking about deadlocks where MySQL detects it and rolls back a transaction. Things would just lock up. No deadlock detected, no lock wait timeout, just locked up, until I killed a query. So while the solution above should work in theory, beware of MySQL locking bugs...