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

前段时间碰到一个线上问题,mysqldump 导出数据报错:

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

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

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

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

目录 [TOC]

正文

1. 问题复现

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

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

1MySQL root@localhost>
2show variables like 'lower_case_table_names'
3+------------------------+-------+
4| Variable_name          | Value |
5+------------------------+-------+
6| lower_case_table_names | 0     |
7+------------------------+-------+

第 2 步,创建测试数据库、表:

1-- 创建测试数据库 test6
2CREATE DATABASE `test6` DEFAULT CHARACTER SET utf8;
3
4-- 创建测试表 Test,不需要插入数据,空表即可
5CREATE TABLE Test (
6    id INT AUTO_INCREMENT PRIMARY KEY,
7    i1 int
8) ENGINE = InnoDB;

第 3 步,查看 test6 数据库目录下的文件:

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

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

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

1MySQL root@localhost>
2show variables like 'lower_case_table_names'
3+------------------------+-------+
4| Variable_name          | Value |
5+------------------------+-------+
6| lower_case_table_names | 1     |
7+------------------------+-------+

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

1-- 删除测试数据库 test6
2DROP DATABASE test6;
3
4-- 会报以下错误
5(1010, "Error dropping database 
6    (can't rmdir './test6', errno: 39)")

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

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

从上面的结果可以看到,db.optTest.ibd 都已经删除,只剩下 Test.frm

InnoDB 删除表时,会先把表的元数据从 information_schema 库的 INNODB_SYS_TABLESPACESINNODB_SYS_TABLESINNODB_SYS_COLUMNSINNODB_SYS_INDEXES 等数据字典表中删除,最后才会删除 ibd 文件。

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

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

1MySQL root@localhost>
2SHOW TABLES FROM test6
3+-----------------+
4| Tables_in_test6 |
5+-----------------+
6| Test           |
7+-----------------+

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

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

1MySQL root@localhost>
2SELECT COUNT(*)
3FROM information_schema.INNODB_SYS_TABLES
4WHERE `name` LIKE 'test6/%'
5+----------+
6| COUNT(*) |
7+----------+
8| 0        |
9+----------+

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

第 7 步,导出数据:

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

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

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

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

2. 解决方案

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

1mysqldump -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 时,创建测试数据库、表:

1-- 创建测试数据库
2CREATE DATABASE Db_Lower_Case_0 DEFAULT CHARACTER SET utf8;
3
4-- 创建测试表
5CREATE TABLE Test_Table_0 (
6    id INT AUTO_INCREMENT PRIMARY KEY,
7    i1 int
8) ENGINE = InnoDB;

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

1## 查看数据库目录名
2[root@Centos mysql]# ls -l | grep Db_Lower_Case_0
3drwxr-x--- 2 mysql mysql ... Db_Lower_Case_0
4
5## 查看表名
6[root@Centos mysql]# ls -l Db_Lower_Case_0
7-rw-r----- 1 mysql mysql ... db.opt
8-rw-r----- 1 mysql mysql ... Test_Table_0.frm
9-rw-r----- 1 mysql mysql ... Test_Table_0.ibd

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

 1MySQL root@localhost>
 2SELECT COUNT(*) FROM Test_Table_0
 3+----------+
 4| COUNT(*) |
 5+----------+
 6| 0        |
 7+----------+
 8
 9MySQL root@localhost>
10SELECT COUNT(*) FROM test_table_0
11(1146, "Table 'Db_Lower_Case_0.test_table_0' doesn't exist")
12
13MySQL root@localhost>
14SELECT COUNT(*) FROM Test_table_0
15(1146, "Table 'Db_Lower_Case_0.Test_table_0' doesn't exist")

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

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

 1MySQL root@localhost>
 2SELECT * FROM information_schema.INNODB_SYS_TABLES
 3WHERE name LIKE 'Db_Lower_Case_0%'\G
 4***************************[ 1. row ]***************************
 5TABLE_ID      | 151
 6NAME          | Db_Lower_Case_0/Test_Table_0
 7FLAG          | 33
 8N_COLS        | 5
 9SPACE         | 161
10FILE_FORMAT   | Barracuda
11ROW_FORMAT    | Dynamic
12ZIP_PAGE_SIZE | 0
13SPACE_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 时,创建测试数据库、表:

1-- 创建测试数据库
2CREATE DATABASE Db_Lower_Case_1 DEFAULT CHARACTER SET utf8;
3
4-- 创建测试表
5CREATE TABLE Test_Table_1 (
6    id INT AUTO_INCREMENT PRIMARY KEY,
7    i1 int
8) ENGINE = InnoDB;

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

1# 查看数据库目录名
2[root@Centos mysql]$ ls -l | grep db_lower_case_1
3drwxr-x--- 2 mysql mysql ... db_lower_case_1
4
5# 查看表名
6[root@Centos mysql]# ls -l db_lower_case_1
7-rw-r----- 1 mysql mysql ... db.opt
8-rw-r----- 1 mysql mysql ... test_table_1.frm
9-rw-r----- 1 mysql mysql ... test_table_1.ibd

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

 1MySQL root@localhost:Db_Lower_Case_1>
 2SELECT COUNT(*) FROM Test_Table_1
 3+----------+
 4| COUNT(*) |
 5+----------+
 6| 0        |
 7+----------+
 8
 9MySQL root@localhost:Db_Lower_Case_1>
10SELECT COUNT(*) FROM test_table_1
11+----------+
12| COUNT(*) |
13+----------+
14| 0        |
15+----------+

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

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

 1MySQL root@localhost>
 2SELECT * FROM information_schema.INNODB_SYS_TABLES
 3WHERE name LIKE 'Db_Lower_Case_1%'\G
 4***************************[ 1. row ]***************************
 5TABLE_ID      | 152
 6NAME          | db_lower_case_1/test_table_1
 7FLAG          | 33
 8N_COLS        | 5
 9SPACE         | 163
10FILE_FORMAT   | Barracuda
11ROW_FORMAT    | Dynamic
12ZIP_PAGE_SIZE | 0
13SPACE_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 时,创建测试数据库、表:

1-- 创建测试数据库
2CREATE DATABASE Db_Lower_Case_2 DEFAULT CHARACTER SET utf8;
3
4-- 创建测试表
5CREATE TABLE Test_Table_2 (
6    id INT AUTO_INCREMENT PRIMARY KEY,
7    i1 int
8) ENGINE = InnoDB;

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

1# 查看数据库目录名
2[test@MacOS data]$ ls -l | grep Db_Lower_Case_2
3drwxr-x---  5 test  staff ... Db_Lower_Case_2
4
5# 查看表名
6[test@MacOS data]$ ls -l Db_Lower_Case_2
7-rw-r-----  1 test  staff ... db.opt
8-rw-r-----  1 test  staff ... Test_Table_2.frm
9-rw-r-----  1 test  staff ... test_table_2.ibd

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

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

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

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

 1MySQL root@localhost:Db_Lower_Case_2>
 2SELECT COUNT(*) FROM Test_Table_2
 3+----------+
 4| COUNT(*) |
 5+----------+
 6| 0        |
 7+----------+
 8
 9MySQL root@localhost:Db_Lower_Case_2>
10SELECT COUNT(*) FROM test_table_2
11+----------+
12| COUNT(*) |
13+----------+
14| 0        |
15+----------+

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

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

 1MySQL root@localhost>
 2SELECT * FROM information_schema.INNODB_SYS_TABLES
 3WHERE name LIKE 'Db_Lower_Case_2%'\G
 4***************************[ 1. row ]***************************
 5TABLE_ID      | 236
 6NAME          | db_lower_case_2/test_table_2
 7FLAG          | 33
 8N_COLS        | 5
 9SPACE         | 458
10FILE_FORMAT   | Barracuda
11ROW_FORMAT    | Dynamic
12ZIP_PAGE_SIZE | 0
13SPACE_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 文件如下:

1[root@VM-24-13-centos test6]$ ls -l
2-rw-r----- 1 mysql mysql ... db.opt
3-rw-r----- 1 mysql mysql ... Test.frm
4-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 知识: