Trick for AUTO_INCREMENT on composite keys in InnoDB
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.