修改 lower_case_table_names 导致 frm 文件删除失败

最近碰到一个线上问题，mysqldump 导出数据报错：

mysqldump: Got error: 1146: 
  Table 'xxx.xxx' doesn't exist 
  when using LOCK TABLES

经过分析发现，报错信息中的数据库，所有表名都混用了大小写字母，因为创建表之后，系统变量 lower_case_table_names 的值被从 0 修改为 1，导致删除这个数据库时，每个表的 ibd 文件删除成功，frm 文件删除失败。

本文我们就来聊聊这个 mysqldump 问题产生的原因，以及在删除数据库的过程中，lower_case_table_names 是怎么影响 frm、ibd 文件的删除逻辑的。

本文内容基于 MySQL 5.7.35 源码，涉及存储引擎为 InnoDB。

1. 问题复现
2. 解决方案
3. lower_case_table_names

3.1 lower_case_table_names = 0
3.1 lower_case_table_names = 1
3.2 lower_case_table_names = 2

4. 为什么 frm 文件会删除失败？
5. 为什么 ibd 文件能删除成功？
6. 总结

正文

问题复现

我们先通过几个步骤，来复现 mysqldump 问题的产生过程。

第 1 步，确认系统变量 lower_case_table_names 的值是 0：

MySQL root@localhost>
show variables like 'lower_case_table_names'
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

第 2 步，创建测试数据库、表：

-- 创建测试数据库 test6
CREATE DATABASE `test6` DEFAULT CHARACTER SET utf8;

-- 创建测试表 Test，不需要插入数据，空表即可
CREATE TABLE Test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

第 3 步，查看 test6 数据库目录下的文件：

## ls -l 的结果省略了一些信息，用 ... 表示
[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test.frm
-rw-r----- 1 mysql mysql ... Test.ibd

第 4 步，修改 MySQL 配置文件，把系统变量 lower_case_table_names 的值修改为 1，然后重启 MySQL。

第 5 步，重新连接 MySQL，确认系统变量 lower_case_table_names 的值是 1：

MySQL root@localhost>
show variables like 'lower_case_table_names'
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

第 6 步，在 lower_case_table_names = 1 的场景下，删除测试库：

-- 删除测试数据库 test6
DROP DATABASE test6;

-- 会报以下错误
(1010, "Error dropping database 
    (can't rmdir './test6', errno: 39)")

报错信息说明不能删除 ./test6 目录，这是因为 test6 目录下还有 frm 文件：

## ls -l 的结果省略了一些信息，用 ... 表示
[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... Test.frm

从上面的结果可以看到，db.opt、Test.ibd 都已经删除，只剩下 Test.frm。

InnoDB 删除表时，会先把表的元数据从 information_schema 库的 INNODB_SYS_TABLESPACES、INNODB_SYS_TABLES、INNODB_SYS_COLUMNS、INNODB_SYS_INDEXES 等数据字典表中删除，最后才会删除 ibd 文件。

删除表的过程中，Test.ibd 文件被删除了，就说明 Test 表被成功删除了。Test.frm 文件虽然还在，但已经没有实际用处了。

此时，通过 show tables 还能列出测试库 test6 中的 Test 表：

MySQL root@localhost>
SHOW TABLES FROM test6
+-----------------+
| Tables_in_test6 |
+-----------------+
| Test           |
+-----------------+

show tables 会扫描数据库目录，获取其中的 frm 文件名（不含 .frm 后缀），并根据 lower_case_table_names 的值，把 frm 文件名转换为相应的大小写形式，作为该 frm 文件对应的表名。

因为 test6 的数据库目录中还存在 Test.frm 文件，所以执行结果中能看到 Test 表，但这并不表示 Test 表还存在，通过以下 SQL 可以验证：

MySQL root@localhost>
SELECT COUNT(*)
FROM information_schema.INNODB_SYS_TABLES
WHERE `name` LIKE 'test6/%'
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

从上面的执行结果可以看到，InnoDB 的数据字典表中，已经没有测试库 test6 的表了。

第 7 步，导出数据：

[root@VM-24-13-centos test6]#
mysqldump -uroot -p --all-databases > backup.sql
mysqldump: Got error: 1146: 
  Table 'test6.test' doesn't exist 
  when using LOCK TABLES

到这里，我们就已经复现出来 mysqldump 导出数据报错的问题了。

为什么报错信息里的表名不是 Test，而是 test？

这是因为 lower_case_table_names = 1 时，MySQL 内部会使用小写形式的表名，具体请看后面关于 lower_case_table_names 的介绍。

解决方案

如果只想临时解决 mysqldump 导出数据问题，可以通过 --databases 指定需要导出的数据库：

mysqldump -uroot -p --databases db1 > db1.sql

如果想一劳永逸的解决问题，直接把已删除数据库的残留目录删掉就可以了。

还是以前面的测试数据库 test6 为例，因为已经通过 DROP DATABASE 对 test6 进行了删除操作，该数据库中的所有表都已经被删除了。

test6 目录还在，是因为表的 frm 文件没有被删除，这些 frm 文件也没有实际用处了，此时，test6 目录属于残留目录，可以删除。

为了保险起见，可以先把残留目录移动到其它目录下暂存，确认 MySQL 一切正常之后，再删除残留目录。

3. lower_case_table_names

系统变量 lower_case_table_names 会影响数据库名、数据库目录名、表名、frm 文件名、ibd 文件名，它有 3 种取值（0、1、2)，接下来详细介绍。

3.1 lower_case_table_names = 0

lower_case_table_names = 0，Linux、Unix 的默认值，表示数据库名、表名区分大小写：

server 层的数据库名 & 目录名、InnoDB 数据字典表中存放的数据库名是 CREATE DATABASE 中指定的 数据库名。
frm & ibd 文件名、InnoDB 数据字典表中存放的表名是 CREATE TABLE 中指定的 表名。

lower_case_table_names = 0 时，创建测试数据库、表：

-- 创建测试数据库
CREATE DATABASE Db_Lower_Case_0 DEFAULT CHARACTER SET utf8;

-- 创建测试表
CREATE TABLE Test_Table_0 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看数据库目录名、表的 frm、ibd 文件名：

## 查看数据库目录名
[root@Centos mysql]# ls -l | grep Db_Lower_Case_0
drwxr-x--- 2 mysql mysql ... Db_Lower_Case_0

## 查看表名
[root@Centos mysql]# ls -l Db_Lower_Case_0
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test_Table_0.frm
-rw-r----- 1 mysql mysql ... Test_Table_0.ibd

server 层通过表名去 InnoDB 中查找对应的表时，也会区分大小写：

MySQL root@localhost>
SELECT COUNT(*) FROM Test_Table_0
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost>
SELECT COUNT(*) FROM test_table_0
(1146, "Table 'Db_Lower_Case_0.test_table_0' doesn't exist")

MySQL root@localhost>
SELECT COUNT(*) FROM Test_table_0
(1146, "Table 'Db_Lower_Case_0.Test_table_0' doesn't exist")

从示例 SQL 可以看到，只有指定正确的大小写，SQL 才能执行成功，否则都会报错说表不存在。

通过以下 SQL 也可以验证，存放在 InnoDB 数据字典中的数据库名、表名是 CREATE DATABASE、CREATE TABLE 中指定的数据库名、表名：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_0%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 151
NAME          | Db_Lower_Case_0/Test_Table_0
FLAG          | 33
N_COLS        | 5
SPACE         | 161
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

3.1 lower_case_table_names = 1

lower_case_table_names = 1，Windows 的默认值，表示数据库名、表名都不区分大小写：

server 层的数据库名 & 目录名、InnoDB 数据字典表中存放的数据库名是 CREATE DATABASE 中指定数据库名的 小写形式。
frm & ibd 文件名、 InnoDB 数据字典表中存放的表名是 CREATE TABLE 中指定表名的 小写形式。

lower_case_table_names = 1 时，创建测试数据库、表：

-- 创建测试数据库
CREATE DATABASE Db_Lower_Case_1 DEFAULT CHARACTER SET utf8;

-- 创建测试表
CREATE TABLE Test_Table_1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看数据库目录名、表的 frm、ibd 文件名，全部被转换为小写了：

# 查看数据库目录名
[root@Centos mysql]$ ls -l | grep db_lower_case_1
drwxr-x--- 2 mysql mysql ... db_lower_case_1

# 查看表名
[root@Centos mysql]# ls -l db_lower_case_1
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... test_table_1.frm
-rw-r----- 1 mysql mysql ... test_table_1.ibd

server 层通过表名去 InnoDB 查找对应的表之前，也会把表名转换为小写形式：

MySQL root@localhost:Db_Lower_Case_1>
SELECT COUNT(*) FROM Test_Table_1
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost:Db_Lower_Case_1>
SELECT COUNT(*) FROM test_table_1
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

从示例 SQL 可以看到，表名包含大小写字母、全部是小写字母，SQL 都能执行成功。

通过以下 SQL 也可以验证，存放在 InnoDB 数据字典中的数据库名、表名都转换为小写形式了：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_1%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 152
NAME          | db_lower_case_1/test_table_1
FLAG          | 33
N_COLS        | 5
SPACE         | 163
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

3.2 lower_case_table_names = 2

lower_case_table_names = 2，这是 MacOS 的默认值，这个选项值的情况比前面两种复杂一些：

数据库名、数据库目录名是 CREATE DATABASE 中指定的 数据库名。
表的 frm 文件名是 CREATE TABLE 中指定的 表名。
表的 ibd 文件名是 CREATE TABLE 中指定表名的 小写形式。
InnoDB 数据字典表中存放的数据库名、表名 小写形式。

上面 4 条可以归纳为 2 条：

server 层使用 CREATE DATABASE、CREATE TABLE 中指定的 数据库名、 表名。
InnoDB 使用 CREATE DATABASE、CREATE TABLE 中指定数据库名、表名的 小写形式。

lower_case_table_names = 2 时，创建测试数据库、表：

-- 创建测试数据库
CREATE DATABASE Db_Lower_Case_2 DEFAULT CHARACTER SET utf8;

-- 创建测试表
CREATE TABLE Test_Table_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看数据库目录名、表的 frm、ibd 文件名：

# 查看数据库目录名
[test@MacOS data]$ ls -l | grep Db_Lower_Case_2
drwxr-x---  5 test  staff ... Db_Lower_Case_2

# 查看表名
[test@MacOS data]$ ls -l Db_Lower_Case_2
-rw-r-----  1 test  staff ... db.opt
-rw-r-----  1 test  staff ... Test_Table_2.frm
-rw-r-----  1 test  staff ... test_table_2.ibd

数据库目录由 server 层创建，目录名是 CREATE DATABASE 中指定的数据库名。

frm 文件由 server 层创建，文件名是 CREATE TABLE 中指定的表名。

ibd 文件由 InnoDB 创建，文件名是 CREATE TABLE 中指定表名的小写形式。

server 层通过表名去 InnoDB 查找对应的表之前，也会把表名转换为小写形式：

MySQL root@localhost:Db_Lower_Case_2>
SELECT COUNT(*) FROM Test_Table_2
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost:Db_Lower_Case_2>
SELECT COUNT(*) FROM test_table_2
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

从示例 SQL 可以看到，表名包含大小写字母、全部是小写字母，SQL 都能执行成功。

通过以下 SQL 也可以验证，存放在 InnoDB 数据字典中的数据库名、表名都转换为小写形式了：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_2%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 236
NAME          | db_lower_case_2/test_table_2
FLAG          | 33
N_COLS        | 5
SPACE         | 458
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

4. 为什么 frm 文件会删除失败？

我们先来回顾一下 frm 文件删除失败的场景：

lower_case_table_names = 0 时，创建了数据库和表（表名包含大小写字母）。
lower_case_table_names = 1 时，删除数据库，ibd 文件删除成功，frm 文件删除失败。

我们还是以 1. 问题复现中的测试数据库、表为例，lower_case_table_names = 0 时，创建测试数据库、表之后，frm、ibd 文件如下：

[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test.frm
-rw-r----- 1 mysql mysql ... Test.ibd

3.1 lower_case_table_names = 1 小节介绍过，lower_case_table_names 修改为 1 之后，server 层通过表名去 InnoDB 查找对应的表之前，会把表名转换为小写形式。

接下来，我们先来看看删除数据库的主要逻辑：

第 1 步，遍历待删除数据库的目录，找到该目录下所有的 frm 文件，把 frm 文件名（不含 .frm 后缀）转换为小写，作为表名。

以 test6 数据库为例：

遍历 test6 目录，找到该目录下的 frm 文件，该目录下只有一个 frm 文件：Test.frm。

把 frm 文件名转换为小写，得到表名 test。

第 2 步，执行第一种删表逻辑：以第 1 步中根据 frm 文件名得到的表名执行删表操作，由 InnoDB 和 server 层共同完成，InnoDB 负责删除表的元数据和 ibd 文件，server 层负责删除 frm 文件。

遍历第 1 步得到的表名，加上 .frm 后缀，得到 frm 文件名，然后根据 frm 文件是否存在执行不同的逻辑。

如果 frm 文件存在，则调用 InnoDB 的删表方法，从 InnoDB 数据字典表中删除该表的元数据，以及删除 ibd 文件。

InnoDB 删表成功之后，server 层会删除该表的 frm 文件；InnoDB 删表失败，server 层会记录第一种删表逻辑中存在删除失败的表。

如果 frm 文件不存在，不会调用 InnoDB 的删表方法，server 层也会记录第一种删表逻辑中存在删除失败的表。

以测试数据库 test6 为例：

第 1 步得到的表名为 test，加上 .frm 后缀，得到文件名：test.frm。

Linux 系统的文件名是区分大小写的，test6 目录下只存在 Test.frm，用 test.frm 无法匹配 Test.frm 文件，也就是说，test.frm 文件不存在。

因为 test6 目录下不存在 test.frm 文件，server 层会记录第一种删表逻辑中存在删除失败的表。

第 3 步，判断第 2 步是否存在删除失败的表。

如果存在删除失败的表，会执行第二种删表逻辑，由 InnoDB 独自完成：
从 information_schema.INNODB_SYS_TABLES 中获取要删除的数据库中的表名，逐个执行删表操作。

从 INNODB_SYS_TABLES 中获取表名，以及删表操作都在 InnoDB 中进行，不会受到 lower_case_table_names 的影响。

以 test6 数据库为例，第二种删表逻辑如下：

① 从 INNODB_SYS_TABLES 表获取 test6 数据库中未被删除的第一个表名。

② 把该表的元数据信息从对应的数据字典表中删除。

③ 删除该表的 ibd 文件。

循环 ① ~ ③，直到 test6 中的所有表都被删除之后，第二种删表逻辑结束。

介绍完删除数据库的逻辑，我们来总结一下：为什么 frm 文件会删除失败？

lower_case_table_names 的值从 0 修改为 1 之后，第一种删表逻辑，因为表名的大小写问题，导致找不到 frm 文件，执行失败，转而执行第二种删表逻辑。

第二种删表逻辑，只会从 InnoDB 数据字典表中删除表的元数据，然后删除表的 ibd 文件，不包含删除 frm 文件的操作，frm 文件也就不会被删了。

5. 为什么 ibd 文件能删除成功？

通过 4. 为什么 frm 文件会删除失败？小节的介绍，我们可以看到，第一种删表逻辑，由于找不到表的 frm 文件，不会触发 InnoDB 的删表操作，也就不会删除 ibd 文件了。

第二种删表逻辑，先从 INNODB_SYS_TABLES 表中获取表名，然后通过表名找表对应的表空间，表空间信息中包含从 INNODB_SYS_DATAFILES 表中读取到的 ibd 文件路径。

删除 ibd 文件时，会从表空间信息中获取 ibd 文件路径。

ibd 文件能删除成功，取决于以下 2 个因素：

第二种删表逻辑，从 INNODB_SYS_TABLES 中获取表名之后，不会进行大小写转换（也就是不会受到 lower_case_table_names 的影响），而是直接以获取到的表名，加载表的元数据信息。
创建表时写入 INNODB_SYS_DATAFILES 表中的 ibd 文件路径，不管系统变量 lower_case_table_names 的值修改成什么，该表中存放的 ibd 文件路径都不会变。

6. 总结

如果程序代码中已经使用了某个数据库的表，或者 MySQL 实例已经在线上正式使用，最好不要修改 lower_case_table_names 的值，否则，可能会造成意想不到的问题。

有想了解的 MySQL 知识点或想交流的问题可以关注我公众号：《一树一溪》

修改 lower_case_table_names 导致 frm 文件删除失败原创

问题复现

解决方案

3.1 lower_case_table_names = 0

3.1 lower_case_table_names = 1

3.2 lower_case_table_names = 2

6. 总结

为你推荐

日常Bug排查-偶发性读数据不一致

推荐阅读

专题推荐

本月精选性能专题

本月精选线上案例

本月精选原创好文

联系我们

网媒渠道

友情链接