This can be so dang frustrating. If you have an auto_increment id and you delete the highest row or rows, the auto_increment number isn't decremented to reflect this. Here is how you can fix the problem:
ALTER TABLE tbl_name AUTO_INCREMENT = 1;
This will reset the auto_increment value to the next highest id available. Cool!
This is not smart (understatement of the millenium)
The auto_increment values are for internal use only.
You do not care what the value is, and if you do, you're not getting the point of auto_increment values.
Auto_increment is NOT a rownumber, it NEVER EVER uses the same value twice. This is what makes it usefull, you can depend on it to never give a duplicate value.
If you reset the auto_increment value, you risk creating a record that uses the same value as a record you previously deleted by accident, without deleting all the records that were linked to it)
That means that there may still be records in other tables that are linked to that value. So creating a new record that re-uses an old value can instantly f*ck up your entire database.
do NOT EVER try to outsmart the database, because you can't. Let the database take care of auto_increment sequences, and keep your hands off of them. (unless you like restoring data from backup tapes)
A forum, a FAQ, what else do you need?
Well, that's fine. You are probably right ... good for you.
BUT, I believe the question was: Is there any way at all to reset the auto_increment?
AND, the answer is YES. Regardless of whether you believe there is no reason to EVER do this, the fact is that it CAN be done without deleting all of the rows and without using myisamchk. This was the gist of my resonse.
I apologize I somehow offended the mySQL wizards among us...
Your answer was spot in, no question about it, 100% correct. But the question was wrong, and the answer should reflect that.
If someone asks: How can I jump off a tall building?
The answer would be:"go to the roof and walk in a random direction untill you feel a rapid acceleration in the direction of the ground".
But I feel it's quite important to note that if you do jump off a high building you will end up very very dead.
The same goes here. Yes you can reset the auto_increment value, but it's something that you never do because it can quickly destroy all the data in your database.
I tend to be a little agressive in my replies to these answers, because I've learnt that a simple "Be carefull" doesn't work. Poeple just go "oh yeah, I'll be carefull." and proceed with "format C: /q".
Shouting:"this will kill you" has more effect.
No offence to you, your answer was correct, just dangerous. very dangerous.
See, there are no wrong questions, they say. There are only wrong answers - like yours... It didn't bring anything contructive to the subject. The question could not be "wrong" itself, it was 100% correct. And YES, it is good to perform a reset of auto_increment counter - in some situations ofcourse. I know, you'll say that if one runs into such a situation then... and whatever.
But that's not the matter here, if someone WANTS to format his hdd, and if he KNOWS what he is doing... well, that's his right to do it, isn't it.
I made a use of the solution I have been given here (thanks!), and I haven't destroyed anything. Try to assume that people can think, maybe.
EOT.
sigh here we go again...
"There are only wrong answers - like yours"
Care to run that by me again? Someone picks up a gun and asks you how to pull the trigger and the reply "carefull, you might kill yourself" is wrong?
"It didn't bring anything contructive to the subject."
You'd rather not know about any risks of resetting the auto increment then...
"And YES, it is good to perform a reset of auto_increment counter - in some situations ofcourse."
Oh really? can you give me an example where it is good to reset the auto_increment while there is still data in the table?
Resetting the auto_increment is NEVER good. It simple isn't. If you think it is, then you are either completely missing the point of auto_increment, or you don't care about consistency at all.
"I know, you'll say that if one runs into such a situation then... and whatever. "
yeah, whatever... best not to know about what can go wrong. What you don't know won't hurt you right?
"if someone WANTS to format his hdd, and if he KNOWS what he is doing... well, that's his right to do it, isn't it."
Sure, IF HE KNOWS WHAT HE IS DOING.
Point is, you don't know what you are doing. If you did, you'd know that what you're doing is a very bad idea.
I warn you against that, but apparently you don't need other peoples advice, you know what you're doing. That's probably why you didn't know how to do it.
"I made a use of the solution I have been given here (thanks!), and I haven't destroyed anything."
... not yet. Maybe nothing will happen and you'll live happily ever after, maybe tomorrow none of your data makes sense anymore.. ah, who cares, it's only data...
"Try to assume that people can think, maybe"
You know as well as I do that most people DONT think. I've been around, I've seen people f*ck up their database because 'they knew what they were doing', while back in the real world, they were clueless.
I guess ignorance really is bliss...
PS: love the EOT, almost makes me feel guilty for replying.
you guys are wasting everyone's time and space. You both care about spreading knowledge. One the best way to do something and the dangers of not using best practices. The other in providing the fastest solution to the question. I think you are both great and I have learned from both of you. Now i know how in case of emergency to roll back my auto-increments and also why i should be very careful about doing so. So bravo to everyone...problem solved...let's move on and help the each other solve the next one...
You're right!
Just a note to anyone who happens to read this from a search, as I did. Resetting the AUTO_INCREMENT is fine, regardless of what any of my colleagues are saying here.
ALTER TABLE table_name AUTO_INCREMENT = 1 allows the database to reset the AUTO_INCREMENT to:
MAX(auto_increment_column)+1
It does not reset it to 1.
This prevents any duplication of AUTO_INCREMENT values. Also, since AUTO_INCREMENT values are either primary/unique, duplication would never happen anyway. The method to do this is available for a reason. It will not alter any database records; simply the internal counter so that it points to the max value available. As stated earlier by someone, don't try to outsmart the database... just let it handle it. It handles the resetting of AUTO_INCREMENT very well.
Actually, I did come across this by search, and for anyone else wading through this, I should advise you to beware of many of the comments, such as the previous, because AUTO_INCREMENT fields can be secondary, in which case you can actually have duplicate auto increment values. The intent is that the "composite" index that the AUTO_INCREMENT sits in be unique. See the mySQL CREATE TABLE section for further clarification.
Csaba GaborOften times a developer while creating an application will generate a large number of rows in a database through the process of testing and debugging, causing the table counters to increment accordingly. This is perfectly normal, but at the end, the test data is purged and the database is handed back over to the client with counter is still at a higher number.
In cases such as inventory management or invoice record keeping, it may sometimes be necessary to set the counter to a very specific value upon launch so as to keep new data in sequence with the old, even by starting a high number in a table without any data rows in it. In cases such as this, NOT being able to set the counter would be a pretty big problem, since clients can be very particular when accounting is involved.
Not sure it's a good idea to revive a thread which has been dead for 9 years...
But, since you did...
brianberneker;10988701 wrote:the test data is purged and the (...) counter is still at a higher number.
which it will no longer be if you TRUNCATE the tables or drop the schema and recreate it from scratch.
brianberneker;10988701 wrote:In cases such as inventory management or invoice record keeping (...) NOT being able to set the counter
But OP was not talking about SETting the auto increment value, he was talking about REsetting it. That is, he wanted to tell the DB to ignore all previously inserted data and reuse some previously used number, rather than simply initializing it.