CODEWITHSUNDEEP
mysqlprimary-keyauto-increment
Petrogad
Petrogad

Reputation: 4423

MySQL Auto-Inc Bug?

In my MySQL table I've created an ID column which I'm hoping to auto-increment in order for it to be the primary key.

I've created my table:

CREATE TABLE `test` (
        `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `name` VARCHAR( 50 ) NOT NULL ,
        `date_modified` DATETIME NOT NULL ,
    UNIQUE (
        `name`
    )
) TYPE = INNODB;

then Inserted my records:

INSERT INTO `test` ( `id` , `name` , `date_modified` ) 
VALUES (
    NULL , 'TIM', '2011-11-16 12:36:30'
), (
    NULL , 'FRED', '2011-11-16 12:36:30'
);

I'm expecting that my ID's for the above are 1 and 2 (respectively). And so far this is true. However when I do something like this:

insert into test (name) values ('FRED') 
on duplicate key update date_modified=now();

then insert a new record, I'm expecting it to be 3, however now I'm shown an ID of 4; skipping the place spot for 3.

Normally this wouldn't be an issue but I'm using millions of records which have thousands of updates every day.. and I don't really want to even have to think about running out of ID's simply because I'm skipping a ton of numbers..

Anyclue to why this is happening?

MySQL version: 5.1.44

Thank you

Upvotes: 2

Views: 213

Answers (3)

Adrian Cornish
Adrian Cornish

Reputation: 23858

Is it possible to change your key to unsigned bigint - 18,446,744,073,709,551,615 is a lot of records - thus delaying the running out of ID's

Found this in mysql manual http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Use a large enough integer data type for the AUTO_INCREMENT column to hold the
maximum sequence value you will need. When the column reaches the upper limit of
the data type, the next attempt to generate a sequence number fails. For example,
if you use TINYINT, the maximum permissible sequence number is 127. 
For TINYINT UNSIGNED, the maximum is 255.

More reading here http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id it could be inferred that the insert to a transactional table is a rollback so the manual says "LAST_INSERT_ID() is not restored to that before the transaction"

What about for a possible solution to use a table to generate the ID's and then insert into your main table as the PK using LAST_INSERT_ID();

From the manual:

Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to
LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that
value. The mysql_insert_id() C API function can also be used to get the value. 
See Section 20.9.3.37, “mysql_insert_id()”.

Upvotes: 3

Hamikzo
Hamikzo

Reputation: 254

It's really a bug how you can see here: http://bugs.mysql.com/bug.php?id=26316 But, apparently, they fixed it on 5.1.47 and it was declared as INNODB plugin problem. A duplicate, but same problem, you can see here too: http://bugs.mysql.com/bug.php?id=53791 referenced to the first page mentioned here in this answer.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

My guess is that the INSERT itself kicks off the code that generates the next ID number. When the duplicate key is detected, and ON DUPLICATE KEY UPDATE is executed, the ID number is abandoned. (No SQL dbms guarantees that automatic sequences will be without gaps, AFAIK.)

MySQL docs say

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

That page also says

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value.

which stops far short of describing the internal behavior I guessed at above.

Can't test here; will try later.

Upvotes: 4

Related Questions