Trick for AUTO_INCREMENT on composite keys in InnoDB In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table:

CREATE TABLE `ExampleTable` (
     `KeyPartA` mediumint(8) unsigned NOT NULL,
     `KeyPartB` smallint(5) unsigned NOT NULL,
     `Data` text NOT NULL,
     PRIMARY KEY (`KeyPartA`,`KeyPartB`)
) ENGINE=InnoDB;


Now to simulate AUTO_INCREMENT, I learned, it is possible to create the following trigger

DELIMITER $$

CREATE TRIGGER ExampleAutoIncrement BEFORE INSERT ON ExampleTable
FOR EACH ROW BEGIN
        SET NEW.KeyPartB = (
             SELECT IFNULL(MAX(KeyPartB), 0) + 1
             FROM ExampleTable
             WHERE KeyPartA  = NEW.KeyPartA
        );
END $$

DELIMITER ;


Now inserts work correctly, generating auto-incremented values. Unfortunately I can not think of a sensible way to get the newly created key value. 

Neither LAST_INSERT_ID() nor its C counterpart return it, as there is no actual AUTO_INCREMENT column. Also it is not possible to set the value with LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1) inside the ExampleAutoIncrement trigger, because MySQL resets the value after the trigger finishes.

The only way I thought of was to issue a SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ? after an insert, which defeats the whole purpose of the trigger. I.e. I could just as well do this before the insert and increment manually.

Is there any way to get the incremented key value from the trigger or some other means of simulating the AUTO_INCREMENT I want?
My solution is in migrating from myisam to innodb.  It goes something like:

  BEGIN;
  SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, @id, ...);
  COMMIT;


Having a transaction is mandatory to prevent another thread from grabbing the same id.