1

Possible Duplicate:
Why does MySQL autoincrement increase on failed inserts?

I have a table

CREATE TABLE `authors` (
`author_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

which I use to collect names of authors. I use oursql to populate this table. The query is

query_author ="""INSERT INTO authors (name) VALUES (?) ON DUPLICATE KEY UPDATE author_id=LAST_INSERT_ID(author_id)"""

and I execute it like this:

cursor.execute(query_author, (author,))
author_id = cursor.lastrowid # further using of the authors id.

Now I observe, that the auto increment does not always increment author_id by 1. It jumps. In the 1000-th row I have the index 1258. I doublechecked my script, but this is the only passage where I modify the table authors. What could possibly affect this behaviour? Some settings in my.cnf I don't know about? I am clueless with this.

Community
  • 1
  • 1
Aufwind
  • 25,310
  • 38
  • 109
  • 154
  • As supplement to the answer in the Q&A that ypercube linked: `INSERT INTO ... ON DUPLICATE KEY` must actually attempt the insert before it is aware there is a duplicate key. In order to attempt the insert, it must increment. The other page explains the rest. – Joel B Fant Aug 08 '11 at 22:12
  • @Joel: Please make this an answer, so I can accept. I understand the behavior of InnoDB regarding autoincrement know. I just have to think of a way to bypass it, since I don't want the size of my `author_id` to go through the ceiling. :-) – Aufwind Aug 08 '11 at 22:17
  • Yes, check also this question: http://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert and the MySQL docs on AUTO_INCREMENT: http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html – ypercubeᵀᴹ Aug 08 '11 at 22:33
  • 1
    @Aufwind: check the answer on question 5924762 on how you can use `WHERE NOT EXISTS` so you avoid `ON DUPLICATE KEY`. You may find suitable to write a stored procedure for the `INSERT` (check for duplicates with not exists and do INSERT or UPDATE). But I see no issues with having missing IDs in your table (and many other database users/programmers agree with that). – ypercubeᵀᴹ Aug 08 '11 at 22:37

0 Answers0