2

I have implemented a typical "audit log" trigger in Mysql version 5.5. I use AFTER INSERT to copy inserted rows from table user into my 'audit_log' table.

So for this sequence:

BEGIN;
insert into user (name) values ('joe');
<--trigger fires, adds new row to audit_log table-->
COMMIT;

Then I get a new row in 'audit_log' with 'joe'. Great.

However, it appears that the results of my trigger are applied even if the insert which fired the trigger is aborted by its enclosing transaction.

So for this sequence:

BEGIN;
insert into user (name) values ('mary');
<--trigger fires, adds new row to audit_log table-->
ROLLBACK;

I STILL end up with a new row 'mary' in audit_log, which refers to data that was never committed to my user table. This seems pretty clearly wrong.

It appears that trigger updates performed in Postgres execute within the original transaction, which is what I would expect to happen. Anyone have experience with this in MySQL? Perhaps there is a setting I have wrong?

I note that this question implies that all updates happen in the original transaction. However, the answer refers to the Mysql manual page on triggers, which in fact has no mention of "transation" at all.

Community
  • 1
  • 1
Scott Persinger
  • 3,554
  • 2
  • 20
  • 12
  • I've just done a quick test in MySQL 5.5 and the trigger seems to have rolled back ok. What version of MySQL are you using? – Steve Mar 27 '13 at 21:20
  • mysql normally support triggers in transactions. See http://stackoverflow.com/questions/13823165/mysql-trigger-with-transaction-insert-or-update – didierc Mar 27 '13 at 21:38
  • Hmmm..I'm certainly not TRYING to avoid the transaction. I'll try to create a more limited test case. – Scott Persinger Mar 28 '13 at 22:06
  • 1
    Haha, can I hide my name from the original post? I was writing into a MyISAM table with my trigger. Stick with InnoDB and it's all fine. – Scott Persinger Mar 28 '13 at 22:55

1 Answers1

3

Turns out my audit table was using the MyISAM engine which of course prevented it from obeying the transaction properly.

My colleague says I owe Larry Ellison an apology.

Larry, I'm sorry I doubted the transactional semantics of Mysql.

Scott Persinger
  • 3,554
  • 2
  • 20
  • 12