MySQL Bugs: #13473: Before insert trigger executed, without insert happening

Bug #13473	Before insert trigger executed, without insert happening
Submitted:	25 Sep 2005 22:49	Modified:	7 Sep 2006 0:16
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.12	OS:	* / windows
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
From http://dev.mysql.com/doc/mysql/en/using-triggers.html
> The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table.

So, if no row is inserted (for example due to unique key), the trigger should not be executed.

This is correctly handled for a simple "insert into table" statement, on both InnoDB and MyIsam. The fact that the trigger is not executed in the MyIsam example, also shows that this is not the result of rolling the trigger back.

On both Engines:
If you execute a "insert ignore" or an "insert on duplicate key", with values that can not be inserted, the "before insert" trigger is executed, without the row beeing inserted.

The documentation speaks only of trigger execution "before each row inserted",  not before the attempt to do so.

A similiar error happens on replace:
http://dev.mysql.com/doc/mysql/en/replace.html
>REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. 

It triggers:
before insert, before update and after update. According to the documentation it doesnt update anything.

How to repeat:
CREATE TABLE log (a varchar(200));

CREATE TABLE ti (a int(11) NOT NULL default 0, b int(11),  PRIMARY KEY  (a)) ENGINE=InnoDB;
create trigger ti1 before insert on ti
for each row insert into log select concat("before insert ",new.a,new.b);
create trigger ti2 after insert on ti
for each row insert into log select concat("after insert ",new.a,new.b);
create trigger ti3 before update on ti
for each row insert into log select concat("before update ",new.a,new.b);
create trigger ti4 after update on ti
for each row insert into log select concat("after update ",new.a,new.b);

CREATE TABLE tm (a int(11) NOT NULL default 0, b int(11),  PRIMARY KEY  (a)) ENGINE=MyIsam;
create trigger tm1 before insert on tm
for each row insert into log select concat("before insert ",new.a,new.b);
create trigger tm2 after insert on tm
for each row insert into log select concat("after insert ",new.a,new.b);
create trigger tm3 before update on tm
for each row insert into log select concat("before update ",new.a,new.b);
create trigger tm4 after update on tm
for each row insert into log select concat("after update ",new.a,new.b);

# test for InnoDB

#this will insert one row
insert into ti  select 1,1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
| after insert 11  |
+------------------+
# correct both triggers

#this will NOT insert a  row
insert into ti  select 1,1;
select * from log; delete from log;
#Empty set (0.00 sec)
#correct NO triggers

#this will NOT insert a  row
insert ignore into ti  select 1,1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
+------------------+
1 row in set (0.00 sec)
# not correct, we have not inserted the row

#this will NOT insert a  row, but update
insert into ti  select 1,1 on duplicate key update b=b+1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
| before update 12 |
| after update 12  |
+------------------+
# we have not inserted anything

# same for MyIsam

#this will insert one row
insert into tm  select 1,1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
| after insert 11  |
+------------------+
# correct both triggers

#this will NOT insert a  row
insert into tm  select 1,1;
select * from log; delete from log;
#Empty set (0.00 sec)
#correct NO triggers

#this will NOT insert a  row
insert ignore into tm  select 1,1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
+------------------+
1 row in set (0.00 sec)
# not correct, we have not inserted the row

#this will NOT insert a  row, but update
insert into ti  select 1,1 on duplicate key update b=b+1;
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
| before update 12 |
| after update 12  |
+------------------+
# we have not inserted anything

#replace
replace into ti values (1,6);
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 16 |
| before update 16 |
| after update 16  |
+------------------+

drop table log; drop table ti; drop table tm;

Suggested fix:
always check if the row can be inserted, before executing the trigger.

This seems to happen for the simple "insert" (without ignore) statement, as even the MyIsam table does not leave any data in log.

Rolling back the trigger, would not do that, and therefore not be as good as a solution.

Why is replace performing an update?

a "create table if not exist" does also trigger before insert, in case of duplicare key.

on the above table, after inserting (1,1), run:

mysql> create table if not exists tm (a int, b int) select 1 a,1 b;
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
+------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.03 sec)

I tested on 5.0.14 and found that before insert trigger is executed everytime.
For example When I insert the duplicate value the first time I've got the follow result:
mysql> insert into ti  select 1,1;
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
+------------------+
1 row in set (0.00 sec)

According to MySQL reference manual :  The keyword INSERT indicates the event that activates the trigger. In the example, INSERT statements cause trigger activation. 
I would like to notice the trigger activation is not inserting any row in a table. It is INSERT statement. 
What do you think about my thoughts ?

At the end of the same page of the manual you quoted it is written:

"MySQL handles errors during trigger execution as follows: 

- If a BEFORE trigger fails, the operation on the corresponding row is not performed. 

- An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully. 

- An error during either a BEFORE or AFTER trigger results in failure of the whole statement that caused trigger invocation. 

For transactional tables, failure of a trigger (and thus the whole statement) should cause rollback of all changes performed by the statement. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect."

Note the last paragraph. What you described is documented, intended behaviour, that can be used, with nontransactional tables, to register the attempt to perform a change. So, I think, it is at most a request for proper documentation, that will explicitely describe the sequence of actions in case there are integrity constraints involved.

Have you ever used row-level triggers in other databases? In Oracle, for example, before row trigger alway fires before any attempt to, say, insert a row, either successfull or not.

the behaviour also hapens on transactional InnoDb tables, as shown in my test.

According to Vasily Kishkin, in the current BK version:
mysql> insert into ti  select 1,1;
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 11 |
+------------------+
1 row in set (0.00 sec)

he got the result of a "before insert", even on a failed insert to a transactional table.

Also the documentayion, only speaks of what happens, if any of the triggers fails.

The documentation does not speak, about what happens, if the actual statement fails, or will fail.
So, if this behaviour is intended, it should be documented. Also it is possible, that a test for it is missing? (point 3 below).
Also, if this behaviour is intended, it should also be for point 2 below, unless allready changed.

There has been no comment, to the behaviour of replace.

--

I was just to add a couple of other thougths, while this got closed. Please see point 1, where I believe the behaviour to be inconsistent, but yes this might be intend. (same for point 2, unless changed after 5.0.12).

1) attempted insert vs succesfull insert. If the "before" trigger is on attempted insert, then the after insert should be the same (There would still have been an attempt).
I thing "before" and "after" should be consistent, any event that triggers "before", must be triggering "after" too.

2) compare vs update. In the above table, insert the values (2,2) in addition to (1,1)
insert into ti  select 2,2; delete from log;
update ti set a = 1 where a = 2;
#ERROR 1062 (23000): Duplicate entry '1' for key 1
select * from log; delete from log;
#Empty set (0.00 sec)

on 5.0.12 an update, which fails on dup key, does not trigger the "before" for the attempted update

3) According to Vasily Kishkin's test, the behaviour changed from version 5.0.12 to BK, this indicates, that a test and/or definition for this behaviour might be missing.

The question is: what storage engine is used for log table (show create table log will help). If it is MyISAM (non-transactional), then I see nothing more than a documentation request. If it is InnoDB, then it is a bug really.

good point.

In my case log is innodb. If I change it to MyIsam, I get the same result, as Vasily Kishkin.
So the behaviour has at least not changed unexpectingly.

Yes, I agree, this becomes a documentation issue. the following should be documented:
- "before" triggers are executed, for any row that will be attempted to be inserted
- "after" triggers are executed only on success
- failure of the executed statement (and not only of any trigger), will also cause rollback (if available)
- the behaviour of "ignore" and "on duplicate", might also be pointed out

Thanks for the feedback
Martin

PS:
What about the behaviour and triggers for a replace statement (update is triggerred)? Shall I open as a differnt bug?

I agree that documentation should be modified to describe the existing behaviour more precisely. As for REPLACE, i do not think it needs special attention, because of the following sentence in the manual (http://dev.mysql.com/doc/mysql/en/replace.html):

"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted."

So, this "works exactly like INSERT" explains the triggers behaviour.

We have some internal email discussion on this bug report. Before changing documentation there is a REPLACE-related bug to be fixed (and it is known already). Look at the end of original "How to repeat" section:

#replace
replace into ti values (1,6);
select * from log; delete from log;
+------------------+
| a                |
+------------------+
| before insert 16 |
| before update 16 |
| after update 16  |
+------------------+

REPLACE should NOT call trigger on UPDATE, anyway. See http://dev.mysql.com/doc/refman/5.0/en/replace.html:

"REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. If you're looking for a statement that follows the SQL standard, and that either inserts or updates, see Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”."

So, replace can activate DELETE triggers, but NOT UPDATE. We have a separate bug report about it, bug #13479. Still not fixed. This bug should become a documentation request only when bug #13479 will be fixed.

Fix for bug#13479 was pushed into the source tree and should appear in 5.0.23 and 5.1.12.
So this bug becomes documentation request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added notes to clarify that:

BEFORE triggers are activated by the attempt to perform the triggering
statement, before we know whether the statement will succeed.

Failure of a statement *or* its triggers cause rollback, if available.