java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist

在2014年的最后一个工作日，修改了生产环境中mysql的访问权限。
原来应用是以root身份访问数据库，现在给每个应用建了单独的用户访问数据库。
大约过去了两周，未见有异常。在反复确认了没有用root用户访问数据库后，决定彻底禁止root远程访问mysql。
在删除root@%用户后，发了一封邮件通知这件事情。

1分钟后悲剧开始，有人反映业务有问题。检查应用错误日志如下：
java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1160)

仔细检查发现数据库中有些trigger的definer是root@%，root@%用户被删除后，trigger找不到这些definer，就报错了。

mysql> SELECT
 t.TRIGGER_SCHEMA,     t.TRIGGER_NAME,
 t.EVENT_MANIPULATION, t.EVENT_OBJECT_SCHEMA,
 t.EVENT_OBJECT_TABLE, t.ACTION_TIMING, t.`DEFINER`
FROM  information_schema.`TRIGGERS` t where t.TRIGGER_SCHEMA='tion';
+----------------+------------------------------+--------------------+---------------------+--------------------+---------------+---------+
| TRIGGER_SCHEMA | TRIGGER_NAME                 | EVENT_MANIPULATION | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_TIMING | DEFINER |
+----------------+------------------------------+--------------------+---------------------+--------------------+---------------+---------+
| tion           | orderUpdate_TRIGGER          | INSERT             | tion                | orderreply         | AFTER         |root@%  |
+----------------+------------------------------+--------------------+---------------------+--------------------+---------------+---------+

直接update  information_schema.`TRIGGERS` 返回ERROR 1044 (42000): Access denied
mysql> update information_schema.`TRIGGERS` set `DEFINER`='tion@%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

为了临时解决问题，直接通过工具SQLyog修改了tregger的definer，应用A的问题得到解决。

 

同时应用B，应用C也出现了异常.

应用B中有一条SQL执行时快时慢，有时还会超时，但总是没有返回结果。同事说：如果应用B中的SQL没返回结果，则应用C也不正常。

我们着重分析应用B的日志，除了偶尔超时，未见其他明显错误。我们开始去解决SQL执行时快时慢的问题。
SELECT * from information_schema.`PROCESSLIST` where info  is not null ;

看到有来自应用C的一些SQL执行时间异常，这时去执行应用B的SQL会很慢。

如果应用C的SQL执行完毕，执行应用B的SQL会很快。看来似乎找到了问题的原因。

是不是应用C有问题，因为应用C的SQL执行时间明显异常。(这个判断是从数据库的长期监控的结果得出的)

于是赶紧去看系统C的日志：
java.sql.SQLException: The user specified as a definer ('root'@'%') does not exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

与系统A的错误十分相似。也是trigger中的definer引用了root@%这个用户。
原来应用A,B,C原来都是root用户访问Mysql。修改应用访问Mysql的权限后，分别使用了不同的用户。
但其中trigger的definer还是root，知道原因解决起来就比较简单了。

问题虽然解决了，但还是对生产照成了一定的影响。

这个事件再次提醒自己，生产环境的修改一定要谨慎，尤其是数据库，要有严谨的回退方案。

切不可以为操作简单，就可以大意。

根据这个事件引出的两个问题：

一、如何批量修改mysql中trigger的definer？

1.用mysqldump导出指定用户的trigger到文件
mysqldump -uroot -p   --triggers --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt   tion > triggers.sql

/*!50032 DROP TRIGGER IF EXISTS orderUpdate_TRIGGER */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `orderUpdate_TRIGGER` AFTER INSERT ON `orderrep`

导出的文件中包括drop 和create trigger的语句，所以直接修改触发器的definer后直接导入就可以了。

2.修改导出文件中要修改trigger的definer

3.执行修改过的文件

 

二、怎么检查mysql中view,function,procedure,event,trigger的definer呢？
-- view
select table_name  ,view_definition,DEFINER FROM information_schema.views order by  definer ;

-- FUNCTION ,PROCEDURE
select  p.db ,p.`name` , p.type  , p.`definer` ,  p.created ,p.modified from  mysql.proc p  order by  definer ;

-- TRIGGER
SELECT t.TRIGGER_NAME,t.TRIGGER_SCHEMA ,t.EVENT_MANIPULATION ,t.EVENT_OBJECT_SCHEMA,t.EVENT_OBJECT_TABLE ,t.`DEFINER` from information_schema.`TRIGGERS`  t
ORDER BY t.`DEFINER`

-- EVENT
select e.EVENT_NAME,e.EVENT_SCHEMA , e.`DEFINER`  from information_schema.`EVENTS` e  order by  definer ;

解决生产中的问题时，保持一个清醒的头脑，平常心很重要。