Bug #44642 before trigger ignore auto_increment values
Submitted: 4 May 2009 12:17 Modified: 8 May 2009 19:42
Reporter: Susanne Ebrecht Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0, 5.1, 5.4, 6.0 OS:Any
Assigned to: CPU Architecture:Any

[4 May 2009 12:17] Susanne Ebrecht
Description:
Auto_increment values will be ignored in before triggers.

If you want to set a value to the same value as the auto_increment value then it will fail.

All works fine if you set a value to a constant value or another function.

How to repeat:
CREATE TABLE t(i serial, j bigint usigned);

/* this also works: 
CREATE TABLE t(i integer auto_increment, j integer, primary key(i));
*/

DELIMITER §

CREATE TRIGGER tr_t BEFORE INSERT ON t
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i;
END§

DELIMITER ;

INSERT INTO t VALUES();

SELECT * FROM t;

Expected would be i and j with value 1, but i=1 and j=0.

Suggested fix:
...
[8 May 2009 19:42] Omer Barnir
This is a known (and documented) limitation of MySQL: see bug#9629