Can not drop schema in MySql workbench

Question

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.

See if this helps https://anothercoffee.net/fix-error-code-2013-lost-connection-mysql-server-query/ — Tarun Lalwani, Apr 09 '18 at 09:48
Please provide `SHOW CREATE TABLE` and `SHOW VARIABLES LIKE '%timeout%';` — Rick James, Apr 09 '18 at 13:26
This question (and the answer) are [discussed on Meta](https://meta.stackoverflow.com/questions/365792/question-answered-on-a-different-network) — Filnor, Apr 10 '18 at 07:58
@TarunLalwani you can use terminal to connect `mysql -u {username} -p{password} -h {remote server ip} {DB name}` and execute `Drop {tablename}` — chudasamachirag, Apr 16 '18 at 05:24

score 2 · Answer 1 · edited Apr 10 '18 at 09:48

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 !!!

Heider Sati · Answer 2 · 2019-09-16T10:17:07.317

I can't believe this question isn't answered yet...

To remove your schema, please do the following:

1) shutdown mysql (on my MacBook I use):

launchctl unload /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

2) Go into your DATA directory of your MySql, example:

cd /usr/local/mysql/data

3) The database over there is stored as a directory, just remove the dir with it's contents using rm -rf command:

rm -rf YourSchema

4) Start MySql, on the MacBook it's under:

launchctl load /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

5) Login to your GUI (or whatever ADMIN tool you have), and then refresh.

6) It's gone.

7) Now, when you restart MySql you'd get errors in the logs saying ".. not found etc..." these will not stop the server from starting, to fix those, using the MySQL Admin tool, create a Schema with exactly the same name as the old one and then DROP it, this resolves the issue.

8) Restart MySQL, check all logs, all should be fine.

I hope this helps.

score 0 · Answer 3 · answered Dec 29 '22 at 15:40

0

Inside your data folder ".../MySQL/.../Data", create a folder with the same name as the schema, then delete it from MySQL workbench again.

This fix should work on MySQL 8+. It may well work on previous versions, but I haven't tested it.

answered Dec 29 '22 at 15:40

Observer

755
1
14
23

Can not drop schema in MySql workbench

3 Answers3