最近碰到一個線上問題，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. 總結

正文

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)，接下來詳細介紹。

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

修改 lower_case_table_names 導緻 frm 檔案删除失敗

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. 總結

繼續閱讀

資料庫增量同步(二台SQL Server伺服器的資料庫之間增量傳輸資料)(jaime原創)

利用expdp 和impdp來導出，導入 oracle 資料（含比較兩個schema的表結構）

DataTable..::.ToString 方法

得到access表字段名VBS腳本

OBJ分析代碼

Java裝逼三部曲之泛型

Partitioning Concepts

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

删除表中重複記錄隻留一條即可

資料庫表中有重複記錄，如何删除這些重複記錄保留ID最大的一個資訊

查詢表中重複記錄

使用WinRAR解壓縮檔案

DB2表壓縮功能

磁盤結構及在Linux中的命名