MySQL Bugs: #9629: Triggers: auto_increment column value not available during action

Bug #9629	Triggers: auto_increment column value not available during action
Submitted:	5 Apr 2005 0:43	Modified:	28 Apr 2005 1:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:		CPU Architecture:	Any

Description:
During a INSERT trigger action, the value of an auto_increment column should be visible in NEW.auto_increment_column. By analogy with standard IDENTITY columns, the value should be the "new" value even in a BEFORE trigger. (That's not absolutely necessary provided that there's documentation of when the auto-increment occurs.) But the BEFORE trigger appears to have an "old" value, or zero. The AFTER trigger is usually okay, it has the "new" value, but one occasion (unrepeatable) it had NULL.

How to repeat:
mysql> create table t83 (s1 int auto_increment primary key, s2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t83_bi before insert on t83 for each row set @new = new.s1;
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t83_ai after insert on t83 for each row set @newa = new.s1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t83 (s2) values (0);
Query OK, 1 row affected (0.01 sec)

mysql> select s1, @new, @newa from t83;
+----+------+-------+
| s1 | @new | @newa |
+----+------+-------+
|  1 | 0    | 1     |
+----+------+-------+
1 row in set (0.00 sec)

mysql> insert into t83 (s2) values (0);
Query OK, 1 row affected (0.00 sec)

mysql> select s1, @new, @newa from t83;
+----+------+-------+
| s1 | @new | @newa |
+----+------+-------+
|  1 | 0    | 2     |
|  2 | 0    | 2     |
+----+------+-------+
2 rows in set (0.00 sec)

I would say this is an documentation issue (for now)

In MySQL, any integer, not null column has a 'implicite' default value of 0 (if now default value is set).
This is what you should see in the BEFORE trigger for the auto_increment column.
(It's quite hard to do anything else in the current MySQL version)
The same is true for any other number column (without a default value)

For us to fix the NULL problem, please try to find a way to repeat it
(I can't see any way this could happen as an AUTO_INCREMENT column should always be defined as NOT NULL and should thus never be able to generate a NULL value)

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
product(s).