MySQL数据库frm文件丢失会触发哪些错误？

相关错误号：

Last_Errno: 1813
Last_Error: Error 'Tablespace '`cjc`.`t1`' exists.' on query. Default database: 'cjc'. Query: 'create table t1(id int)'
Last_Errno: 1051
Last_Error: Error 'Unknown table 'cjc.t1'' on query. Default database: 'cjc'. Query: 'DROP TABLE `t1` /* generated by server */'
ERROR 1030 (HY000): Got error 168 from storage engine
ERROR 1813 (HY000): Tablespace '`cjc`.`t1`' exists.
ERROR 1051 (42S02): Unknown table 'cjc.t1'

场景一：主库t1表丢失t1.frm文件

MySQL主从架构，主库t1表缺失t1.frm文件，主库执行：

DROP TABLE IF EXISTS `t1`;

从库自动删除t1表。

主库重新创建t1表时，报错如下：

mysql> create table t1(id int);
ERROR 1813 (HY000): Tablespace '`cjc`.`t1`' exists.

也无法删除：

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'cjc.t1'

如果删除t1.ibd，是否可以重新创建表呢？

mv t1.ibd t1.ibd.bak

还是无法创建和删除同名表

mysql> use cjc;

无法创建

mysql> create table t1(id int);
ERROR 1030 (HY000): Got error 168 from storage engine

对应error.log日志如下

2023-12-28T15:07:31.703109+08:00 10 [ERROR] InnoDB: Cannot create file './cjc/t1.ibd'
2023-12-28T15:07:31.703117+08:00 10 [ERROR] InnoDB: The file './cjc/t1.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './cjc/t1.ibd' under the 'datadir' of MySQL.
2023-12-28T15:09:11.861582+08:00 11 [ERROR] InnoDB: Operating system error number 17 in a file operation.
2023-12-28T15:09:11.861608+08:00 11 [ERROR] InnoDB: Error number 17 means 'File exists'
2023-12-28T15:09:11.861615+08:00 11 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2023-12-28T15:09:11.861622+08:00 11 [ERROR] InnoDB: Cannot create file './cjc/t1.ibd'
2023-12-28T15:09:11.861631+08:00 11 [ERROR] InnoDB: The file './cjc/t1.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './cjc/t1.ibd' under the 'datadir' of MySQL.
2023-12-28T15:09:43.257378+08:00 12 [Warning] InnoDB: Tablespace 'cjc/t1' exists in the cache with id 401 != 404

生产环境，谨慎操作，手动移动ibd文件，还会导致数据库无法启动。

重现过程：

配置主从

CHANGE MASTER TO
MASTER_HOST='192.168.0.10',
MASTER_USER='xxx',
MASTER_PASSWORD='******',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;

start slave;

创建测试数据:

create database cjc;
use cjc;
create table t1(id int);
create table t2(name varchar(10));
insert into t1 values(1),(2);
insert into t2 values('a'),('b');

主库：

mv t1.frm t1.frm.bak

删除表：

DROP TABLE IF EXISTS `t1`;

从库自动删除t1;

主库，创建表失败

mysql> create table t1(id int);
ERROR 1813 (HY000): Tablespace '`cjc`.`t1`' exists.

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'cjc.t1'

解决方案：

主库：关闭sql_log_bin

mysql> set sql_log_bin=OFF;

拷贝任一一表frm文件；

cp t2.frm t1.frm

主库删除，可以正常执行

mysql> drop table t1;

打开sql_log_bin

mysql> set sql_log_bin=ON;

可以正常创建表

mysql> create table t1(id int);

从库可以正常同步。

场景二：备库t1表丢失t1.frm文件

重命名备库 t1.frm 文件

mysql@CJC-DB-001:/mysqldata/3306/data/cjc$ls -lrth
total 124K
-rw-r----- 1 mysql mysql 67 Jan 13 15:51 db.opt
-rw-r----- 1 mysql mysql 8.4K Jan 13 15:51 t1.frm
-rw-r----- 1 mysql mysql 96K Jan 13 15:51 t1.ibd
-rw-r----- 1 mysql mysql 8.4K Jan 13 15:51 t2.frm
-rw-r----- 1 mysql mysql 96K Jan 13 15:51 t2.ibd

重命名

mysql@CJC-DB-001:/mysqldata/3306/data/cjc$mv t1.frm t1.frm.bak

备库，还可以继续查询表

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

分析表

mysql> analyze table t1;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| cjc.t1 | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

无法查询

mysql> select * from t1;
ERROR 1146 (42S02): Table 'cjc.t1' doesn't exist

mysql> show tables;
+---------------+
| Tables_in_cjc |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)

主库删除 t1 表

mysql> drop table t1;

备库 

同步报错

show slave status\G;
Replicate_Wild_Ignore_Table:
Last_Errno: 1051
Last_Error: Error 'Unknown table 'cjc.t1'' on query. Default database: 'cjc'. Query: 'DROP TABLE `t1` * generated by server */'
Skip_Counter: 0
Exec_Master_Log_Pos: 1258
Relay_Log_Space: 1856
Until_Condition: None

如果将drop table t1; 改成 DROP TABLE IF EXISTS `t1`; 同步不会报错：

主库：

DROP TABLE IF EXISTS `t1`;

备库同步没有报错：

show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

同理，创建表同步也会报错：

主库：

create table t1(id int);

备库

同步报错

show slave status\G;
Last_Errno: 1813
Last_Error: Error 'Tablespace '`cjc`.`t1`' exists.' on query. Default database: 'cjc'. Query: 'create table t1(id int)'
Skip_Counter: 0

备库，无法删除表

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'cjc.t1'

无法创建表

mysql> create table t1(id int);
ERROR 1813 (HY000): Tablespace '`cjc`.`t1`' exists.

拷贝其他表结构文件

mysql@CJC-DB-001:/mysqldata/3306/data/cjc$cp t2.frm t1.frm

mysql> use cjc;


mysql> show tables;
+---------------+
| Tables_in_cjc |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)

不能正常使用表

mysql> select * from t1;
+------+
| name |
+------+
| |
| |
+------+
2 rows in set (0.00 sec)

可以正常删除

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

思考1：如何查询缺失frm的表?

检查 ibd 比 frm多的表

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME NOT IN
(SELECT CONCAT(TABLE_SCHEMA,'/',TABLE_NAME) NAME FROM INFORMATION_SCHEMA.TABLES);
+-----------------------+
| NAME |
+-----------------------+
| cjc/t1 |
+-----------------------+
1 rows in set (1.23 sec)

思考2：创建一张表，会在哪些系统表插入数据？

删除t1.frm文件后，INFORMATION_SCHEMA.TABLES表数据自动消失

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES where TABLE_NAME='t1' and TABLE_SCHEMA='cjc';
Empty set (0.00 sec)

但是INFORMATION_SCHEMA.INNODB_SYS_TABLES、INNODB_SYS_DATAFILES、FILES、INNODB_SYS_TABLESPACES等表数据还在，所以不要通过操作系统命令mv,rm等操作frm,ibd等物理文件。

select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES where name='cjc/t1';
+-------+--------------+
| SPACE | PATH |
+-------+--------------+
| 1779 | ./cjc/t1.ibd |
+-------+--------------+
1 row in set (0.00 sec)




SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH='./cjc/t1.ibd';
+-------+--------------+
| SPACE | PATH |
+-------+--------------+
| 1779 | ./cjc/t1.ibd |
+-------+--------------+
1 row in set (0.00 sec)




SELECT FILE_ID,FILE_NAME,FILE_TYPE,TABLESPACE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME='./cjc/t1.ibd';
+---------+--------------+------------+----------------------------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME |
+---------+--------------+------------+----------------------------+
| 1779 | ./cjc/t1.ibd | TABLESPACE | innodb_file_per_table_1779 |
+---------+--------------+------------+----------------------------+
1 row in set (0.00 sec)




SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='cjc/t1';
+-------+--------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+--------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 1779 | cjc/t1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 49152 |
+-------+--------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.00 sec)

###chenjuchao 20240113###