MySQL Trigger to generate a hash for an auto_increment index

Question

I'm trying to create a trigger that generates and store a hash for the auto_increment index but all solutions I've tried did not work:

DELIMITER //
CREATE TRIGGER insertTable1 AFTER INSERT ON Table1
FOR EACH ROW 
   BEGIN
     SET NEW.hash = calc_hash_udf(NEW.id);
   END //
DELIMITER ;;

It says I cannot modify a NEW after the INSERT, and before the INSERT I don't have the auto_increment value:

ERROR 1442 (HY000): Can't update table 'Table1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Answer 1

You cannot modify the value anymore after the row was inserted. Therefore SET NEW.column is only available in a BEFORE trigger.

Also you cannot use a regular UPDATE either because:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Finally, in a BEFORE INSERT trigger, the AUTO_INCREMENT value has not been generated yet, and NEW.id is 0.

The trick: in a BEFORE trigger, manually check the table definition for the next AUTO_INCREMENT value:

WARNING: this only works with MyISAM, not with InnoDB

I suppose it could work with InnoDB if innodb_autoinc_lock_mode = 0 but I am unable to tell for sure.

DELIMITER //    

    CREATE TRIGGER insertTable1 BEFORE INSERT ON Table1 FOR EACH ROW
    BEGIN
        DECLARE next_ai INT;
        SELECT auto_increment INTO next_ai
          FROM information_schema.tables
          WHERE table_schema=DATABASE() AND table_name = 'Table1';
        SET NEW.hash = calc_hash_udf(next_ai);
    END //

DELIMITER ;

[edit 1]

As for the concurrent-proof property of this approach, I can say:

• with MyISAM, where only table-locks are available, the safety is obvious: an exclusive lock on the table(s) is acquired by any INSERT/UPDATE/DELETE, and no concurrent access can happen.

• with InnoDB, this is less obvious. For the "traditional lock mode", the manual says:

InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement

I suppose this is safe in this case.

I am not familiar with these concepts, so I couldn't tell for sure. It seems dubious indeed.

[edit 2]

I have run the below test with different settings for innodb_autoinc_lock_mode:

CREATE TABLE t ( ai INT AUTO_INCREMENT PRIMARY KEY, trigval INT, flag BOOL );

A trigger on the table does SET NEW.trigval = next_ai with the method above.

In one transaction, a long INSERT is made by:

INSERT INTO t SELECT null, null, 0 FROM (SELECT * FROM a_very_big_table) AS tmp;

In a second transaction, I constantly issue the following statement:

INSERT INTO t VALUES (null, null, 1);

At the end, I search for discrepancies:

SELECT * FROM t WHERE ai <> trigval;

With innodb_autoinc_lock_mode = 0 ("traditional") it seems to be safe. Any concurrent attempt to insert into the table is locked until completion of the long INSERT.

However, I did not expect that, with modes 1 (the default) and 2, this approach is plainly wrong. information_schema.tables.auto_increment is updated by steps. This is the result I got:

+-------+---------+------+
| ai    | trigval | flag |
+-------+---------+------+
|     3 |       4 |    0 |
|     5 |       8 |    0 |
|     9 |      16 |    0 |
|    17 |      32 |    0 |
|    33 |      64 |    0 |
|    65 |     128 |    0 |
...