MySQL資料連結常見問題

1.1 版本

1.2 應用場景

本文介紹 MySQL資料連結 中，常見的問題及排查步驟。

問題描述：

MySQL資料集預覽時，資料亂碼，日期錯亂。

2.1 檢查資料庫字元集

原因分析：

MySQL 的字元集支援有兩個方面：字元集(Character set)和排序方式(Collation)。

對於字元集的支援細化到四個層次：伺服器(Server)， 資料庫(Database)， 資料表(Table)， 連結(Connection)。

預設情況下，MySQL 的字元集是 latin1(ISO_8859_1)，為了防止出現資料亂碼現象，MySQL 字元集應與平台保持一致，字元集(Character set)為 utf8，排序方式(Collation)為 utf8_general_ci

排查步驟：

透過以下兩條命令檢查 MySQL 的字元集支援：

1）查看字元集：SHOW VARIABLES LIKE 'character%';

2）查看排序方式：SHOW VARIABLES LIKE 'collation_%';

解決方案：

修改 MySQL 的 my.ini 檔案中的字元集鍵值。

default-character-set = utf8 character_set_server = utf8

修改完後，重啟 MySQL 的服務。

service mysql restart

使用命令查看，發現資料庫編碼均已改成 UTF-8。

mysql> SHOW VARIABLES LIKE 'character%';

2.2 修改資料連結URL

修改資料連結的資訊。

1）資料連結的「編碼」類型設定為「預設」。

2）資料連結的「資料連結URL」後加後綴，如下圖所示。格式為：

jdbc:mysql://hostname:port/database?generateSimpleParameterMetadata=true&useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai

其中：

• serverTimezone=Asia/Shanghai：設定以"上海時區"為準
  

• characterEncoding=utf8：編碼轉化

2.3 檢查伺服器字體

原因分析：

系統未安裝中文字體，所以只能亂碼

排查步驟：

• Windows系統：查看C:\WINDOWS\Fonts資料夾下的字體。

• Linux系統：檢查/usr/share/fonts資料夾下的字體。

解決方案：

伺服器字體安裝請參見：伺服器安裝字體。

問題描述：

MySQL資料連結測試連結時，報錯「No suitable driver found for localhost」。

3.1 URL格式錯誤

原因分析：

檢查MySQL的資料連結URL格式是否為jdbc:mysql://<ip>:<port>/，若格式錯誤，則有可能出現該報錯。

解決方案：

請參考 MySQL資料連結 修改資料連結URL格式。

3.2 驅動版本不匹配

原因分析：

工程中使用的MySQL驅動版本，與資料連結的MySQL資料庫版本不匹配。

解決方案：

請參考 MySQL資料連結 獲取匹配版本的驅動聯集傳到工程中。

問題描述：

MySQL資料連結測試連結時，報錯「Unknown system variable 'query_cache_size'」。

原因分析：

工程中使用的MySQL驅動版本，與資料連結的MySQL資料庫版本不匹配。

query_cache_size參數在MySQL 8中已經移除，它存在於5.1.44版本驅動中。

解決方案：

請參考 MySQL資料連結 獲取匹配版本的驅動聯集傳到工程中。

問題描述：

MySQL資料連結測試連結時，報錯「Access denied for user」。

原因分析：

資料庫伺服器拒絕了來源於這個IP的這個使用者。

解決方案：

請排查資料連結中輸入帳號和密碼是否正確。

請排查資料庫所在伺服器是否禁止工程所在伺服器IP存取，請為工程所在伺服器IP開啟相關權限。

問題描述：

MySQL資料連結測試連結時，報錯「The server time zone value 'XXX' is unrecognized」。

原因分析：

伺服器的時區無法被識別或者代表不止一個時區，表示無法確定時區，需要指定。

解決方案：

修改資料連結的資訊。資料連結的「資料連結URL」後加上時區參數。

格式為：&serverTimezone=UTC

問題描述：

MySQL資料連結測試連結時，報錯「Establishing SSL connection without server's identity verification is not recommended」

MySQL資料連結測試連結時，報錯「Unsupported record version Unknown-0.0」

原因分析：

Mysql資料庫的SSL連結問題，提示警告不建議使用沒有帶伺服器身分驗證的SSL連結。

解決方案：

修改資料連結的資訊。資料連結的「資料連結URL」後加上ssl使用參數。

格式為：&useSSL=false

問題描述：

資料預覽時報錯「can not be represented as java.sql.Date」

解決方案：

修改資料連結的資訊。資料連結的「資料連結URL」後加上參數。

格式為：zeroDateTimeBehavior=convertToNull

問題描述：

資料預覽時資料集出錯，日誌報錯「wait millis 20000, active 0, maxActive 100」。重新連結即可恢復。

解決方案：

修改資料連結的資訊。資料連結的「資料連結URL」後加上參數。可根據SQL查詢耗時，調大socketTimeout值。

格式為：&connectTimeout=5000&socketTimeout=5000

問題描述：

資料預覽時資料集出錯，報錯last packet sent to the server was 9 ms ago

原因分析：

MySQL 連結時間限制

解決方案：

去掉 MySQL 連結的 url 後面的 ?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false 這些參數

問題描述：

資料預覽時資料集出錯，報錯:

錯誤程式碼:11300001 資料集配置錯誤 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '資料庫名.表名.欄位名' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因分析：

MySQL 資料庫 5.7.5 之後的版本預設開啟 only_full_group_by 模式。

sql_mode 的 only_full_group_by 模式開啟，會導致一些不規範的 SQL語 法不再被相容，進而出現報錯。

注：only_full_group_by 模式關閉，其實是比較冒險的一種設定，因為在這種設定下可以允許一些非法操作。

排查步驟：

1）檢查 MySQL 資料庫版本是否大於 5.7.5 。

SELECT VERSION();

2）檢查 sql_mode 是否開啟了 only_full_group_by 模式。

select @@GLOBAL.sql_mode;

解決方案1：在 SQL 查詢語句中不需要 group by 的欄位上使用 any_value() 函式。any_value(field) 函式允許非分組欄位的出現。

解決方案2：透過 SQL 語句暫時性修改 sql_mode 值。

1）修改全局 sql_mode，只對建立資料庫生效。

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

2）對於已存在的資料庫，則需要在對應的資料庫下執行：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

解決方案3：透過配置檔案永久修改 sql_mode 值。

• Windows系統：修改 my.ini 配置檔案，在「mysqld」標籤下追加內容。

[mysqld]   
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• Linux系統：編輯 my.cnf 配置檔案，找到 sql_mode 的位置，刪掉 only_full_group_by 。如果 my.cnf 檔案中不存在 sql_mode，在「mysqld」標籤下追加內容。

[mysqld]
sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

修改完後，重啟 MySQL 服務。

service mysql restart

問題描述：

透過proxy sql代理連結MySQL8.0報錯Unknown system variable 'query_cache_size'

原因分析：

proxy sql最新內建mysql版本是5的，所以需要修改它內建MySQL版本才能連結。

解決方案：

查看proxy sql用的驅動版本，並改成對應版本的驅動:

查詢版本語句：select * from global_variables where variable_name='mysql-server_version';

問題描述：

使用MySQL資料庫的資料集查詢，嵌入客戶自己的平台後，報錯：This application has no explicit mapping for /error, so you are seeing this as a fallback.There was an unexpected error (type=Internal Server Error, status=500).syntax error, error in :' 1=1 ${if(len(year) == 0,""," and D',expect VARIANT, actual VARIANT ${if(len(year) == 0,""," and DATE_FORMAT(tda.registerDate,'%Y') = '" +year+ "'")}

原因分析：

檢查到資料集查詢語句中有，使用${if()}函式進行參數設定，但是MySQL中沒有這個文法

解決方案：

換成and IF('${year}' is null or '${year}' = '', 0 = 0, t.registerDate = '${year}')   

問題描述：

MySQL 資料連結測試連結時，報錯Unable to load authentication plugin 'caching_ sha2_password'

原因分析：

不同版本 MySQL 的認證插件不同造成的連結失敗。

MySQL 8.0.4 開始，MySQL 伺服器的默認身分驗證插件從 mysql_native_password 更改為 caching_sha2_password，如果驅動版本過低，則無法使用 MySQL 的身分驗證插件。

排查步驟：

檢查 MySQL 資料庫版本是否高於 8.0.4，同時檢查驅動版本是否是 5.X 版本，如果是則驅動版本過低。

解決方案：

從 MySQL官網 下載 MySQL 8.X 版本的驅動，並將其上傳至 FineReport，如何上傳可參見：驅動管理 2.1 節。

問題描述：

MySQL 資料連結測試連結時，報錯message from server: "Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

原因分析：

同一 IP 在短時間內連結 MySQL 資料庫失敗次數超過 max_connection_errors 參數設定值（MySQL 預設值為 10），進而被拒絕連結。

排查步驟：

檢查 max_connect_errors 的值是否過小：

show global variables like '%max_connect_errors%';

解決方案：

1）使用者可以根據業務需求調整 max_connect_errors 的值，比如設定為 1000 ：

set global max_connect_errors=1000;

2）查看是否修改成功：

show variables like '%max_connection_errors%';

3）進入 MySQL 控制台，清理 hosts 檔案：

flush hosts;

問題描述：

MySQL 資料連結測試連結時，報錯message from server: "Host 'xxx.xxx.xxxx.xxx' is not allowed to connect to this MySQL server

原因分析：

資料庫未允許來自該 IP 的用戶端主機的存取。

排查步驟：

進入 MySQL 資料庫執行：

select host from mysql.user where user ='帳號';

如果顯示值為 localhost，說明該資料庫只允許 localhost 存取，需要開放其他 host 來源。

解決方案1：開放來自其他 host 的存取。

GRANT ALL PRIVILEGES ON *.* TO '帳號'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

解決方案2：設定 host 為任意。

update mysql.user set host='%' where user = '帳號';
FLUSH PRIVILEGES;

問題描述：

資料更新失敗，日誌報錯

錯誤程式碼:62400001 Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.

原因分析：

MySQL 資料庫寫逾時時間配置過短，預設為60s，當連結持續寫入超過設定值時會被 MySQL 伺服器斷開 。

排查步驟：

檢查 net_write_timeout 的值是否過小：

show global variables like '%timeout%';

解決方案：

1）調整更新任務，減少任務量，避免長時間佔用連結 。

2）根據實際情況調整 net_write_timeout 參數值：

set global net_write_timeout = XXX;

問題描述：

MySQL 資料庫連結失敗，報錯com.mysql.jdbc.PacketTooBigException: Packet for query is too large ( 4739923 > 1948576). You can change this value on the server by setting the max_ allowed_ packet' variable

原因分析：

本地 MySQL 資料庫中「max_allowed_packet」值設定過小導致單個記錄超過限制後寫入資料庫失敗，且後續記錄寫入也會失敗。

解決方案1：

MySQL 安裝目錄下的「my.ini」檔案中的[mysqld] 欄位中的「max_allowed_packet = 1M」修改為 500M ，重啟 MySQL 即可。

解決方案2：

1）使用「set global max_allowed_packet = 524288000;」 語句將「max_allowed_packet」的值設定為 500 M。

2）使用「show VARIABLES like '%max_allowed_packet%';」 語句查看是否修改成功。