MySQL ON DUPLICATE KEY seems to increment the auto_increment column when triggered

Question

Consider the following SQL:

CREATE TABLE USER1
(
  pkUSER1_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  DATE_UPDATED TIMESTAMP NULL DEFAULT NULL,
  NAME VARCHAR(25) NOT NULL,
  CONSTRAINT PRIMARY KEY (pkUSER1_ID),
  CONSTRAINT UNIQUE (NAME)
)
ENGINE = INNODB;

INSERT INTO USER1
   SET NAME = 'asdf'
    ON DUPLICATE KEY
UPDATE DATE_UPDATED = NOW();

INSERT INTO USER1
   SET NAME = 'asdf'
    ON DUPLICATE KEY
UPDATE DATE_UPDATED = NOW();

INSERT INTO USER1
   SET NAME = 'asdf1'
    ON DUPLICATE KEY
UPDATE DATE_UPDATED = NOW();

SELECT * FROM USER1;

And now notice the result set. The auto_increment was increased despite nothing being inserted.

+------------+---------------------+-------+
| pkUSER1_ID | DATE_UPDATED        | NAME  |
+------------+---------------------+-------+
|          1 | 2010-02-09 13:29:15 | asdf  |
|          3 |                NULL | asdf1 |
+------------+---------------------+-------+

I get different behavior on two separate servers... the output above is from MySQL v5.0.45 running on 2.6.9-023stab048.6-enterprise (I think it's Red Hat). The problem doesn't exist on MySQL v5.0.51a-24+lenny2-log running on 2.6.26-2-amd64 (which is obviously Debian).

Is there a configuration setting I can change to avoid this? I have around 300 users in my database, but due frequency that the insert/update statement is run, the latest user id is over 600,000.

Answer 1

This is a bug... http://bugs.mysql.com/bug.php?id=28781

Not sure why the client is running a 3 year old version of MySQL.