Problem: auto_increment has stopped increasing!

Hey. I have a problem with my 'id' field - it's set to auto_increment and has been working fine for a few weeks. However, I've now got up to row 127, and as it's a unique field it won't insert - phpMyAdmin tells me that the next autoindex is 127.

Nothing was changed in the table other than the entries...it seems like a total mystery to me. I've tried every which way round I can other than deleting the column and re-inserting it (not a good idea, I'd have to go through 127 rows afterwards and put the IDs back in, with no guarantee that it'll fix the problem).

Obviously, if there's no solution to the problem I'll just have to set up a work-around with last_insert_id(), but I really don't see why it's not working! Anyone got any ideas?

Can I see the SQL query which you use to insert data into the table? Mystery stuff!

What data type is the auto_increment field? If it stopped at 127, I'm betting it only has 8 bits and is signed.

Thanks for the replies, guys (or girls).

It's tinyint, length 5 and it's a primary key.

I've tried using my query (see below) and doing it through phpMyAdmin...neither seems to work. If I do it through phpMyAdmin it tells me there's a duplicate entry with id 127 - it seems to have stuck at 127?!

Query:
insert into pw2_releases (title, image_location, category, id, description, user, date) values ('$release_title', '$image_location', '$category', '', '$description', '$user', '$date')

Using a tinyint is your problem. From http://www.mysql.com/doc/en/Column_types.html:


Change it to something like an unsigned int instead.

Thankyou! You really are 'A Master Codewalker'! Although I might just be too much of a n00b ;)