MySQL5.7无法重建表

一、数据库环境：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+

参数：innodb_file_per_table=ON

二、数据库启动时报错信息： 

2024-01-19T09:15:57.352382+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2024-01-19T09:15:57.352455+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2024-01-19T09:15:57.352464+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2024-01-19T09:15:57.352471+08:00 0 [ERROR] InnoDB: Cannot open datafile for read-only: './test/ttest01.ibd' OS error: 71 2024-01-19T09:15:57.352478+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2024-01-19T09:15:57.352483+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2024-01-19T09:15:57.352488+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2024-01-19T09:15:57.352496+08:00 0 [ERROR] InnoDB: Could not find a valid tablespace file for `test/ttest01`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2024-01-19T09:15:57.352508+08:00 0 [Warning] InnoDB: Ignoring tablespace `test/ttest01` because it could not be opened.

三、查看ttest01表，已不存在

mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t_range        |
| ti             |
| us             |
| usertb3        |
+----------------+
5 rows in set (0.00 sec)

四、创建ttest01表失败，报表已存在的错误

mysql> CREATE TABLE `ttest01` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(255) DEFAULT NULL,
       `creat_date` datetime DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1050 (42S01): Table '`test`.`ttest01`' already exists

mysql> drop table ttest01;
ERROR 1051 (42S02): Unknown table 'test.ttest01'

五、解决办法

5.1> 关闭数据库，修改my.cnf文件：

mysql> shutdown;
临时修改my.cnf文件：
innodb_file_per_table=0

5.2> 创建临时库并创建ttest01表：

mysql> create database test01;

mysql> use test01;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `ttest01` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(255) DEFAULT NULL,
       `creat_date` datetime DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (3.61 sec)

5.3> 关闭数据库，拷贝表文件：

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
mysql@master:~> cd /data/mysql/data/test01

/data/mysql/data/test01> cp ttest01.frm ../test/

5.4> 启动数据库，删除test库中的ttest01表：

mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t_range        |
| ti             |
| ttest01        |
| us             |
| usertb3        |
+----------------+
6 rows in set (0.00 sec)

ttest01表已经存在了，执行删除ttest01表：

mysql> drop table ttest01 ;
Query OK, 0 rows affected (3.60 sec)

5.5> 关闭数据库，改回参数：

修改my.cnf文件：
innodb_file_per_table=1