Problem with autoincrement

[baggins2000]

I have a table where the autoincrement has reached 27515329. Whenever I try to insert another record I get an error stating
ERROR 1062: Duplicate entry '27515329'
When I try to Select * from Table where ID = 275153239;
I get
ERROR 1030: Got error 127 from table handler
What's going on here? Any ideas on what I should do?

 

[simanek]

well, it appears that the datatype of your autoincrement column has been maxed out. Depending on what type it is, there is a limited number that it can represent. Go to your mysql command line and do a SHOW COLUMNS FROM table
and post here what the data type of your ID is. Mike
~~~~
simanek@uiuc.edu
"It's a Swingline!"
~~~~

 

[BigBrother]

Is the number 275153239 or is it 2147483647? This is the upper bound for the INT data type in mySQL - you are using mySQL right? If you convert the number to UNSIGNED INT, it will double it's capacity to 4292467295.

Also, if you need domething more substantial, check out BIGINT (range: -9223372036854775808 to 9223372036854775807) or UNSIGNED BIGINT (0 to 18446744073709551615).

 

[baggins2000]

I have deleted my Primary Key column with the intention of adding it back to renumber the sequence. When I enter
AlterTable TheTable ADD TheNum INT(15) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY;

I get the error
ERROR 1030: Got error 27 from table handler

 

[rhodesiascout]

I have the same problem with baggins.Let me explain.

I have a table with data in it . One of my columns is called "id" and its auto increment. However because i have deleted a lot of rows from the table , my id column has values like this : 1, 2, 3, 5, 11, 14, etc.
How do i reset " id" so it can begin at 1 again?

Is it possible to do this?

many thanks.

 

[thumbelina]

I think to reset the auto_increment values you have to either drop the coloumn off the table, or maybe drop the table itself altogether. I'm not sure how the table will react to you adding a primary key like that, since a primary key can't have a null value. Why do you need to reset the vaules anyway? wouldn't it be better to let it keep counting?

 

[rhodesiascout]

Hey thumbelina how ya doin'

Well the reason i wanted to reset was that i had a id number that went from 1 to 2000. (That was how many entries i had in my database.) One day i wanted to test something out and i inserted a new entry in my db with a id number of 5000. I put that in so i could easily remember it to delete it later when i was done with it. Anyway when i deleted it and tried to insert a new entry now,it gave me a id of 5001 instead of 2001. It had remembered that my last id was 5001 and it carried on from there.

So i have a db with id entries from 1-2000 and 5001-5125. It looked kinda stupid.

But i managed to fix it.
You are right though. If you drop the id collumn you cant make it again. It wont let you.
So what i did was use excell to resort the numbers.It was a pretty good trick if i say so myself.