今天遇到一个问题,早上对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的理解还是不够,还是要继续学习!

记录一下今天的MySQL故障