MySQL can’t delete table

innodbMySQL

I have a corrupt table and I can't delete it to restore it. How do I delete this table from the database?

When I try to query on the table this is what I get…

mysql> SELECT * FROM tbl_company LIMIT 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: us_businessdb

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.soc            k' (111)
ERROR:
Can't connect to the server

Here is the error log;

2017-08-10T16:11:28.492290Z 4 [ERROR] InnoDB: Trying to access page number 3 in space 35, space name us_businessdb/tbl_company, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-08-10T16:11:28.492335Z 4 [ERROR] InnoDB: Server exits.
2017-08-10T16:11:28.724161Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-08-10T16:11:28.724259Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

So with force recovery set to innobd_force_recovery=6 this is what I get…

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| jnct_tbl_user           |
| tbl_company             |
| tbl_contact             |
| tbl_data                |
| tbl_details             |
| tbl_sic                 |
| tbl_user                |
| tbl_zipcode             |
+-------------------------+
8 rows in set (0.00 sec)

mysql> DROP TABLE tbl_company;
ERROR 1051 (42S02): Unknown table 'us_businessdb.tbl_company'
mysql> DROP VIEW  tbl_company;
ERROR 1347 (HY000): 'us_businessdb.tbl_company' is not VIEW
mysql> CREATE TABLE tbl_company ( id int(10));
ERROR 1050 (42S01): Table 'tbl_company' already exists

For the record every time I have tried to delete this table I have:

  • SET_FOREIGN_KEY_CHECK=0 before I attempted to delete the table.
  • still received this same error every time I have tried to drop table (so no partial deletes that I am aware of.
  • In force recovery I can still query other tables

I just want to drop this table and reload it with the original data that I dumped originally.

What do I do to fix this? The only thing I know to do is delete the file manually but then I get errors when MySQL loads and I'm sure there has to be a better way to do this.

And also for the record, I have recreated this database several times and this same table which is the main table keeps corrupting (which is another issue in itself that I will reserve for another question). Since it's a clean MySQL Server install on Linux, previously I just reinstalled MySQL server to get rid of the error on MySQL startup for missing files where I deleted the db file manually, but this is a pain and I'm sure there is a better way.

EDIT:
This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.

Best Answer

That table, us_businessdb.tbl_company may have a broken data dictionary entry. Back in June/July of 2015, I answered 3 questions about a similar situation (where mysqld crashes accessing the table data). My condolences on this table's detachment.

If you have a previous mysqldump of that table's data, you could load it into another database. Then, transfer all the good tables into that other database.

Your current database with the corrupt table is called us_businessdb. Create a database with a similar name (us_business_db) and move the good stuff into it.

CREATE DATABASE us_business_db;
ALTER TABLE us_businessdb.jnct_tbl_user RENAME us_business_db.jnct_tbl_user;
ALTER TABLE us_businessdb.tbl_contact   RENAME us_business_db.tbl_contact;
ALTER TABLE us_businessdb.tbl_data      RENAME us_business_db.tbl_data;
ALTER TABLE us_businessdb.tbl_details   RENAME us_business_db.tbl_details;
ALTER TABLE us_businessdb.tbl_sic       RENAME us_business_db.tbl_sic;
ALTER TABLE us_businessdb.tbl_user      RENAME us_business_db.tbl_user;
ALTER TABLE us_businessdb.tbl_zipcode   RENAME us_business_db.tbl_zipcode;

OK, NOW FOR THE GORY STUFF ...

You mentioned in your question

EDIT: This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.

When you attempted to update too much data in a single UPDATE query and you ran into a table is full error, you did not run out of space on your HD. What ran out of space is the undo log inside ibdata1. I have discussed this back on Jun 16, 2014 (MySQL Index creation failing on table is full) where I personally worked with a 2TB ibdata1/ibdata2 and someone's query failed with that error even with 106GB of unused space in the ibdata files. That unused space is used for undo logs (128 of them).

I discussed this situation even further back:

I cannot definitively say that the "Table is Full" broke your table or not. I just wanted to clarify with that error meant.

OK, NOW FOR THE GORIER STUFF (is gorier a word ?)...

Here is something you will find interesting. Run the following:

USE us_businessdb
SHOW TABLES;

You might see this:

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| tbl_company             |
+-------------------------+

Did you ever wonder why mysqld did not crash when doing SHOW TABLES; ?

Doing SHOW TABLES; simply passes through the data folder looking for .frm files.

Running this query

SELECT table_name Tables_in_us_businessdb.
FROM information_schema.tables
WHERE table_schema='us_businessdb';

does the exact same thing. In both cases, it looks for tbl_company.frm.

If you attempt to access the metadata of tbl_company.ibd, by doing

SELECT data_length,index_length,create_time,update_time
FROM information_schema.tables
WHERE table_schema='us_businessdb';

or trying to access the actual data with

SELECT * FROM tbl_company LIMIT 10;

then, mysqld blows up and crashes.

OK, ENOUGH OF THE GORY STUFF. WHAT NOW ???

If you have a mysqldump of that tbl_company table, you could load it into the new database we created (us_business_db).

If you have the original CREATE TABLE tbl_company command, you can execute it in the new database. I will leave the details of reloading it s data to you.

If you do not have the original CREATE TABLE tbl_company command, you are going to need to do is get the table structure from the tbl_company.frm file. How ???

Run this query: SELECT @@global.datadir;

Let's say, for this example, that datadir is /var/lib/mysql

Go to the OS and go to the database folder with the tbl_company.frm

cd /var/lib/mysql/us_businessdb
ls -l

You should see, the tbl_company.frm and tbl_company.ibd files. Copy the .frm to another folder or another machine.

Back on Jun 11, 2013 I answered the question How can extract the table schema from just the .frm file?. In that post, I wrote about something I saw in Chuck's Blog regarding a mysqlfrm utility. I tried it out and it worked. You will have to use it to recreate the empty table in the new database.

Once you have done, you can drop the old database, create the old database back, move the tables back into the old database.

DROP DATABASE us_businessdb;
CREATE DATABASE us_businessdb;
ALTER TABLE us_business_db.jnct_tbl_user RENAME us_businessdb.jnct_tbl_user;
ALTER TABLE us_business_db.tbl_contact   RENAME us_businessdb.tbl_contact;
ALTER TABLE us_business_db.tbl_data      RENAME us_businessdb.tbl_data;
ALTER TABLE us_business_db.tbl_details   RENAME us_businessdb.tbl_details;
ALTER TABLE us_business_db.tbl_sic       RENAME us_businessdb.tbl_sic;
ALTER TABLE us_business_db.tbl_user      RENAME us_businessdb.tbl_user;
ALTER TABLE us_business_db.tbl_zipcode   RENAME us_businessdb.tbl_zipcode;
ALTER TABLE us_business_db.tbl_comnpany  RENAME us_businessdb.tbl_comnpany;

EPILOGUE

Sorry about the corrupt .ibd file. You must see to that. Yet, these steps will help clean up the data dictionary and restore the us_businessdb to some sense of normalcy.

Related Question