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 actually just happened to me too (still not sure why). On a hunch I did:

ALTER TABLE  `{table name here}` AUTO_INCREMENT = {your number here};
FLUSH TABLE `{table name here}`;

and it seems to have fixed it. First I tried just setting the auto increment value first but it went back to 4294967295. Again, not sure why changing the increment value and flushing worked (I'm not a database guy), but I figured I'd post this here as it may help others.

Answer 2

I had the same problem with the exact same number. My problem was that I had the field on int(10) when I changed it to bigint(20) it solved my problem.

If others are having this problem. Check your field size first. :)

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

Answer 5

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

Answer 6

It happened to me too. The problem is a silly one.

If it receives a character string it converts it 0 and if integer, as in my case, where i was trying to insert a mobile no (in india it's 10 digits and starts with 9 like9123456789) into aint` type column.

However, the limit for signed int is 2147483647.

I tried converting it to unsigned, but still the limit is 4294967295. Thus error "4294967295" something, but when I converted it to bigint, which has a limit of 9223372036854775807 (which is more for 10 digit mobile no), it started to accept it.

For the sake of mobile no I converted it to unsigned which increased its limit to 18446744073709551615.