1

I had a corrupted database on my development environment that I could not access any more - probably due to a system crash. As I could not DROP the database (I can't remember why - doesnt matter any more now) I just deleted the databases folder in mysql/data.

This obviously was not a very good idea. After creating a new database with the same name, there are still some tablespace definitions somewhere in the database. When I try create new tables I get following error:

Tablespace for table 'database_name.table_name' exists. Please DISCARD the tablespace before IMPORT.

Is there a decent way to get rid of all references/indexes/tablespace information for the given database? When I try to DROP the newly created Database with the same name, I get: "ERROR 2013 (HY000): Lost connection to MySQL server during query".

Alex
  • 1,141
  • 1
  • 13
  • 24
  • http://stackoverflow.com/questions/2183645/mysql-database-corrupted – Nagaraj S Dec 27 '13 at 12:12
  • Thanks I tried several things but I want to delete the whole database, not just a single table and I am not interested in recovering any data either. – Alex Dec 27 '13 at 12:21

2 Answers2

0

when you get to a situation like that, the best thing to do is:

  1. backup your database
  2. delete entire database
  3. restore your database from backup
Marjeta
  • 1,111
  • 10
  • 26
  • I did delete the database but there were still tablespace entries aso. In the end I reinstalled the whole mysql server to fix it...what a waste of time though. – Alex Dec 28 '13 at 12:33
  • Yes, that happens. You shouldn't mess with MySQL files through the file system... I've learned that lesson the hard way, too :) – Marjeta Dec 30 '13 at 16:39
0

For each table run these two queries:

    ALTER TABLE tablename DISCARD TABLESPACE;
    ALTER TABLE tablename IMPORT TABLESPACE;

If there are too many tables just write a script to loop through them using SHOW TABLES and then the two queries above.

Heitor
  • 683
  • 2
  • 12
  • 26