Auto increment value needs to be changed inside trigger

Question

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),
seqno int(11) auto increment 
Joint PK index on the above 2 fields

New structure:

auto_id INT (11) auto increment,
id varchar(15),
seqno int(11)

The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;
//

CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`
 FOR EACH ROW BEGIN

SET @newseqno = 0;

SELECT MAX(seqno) INTO @newseqno
FROM my_table
WHERE id = NEW.id;

SET NEW.seqno = (@newseqno + 1);
/* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */
END;
//

Thanks Peter

Answer 1

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly):

CREATE TRIGGER innodb_seqno_trigger 
   BEFORE INSERT ON my_table 
   FOR EACH ROW 
BEGIN

  SELECT MAX(seqno) INTO @newseqno 
  FROM my_table 
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1); 

END; 

Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?

Answer 2

because innodb doesnt support joint primary keys

Wait, what?

Unless a "joint primary key" describes something other than a composite key or compound key as primary key, then, of course, InnoDB does support this.

mysql> CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ERROR 1075 (42000): Incorrect table definition; there can be only one 
auto column and it must be defined as a key

All right, fine... it's not that InnoDB won't allow it, it's just that the auto_increment columns has to be declared as a key (index)... so, make seqno a key, and InnoDB has no problem:

mysql> CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`seqno`),
  KEY `seqno` (`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Query OK, 0 rows affected (0.25 sec)

Or did I miss something?

Answer 3

I wish InnoDB could support auto_increment the same way MyISAM does. I wrote about this before

• Apr 30, 2013 : Issues converting MyISAM table to InnoDB (auto column issue)
• Apr 21, 2012 : How can you have two auto-incremental columns in one table?

This being the case, you must accept full responsibility for sequencing the two columns. For starters, you must remove the auto_increment option from all columns that involve your sequencing.

CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL,
  ...
  PRIMARY KEY (`id`,`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Your trigger has be just as ypercube specified. He gets +1 for that.

Answer 4

Based on your new structure, the auto_id field holds the auto_increment value from where mysql would fetch the "next" if we need it.

The value you want as the auto_increment value can be got using:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table name';

You can directly do

SET NEW.seqno = SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table name'

in your trigger definition.