Cannot delete database with tables missing

[simple99]

Over the weekend, one of our MySQL databases has suffered some errors and I'm trying to restore from backup but can't do it as the existing database is missing tables and won't let me remove the corrupt database.

The Windows Server 2003 event logs report the following:
Event ID: 100
Description:
Cannot find table 'database1/table_name' from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database?

Using PhPMyAdmin if I click the table names I get the following error "#1146 - Table 'database1.table_name' doesn't exist"

If I try to delete or drop the Database via PhPMyAdmin I get the error "#1051 - Unknown table..." giving a list of tables that it can't find.

I'm stuck here as I can even do restore from an earlier backup.

Hoping someone can help with this. Thanks

 

[guelphdad]

just create the table in your database. you can create it with a single column and have no data in it.

then drop your database.

 

[simple99]

thanks for the response.
There's about a 100 tables missing so I'm not sure how I can recreate them.

the Event ID: 100 talks about missing .frm files but I can see them in the database folder. Its like the reference to the .frm files is missing somewhere.

Appreciate any other ideas.
Thanks.

 

[simple99]

guelphdad, I tried creating the table but it says it already exists.

mysql> show create table wp_63_posts;
ERROR 1146 (42S02): Table 'wordpress.wp_63_posts' doesn't exist
mysql> create table wp_63_posts(i int) engine=innodb;
ERROR 1050 (42S01): Table 'wp_63_posts' already exists

So 'show create table' says table doesn't exist.
But 'create table' says it already exists.
I'm really confused ???

 

[ingresman]

it looks like you have a bit of corruption going on.
Is it desperate that the database retains the same name ?
If not you could edit the restore script to create and load into a differecnt database, then point your app at it.
Even if you can't use it for your application you will at leat have a clean (hopefully) copy of your DB to work on.

 

[simple99]

The database doesn't have to be the same name so I've tried to restore the mysql dump file to a different database but I think it keeps trying to restore to the original database.
Is this command correct,

mysql -u username -p password newDatabaseName < oldDatabasedump.sql

I got "ERROR 1051 (42S02) at line 28: Unknown table wp_100_comments"
(wp_100_comments, is one of the corrupt tables in the original database)

I edited the oldDatabasedump.sql file and change the database name in there to 'test', then used the command:
mysql -u username -p password test < oldDatabasedump.sql
I got "ERROR 1064 (42000) at line 29: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null)' at line 1"

I don't know what's wrong with the syntax in the mysql dump:
25 -- Definition of table `wp_100_comments`
26 --
27
28 DROP TABLE IF EXISTS `wp_100_comments`;
29 (null);
30

Hope you can spot whats wrong with line 29.

Thanks.

 

[simple99]

Ok, managed to fix this. I will list the steps I took for anyone else who gets this error.

1. Went into the database_name directory and deleted all the .frm files (or u can move them to a differen folder and delete later)
2. Checked with PHPmyAdmin that the database was showing as empty now.
3. Using commandline, I tried to restore from mysql dump file.
> mysql -u user -p database_name < database_name_dump.sql
Got error that database_name already exists.
4. Edited dump file to remove the line about 'Create database...'
5. Then ran the restore command again. This time got the error about syntax issue.
"ERROR 1064 (42000) at line 29: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null)' at line 1"
6. Edited dump file again to remove all lines which said '(null);'
7. Then ran the restore command, step 3, again. This time successfully restored database and got all the the tables back.

Thanks for your assistance.
regards

 

[ingresman]

glad you made it !