mySQL auto increment problem: Duplicate entry '4294967295' for key 1

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

Just change it to a BIGINT and you can create "some" extra new records. A couple of hundred billion... ;)

Answer 2

What version of mysql? Did you read any of the "about 3,020 for Duplicate entry '4294967295' for key 1" results from Google? Have you checked to see if there's any gaps in the current ids? Have you checked to see if there really is a record with id 4294967295? Are you sure your code doesn't do an explicit insert/update on this field to set it to -1? What do you mean by "the last record"? The most recent? The max id?

You can read back the current insert id (I can see this in PMA) though I've no idea how to check it.

C.

Answer 3

Its still not totally clear to me what happened here, but I thought I'd follow up.

In my persistence engine, I had one type of object with a auto-increment id, and a subclass with a GUID id.

Obviously the two were incompatible. I have a reason to convert the object to its parent and then save it (basically the subclass is an email TEMPLATE that has additional functionality, but when i want to actually SEND the email, I convert it to the parent object and save it to the regular outgoing mail queue). Stupidly, I didn't realize the id formats were different. This resulted in trying to insert a record with a 36 character long string into an int. The string resolved to '0' in the prepared statement and for whatever reason this cause the auto-increment system to WIG OUT and max out the INT auto increment id field on the original table.

Long story short, good thing I was keeping logs.

Josh

Answer 4

by phpmyadmin simply you can change last id with operations tab of that table