MySQL sql_mode修改不生效的原因及解決
前言
近期多次聊到sql_mode的話題,也是多次遇到相關問題,今天就趁熱打鐵,再給大傢帶來一個sql_mode的案例分享。
場景模擬
基於業務敏感性的考慮,下面涉及的表、存儲過程等均非真實數據,但並不影響排查過程。
(1)客戶側開發童鞋創建瞭一個存儲過程,該存儲過程沒有嚴格遵守group by標準語法
session 1: mysql> delimiter // mysql> create procedure test_for_group_by() -> begin -> select k,pad,count(*) from test.test group by k; -> end // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ;
(2)客戶側開發童鞋調用該存儲過程,報錯ERROR 1140;因為當時存儲過程比較復雜,改造起來比較麻煩,所以客戶側選擇修改sql_mode
session 1: mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
(3)客戶側修改完sql_mode,再次執行,發現仍然報錯ERROR 1140
session 2: mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) session 1: mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
(4)此時想到,修改系統變量,隻對新建連接有效,對已有連接不起作用;於是,讓客戶側重新建立連接,確認系統變量已生效,再次調用存儲過程,但仍然報錯ERROR 1140,重復嘗試幾次都是這個結果
session 3: mysql> show variables like 'sql_mode'; +---------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> call test_for_group_by(); ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by
(5)進一步排查,讓客戶側在該會話,執行非標準的group by語句,發現可以正常執行
session 3: mysql> select user,host,count(*) From mysql.user group by user; +---------------+-----------+----------+ | user | host | count(*) | +---------------+-----------+----------+ | mysql.session | localhost | 1 | | mysql.sys | localhost | 1 | | root | localhost | 1 | | rpl_user | % | 1 | | test | % | 1 | +---------------+-----------+----------+ 5 rows in set (0.00 sec)
(6)繼續排查發現,該存儲過程的sql_mode,還是包括ONLY_FULL_GROUP_BY,因此執行報錯
session 2: mysql> select routine_catalog,routine_schema,routine_name,routine_type,created,last_altered,sql_mode from routines where routine_name='test_for_group_by'; +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | routine_catalog | routine_schema | routine_name | routine_type | created | last_altered | sql_mode | +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | def | test | test_for_group_by | PROCEDURE | 2020-12-24 12:12:10 | 2020-12-24 12:12:10 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
(7)這裡我們也可以知道,系統變量修改隻對新建對象有效,對已有對象不生效;解決辦法很簡單,重建該存儲過程即可
session 3: mysql> drop procedure test_for_group_by; Query OK, 0 rows affected (0.01 sec) mysql> delimiter // mysql> create procedure test_for_group_by() -> begin -> select k,pad,count(*) from test.test group by k; -> end // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> call test_for_group_by(); +--------+-------------------------------------------------------------+----------+ | k | pad | count(*) | +--------+-------------------------------------------------------------+----------+ | 393975 | 35227182905-15234265621-59793845249-15413569710-23749555118 | 1 | | 495688 | 09512147864-77936258834-40901700703-13541171421-15205431759 | 1 | | 497896 | 13152283289-69561545685-52868757241-04245213425-69280254356 | 1 | | 498573 | 43131080328-59298106536-35954612339-97546855884-75769514803 | 1 | | 500775 | 27590239742-20204899609-34345212327-79811525340-24267764271 | 1 | | 501885 | 63188288836-92351140030-06390587585-66802097351-49282961843 | 1 | | 503330 | 01495266405-82925129145-92643983850-90243995398-18709399387 | 1 | | 503666 | 40929980986-33813039690-13155419391-97985458477-39771362212 | 1 | | 504353 | 00505722282-72931248925-57037623248-81117963809-88658076981 | 1 | | 514246 | 21979564480-87492594656-60524686334-78820761788-57684966682 | 1 | +--------+-------------------------------------------------------------+----------+ 10 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
總結
通過這個案例,我們可以知道,修改sql_mode系統變量,隻對新建連接和新建對象(主要包括函數和存儲過程)有效,對已有連接和已有對象不生效。
以上就是MySQL sql_mode修改不生效的原因及解決的詳細內容,更多關於MySQL sql_mode修改不生效的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- MySQL 5.7之關於SQL_MODE的設置
- sql模式設置引起的問題解決辦法
- mysql中整數數據類型tinyint詳解
- MySQL運行報錯:“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解決方法
- MySQL sql_mode的使用詳解