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=0before 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.