MySQL auto_increment delivers different primary key in MyISAM and InnoDB

Question

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is:

pk.. animal_code animal_name animal_class effective_date expiry_date
1... 1... dogg... m... 2013年10月21日.... 9999年12月31日
2... 2... frog..... a... 2013年10月21日..... 9999年12月31日

If I run a update / insert code using MyISAM I get this:

pk.. animal_code animal_name animal_class effective_date expiry_date
1... 1... dogg... m... 2013年10月21日.... 2013年10月20日
2... 2... frog..... a... 2013年10月21日..... 9999年12月31日
3... 1... dog..... m... 2013年10月21日..... 9999年12月31日
4... 3... cat...... m... 2013年10月21日..... 9999年12月31日

And using InnoDB: pk.. animal_code animal_name animal_class effective_date expiry_date
1... 1... dogg... m... 2013年10月21日.... 2013年10月20日
2... 2... frog..... a... 2013年10月21日..... 9999年12月31日
4... 1... dog..... m... 2013年10月21日..... 9999年12月31日
5... 3... cat...... m... 2013年10月21日..... 9999年12月31日

The new primary keys differs between MyISAM and InnoDB. Obviously, there is a different enumarating paradigma behind these two engines. I just wonder what happens in this special case, that is, why InnoDB jumps one primary key. The code to get this results is below.
Thanks for any answer.

Update (22.10.2013):
I put the code on sqlfiddle: http://sqlfiddle.com/#!2/30cae/1
The scenarios can be changed commenting out one engine and commenting in the other engine.
First run on the left panel Build Schema
Then see results clicking on the left panel on Run SQL
Changing scenario: in rows 10 and 11:

 ) ENGINE=MyISAM
-- ) ENGINE=INNODB 

PS: I have no problem if there are gaps between ids, I just wonder why InnoDB jumps.

use test01;
DROP TABLE animals;
DROP TABLE stage; 
/* (1) Create data base */
CREATE TABLE animals (
 pk MEDIUMINT NOT NULL AUTO_INCREMENT
 , animal_code INTEGER NOT NULL
 , animal_name CHAR(30) NOT NULL
 , animal_class CHAR(30) NOT NULL
 , effective_date DATE NOT NULL
 , expiry_date DATE NOT NULL
 , PRIMARY KEY (pk)
-- ) ENGINE=MyISAM
 ) ENGINE=INNODB
;
/* (2) Create a stage table */
CREATE TABLE stage (
 animal_code INTEGER NOT NULL
 , animal_name CHAR(30) NOT NULL
 , animal_class CHAR(30) NOT NULL
) ENGINE=MyISAM
;
/* (3.1) First import */
INSERT INTO stage (animal_code, animal_name,animal_class) VALUES
(1,'dogg','m'),(2,'frog','a')
;
/* (3.2) Second import 
TRUNCATE stage;
INSERT INTO stage (animal_code, animal_name, animal_class) VALUES
(1,'dog','m'),(2,'frog','a'),(3,'cat','m')
;
*/
/* --- (4) - (6) are use as soon new data are loaded in table stage --- */ 
/* --- So, first insert (3.1) and run (4)-(6) --- */ 
/* --- Then import new data (3.2) into stage and run (4)-(6) --- */ 
/* (4) expire the existing product */
UPDATE 
 animals a
, stage b
SET
 expiry_date = SUBDATE(CURRENT_DATE, 1)
WHERE
 a.animal_code = b.animal_code
 AND ( a.animal_name <> b.animal_name
 OR b.animal_class <> b.animal_class
 )
AND expiry_date = '9999-12-31'
;
/* (5) add a new row for the changing product */ 
INSERT INTO animals 
SELECT
 NULL 
, b.animal_code
, b.animal_name
, b.animal_class
, CURRENT_DATE
, '9999-12-31'
FROM 
 animals a
, stage b 
WHERE a.animal_code = b.animal_code
 AND (a.animal_name <> b.animal_name
 OR b.animal_class <> b.animal_class
 )
 AND EXISTS 
 (SELECT * 
 FROM animals x 
 WHERE b.animal_code = x.animal_code 
 AND a.expiry_date = SUBDATE(CURRENT_DATE, 1) 
 )
 AND NOT EXISTS 
 (SELECT *
 FROM animals y 
 WHERE b.animal_code = y.animal_code 
 AND y.expiry_date = '9999-12-31' 
 );
/* (6) add new product */
INSERT INTO animals
SELECT
 NULL 
, animal_code
, animal_name
, animal_class
, CURRENT_DATE
, '9999-12-31'
FROM stage 
WHERE animal_code NOT IN(
 SELECT y.animal_code 
 FROM animals x, stage y 
 WHERE x.animal_code = y.animal_code 
 );

• ypercubeᵀᴹ: I don't see the initial inserts into animals.
• giordano: @ypercube I enumerated the codes. The initial insert is code chunk (3.1). Once table are created ((1) and (2)) run (3.1) and (4)-(6). Updating: (3.2) and (4)-(6).
• Michael - sqlbot: Your code, as posted, is difficult to understand and does not seem to generate results as you have described. Please consider familiarizing yourself with sqlfiddle.com ... you can define the exact two scenarios you describe on that site, with the different storage engines, and update your question with links to the two scenarios. You can start with this: sqlfiddle.com/#!2/cdcc3a/1
• giordano: @Michael-sqlbot Thanks! I didn't know about sqlfiddle. I will update my question.

Answer 1

All you can guarantee with an auto-increment is that (unless you jiggle about with it to reset the value) the next value that is generated will be higher than the last one. It is usually just one higher, but you should never rely on this: beyond simply always increasing further detail is undefined behaviour.

As to why it might be different with different table types: different tables types vary in their locking/transaction/concurrency behaviours (myISAM being pretty simple/naive and InnoDB far less so in order to offer the referential integrity and performance benefits). It may be that InnoDB was being cautious and allowing for concurrent transactions, and when you inserted rows from details in animals (step 5) "reserved" the IDs it thought it might need but overestimated so didn't use one of them leaving the gap. myISAM probably just applied a table lock for the duration of that update so no concurrency could happen, so it has no need to reserve IDs in case of concurrent insert operations (so consequently won't leave gaps if it overestimates the number of rows output).

• giordano: Thanks. This helps me to understand better that the counting process depends on engine and that auto-increment provides unique key in increasing order but not necesserly consecutively.
• David Spillett: Actually, depending on the transaction/locking options used (assuming mysql can be as flexible as other DBMSs in this regard) there might be times when long running transactions make the IDs look like they are not always guaranteed to be increasing (they are, and will appear so for the standard/default options, but some may not appear in the data as other transactions see it until the long running transaction has completed).