MySQL查询表显示“doesn‘t exist“错误？不要惊慌，让我带你揭开解决之谜！

背景

      上周有朋友反馈线上Mysql5.7数据库查询 audit_log表出现doesn't exist，查询不到任何数据，其他表都正常。他也不想删库重做，这种问题要怎么修复？

问题分析

      查询audit_log表出现doesn't exist现象，一般都是数据损坏。首先排查了mysql的数据目录，发现audit_log表的frm文件没有了。接下来做了数据修复REPAIR TABLE操作，发现不起作用，最后只能使用处理表空间的操作解决此问题了。为了模拟该问题，我分为两个维度分进行模拟，一种是丢失frm文件，另外一种是将当前实例的frm、ibd文件全部废弃，导入从备份文件恢复后的frm、ibd文件，具体操作如下：

表修复：

REPAIR TABLE audit_log；

frm丢失环境模拟：

当前audit_log表查询一切正常，接下来手动删除frm文件

手动删除frm文件

查询开始报doesn't exist

场景一：恢复frm文件

1、创建一个新库，创建一个新的audit_log表，同故障表表结构要一致，执行如下操作:

mysql> create database vss_tmp;
Query OK, 1 row affected (0.00 sec)
mysql> use vss_tmp
Database changed
mysql> CREATE TABLE `audit_log`  (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `user_id` int(11) NOT NULL,
    ->   `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    ->   `user_display` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    ->   `action` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    ->   `extra_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    ->   `action_time` datetime(6) NOT NULL,
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE = InnoDB AUTO_INCREMENT = 211 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
Query OK, 0 rows affected (0.01 sec)

2、将新建的vss_tmp库下的表结构拷贝到故障库vss中，操作步骤如下：

这些操作通常需要在数据库处于只读模式下执行，以确保数据的一致性和完整性。

1）拷贝前先执行ALTER TABLE ... DISCARD TABLESPACE：由于frm已丢失，执行报错。

用途：将表的表空间丢弃，表数据仍然存在，但是表空间文件被标记为不可用。这个命令通常用于在备份或迁移数据库时，临时移除表的表空间，以便在另一个MySQL实例中进行恢复或导入。

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




mysql> select * from audit_log;
ERROR 1146 (42S02): Table 'vss.audit_log' doesn't exist
mysql> alter table audit_log discard tablespace;
ERROR 1146 (42S02): Table 'vss.audit_log' doesn't exist

2）执行cp操作，要记得授权.

bash-4.2# cd /var/lib/mysql/vss/
bash-4.2# ls
audit_log.ibd  db.opt
bash-4.2# cd /var/lib/mysql/vss_tmp/
bash-4.2# ls
audit_log.frm  audit_log.ibd  db.opt
bash-4.2# cp audit_log.frm ../vss/
bash-4.2# cd ../vss
bash-4.2# ls
audit_log.frm  audit_log.ibd  db.opt


#docker部署的mysql
[root@sql-audit-20230526 db1]# cd /var/lib/docker/volumes/5ad9cfe140f098b1352fad8dff1eea69e26ccf2dcab74ed0054de4c15302ae8a/_data/vss
[root@sql-audit-20230526 vss]# ll -h
total 112K
-rw-r----- 1 root             root     8.6K May 19 11:32 audit_log.frm
-rw-r----- 1 systemd-coredump ssh_keys  96K May 19 10:58 audit_log.ibd
-rw-r----- 1 systemd-coredump ssh_keys   65 May 19 10:56 db.opt
[root@sql-audit-20230526 vss]# chown -R systemd-coredump:ssh_keys audit_log.frm

3) 执行IMPORT TABLESPACE操作,cp文件后的正常报错

用途：导入一个之前丢弃的表空间文件，使得表再次可用。这个命令通常用于将备份的表空间文件导入到数据库实例中。

mysql> use vss
mysql> ALTER TABLE audit_log IMPORT TABLESPACE;
ERROR 1813 (HY000): Tablespace 'vss/audit_log' exists.

4）重启下mysql

[root@sql-audit-20230526 vss]# docker restart mysql5.7
mysql5.7

5)查询正常

场景二：从备份恢复的新实例拷贝数据frm、ibd文件

环境准备

新实例：vss_tmp库，audit_log表，9条数据

故障实例：vss库，数据文件都已清除，报doesn't exist

操作步骤和上面的几乎是一样的

1）拷贝前先执行ALTER TABLE ... DISCARD TABLESPACE：由于frm已丢失，执行报错。

2）执行cp操作，要记得授权.

bash-4.2# cd /var/lib/mysql/vss
bash-4.2# ls
db.opt
bash-4.2# cp /var/lib/mysql/vss_tmp/
audit_log.frm  audit_log.ibd  db.opt         
bash-4.2# cp /var/lib/mysql/vss_tmp/audit_log.frm ./              
bash-4.2# cp /var/lib/mysql/vss_tmp/audit_log.ibd ./
bash-4.2# ls
audit_log.frm  audit_log.ibd  db.opt


#docker部署的mysql
[root@sql-audit-20230526 vss]# cd /var/lib/docker/volumes/5ad9cfe140f098b1352fad8dff1eea69e26ccf2dcab74ed0054de4c15302ae8a/_data/vss
vss/     vss_tmp/ 
[root@sql-audit-20230526 vss]# cd /var/lib/docker/volumes/5ad9cfe140f098b1352fad8dff1eea69e26ccf2dcab74ed0054de4c15302ae8a/_data/vss
[root@sql-audit-20230526 vss]# ll -h
total 112K
-rw-r----- 1 root             root     8.6K May 19 12:00 audit_log.frm
-rw-r----- 1 root             root      96K May 19 12:01 audit_log.ibd
-rw-r----- 1 systemd-coredump ssh_keys   65 May 19 10:56 db.opt
[root@sql-audit-20230526 vss]# chown systemd-coredump:ssh_keys *
[root@sql-audit-20230526 vss]# ll -h
total 112K
-rw-r----- 1 systemd-coredump ssh_keys 8.6K May 19 12:00 audit_log.frm
-rw-r----- 1 systemd-coredump ssh_keys  96K May 19 12:01 audit_log.ibd
-rw-r----- 1 systemd-coredump ssh_keys   65 May 19 10:56 db.opt

3) 执行IMPORT TABLESPACE操作

mysql> use vss
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> ALTER TABLE audit_log IMPORT TABLESPACE;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

4）重启下mysql

[root@sql-audit-20230526 vss]# docker restart mysql5.7
mysql5.7

5）查询正常了，9条数据

注意事项：

    处理表空间的操作,通常用于数据库备份、恢复和迁移的过程中，能够有效地管理表的表空间文件。需要注意的是，这些操作通常需要在数据库处于只读模式下执行，以确保数据的一致性和完整性。