排除 MySQL 技術疑難問題

排除 MySQL 技術疑難問題

🌐 Switch language: English, 漢字

本文探討了使用 MySQL 時遇到的各種常見問題，並根據其性質和影響將它們分為七個不同的類別。這些類別有助於識別潛在的問題並促進高效的故障排除。這些問題分類如下：

1. 安裝和配置問題：與 MySQL 設置和配置相關的問題。
2. 資料庫和表操作錯誤：在創建、刪除或訪問資料庫和表時出現的問題。
3. 訪問和認證錯誤：涉及用戶權限和認證的錯誤。
4. SQL 和查詢錯誤：與 SQL 查詢的語法和執行相關的問題。
5. 連接和網路錯誤：用戶端與 MySQL 伺服器之間的連接問題。
6. 資源和環境錯誤：由系統資源如磁碟空間、記憶體限制或文件系統權限引起的錯誤。
7. 其他工具特定錯誤：特定於資料庫工具（如 Navicat）的錯誤，或來自腳本環境（如 PHP）的問題。

安裝和配置問題

找不到檔案 mysqldump

1. 尋找 mysqldump 檔案的位置
   • 在 macOS 或 Linux 上輸入命令 sudo find / -iname mysqldump
   • 如果你安裝了
     • XAMPP 在 macOS 上: 完整路徑是 /Applications/XAMPP/xamppfiles/bin/mysqldump
     • XAMPP 在 X 磁碟上的 Win : 完整路徑是 X:\xampp\mysql\bin\mysqldump.exe
     • MAMP & $ MAMP PRO 在 macOS 上: 完整路徑是 /Applications/MAMP/Library/bin/mysqldump
2. 輸入 mysqldump 命令的完整路徑
   • 之前導致錯誤的命令 mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz
   • 新命令 /Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz

無法在 Windows 上啟動 MySQL 服務

1. MySQL 數據目錄 需被 NETWORK SERVICE 在 Windows Server 2008 上有寫入權限
2. 目錄 secure-file-priv 需被 NETWORK SERVICE 在 Windows Server 2008 上有寫入權限

資料庫和表操作錯誤

無法開啟單表表空間檔案 filename.ibd

版本：XAMPP 5.6.15-1，運行於macOS

狀況：Mac 意外關機，且資料庫沒有正常關閉。重啟 Mac 後，無法啟動 MySQL 服務。

可能的解決方案：

1. 編輯位於：/Applications/XAMPP/xamppfiles/etc/my.cnf 的 MySQL 設定檔
2. 增加此行：innodb_force_recovery = 1
3. 嘗試啟動 MySQL 服務
4. 如果 MySQL 服務成功啟動，編輯 MySQL 設定檔並註釋掉這行：#innodb_force_recovery = 1
5. 重啟 MySQL 服務

errno 41 - 刪除資料庫時出錯

訊息：執行 DROP DATABASE `TABLE_NAME` 時出現 錯誤刪除資料庫（無法刪除目錄 '.\TABLE_NAME'，errno: 41）；

解決方案：phpmyadmin - 刪除 MySQL 中的資料庫時出錯（無法刪除目錄 '.\oro'，errno: 41）- Stack Overflow

ERROR 1005 (HY000) at line xx: 無法創建表 'TABLE_NAME' (errno: 28)

解決方案

• 檢查 mysql 數據文件夾所在的磁碟空間是否足夠。例如，在 Linux 上輸入 df -h
• 更多資訊請見 mysql - ERROR 1005 (HY000): 無法創建表 (errno: 150) - Stack Overflow。

ERROR 1006 (HY000): 無法創建資料庫 'DATABASE_NAME' (errno: 28)

解決方案

• 檢查 mysql 數據文件夾所在的磁碟空間是否足夠。例如，在 Linux 上輸入 df -h
• 更多資訊請見 ERROR 1006 (HY000) 無法創建資料庫 (errno: 13) MySQL 5.6.12 - Stack Overflow。

ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

訊息： [Err] 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

解決方案：

• 檢查 DATABASE\TABLE.frm 文件是否存在。如果不存在，可能需要在執行 MySQL 查詢前先創建該表。
• 檢查 DATABASE\TABLE.frm 文件或所在文件夾的權限。未經驗證

ERROR 1052 - Column 'column_name' in field list is ambiguous

訊息：Error Code: 1052. Column 'column_name' in field list is ambiguous 欄位列表中的 'column_name' 模糊不清

原因：由於 'column_name' 出現在兩個或多個表中...

解決方案：只保留一個表名 'column_name' 或添加表名別名。

錯誤 1054 - 'where 子句' 中未知的欄位

訊息：[Err] 1054 - 'where 子句' 中未知的欄位 'xxx'

解決方案：

1. 檢查 'xxx' 欄位名稱是否存在
2. 如果 'xxx' 欄位名稱是由 使用者定義的變數 計算得出。將整個查詢放入另一個母查詢中。

-- 遇到錯誤訊息的查詢：[Err] 1054 - 'where 子句' 中未知的欄位 'rank'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
FROM
(
        ...
) semi
WHERE semi.rank <= 10

將整個查詢放入另一個母查詢中。

SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10

錯誤 1114 (HY000): 表格 `TABLE_NAME` 已滿

可能的解決方案

• 因為 MySQL 數據目錄 所在的硬碟分區已滿或幾乎已滿，釋放一些硬碟空間。

錯誤 1170: 在鍵規格中使用 BLOB/TEXT 欄位且沒有鍵長

情況

• 嘗試建立新表格時的 SQL 語法

DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

解決方案：在鍵規格中使用 BLOB/TEXT 欄位並指定鍵長，例如 `url`(500)

DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

錯誤 1813: 表格 xxx 的表空間已存在

訊息：錯誤 1813 表格 xxx 的表空間已存在。

解決方案

• mysql - 錯誤：表格 xxx 的表空間已存在。請在導入前丟棄表空間 - Stack Overflow

錯誤 1049 (42000): 未知的資料庫

訊息：錯誤 1049 (42000): 未知的資料庫 'MY_DATABASE_p'

解決方案：

• 檢查 'MY_DATABASE_p' 資料庫是否存在
• 如果您在使用控制台時希望連接到 'MY_DATABASE' 資料庫，請檢查資料庫名稱後是否沒有 TAB 字元。

錯誤 1070 (42000): 指定的鍵太長；最大鍵長為 767 位元組

環境: MySQL 5.6

根本原因: "預設情況下，索引鍵前綴長度限制為 767 位元組。參見第 13.1.13 節，“CREATE INDEX 語句”。例如，在 TEXT 或 VARCHAR 列上，如果使用 utf8mb3 字元集，並且每個字元最多 3 位元組，您可能會在列前綴索引超過 255 個字元時達到此限制。當啟用 innodb_large_prefix 配置選項時，對於使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引鍵前綴長度限制提高到 3072 位元組。"

解決方案:

• 執行以下 SQL 查詢：

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;

• 修改 MySQL 配置文件

innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA

• 重啟 MySQL 伺服器

• 執行以下 SQL 查詢：

ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;

訪問和認證錯誤

[Warning] Using a password on the command line interface can be insecure

問題描述： 使用 bash 命令匯出或匯入 MySQL 資料庫時，可能會遇到以下警告：[警告] 在命令行介面上使用密碼可能不安全。

mysql: [Warning] Using a password on the command line interface can be insecure.

or

mysqldump: [Warning] Using a password on the command line interface can be insecure.

這通常發生在使用如下命令時：

mysqldump -u USERNAME -p -h 127.0.0.1 --default-character-set=utf8 DATABASE > DATABASE.sql

解決方案：要解決此警告並提高安全性，請按照以下步驟操作：

1. 使用 mysql_config_editor 設定登入路徑 (login path)：mysql_config_editor set --login-path=local --host=localhost --user=root --password
2. 匯出或匯入 MySQL 資料庫時使用登入路徑 (1) 匯出時：mysqldump --login-path=local --default-character-set=utf8 資料庫名稱 > 資料庫名稱.sql (2) 匯入時：pv 資料庫名稱.sql.gz | gunzip | mysql --login-path=local --default_character_set utf8 --force 資料庫名稱
3. 查看所有已設定的登入路徑 mysql_config_editor print --all

透過使用登入路徑，可以避免在命令行中暴露密碼，解決安全警告。

錯誤 1044 (42000): 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME'

訊息：錯誤 1044 (42000): 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME'

pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME

解決方案：

• 檢查指定使用者名稱和資料庫名稱的權限

$ mysql -u USER -p --host=127.0.0.1
輸入密碼：

mysql> use DATABASE_NAME

訊息：mysqldump: 執行 LOCK TABLES 時出錯: 1044: 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME'

$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

解決方案：

• 如果無法給予使用者權限，添加 mysqldump 選項 --skip-lock-tables 。

$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

錯誤 1827 (HY000): 密碼哈希不符合預期格式。檢查是否使用了正確的密碼算法與 PASSWORD() 函數。

錯誤的 SQL 查詢如下：

CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';

解決方案： (1) 檢查帳戶是否已創建

SELECT User,Host FROM mysql.user;

(2a) 如果帳戶已創建，為該帳戶設定密碼。

SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');

(2b) 如果帳戶未創建，重新創建帳戶。

CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';

參考資料

• MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.2 CREATE USER 語法
• MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.7 SET PASSWORD 語法
• MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.3 DROP USER 語法

錯誤 1045 (28000): 拒絕使用者存取

訊息：錯誤 1045 (28000): 拒絕使用者 'user'@'localhost' 存取（使用密碼：YES）

解決方案：

• 檢查使用者名稱的打字錯誤。
• 檢查密碼的打字錯誤。
• 如果您正在使用控制台命令，如果密碼包含特殊字元，請轉義密碼，例如 mysql -u root -p'PASSWORD'
• 您可能需要刪除現有的帳戶設定並重新配置。

錯誤！: SQLSTATE[28000]: 無效的授權規格：1045 拒絕訪問

訊息：錯誤！: SQLSTATE[28000]: 無效的授權規格：1045 拒絕使用者 'user'@'localhost' 存取（使用密碼：YES）

解決方案：

• 如果您執行了查詢 INTO OUTFILE，您需要授予文件權限，例如：GRANT FILE ON *.* TO 'user'@'localhost';。

相關問題："錯誤！: SQLSTATE[HY000]: 一般錯誤：1290 MySQL 伺服器正在使用 --secure-file-priv 選項運行，所以它不能執行這個聲明"

SQL 查詢語法錯誤

捕獲異常：SQLSTATE[HY093]：無效的參數編號：參數未定義

解決方案

• 問號的數量與查詢值的數量不一致
• 查詢值的數組不應該是關聯數組。使用順序數組！

錯誤！：SQLSTATE[HY000]：一般錯誤

訊息：錯誤！：SQLSTATE[HY000]：一般錯誤

狀況：當我使用 PHP: PDO

解決方案：

• "您不應該在 update 或 insert 查詢中使用 $result = $stmt->fetchAll();" 。
• 您不應該在查詢 INTO OUTFILE ... 中使用 $result = $stmt->fetchAll(); 。

錯誤：ASCII '\0' 出現在語句中，但這是不允許的，除非啟用了 --binary-mode 選項並且 mysql 以非互動模式運行

訊息：錯誤：ASCII '\0' 出現在語句中，但這是不允許的，除非啟用了 --binary-mode 選項並且 mysql 以非互動模式運行。如果期望 ASCII '\0'，請將 --binary-mode 設為 1。查詢：''。

狀況：當我導入 sql 文件時遇到上述錯誤訊息。

解決方案：解壓縮文件然後再次導入文件。sql 文件是壓縮文件。您可以使用 file (command) 來識別文件的類型。

$ file compressed.sql
compressed.sql：gzip 壓縮數據

$ file plain_text.sql
plain_text.sql：UTF-8 Unicode 文本，帶有非常長的行

錯誤 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

訊息

查詢發生錯誤 (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

遇到錯誤的查詢

-- 建立範例資料表
CREATE TABLE posts (
    id INT,
    post_id VARCHAR(10),
    author VARCHAR(50),
    content TEXT,
    likes INT,
    post_time DATETIME
);

-- 插入範例資料
INSERT INTO posts VALUES
(1, 'A123', 'Alice', '第一篇文章', 10, '2024-01-01'),
(2, 'A123', 'Bob', '推推', 5, '2024-01-02'),
(3, 'A123', 'Bob', '讚讚', 3, '2024-01-03');

-- 這會報錯
SELECT 
    id,          -- 問題: 沒有在 GROUP BY 中
    post_id,
    author,
    GROUP_CONCAT(content SEPARATOR "\n") AS content,     -- 問題: 沒有在 GROUP BY 中
    likes        -- 問題: 沒有在 GROUP BY 中
FROM posts
GROUP BY post_id, author;

解決方案：修正後的查詢 (1) SELECT 中的每個欄位要麼放在 GROUP BY 中、或 (2) 使用彙總函數 (如 MAX, MIN, SUM, COUNT, GROUP_CONCAT)

-- 方法一：取消 `sql_mode=only_full_group_by`:
有兩種方法可以取消 `sql_mode=only_full_group_by`:

1. 暫時性修改 (僅對當前會話有效)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```

2. 永久性修改 (修改 MySQL 設定檔)
- 找到 MySQL 設定檔 (通常是 `my.cnf` 或 `my.ini`)
- 在 `[mysqld]` 區段加入或修改:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

注意事項:
1. 修改設定檔後需要重啟 MySQL 服務才會生效
2. 不建議取消這個設定，因為:
   - 可能導致查詢結果不可預測
   - 違反 SQL 標準
   - 可能在未來版本中產生相容性問題

建議的做法是修正 SQL 查詢而不是更改 SQL mode。

-- 方法二: 把所有欄位加入 GROUP BY
SELECT 
    id,
    post_id,
    author,
    content,
    likes
FROM posts
GROUP BY post_id, author, id, content, likes;

-- 方法三: 對非 GROUP BY 的欄位使用彙總函數
SELECT 
    MAX(id) as id,
    post_id,
    author,
    GROUP_CONCAT(content) as contents,
    SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;

錯誤 1235: 這個版本的 MySQL 尚未支持 'LIMIT & IN/ALL/ANY/SOME 子查詢'

訊息：錯誤 1235 (42000)：這個版本的 MySQL 尚未支持 'LIMIT & IN/ALL/ANY/SOME 子查詢'。

解決方案：將子查詢的語法更改為 column_name BETWEEN start_number to end_number

進一步閱讀：

• MySQL :: MySQL 限制和局限 :: 4 子查詢的限制

錯誤 1267: 字元集混合不合法 (utf8mb4_general_ci,COERCIBLE) 與 (latin1_swedish_ci,IMPLICIT) 用於 'concat' 操作

錯誤情況：嘗試合併不同類型的數據，例如 CONCAT(string, int)

解決方案：SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR)) 或 SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))

錯誤 1305 - 函數 MY_TABLE.MY_FUNCTION 不存在

訊息：MySQL 錯誤 1305 - 函數 MY_TABLE.MY_FUNCTION 不存在

解決方案：修正函數名稱中的打字錯誤

錯誤 1690 - BIGINT UNSIGNED 值超出範圍

訊息：MySQL 錯誤 #1690 (BIGINT UNSIGNED 值超出範圍)

解決方案 :

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

錯誤 1366: 字串值不正確

訊息：SQLSTATE[HY000]: 一般錯誤：1366 字串值 '\xF0\x9F\x87\xAF\xF0\x9F...' 對於欄位 'XXX' 不正確

解決方案：

• 檢查 PHP PDO 的字元集。並執行 set names utf8mb4
• 檢查表的配置
  • 字元集 (字元集、字元集): utf8mb4
  • 排序規則 (定序、字元序): utf8mb4_unicode_ci

錯誤！: SQLSTATE[42000]: 語法錯誤或存取違規

訊息：錯誤！: SQLSTATE[42000]: 語法錯誤或存取違規：1064 您在 SQL 語法中有錯誤；請檢查手冊，了解您的 MySQL 伺服器版本對應的正確語法以用於 ...

解決方案：

• SQL 語法除錯
• SQL 語法檢查在線上，SQL 驗證器，即時 SQL 編譯器在線上 - EverSQL 不支持 PHP: PDO::prepare，其中查詢語法包含問號。

連接和網路錯誤

MySQL 伺服器已斷開 (has gone away)

步驟

• 在 MySQL 設定檔啟用 log_error 選項，例如 log_error="file_name_of_error_log"。
• 範例錯誤日誌位於 file_name_of_error_log 如下：

2019-05-23T08:52:19.989876Z 99980 [Note] 中斷連接 99980 到資料庫：'DB_NAME' 用戶：'DB_USER' 主機：'localhost' (收到的封包大於 'max_allowed_packet' 位元組)

• 如果 MySQL 用戶具有 SUPER 權限，則增加 max_allowed_packet 的值。

錯誤：SQLSTATE[HY000] [2002] 沒有這樣的文件或目錄

錯誤訊息：SQLSTATE[HY000] [2002] 沒有這樣的文件或目錄

解決方案：

• 將資料庫主機從 localhost 修改為 127.0.0.1

錯誤 2002: 無法連接: SQLSTATE[HY000] [2002]

Windows Cygwin 終端機狀況：

$ mysql -u root -p
輸入密碼：
錯誤 2002 (HY000): 無法通過 '/var/run/mysql.sock' 連接到本地 MySQL 伺服器 (2)

$ mysql -h localhost -u root -p
輸入密碼：
錯誤 2002 (HY000): 無法通過 '/var/run/mysql.sock' 連接到本地 MySQL 伺服器 (2)

解決方案：

• 將 -h localhost 更改為 -h 127.0.0.1

$ mysql -h 127.0.0.1 -u root -p

• 如果仍然不行，重啟伺服器並重新啟動 MySQL 服務。

錯誤 2002: SQLSTATE[HY000] [2002] 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用

訊息：(1) [2002] 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" 用中文表示

解決方案：MySQL/PHP 錯誤:(2002) 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 - Stack Overflow

錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器

錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器

解決方案：

• 檢查 IP 是否有效

錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器 (111 "連接被拒絕")

解決方案：

• 檢查 MySQL 服務是否在運行。如果沒有，啟動 MySQL 服務。
• 檢查防火牆規則

錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器 (116 "連接超時")

解決方案：

• 檢查 MySQL 的配置
  • 註釋掉 bind-address = 127.0.0.1 或設置為 *
  • 註釋掉 skip-networking
• 檢查 MySQL 資料庫用戶的權限。
• 在 (1) 個人電腦 (2) MySQL 服務所在的伺服器 (3) ISP/VM 供應商處檢查防火牆規則。更多資訊請參考 在 CentOS 7 上安裝 MySQL | ProfitBricks DevOps Central
  • 檢查您的 IP 地址是否包含在允許的 IP 地址列表中的防火牆規則。
  • 透過使用 我的 IP 地址是什麼? 服務檢查您的 IP 地址是否有更改。
• (可選) 監控防火牆活動。更多資訊請參考 如何使用 Windows 防火牆日誌追蹤防火牆活動 在 Win

參考資料：

• 遠程連接到 MySQL 資料庫
• 錯誤 2003 - Navicat Wiki
• SQLyog MySQL 管理員常見問答 - 錯誤號 2003: 無法連接...

錯誤 2013: 在查詢過程中與 MySQL 伺服器失去連接

訊息：錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接

情況：在執行包含超過 1,000,000 行的下列查詢後，我遇到了 '錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接' 的錯誤訊息。

INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;

解決方案：

• 在 MySQL Workbench 上增加 (1) 資料庫連接保持活躍間隔（秒） 和 (2) 資料庫連接讀取超時（秒） 的設定 。修改設定後請重新啟動 MySQL Workbench。例如，預設的 資料庫連接讀取超時（秒） 設定為 30 秒，你可以增加到 6000 秒（100 分鐘）。
• 減少行數以縮短執行時間 (1) 使用 LIMIT 子句 (2) 或分割查詢大小，例如 MOD(column, 2) = 0 和 MOD(column, 2) > 0，如果欄位是數字的話。

INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;

從 MySQL 資料轉移至 MSSQL

情況: 需要將 MySQL 資料庫中的資料同步或遷移至 Microsoft SQL Server。

解決方案:

• (手動方式) 匯出 MySQL 資料為 MSSQL 相容格式: mysqldump --compatible=mssql [database_name]
• (自動方式) 使用 Microsoft SQL Server Migration Assistant、$ SQLyog 工具 (適用於 Win )。設定特定查詢來控制要從 MySQL 轉移至 Microsoft SQL Server 的資料。

常見挑戰:

• 大型資料集的連線逾時問題: 參考 2013 錯誤的故障排除方法，例如延長逾時參數或實施批次處理。
• 資料類型不相容: 在遷移前驗證兩系統間的類型對應，以防止資料損壞或遺失。

資源和環境錯誤

無法存取 MySQL 日誌

版本：XAMPP 5.6.15-1，運行於macOS

原因：

• 錯誤日誌只能由名為 mysql 的用戶訪問

解決方案 ：

找到 MySQL 日誌的路徑
$ ls /Applications/XAMPP/xamppfiles/var/mysql/*.local.err

找出 MySQL 日誌的檔案名稱，例如：XXXMacBook-Pro.local.err

設置日誌的權限
$ sudo chmod 774 /Applications/XAMPP/xamppfiles/var/mysql/XXXMacBook-Pro.local.err

錯誤代碼: 13 拒絕存取

當我嘗試啟動服務，執行 mysqld.exe --datadir=..\data --console 後出現此訊息。（MySQL版本：5.5.5-10.0.12-MariaDB 在 Win ）

> mysqld.exe --datadir=..\data --console
180430 10:33:38 [錯誤] mysqld.exe: 找不到檔案 'C:\MariaDB_10.0\bin\..\data\aria_log_control' (錯誤代碼: 13 "拒絕存取")
180430 10:33:38 [錯誤] mysqld.exe: 嘗試使用 aria 控制檔案 'C:\MariaDB_10.0\bin\..\data\aria_log_control' 時出錯 '無法打開檔案'
180430 10:33:38 [錯誤] 插件 'Aria' 初始化函數返回錯誤。
180430 10:33:38 [錯誤] 插件 'Aria' 註冊為儲存引擎失敗。
180430 10:33:38 [注意] InnoDB: 使用互斥鎖來計數緩衝池頁面
180430 10:33:38 [注意] InnoDB: InnoDB 記憶體堆疊已停用
180430 10:33:38 [注意] InnoDB: 互斥鎖和 rw_locks 使用 Windows 交錯函數
180430 10:33:38 [注意] InnoDB: 壓縮表使用 zlib 1.2.3
180430 10:33:38 [注意] InnoDB: 未使用 CPU crc32 指令
180430 10:33:38 [注意] InnoDB: 初始化緩衝池，大小 = 4.0G
180430 10:33:38 [注意] InnoDB: 完成緩衝池初始化
180430 10:33:38 [錯誤] InnoDB: 無法以讀寫模式打開 .\ibdata1
180430 10:33:38 [錯誤] InnoDB: 系統表空間必須可寫！
180430 10:33:38 [錯誤] 插件 'InnoDB' 初始化函數返回錯誤。
180430 10:33:38 [錯誤] 插件 'InnoDB' 註冊為儲存引擎失敗。
180430 10:33:38 [注意] 插件 'FEEDBACK' 已停用。
180430 10:33:38 [錯誤] 未知/不支持的儲存引擎: innodb
180430 10:33:38 [錯誤] 終止

180430 10:33:38 [注意] mysqld.exe: 關機完成

解決方案：

以管理員特權開啟命令行。（如何操作: 如何在 Windows 8 或 10 中以管理員身份開啟命令提示符）

錯誤！：SQLSTATE[HY000]：一般錯誤：3 寫入檔案 'xxx\Temp\xxx.tmp' 時出錯（錯誤代碼：28 - 設備上沒有剩餘空間）

錯誤訊息範例：錯誤！：SQLSTATE[HY000]：一般錯誤：3 寫入檔案 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\MY2713.tmp' 時出錯（錯誤代碼：28 - 設備上沒有剩餘空間）

條件：檢查 mysql tmpdir 文件夾的磁碟可用空間

解決方案：增加 mysql tmpdir 文件夾的可用空間。或者使用另一個硬碟更改 mysql tmpdir 文件夾，該驅動器應具有更多的可用空間。

• 檢查當前的 mysql tmpdir 文件夾。查詢語法 SHOW VARIABLES LIKE 'tmpdir';。
  • 在 Win 上，預設的臨時文件夾 是 %TMP% = %USERPROFILE%\AppData\Local\Temp。
  • 在 Linux 上，預設的臨時文件夾可能是 /tmp
• 編輯 mysql 配置文件

[mysqld]
tmpdir=X:/temp

• 重啟 MySQL 服務

驗證

• 查詢語法 SHOW VARIABLES LIKE 'tmpdir'; 來驗證 mysql 配置文件的修改。

mysqldump: 寫入時遇到 errno 32 錯誤

錯誤情況

$ mysqldump -h localhost -u root -p --force --single-transaction --default-character-set=utf8 --quick mytable  | pv | gzip -c > mytable.sql.gz

遇到錯誤訊息：

-bash: pv: 命令找不到
...
mysqldump: 寫入時遇到 errno 32 錯誤

根本原因

• 未安裝 pv
• 輸入指令檢查 pv 是否安裝 which pv
• 輸入指令安裝 pv：sudo yum -y install pv

參考資料

• mysqldump: 寫入時遇到 errno 32 錯誤 | mysqldump.guru

錯誤 1205: 鎖定等待超時，嘗試重新啟動事務

解決方案：

1. SHOW OPEN TABLES WHERE in_use > 0;
2. SHOW [FULL] PROCESSLIST;
3. KILL <process id>;

相關文章

• 解決 MySQL 錯誤 1205: 鎖定等待超時，嘗試重新啟動事務

錯誤 1206: 鎖的總數量超過鎖表的大小

訊息：錯誤代碼：1206。鎖的總數量超過鎖表的大小

當前狀態：

• Keywin SHOW VARIABLES LIKE 'innodb_buffer_pool_size';。如果返回 8388608，意味著 8388608 位元組 ≅ 8MB。

解決方案：

• 增加 innodb_buffer_pool_size 例如 SET GLOBAL innodb_buffer_pool_size=402653184;（402653184 位元組 ~ 400MB。預設值是 8MB。）
• 減少查詢數據的大小

進一步閱讀：

• innodb - 如何在 Mac OS 上改變 MySQL 的 innodb_buffer_pool_size 的值？ - Stack Overflow。
• MySQL :: MySQL 5.7 參考手冊 :: 14.6.3.2 配置 InnoDB 緩衝池大小

PHP 嚴重錯誤：在使用 adminer 導入 SQL.GZ 文件時，允許的記憶體大小耗盡

解決方案：使用基本的 mysqldump 命令來生成備份文件。然後使用 mysql 命令導入備份。

資料庫所在硬碟空間不足的狀況

Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況

其他工具特定錯誤

Navicat 錯誤：[Exp] OLE 錯誤 800A03EC

錯誤訊息：

[Msg] [Exp] 導出到 - test.xlsx
[Err] [Row1048576] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048577] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048578] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048579] [Exp] OLE 錯誤 800A03EC

原因：

• 結果的行數超過了 Microsoft Excel 工作表大小的限制：1,048,576 行

解決方案：

• 限制 MySQL 查詢的行數

Navicat error: Field doesn't have a default value

錯誤：

[ERR] 1364 - Field 'source' doesn't have a default value

解決方案：

• 從 Excel 匯入到資料庫時，source 欄位是空值或者沒有指定，需要指定 source 欄位值。或者修改欄位定義，允許欄位值可以 NULL。

Navicat 錯誤: 導入數據筆數不一致

錯誤：

使用 Navicat 將 Excel 文件導入 MySQL 資料庫時，出現總記錄筆數不一致的問題。解決方案：刪除空白行並檢查任何重複的主鍵數據。

解決方案：

• 解決 NaviCat 匯入 Excel 檔案到資料庫，出現資料總筆數不一致的問題 (written in Mandarin)

如何解決 “zsh: operation not permitted: xxx.sql”

錯誤情況

/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

zsh: operation not permitted: PATH_TO_SQL_FILE.sql

解決方案：檢查安全與隱私設定：macOS 可能因安全設定而阻止終端機執行某些操作。你可以嘗試允許終端機完整磁碟存取：

• 開啟系統偏好設定。
• 轉到安全與隱私。
• 選擇隱私權標籤。
• 捲動至完整磁碟存取。
• 點擊鎖定圖示以進行更改（可能需要你的管理員密碼）。
• 尋找終端機應用程式或 iTerm2 或你正在使用的任何終端機，勾選允許完整磁碟存取的方框。
• 重啟你的終端機。

延伸閱讀

• 如果找不到想要的問題答案，可以前往stackoverflow 尋找 Unanswered 'mysql' Questions - Stack Overflow

References

資料工廠流程

• 🔍 資料收集：1. 從網站擷取內容
• 🧹 資料清理：2. 資料清理，3. 正則表達式
• ⚙️ 資料處理：4. JSON 編碼與解碼，5. 日期與 Unix 時間戳轉換，6. MySQL 指令
• 📊 資料分析：7. 資料探索
• 📤 資料輸出：8. 匯出 MySQL 查詢至 Excel 檔案，9. Microsoft Excel
• 🔧 故障排除：10. 排除 MySQL 技術疑難問題