MySQL 常見錯誤代碼與解決方案完整手冊

連線與通訊錯誤

1. "MySQL server has gone away" / "Lost connection to server during query"

原因： 伺服器超時關閉閒置連線 (預設 8 小時，由 wait_timeout 控制)、查詢封包過大 (超過 max_allowed_packet) 或封包錯誤。

解決：

• 調大 wait_timeout 或在客戶端實作自動重連機制。
• 啟動參數調大 max_allowed_packet (如 mysqld --max_allowed_packet=16M)。
• 檢查查詢語法是否正確。

2. "Can't connect to [local] MySQL server" (錯誤 2002)

原因： 服務未啟動、Socket 檔案路徑錯誤/遺失、TCP/IP 埠號不符、主機名解析問題、Linux 核心崩潰導致執行緒殭屍。

排查步驟：

1. 確認 mysqld 進程存在 (ps aux | grep mysqld)。
2. 嘗試多種連線方式測試：
   mysqladmin version
mysqladmin -h `hostname` version
mysqladmin --socket=/tmp/mysql.sock version
3. 檢查 /tmp/mysql.sock 是否被 cron 任務誤刪，若是請修改 my.cnf 指定新路徑：
   [mysqld] socket=/var/lib/mysql/mysql.sock
[client] socket=/var/lib/mysql/mysql.sock
4. 遠端連線失敗請檢查防火牆、skip-networking 參數、bind-address 設定。

3. "Host '...' is blocked because of many connection errors"

原因： 短時間內大量連線失敗 (預設 10 次)，觸發 max_connect_errors 阻擋機制。

解決： 執行 mysqladmin flush-hosts 清除快取，並檢查網路穩定性。可調大 max_connect_errors (如 10000)。

4. "Too many connections"

原因： 連線數達上限 (預設 max_connections=100，實際允許 101，保留 1 給 SUPER 權限)。

解決： 修改 my.cnf 增加 max_connections，並優化程式連線池釋放機制。

資源與記憶體錯誤

5. "Out of memory" (客戶端)

原因： 結果集過大超出客戶端記憶體。

解決： 優化查詢減少回傳列、使用 mysql --quick (採用 mysql_use_result 串流讀取)。

6. "Packet too large"

解決： 同錯誤 1，調大客戶端與伺服器雙端的 max_allowed_packet。

7. "The table is full" (記憶體臨時表)

解決： 增加 tmp_table_size、max_heap_table_size 或查詢前設定 SET SQL_BIG_TABLES=1 強制使用磁碟臨時表。

權限與帳號錯誤

8. "Commands out of sync"

原因： API 呼叫順序錯誤 (如未 mysql_free_result 就發新查詢、或在 mysql_use_result 間發兩條回傳資料的查詢)。

9. "Ignoring user" / "Found wrong password for user"

原因： user 表密碼欄位長度不符 (舊版 16 字元 vs 新版 41 字元)、使用舊式密碼但未啟 --old-protocol、未用 PASSWORD() 函數加密就直接寫入密碼。

修正： 執行 scripts/add_long_password、用 PASSWORD() 更新密碼、或啟用 --old-protocol。

資料表與查詢邏輯錯誤

10. "Table 'xxx' doesn't exist"

注意： Linux 區分大小寫，Windows 不區分但查詢中大小寫必須一致。用 SHOW TABLES 確認實際名稱。

11. 日期處理問題

• 標準格式：'YYYY-MM-DD'。
• 數字語境下可自動轉換 (如 19970505)。
• 寬鬆格式允許任意標點分隔 ('1998#08#15')。
• 陷阱： STRCMP(date_col, '19970505') 會進行字串比較而非日期比較，請用 date_col = '1997-05-05'。
• MySQL 不驗證日期合法性 (如 2 月 31 日會存入 0)，驗證責任在應用層。

12. 時區問題

設定作業系統 TZ 環境變數 (如 TZ=Asia/Taipei) 於 safe_mysqld 或系統啟動腳本中。

13. 大小寫敏感度

• 預設不區分 (非二進位排序規則)。
• 需區分時：使用 BINARY 欄位屬性、STRCMP()、或 COLLATE utf8_bin。
• 中文 Big5 編碼建議全表採用 BINARY。

14. NULL 值陷阱

• NULL != '' (空字串)。NULL 代表「未知」，空字串代表「無」。
• 任何與 NULL 運算結果皆為 NULL。
• 查詢 NULL 必須用 IS NULL / IS NOT NULL，= NULL 永遠為假。
• 索引欄位不可為 NULL (須 NOT NULL)。
• ORDER BY 時 NULL 排最前，DESC 排最後。

15. 別名 限制

GROUP BY、ORDER BY、HAVING 可用別名；WHERE 不可用 別名 (執行順序關係)。

16. 多表刪除關聯資料

MySQL 不支援 DELETE ... JOIN 或子查詢刪除 (舊版)。建議流程：

1. 主表 SELECT 符合條件的 PK。
2. 主表 DELETE 同條件。
3. 從表 DELETE FROM related WHERE fk IN (PK列表)。
4. 若 PK 列表過大 (>1MB)，分批刪除；若 FK 有索引，每次刪 100-1000 筆最快。

17. 複雜查詢無結果排查

1. EXPLAIN 分析執行計畫。
2. 逐步移除 JOIN 表直到出現資料。
3. 檢查 FLOAT/DOUBLE 比較精度問題 (用 BETWEEN 或改 DECIMAL)。
4. 製作最小可重現測試案例 (mysqldump --quick + 手動加入查詢) 回報官方。

18. ALTER TABLE 失敗殘留檔案

錯誤 Error on rename ... Errcode: 17 通常因前次 ALTER 中斷留下 A-xxx、B-xxx 暫存檔。進資料目錄刪除這些檔案即可恢復。

19. 欄位順序調整

SQL 標準不保證 SELECT * 回傳順序，應明確列出欄位。若必須調整儲存順序：建新表 → INSERT SELECT → 刪舊表 → 改名。

系統層面問題

20. 磁碟空間不足

MySQL 每分鐘檢查一次，每 6 分鐘記錄警告。釋放空間後自動繼續；或 mysqladmin kill 終止等待執行緒釋放鎖定。

21. 從文字檔執行 SQL

mysql 資料庫 < script.sql
mysql < script.sql  (腳本內含 USE db_name)

22. 臨時檔案位置

由 TMPDIR 環境變數決定 (預設 /tmp 或 /usr/tmp)。可在 safe_mysqld 或 my.cnf 設定 tmpdir。排序時臨時空間需求約 (欄位長度+指標大小) * 符合列數 * 2。

23. 保護 /tmp/mysql.sock

設定 /tmp 目錄 sticky bit：chmod +t /tmp，僅擁有者與 root 可刪除檔案。

24. Access Denied 權限問題

參考官方手冊「權限系統運作機制」與「Access Denied 成因」章節。

25. 以一般用戶身分執行 mysqld

1. 停止服務。
2. chown -R mysql_user:mysql_group /path/to/datadir (注意符號連結需手動追蹤)。
3. 啟動時指定 --user=mysql_user (啟動腳本或 su - mysql_user -c "mysqld &")。
4. 權限表內容不變，仍以 MySQL root 管理。

26. 重設遺忘的 Root 密碼

1. kill `cat /datadir/hostname.pid` (勿用 -9)。
2. mysqld --skip-grant-tables &。
3. mysql -u root mysql 進入無密碼模式。
4. UPDATE user SET password=PASSWORD('新密碼') WHERE user='root'; FLUSH PRIVILEGES;。
5. 正常重啟服務。

27. 檔案權限 (Errcode 13)

啟動前設定 UMASK=384 (八進位 600) 匯出環境變數再啟 safe_mysqld。

28. 檔案描述符不足 (errno 23/24)

perror 23/24 確認。調小 table_cache (預設 64)、max_connections；或修改 safe_mysqld 中 ulimit -n 256 (需 OS 支援，tcsh 不支援 ulimit 需用 sh 執行)。