1. 問題背景

最近部署了一套測試環(huán)境，同事發(fā)現(xiàn)以前遺留的一個存儲過程未運行，需要我?guī)兔ε挪橄?。下面說一下我的排查過程。列出每種可能得原因以及考慮。

2. 問題排查

2.1. 數(shù)據(jù)庫版本升級的問題

之前我們用的是 8.0.x 版本的 MySQL，最近運維將數(shù)據(jù)庫升級到了 8.4.3 版本。剛開始懷疑是數(shù)據(jù)庫升級導致的，查了相關的版本說明，并沒有發(fā)現(xiàn)會導致存儲過程不執(zhí)行的問題。

2.2. 用戶權限不足的問題

檢查了存儲過程中定義的用戶為root@%，并查看了權限，發(fā)現(xiàn)沒有問題。

這里記錄下涉及到的查詢：

用戶權限查詢

確保執(zhí)行存儲過程的用戶有足夠的權限。你可以使用如下命令查看權限：

SHOW GRANTS FOR 'your_username'@'your_host';

并確保有 EXECUTE 和 ALTER ROUTINE 權限。如果沒有，你可以通過以下命令授予權限：

GRANT EXECUTE, ALTER ROUTINE ON your_database_name.* TO 'your_username'@'your_host';

查詢命令解析

SHOW GRANTS FOR `root`@`%`;

這條 SQL 命令在 MySQL 數(shù)據(jù)庫中用來展示用戶名為 root，且可以從任意主機（由%表示）連接到 MySQL 服務器的用戶的權限。

• root 用戶：root 是 MySQL 的默認超級用戶，擁有對數(shù)據(jù)庫的完全訪問權限，包括創(chuàng)建、修改、刪除數(shù)據(jù)庫和數(shù)據(jù)表，以及管理用戶權限等。
• % 的含義：%在 MySQL 中表示任意主機，即 root 用戶可以從任何主機連接到 MySQL 服務器。

如果在執(zhí)行 SHOW GRANTS FOR 'root'@'%'; 后發(fā)現(xiàn)結果中缺少 EXECUTE 權限，表示 root 用戶當前沒有被授予執(zhí)行存儲過程或函數(shù)的權限。EXECUTE 權限是 MySQL 中用于執(zhí)行存儲過程和存儲函數(shù)的權限。

可以使用 GRANT 語句為 root 用戶添加 EXECUTE 權限：

GRANT EXECUTE ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

這條命令做了以下幾件事：

• GRANT EXECUTE ON *.* TO 'root'@'%';：為 root 用戶授予在所有數(shù)據(jù)庫和所有表上的 EXECUTE 權限。*.* 表示所有數(shù)據(jù)庫和所有表，你也可以根據(jù)需要指定特定的數(shù)據(jù)庫或表。
• FLUSH PRIVILEGES;：刷新 MySQL 的權限緩存，使新的權限設置立即生效。

2.3. 存儲過程本身的問題

首先這個存儲過程在生產(chǎn)環(huán)境還有其他環(huán)境都是可以正常運行的。因為是復制過來的，所以一開始我就沒想到這個錯誤。但是排查到這里后，還是使用CALL()去手動調(diào)度了一下該存儲過程，竟然執(zhí)行報錯了，報錯如下：

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Newton.imsi_transaction_cdr_raw_cn.carrier_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

這下感覺終于有眉目了，這明顯是 SQL 模式導致的問題。查看了下存儲過程的語句，里面還真有GROUP BY，并且不符合ONLY_FULL_GROUP_BY模式的要求。存儲過程未執(zhí)行的原因找到了，我想著接下來去除掉該 SQL 模式應該就能解決問題了。

我趕緊去查了一下當前的全局 SQL 模式和當前會話 SQL 模式，傻眼了，SQL 模式中并沒有ONLY_FULL_GROUP_BY模式，這時我想起來了，當時環(huán)境部署完后，我就要求運維將該ONLY_FULL_GROUP_BY模式去掉了，那為啥現(xiàn)在還會報這種錯誤，不應該啊，想不明白。

這里記錄下 MySQL 查詢 SQL 模式并去除 ONLY_FULL_GROUP_BY 的語句：

查詢?nèi)?SQL 模式，全局SQL模式影響所有新的會話（連接），但不影響已經(jīng)存在的會話

SELECT @@GLOBAL.sql_mode;

查詢當前會話SQL模式，當前會話 SQL 模式僅影響當前連接‌

SELECT @@SESSION.sql_mode;

或者，更簡潔地：

SELECT @@sql_mode;

臨時去除 ONLY_FULL_GROUP_BY（僅影響當前會話）

使用SET SESSION語句來更改當前會話的 SQL 模式：

SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '');

永久去除 ONLY_FULL_GROUP_BY（影響所有新會話）

要永久更改全局 SQL 模式，需要編輯 MySQL 的配置文件（如my.cnf或my.ini），然后重啟 MySQL 服務。但是，直接修改全局 SQL 模式可能會影響其他用戶和應用，因此通常建議只在必要時進行此類更改。

操作步驟：

• 打開MySQL配置文件。
• 找到[mysqld]部分。
• 修改或添加sql_mode行，確保不包含ONLY_FULL_GROUP_BY。
• 保存文件并重啟 MySQL 服務。

例如，配置文件中的設置可能如下所示：

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,...(其他模式，但不包括ONLY_FULL_GROUP_BY)"

3. 問題解決

目前情況是 MySQL 8.4.3 版本，我明明之前已經(jīng)去掉了 ONLY_FULL_GROUP_BY 模式，為什么調(diào)用存儲的時候還會報 1055 的錯誤？

錯誤提示很明顯，肯定是 ONLY_FULL_GROUP_BY 模式導致的，現(xiàn)在就是要找到為什么明明沒有該模式，怎么還會報這個錯誤。

在網(wǎng)上查資料的過程中，就在百思不解的時候，不經(jīng)意看到了一處說明：

‌存儲過程內(nèi)部的 sql_mode‌：存儲過程在創(chuàng)建時可能會捕獲當前的 sql_mode 設置，并在每次執(zhí)行時使用該設置。如果存儲過程是在 ONLY_FULL_GROUP_BY 模式啟用時創(chuàng)建的，那么即使你在之后禁用了該模式，存儲過程內(nèi)部仍然可能使用舊的 sql_mode。要解決這個問題，需要重新創(chuàng)建存儲過程，確保在創(chuàng)建時 ONLY_FULL_GROUP_BY 模式是禁用的。

好吧，問題明朗了，當時是這樣的，運維那邊創(chuàng)建好數(shù)據(jù)庫后，我進行庫表和存儲過程的初始化，服務部署后，通過服務日志發(fā)現(xiàn)有GROUP BY報錯，才找的運維去掉了 ONLY_FULL_GROUP_BY 模式，也就是說存儲過程創(chuàng)建的時候，數(shù)據(jù)庫的 SQL 模式中是含有 ONLY_FULL_GROUP_BY 模式的。所以存儲過程內(nèi)部的 sql_mode‌ 肯定也是含有 ONLY_FULL_GROUP_BY 模式的，所以會報 1055 錯誤。

那解決辦法就很簡單了，刪除掉當前的存儲過程，重新創(chuàng)建即可，我試了下，重新創(chuàng)建后，手動調(diào)用了下，果然正常執(zhí)行了。至此，問題解決！

以上就是MySQL存儲過程未執(zhí)行的問題排查過程的詳細內(nèi)容，更多關于MySQL存儲過程未執(zhí)行的資料請關注腳本之家其它相關文章！

最新評論