Can not drop schema in MySql workbench

I have a database in MySQL Workbench. Usually I can drop table or schema in my database. But when I try to drop schema or table in database, it doesn't work because of:

Lost connection to MySQL server during query

I tried to change DBMS connection read timeout in preference but still I can't drop this schema.

1 Answers

Answers 1

I am only answering this question because I cannot flag it since it has an open bounty.

This question has been answered on the database administrators stack exchange. Please keep in mind database is synonymous with schema.

I am afraid you cannot drop the database.

The problem comes the table that is corrupt. It's not necessarily the .ibd file. It is, more than likely, the data dictionary entry within ibdata1 (See pictorial representation of ibdata1).

I wrote about this back on Mar 02, 2012 : https://dba.stackexchange.com/questions/14259/innodb-table-select-returns-error-2006-hy000-mysql-server-has-gone-away-after/14283#14283

Although tedious, here is what you can do to save the other tables in that corrupt database bad_db:

STEP 01) USE bad_db

STEP 02) Run SELECT COUNT(1) FROM tablename; for every table in bad_db. Collect all table names that do not crash mysqld when you attempt to run the count.

STEP 03) Run mysqldump -u... -p... bad_db table1 table2 ... tableN > bad_db_goodtables.sql for all the tables that did not crash mysqld.

If you are going to use a different database name, then reload bad_db_goodtables.sql into that new database.

If you need the database to be the same name, BAD NEWS !!! You will have overhaul ibdata1. There are more steps:

STEP 04) mysqldump all the other databases into separate dump files

STEP 05) Perform InnoDB Infrastructure Cleanup (See my Oct 29, 2010 StackOverflow Post)

STEP 06) Reload all the other mysqldumps good databases.

Give it a Try !!!

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati