mysql table stopped incrementing

[RanZo]

One of my tables on my forum http://banana*********.net/forum seams to have stopped auto incrementing.
I have been making some minor changes to the code.
My site was also targeted my someone who changed string in the browser to show debateID=0 from a number that was coresponding to the debate. So he put some text in to the database, something silly I dont think it meaters. I have erased some rows from a table that were script injected, I think directly in phpmysql, and other texts I erased in the forum page.
Now one of my tables has stopped incrementing. So no user can post any text to it.
When I echo the $sql from mysql_query($sql), and try it manualy in phpmyadmin it gives out message that :" #1062 - Duplicate entry '127' for key 'PRIMARY'".
I have tried deleting text with that key and I could insert one text after that.
But on the next try it gives the same message.

So I conclude that the auto increment key hung up on 127.
Although when I check in the phpmyadmin in the operations it shows auto_increment 128.
I also tried the alter table 'someTable' auto_increment =0.
I am struggling all day long with this. Pleas help.

[tirna]

Maybe your table has somehow been sql injected with additional rows containing primary key column values higher than your current auto-increment value and hence the duplicate entry error messages.

To kick start your auto-increment again maybe try the following.

Get the current maximum value in your auto-increment column by:

select max(incrementColumn) from tableName;

and then reset the auto-increment to max. value + 1

alter table tableName auto_increment = newValue;

Hopefully this will now allow new records to be inserted into tableName

If this works, then you would need to go and clean up and delete any rubbish records in the table.

I assume you don't have a backup of your database, because the obvuous solution would be to restore the latest backup. Maybe this can be food for thought.

[BIOSTALL]

Sounds like your auto increment field has a datatype of 'tinyint' which only goes up to 127 (or 255 if unsigned). Change this to a larger datatype such as 'int' and that should solve it.

Took me ages to figure this out when it happened to me :-P

Hope that helps,

BIOSTALL