Editing 排除 MySQL 技術疑難問題 (section)

== 連接和網路錯誤 ==
=== MySQL 伺服器已斷開 (has gone away) ===
步驟
* 在 MySQL 設定檔啟用 {{kbd | key = log_error}} 選項，例如 {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>。
* 範例錯誤日誌位於 {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} 如下：

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

* 如果 MySQL 用戶具有 {{kbd | key = <nowiki>SUPER</nowiki>}} 權限<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>，則增加 {{kbd | key = <nowiki>max_allowed_packet</nowiki>}} 的值<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref>。

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

解決方案：
* 將資料庫主機從 {{kbd | key=localhost}} 修改為 {{kbd | key=127.0.0.1}}

=== 錯誤 2002: 無法連接: SQLSTATE[HY000] [2002] ===
Windows Cygwin 終端機狀況：
<pre>
$ 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)
</pre>

解決方案：
* 將 {{kbd | key=<nowiki>-h localhost</nowiki>}} 更改為 {{kbd | key=<nowiki>-h 127.0.0.1</nowiki>}}
<pre>
$ mysql -h 127.0.0.1 -u root -p
</pre>
* 如果仍然不行，重啟伺服器並重新啟動 MySQL 服務。

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

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

解決方案：[https://stackoverflow.com/questions/10317974/mysql-php-error2002-only-one-usage-of-each-socket-address-protocol-network-a MySQL/PHP 錯誤:(2002) 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 - Stack Overflow]

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

解決方案：
* 檢查 IP 是否有效

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

解決方案：
* 檢查 MySQL 服務是否在運行<ref>[https://www.cyberciti.biz/faq/how-to-find-out-if-mysql-is-running-on-linux/ 如何找出 MySQL 是否在 Linux 上運行]</ref>。如果沒有，啟動 MySQL 服務。
* 檢查防火牆規則

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

解決方案：
* 檢查 MySQL 的配置
** 註釋掉 [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address bind-address] = 127.0.0.1 或設置為 *
** 註釋掉 [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-networking skip-networking]
* 檢查 MySQL 資料庫用戶的權限。
* 在 (1) 個人電腦 (2) MySQL 服務所在的伺服器 (3) ISP/VM 供應商處檢查防火牆規則。更多資訊請參考 [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules 在 CentOS 7 上安裝 MySQL | ProfitBricks DevOps Central]
** 檢查您的 IP 地址是否包含在允許的 IP 地址列表中的防火牆規則。
** 透過使用 [https://whatismyipaddress.com/ 我的 IP 地址是什麼?] 服務檢查您的 IP 地址是否有更改。
* (可選) 監控防火牆活動。更多資訊請參考 [https://www.howtogeek.com/220204/how-to-track-firewall-activity-with-the-windows-firewall-log/ 如何使用 Windows 防火牆日誌追蹤防火牆活動] 在 {{Win}}

參考資料：
* [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ 遠程連接到 MySQL 資料庫]
* [http://wiki.navicat.com/wiki/index.php/Error_2003 錯誤 2003 - Navicat Wiki]
* [http://faq.webyog.com/content/23/15/en/error-no-2003-can_t-connect.html SQLyog MySQL 管理員常見問答 - 錯誤號 2003: 無法連接...]

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

情況：在執行包含超過 1,000,000 行的下列查詢後，我遇到了 '錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接' 的錯誤訊息。
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;
</pre>

解決方案：
* 在 [https://www.mysql.com/products/workbench/ MySQL Workbench] 上增加 (1) '''資料庫連接保持活躍間隔（秒）''' 和 (2) '''資料庫連接讀取超時（秒）''' 的設定 <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench 如何在 MySQL Workbench 上執行超過 99,999 秒的 SQL 查詢 - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query 錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接 - Stack Overflow]</ref>。修改設定後請重新啟動 MySQL Workbench。例如，預設的 '''資料庫連接讀取超時（秒）''' 設定為 30 秒，你可以增加到 6000 秒（100 分鐘）。
* 減少行數以縮短執行時間 (1) 使用 {{kbd | key=LIMIT}} 子句 (2) 或分割查詢大小，例如 {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} 和 {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}}，如果欄位是數字的話。
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;
</pre>

=== 解決 Host 'xx.xx.xxx.xxx' is blocked because of many connection errors ===

錯誤訊息

<pre>
Host 'xx.xx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
</pre>

了解問題狀況

(1) 查詢門檻值

MySQL 的機制是：同一個來源主機（依 IP 或 hostname 判斷），累積達到 `max_connect_errors` 設定的次數後，就會自動把這個來源封鎖，拒絕它繼續連線。

<pre>
SHOW VARIABLES LIKE 'max_connect_errors';
</pre>

查到的門檻值是 100。

(2) 查詢目前被記錄/封鎖的 host

<pre>
SELECT IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS
FROM performance_schema.host_cache
ORDER BY SUM_CONNECT_ERRORS DESC;
</pre>

確認唯一被鎖的 IP 就是 `xx.xx.xxx.xxx`，`SUM_CONNECT_ERRORS` 剛好等於門檻值 100，且沒有其他 IP 受影響。


解決問題

⚠️ 提醒：`flush-hosts` / `FLUSH HOSTS` 會清空整個 host cache，不是只清單一 IP。如果查詢結果中還有其他 host 的記錄，執行後會一併重置。若為共用正式環境，執行前建議先跟其他管理者確認。

(1) 到終端機或命令提示字元，輸入指令

<pre>
mysqladmin -u root -p flush-hosts
</pre>

或

<pre>
mysql -u root -p
進入 MySQL SHELL 後再輸入指令：FLUSH HOSTS;
</pre>

補充：`FLUSH HOSTS` 語法自 MySQL 8.0.23 起已標示為 deprecated，8.4 版本中移除<ref>[https://dev.mysql.com/doc/refman/8.4/en/performance-schema-host-cache-table.html MySQL :: MySQL 8.4 Reference Manual :: 29.12.22.3 The host_cache Table]</ref><ref>[https://oneuptime.com/blog/post/2026-03-31-mysql-fix-max-connect-errors-blocked-hosts/view How to Fix max_connect_errors and Blocked Hosts in MySQL]</ref>。若環境版本較新，可改用官方建議的替代語法：
<pre>
TRUNCATE TABLE performance_schema.host_cache;
</pre>

(2) 執行後驗證

<pre>
SELECT IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS
FROM performance_schema.host_cache
ORDER BY SUM_CONNECT_ERRORS DESC;
</pre>

結果：沒有資料行（0 rows），代表 host_cache 已清空，先前的連線錯誤紀錄與封鎖狀態都已重設，該 IP 應可正常連線。

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

解決方案:
* (手動方式) 匯出 MySQL 資料為 MSSQL 相容格式: {{kbd | key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html#option_mysqldump_compatible MySQL :: MySQL 8.4 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program]</ref>
* (自動方式) 使用 [https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver17 Microsoft SQL Server Migration Assistant]、''$'' [https://www.webyog.com/product/sqlyog SQLyog] 工具 (適用於 {{Win}})。設定特定查詢來控制要從 MySQL 轉移至 Microsoft SQL Server 的資料。

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