Environment: Redhat Linux 2.6.32-754.12.1.el6.x86_64
Server version: 5.7.20-log MySQL Community Server (GPL)
I have the following error in mysql-error.log:
2020-01-07T17:38:34.065042Z 0 [Note] InnoDB: Last MySQL binlog file position 0 201350848, file name primary_bin_log.002450
2020-01-07T17:38:35.855483Z 0 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace harry/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17 at filepath: ./harry/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17.ibd uses space ID: 186487. Cannot open filepath: ./rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17.ibd which uses the same space ID.
2020-01-07T17:38:35.855528Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-01-07T17:38:35.855538Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-01-07T17:38:35.855562Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2020-01-07T17:38:35.855574Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17`. Please refer to
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2020-01-07T17:38:35.855593Z 0 [Warning] InnoDB: Ignoring tablespace `rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17` because it could not be opened.
I tried restart MySQL with innodb_force_recovery from 1 to 3, MySQL started fine, but I cannot fix the problem with the table.
mysql> show tables like 'serviceingressoctetslogrecordhourly';
+--------------------------------------------------------+
| Tables_in_rogers (serviceingressoctetslogrecordhourly) |
+--------------------------------------------------------+
| serviceingressoctetslogrecordhourly |
+--------------------------------------------------------+
1 row in set (0.02 sec)
mysql>
mysql> desc serviceingressoctetslogrecordhourly;
ERROR 1030 (HY000): Got error 122 from storage engine
mysql>
mysql> drop table serviceingressoctetslogrecordhourly;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Never mind the data, how do I get rid of the table from the database all together?