mySQL auto increment problem: Duplicate entry '

Question

I have a table of emails.

The last record in there for an auto increment id is 3780, which is a legit record. Any new record I now insert is being inserted right there.

However, in my logs I have the occasional:

Query FAIL: INSERT INTO mail.messages (timestamp_queue) VALUES (:time);
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '4294967295' for key 1
)

Somehow, the autoincrement jumped up to the INT max of 4294967295

Why on god's green earth would this get jumped up so high? I have no inserts with an id field.

The show status for that table, Auto_increment table now reads: 4294967296

How could something like this occur? I realize the id field should perhaps be a big int, but the worry I have is that somehow this thing jumps back up.

Josh

Edit: Update

mysql version 5.0.45 red hat patched

Since I set the id to BIGINT the last few id's look like:

3777
3778
3779
3780
4294967295
4294967296
4294967297
4294967298
4294967299
4294967300

As you can see, they are incremental, with no gaps (so far). Totally weird.

Answer 1

This usually happens when you accidentally insert a new record a provide the auto_increment value.

Most of the time that's because you have a column with a similar name and you make a typo that leads to the auto_increment value updated with the one you provided, which is 4294967295 if you provided a string instead of an int.

To solve this:

• delete the record whose PK value is 4294967295,
• check what was the max increment value by running

select max(autoincrementColumNameHere) from tableNameHere

• update your AUTO_INCREMENT value by running

 ALTER TABLE tableNameHere AUTO_INCREMENT = (maxValue+1)