Help! Mysql auto_increment added 3 million rows after crash

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Help! Mysql auto_increment added 3 million rows after crash

Postby Flo1 » 28. July 2008 05:33

Help, please! Suggestions please on how to clean this up. I have a test database I am using to develop a php site and my computer crashed. Now I notice 3 million rows in a table for one item with auto_increment id numbers that somehow was added, perhaps because of the crash, don't know for sure. How to get rid of them, they are cluttering my table and causing ridiculous # of pages to scroll through? Without deleting the table and reconstructing it, is there any way to eliminate these 3 thousand entries and reset the auto_increment #s back? If so, what would the MySql command look like for this?

I am using PHPMyAdmin to manage my database.
PHP5.04, Mysql 4.1.13, Apach 2.0 on Windows XP
Posts: 14
Joined: 19. May 2005 22:30

Postby Dave_L » 28. July 2008 09:54

Back up that database first, in case the following fails.

Then use the function to check the tables, in case the crash damaged it. If the table status is not "OK", repeat the check. If it's still not "OK", try the repair function.

To delete the excess rows:

DELETE FROM table_name WHERE column_name > value;

The auto-increment value for the table can be changed from the Operations tab for the table. Or you can use the query:

ALTER TABLE table_name AUTO_INCREMENT = value;
User avatar
Posts: 212
Joined: 23. October 2004 00:43

Thank you - auto_increment is reset

Postby Flo1 » 28. July 2008 14:14

Thank you Dave, all is now good. I followed your clear and easy solution and the auto-increment values have now reset back to where they should be!

Great help, I have learned something new that will come in handy in the future.
Posts: 14
Joined: 19. May 2005 22:30

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 59 guests