MySQL存储过程未执行的问题排查过程

目录

• 1. 问题背景
• 2. 问题排查
• 2.1. 数据库版本升级的问题
• 2.2. 用户权限不足的问题
• 2.3. 存储过程本身的问题
• 3. 问题解决

1. 问题背景

最近部署了一套测试环境，同事发现以前遗BLNsC留的一个存储过程未运行，需要我帮忙排查下。下面说一下我的排查过程。列出每种可能得原因以及考虑。

2. 问题排查

2.1编程客栈. 数据库版本升级的问题

之前我们用的是 8.0.x 版本的 mysql，最近运维将数据库升级到了 8.4.3 版本。刚开始怀疑是数据库升级导致的，查了相关的版本说明，并没有发现会导致存储过程不执行的问题。

2.2. 用户权限不足的问题

检查了存储过程中定义的用户为root@%，并查看了权限，发现没有问题。

这里记录下涉及到的查询：

用户权限查询

确保执行存储过程的用户有足够的权限。你可以使用如下命令查看权限：

SHOW GRANTS FOR 'your_username'@'your_host';

并确保有 EXECUTE 和 ALTER Rwww.devze.comOUTINE 权限。如果没有，你可以通过以下命令授予权限：

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

查询命令解析

SHOW GRANTS FOR `root`@`%`;

这条 SQL 命令在 MySQL 数据库中用来展示用户名为 root，且可以从任意主机（由%表示）连接到 MySQL 服务器的用户的权限。

• root 用户：root 是 编程MySQL 的默认超级用户，拥有对数据库的完全访问权限，包括创建、修改、删除数据库和数据表，以及管理用户权限等。
• % 的含义：%在 MySQL 中表示任意主机，即 root 用户可以从任何主机连接到 MySQL 服务器。

如果在执行 SHOW GRANTS FOR 'root'@'%'; 后发现结果中缺少 EXECUTE 权限，表示 root 用户当前没有被授予执行存储过程或函数的权限。EXECUTE 权限是 MySQL 中用于执行存储过程和存储函数的权限。

可以使用 GRANT 语句为 root 用户添加 EXECUTE 权限：

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

这条命令做了以下几件事：

• GRANT EXECUTE ON *.* TO 'root'@'%';：为 root 用户授予在所有数据库和所有表上的 EXECUTE 权限。*.* 表示所有数据库和所有表，你也可以根据需要指定特定的数据库或表。
• FLUSH PRIVILEGES;：刷新 MySQL 的权限缓存，使新的权限设置立即生效。

2.3. 存储过程本身的问题

首先这个存储过程在生产环境还有其他环境都是可以正常运行的。因为是复制过来的，所以一开始我就没想到这个错误。但是排查到这里后，还是使用CALL()去手动调度了一下该存储过程，竟然执行报错了，报错如下：

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模式的要求。存储过程未执行的原因找到了，我想着接下来去除掉该 SQL 模式应该就能解决问题了。

我赶紧去查了一下当前的全局 SQL 模式和当前会话 SQL 模式，傻眼了，SQL 模式中并没有ONLY_FULL_GROUP_BY模式，这时我想起来了，当时环境部署完后，我就要求运维将该ONLY_FULL_GROUP_BY模式去掉了，那为啥现在还会报这种错误，不应该啊，想不明白。

这里记录下 MySQL 查询 SQL 模式并去除 ONLY_FULL_GROUP_BY 的语句：

查询全局 SQL 模式，全局SQL模式影响所有新的会话（连接），但不影响已经存在的会话

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,...(其他模式python，但不包括ONLY_FULL_GROUP_BY)"

3. 问题解决

目前情况是 MySQL 8.4.3 版本，我明明之前已经去掉了 ONLY_FULL_GROUP_BY 模式，为什么调用存储的时候还会报 1055 的错误？

错误提示很明显，肯定是 ONLY_FULL_GROUP_BY 模式导致的，现在就是要找到为什么明明没有该模式，怎么还会报这个错误。

在网上查资料的过程中，就在百思不解的时候，不经意看到了一处说明：

‌存储过程内部的 sql_mode‌：存储过程在创建时可能会捕获当前的 sql_mode 设置，并在每次执行时使用该设置。如果存储过程是在 ONLY_FULL_GROUP_BY 模式启用时创建的，那么即使你在之后禁用了该模式，存储过程内部仍然可能使用旧的 sql_mode。要解决这个问题，需要重新创建存储过程，确保在创建时 ONLY_FULL_GROUP_BY 模式是禁用的。

好吧，问题明朗了，当时是这样的，运维那边创建好数据库后，我进行库表和存储过程的初始化，服务部署后，通过服务日志发现有GROUP BY报错，才找的运维去掉了 ONLY_FULL_GROUP_BY 模式，也就是说存储过程创建的时候，数据库的 SQL 模式中是含有 ONLY_FULL_GROUP_BY 模式的。所以存储过程内部的 sql_mode‌ 肯定也是含有 ONLY_FULL_GROUP_BY 模式的，所以会报 1055 错误。

那解决办法就很简单了，删除掉当前的存储过程，重新创建即可，我试了下，重新创建后，手动调用了下，果然正常执行了。至此，问题解决！

以上就是MySQL存储过程未执行的问题排查过程的详细内容，更多关于MySQL存储过程未执行的资料请关注编程客栈(www.devze.com)其它相关文章！