Re: reset auto_increment

[Paul DuBois]

At 16:06 -0500 1/10/03, "INVALID - TESTING ONLY.  IF YOU GET EMAIL 
WITH THIS ACCOUNT P wrote:
>I need to reset the auto_increment in a table full of data.  I had a problem
>recently where some script was putting InvoiceID numbers into an
>auto_increment CustID column...I since fixed the problem and corrected the
>data but I now have a huge gap in my number sequence and I cant get
>auto_increment reset to a more reasonable value.
>
>
>
>Now I have read the archives and the online manual with user comments and
>nothing has worked as of yet.

What is your table type?

>
>
>
>ALTER TABLE tbl_name AUTO_INCREMENT = 1

Should work.

>
>this command gives me a successful response but when I do a SHOW TABLE
>STATUS the Auto_increment is still unchanged.

What value does it show, and what is the maximum value still in your
table?  If you expect this to set the next value *lower than the current
maximum value in the table*, it won't.  That's not how it works.

>
>
>
>myisamchk -A=1 /path/to/db.MYI

-A=1 is invalid syntax.  Should be -A1 (no space) or
--set-auto-increment=1


>
>this command also gives me a successful message but never changes the
>auto_increment when displayed by SHOW TABLE STATUS
>
>
>
>in section 3.5.9 of the MySQL manual with user comments I noted the
>following comment on Oct 23 20002 by Ethan Pooley
>
>http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html
>
>He stated that the ALTER TABLE tbl_name AUTO_INCREMENT = 1 will only work
>when the table is empty.

That's incorrect.

>
>
>So what I need is the ability to reset the auto_increment without having to
>empty the table first.  Or I need someone to help me figure out why the
>above mentioned commands fail to do what everyone tells me they are suppose
>to do.
>
>
>
>-Jason