Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 are 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 reslts clcking 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 
                       );
share|improve this question
    
I don't see the initial inserts into animals. –  ypercube Oct 21 '13 at 10:27
    
@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). –  giordano Oct 21 '13 at 11:43
    
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 –  Michael - sqlbot Oct 22 '13 at 2:27
    
@Michael-sqlbot Thanks! I didn't know about sqlfiddle. I will update my question. –  giordano Oct 22 '13 at 9:57

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer
    
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. –  giordano Oct 31 '13 at 21:01
1  
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). –  David Spillett Aug 11 at 10:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.