最近碰到一個線上問題，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、問題復現

我們先通過幾個步驟，來復現 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 的介紹。

2、解決方案

如果只想臨時解決 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)，接下來詳細介紹。

（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

（2）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）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 的值，否則，可能會造成意想不到的問題。

本文轉載自微信公眾號「一樹一溪」，可以通過以下二維碼關注。轉載本文請聯系一樹一溪公眾號。