Reset the AUTO_INCEMENT with myisamchk??

[pegasus]

According to the MySQL manual, the auto_increment can be forced to a number by using "myisamchk --set-auto-increment=1 /path-to/table.MYI".

I've tried to reset the number to 1, and linux also responsed the message "Updating MyISAM file: /path-to/table.MYI".
It looks like successful, but while I select the index from the table, the Auto_increment still keep at the same old number.

Is there somebody reset the index successfully? or how to do that? since drop then add index again won't work.
Thanks.

[trellie]

you can empty the table...
(DELETE FROM table)
wich will also reset an auto-increment field.

[[deleted]]

Did you try that on an empty table?
It will not work on a filled table, because that would be stupid.

[trellie]

well... you wouldn't want to reset the auto_increment on a filled table now would ya...

[[deleted]]

You, me and the rest of the people with a brain wouldn't want to do that, but there have been plenty of questons like ' I have deleted a record and now I am missing a number in the sequence, how can I renumber the auto_increment field' so I'm just clarifying.

[Anon]

Well in my case, I dont want to "reset" it to 0, but to move it to a higher number so it can be used for the "Invoice Number" and take off where my previous system left off...

I am working on a new EMPTY table.

I use

myisamchk -A=200 invoices.MYI

But auto_increment  still gets "1" as the first record ID..

Any ideas.

[Anon]

"set insert_id=n"

will set the current insertion id (temporarily unfortunately, because when you exit and enter the counting starts from the number that was before you ran the command)

O D