最近碰到一个线上问题,修改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。
我们先通过几个步骤,来复现 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 一切正常之后,再删除残留目录。
系统变量 lower_case_table_names 会影响数据库名、数据库目录名、表名、frm 文件名、ibd 文件名,它有 3 种取值(0、1、2),接下来详细介绍。
lower_case_table_names = 0,Linux、Unix 的默认值,表示数据库名、表名区分大小写:
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(责任编辑:综合)
Win10 Build 19044.1320 (21H2) Release 预览版发布
鹰君(00041.HK)授出499万份购股期权 惟须待承受人接纳方可作实
长安汽车将成立东南亚事业部 未来将推出至少30款全球产品 -
雷军曝小米造车细节!小米首款汽车最快三年后推出,车型SUV或轿车
神火股份:3月23日融资净偿还37.10万元 当前融资余额为6.29亿元
Windows 11 “偷渡版”依旧可获得首个 RTM 累积更新 KB5006674