MySQL 資料連結常見錯誤解決方案

1.1 版本

1.2 應用場景

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

問題描述：

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

2）報表預覽時，從MySQL資料庫中取出的資料亂碼。

3）透過填報向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資料集，範本預覽時資料集出錯，日誌報錯「wait millis 10014, active 0, maxActive 50, creating 1, createElapseMillis 20028」

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

問題描述：

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

解決方案：

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

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

問題描述：

資料連結成功，查詢資料預覽資料集sql報錯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 這些參數

問題描述：

MySQL 資料集，範本預覽時資料集出錯，報錯:

錯誤程式碼: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

問題描述：

資料填報到MySQL資料庫，提交時報錯「incorrect string value:'xf0x9f」

原因分析：

1）提交的資料中帶有Emoji表情或者某些特殊字元，是4個位元組，而Mysql的utf8編碼最多3個位元組，所以資料插不進去。

2）資料連結驅動版本不匹配。

解決方案：

1）修改mysql的my.ini檔案，然後重啟mysql服務。

[mysqld]                                                                                                                                                                                                   
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4

2）確定資料連結驅動為說明文檔裏的版本。

問題描述：

資料填報空值到MySQL資料庫，提交成功，但開啟資料庫發現空值未改寫原有值。

原因分析：

MySQL 資料庫中 tinyint 類型欄位，欄位值只能是 0 或者 1，不允許為空。

下拉框如果不編輯或者只編輯不選，都不會提交空字串，但是如果編輯了一個非空選項再編輯不選，就會發生值變化，進而提交空字串到資料庫。

解決方案：

設定填報提交條件，將空字串轉 Null。

設計器菜單欄「範本>報表填報屬性」下，設定提交條件，綁定公式if(len(B2)==0,NULL,B2)即可。

問題描述：

透過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，如何上傳可參見：資料連結-驅動管理 。

問題描述：

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%';」 語句查看是否修改成功。

問題描述：

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;