MySQL Bugs: #14053: Impossible to access auto_increment values in triggers on insert

Bug #14053	Impossible to access auto_increment values in triggers on insert
Submitted:	15 Oct 2005 23:44	Modified:	4 Nov 2009 7:46
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.13-rc-standard-log	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
It's impossible to access an auto_increment column in a BEFORE INSERT trigger. NEW.column for an auto_increment column always returns 0. The auto_increment value seems to be generated only after the trigger has fired.

How to repeat:
mysql> CREATE TABLE t (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY (a));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TRIGGER t BEFORE INSERT ON t FOR EACH ROW SET NEW.b = NEW.a;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (a) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+------+
| a | b    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.00 sec)

Suggested fix:
Workaround: Use an additional table/stored routine to generate a sequence and use this sequence value instead of the built-in auto_increment feature.

Docomented behavior, see

http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html

  In a BEFORE trigger, the NEW  value for an AUTO_INCREMENT column is 0, 
  not the automatically generated sequence number that will be generated when 
  the new record actually is inserted.

Okay, sorry about this. I'd suggest to open it again as S4.

I know at first sight it's probably not considered to be good practice to base any computations on an auto_increment primary key and to store the results in an additional column in the same table. But there exist valid scenarios, where exactly this would be really useful: What I want to do for example is to generate a tag URI to be used in an Atom feed. These tags must stay globally unique for all time to come, even if the data should get imported to another database and the primary key maybe changes. A very natural way to achieve this, is to use your current domain name, the current date plus your unique primary key for the article. To enforce this is at the insert of a new row is a typical application for triggers.

Anyway, MySQL 5.0 gives me enough power to work around the existing limitation: I currently use a stored function and an additional table to generate a sequence that's used by the trigger both as pseudo auto_increment primary key and to generate the tag URI. It would just be convenient to use MySQL's already existing auto_increment feature instead of building a lot of code to emulate it the same way it already works just to lift the limitation on the use in triggers.

Just opened it again as a feature request instead of a bug.

Is it a valid workaround to look up the AUTO_INCREMENT value from information_schema.tables?  Or would there be concurrency problems?

Example follow -- you may need to correct for line wrapping.

Dan

/* test_workaround.sql */
USE test ;
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mac_address` CHAR(17) NOT NULL DEFAULT '00:16:DD:xx:xx:xx', /* see http://anonsvn.wireshark.org/wireshark/trunk/manuf */
  `serial_number` VARCHAR(60) NOT NULL DEFAULT '',
  `comment` TEXT NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=400 DEFAULT CHARSET=utf8 COMMENT='Assigned MAC Addresses' ;
/* DROP TRIGGER compute_inserted_mac_address ; */
/* DROP TRIGGER computed_updated_mac_address ; */
DELIMITER //
CREATE TRIGGER compute_inserted_mac_address BEFORE INSERT ON addresses
FOR EACH ROW
    BEGIN
        DECLARE next_id INT(11) UNSIGNED ;
        DECLARE tmp CHAR(6);
        DECLARE right_now TIMESTAMP;
        SELECT AUTO_INCREMENT FROM information_schema.tables
            WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'addresses'
            INTO next_id ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + next_id, 10, 16))), 6) ; /* 0 => ... :C0:00:00 */
        SET right_now = NOW() ;
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':', SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = right_now ;
        SET NEW.updated_on = right_now ;
    END ;
//
DELIMITER ;
DELIMITER //
CREATE TRIGGER computed_updated_mac_address BEFORE UPDATE ON addresses
FOR EACH ROW
    BEGIN
        DECLARE tmp CHAR(6) ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + OLD.id, 10, 16))), 6) ; /* 0 => ... :C0:00:00 */
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':', SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = OLD.created_on ;
        SET NEW.updated_on = NOW();
    END ;
//
DELIMITER ;
SHOW TRIGGERS \G
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0001BKAP8', '');
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0000089JE', '');
SELECT * FROM addresses ;

Has there been any updates on this? Alternatively, can someone speak to the last comment before this one--is that an acceptable work around, or a brittle solution?

Specifically, on using innodb, I have the following trigger (note that I'm not showing all the code):

BEGIN
  DECLARE next_id INT(11) UNSIGNED ;
  SELECT AUTO_INCREMENT FROM information_schema.tables
    WHERE TABLE_SCHEMA = 'my_database_name' AND TABLE_NAME = 'my_table_name'
    INTO next_id ;
  SET NEW.id_master = next_id;
END