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.
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