记录一下今天的MySQL故障

Li_XiaoJin

发布于 2022-06-10 19:31:36

6500

发布于 2022-06-10 19:31:36

今天遇到一个问题，早上对MySQL进行限制IP访问的操作，限制其只能通过 192.168.137.% 这个网段进行访问。没想到马上就出问题了，加拿大网站购卡提交不了订单了。后来查证下来发现日志里，有数据库的报错。

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: execute command denied to user 'mvno-ca'@'localhost' for routine 'cmi_mvno_ca.mvnoNextval'
### The error may exist in URL [jar:file:/home/ca/mvno-ca/lib/mvno-common-business-1.0-SNAPSHOT.jar!/mapper/WebformSubmissionData.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT mvnoNextval('mvno_sequence') from dual;
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: execute command denied to user 'mvno-ca'@'localhost' for routine 'cmi_mvno_ca.mvnoNextval'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: execute command denied to user 'mvno-ca'@'localhost' for routine 'cmi_mvno_ca.mvnoNextval'
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)

最后查证下来是以下这个SQL报错，这是下单获取序列号生成订单号的，因为MySQ没有序列，用了一个函数实现的。我用navicat执行了一下，发现返回是null，感觉问题有点严重了，客户一直在投诉，赶紧进行回退。

SELECT mvnoNextval('mvno_sequence') from dual;

继续查证，再查看这个函数的实现

show create function mvnoNextval;

然后发现 DEFINER=mvno@localhost 上网查了下资料，发现definer这个值不会限制函数和存储过程调调用的权限，但是会限制函数和存储过程访问数据库的权限。

关于definer和invoker的解释创建存储过程的时候可以指定 SQL SECURITY属性，设置为 DEFINER 或者INVOKER，用来奉告mysql在执行存储过程的时候，，是以DEFINER用户的权限来执行，还是以调用者的权限来执行。

默认情况下，使用DEFINER方式，此时调用存储过程的用户必须有存储过程的EXECUTE权限，并且DEFINER指定的用户必须是在mysql.user表中存在的用户。

DEFINER模式下，默认DEFINER=CURRENT_USER，在存储过程执行时，mysql会检查DEFINER定义的用户'user_name'@'host_name'的权限；

INVOKER模式下，在存储过程执行时，会检查存储过程调用者的权限。 from:https://my.oschina.net/u/1424662/blog/485118

现在就是需要解决definer的问题，修改definer。

修改function、procedure的definer

select definer from mysql.proc;  -- 函数、存储过程
update mysql.proc set definer='user@localhost'; -- 如果有限定库或其它可以加上where条件

修改event的definer

select DEFINER from mysql.EVENT; -- 定时事件
update mysql.EVENT set definer=' user@localhost ';

修改view的definer

相比function的修改麻烦点：
select DEFINER from information_schema.VIEWS; 
select concat("alter DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER<>'user@localhost'; 
查询出来的语句再执行一遍就好了。

修改trigger的definer

目前还没有具体方便的方法，可以借助工具端如HeidiSQL、sqlyog等来一个个修改。注意改前有必要锁表,因为如果改的过程中有其它表改变而触发，会造成数据不一致。
Flush tables with readlock
Unlock tables
PlantUML Assignment2 分支限界问题

经过这次教训，发现自己对MySQL的理解还是不够，还是要继续学习！