mysql触发器级别_Mysql 从库触发器 与Innodb表的隔离级别关系 | 学步园

最近有一个业务需要在Mysql的从库上执行一个触发器，但是建完后悲哀的发现，完全没有起作用。

感觉应该跟binlog的记录方式有关，即binlog_format 应该是ROW的。

查询了下，binlog_format    设置的是：MIXED，这种模式应该默认的是STATEMENT，只有某些不确定sql语句执行是才会转为ROW啊(不了解这几种区别的，可以查看http://blog.csdn.net/adparking/article/details/7586054，这个介绍够详细)。为什么全部是ROW呢，于是手动的修改，SET

GLOBAL binlog_format = 'STATEMENT';

悲剧发生了，修改完后前端出现如下错误：

SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1665];

--- The error occurred in com/downjoy/app/forumdata/server/dao/sqlmap/TopicDAOImpl_SqlMap.xml.

--- The error occurred while applying a parameter map.

--- Check the TOPIC.getTopicIdByForumId-InlineParameterMap.

--- Check the statement (query failed).

--- Cause: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in com/downjoy/app/forumdata/server/dao/sqlmap/TopicDAOImpl_SqlMap.xml.

--- The error occurred while applying a parameter map.

--- Check the TOPIC.getTopicIdByForumId-InlineParameterMap.

--- Check the statement (query failed).

--- Cause: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

看这个提示，应该是InnoDB的隔离级别设置的是READ COMMITTED或READ UNCOMMITTED，在这两种模式下，binlog只能用ROW模式。于是修改：

set global transaction isolation level Repeatable read

OK，从库上的触发器完美工作！