JMS之—— ActiveMQ中使用mysql做持久化报错：Cannot execute statement: impossible to write to binary log since BINL

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/69041255

一、问题

ActiveMQ中如果使用MySQL innodb的同时，开启了binlog，那么在ack消息的时候，日志里就可会报错：Java.sql.SQLException: Cannot execute statement: binlogging impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.如下所示：

2017-04-03 16:21:13,481 | WARN  | Could not create JDBC tables; they could already exist. Failure was: INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1) Message: 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. SQLState: HY000 Vendor code: 1665 | org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
2017-04-03 16:21:13,490 | WARN  | Failure details: 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. | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
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.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:908)[mysql-connector-java-5.1.30-bin.jar:]
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:788)[mysql-connector-java-5.1.30-bin.jar:]
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)[commons-dbcp-1.4.jar:1.4]
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)[commons-dbcp-1.4.jar:1.4]
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)[commons-dbcp-1.4.jar:1.4]
	at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.executeStatement(DefaultJDBCAdapter.java:114)[activemq-jdbc-store-5.14.4.jar:5.14.4]
	at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:92)[activemq-jdbc-store-5.14.4.jar:5.14.4]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.init(JDBCPersistenceAdapter.java:304)[activemq-jdbc-store-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.LockableServiceSupport.preStart(LockableServiceSupport.java:89)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.util.ServiceSupport.start(ServiceSupport.java:54)[activemq-client-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.BrokerService.doStartPersistenceAdapter(BrokerService.java:675)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.BrokerService.startPersistenceAdapter(BrokerService.java:659)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.BrokerService.start(BrokerService.java:623)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:73)[activemq-spring-5.14.4.jar:5.14.4]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_45]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_45]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_45]
	at java.lang.reflect.Method.invoke(Method.java:497)[:1.8.0_45]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1700)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1639)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1568)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:755)[spring-beans-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:762)[spring-context-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)[spring-context-4.1.9.RELEASE.jar:4.1.9.RELEASE]
	at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:64)[xbean-spring-4.2.jar:4.2]
	at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:52)[xbean-spring-4.2.jar:4.2]
	at org.apache.activemq.xbean.XBeanBrokerFactory$1.<init>(XBeanBrokerFactory.java:104)[activemq-spring-5.14.4.jar:5.14.4]
	at org.apache.activemq.xbean.XBeanBrokerFactory.createApplicationContext(XBeanBrokerFactory.java:104)[activemq-spring-5.14.4.jar:5.14.4]
	at org.apache.activemq.xbean.XBeanBrokerFactory.createBroker(XBeanBrokerFactory.java:67)[activemq-spring-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:71)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:54)[activemq-broker-5.14.4.jar:5.14.4]
	at org.apache.activemq.console.command.StartCommand.runTask(StartCommand.java:87)[activemq-console-5.14.4.jar:5.14.4]
	at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:63)[activemq-console-5.14.4.jar:5.14.4]
	at org.apache.activemq.console.command.ShellCommand.runTask(ShellCommand.java:154)[activemq-console-5.14.4.jar:5.14.4]
	at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:63)[activemq-console-5.14.4.jar:5.14.4]
	at org.apache.activemq.console.command.ShellCommand.main(ShellCommand.java:104)[activemq-console-5.14.4.jar:5.14.4]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_45]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_45]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_45]
	at java.lang.reflect.Method.invoke(Method.java:497)[:1.8.0_45]
	at org.apache.activemq.console.Main.runTaskClass(Main.java:262)[activemq.jar:5.14.4]
	at org.apache.activemq.console.Main.main(Main.java:115)[activemq.jar:5.14.4]
2017-04-03 16:21:13,526 | INFO  | Database lock driver override not found for : [mysql_connector_java].  Will use default implementation. | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
2017-04-03 16:21:13,535 | INFO  | Attempting to acquire the exclusive lock to become the Master broker | org.apache.activemq.store.jdbc.DefaultDatabaseLocker | main

这是因为，mysql默认的binlog_format是STATEMENT，而在READ COMMITTED或READ UNCOMMITTED隔离级别下，innodb只能使用的binlog_format是ROW。
而在ActiveMQ的store JDBC实现中（TransactionContext），为了提高并发性能，使用的是READ UNCOMMITTED：

// a cheap dirty level that we can live with    
private int transactionIsolation = Connection.TRANSACTION_READ_UNCOMMITTED;

所以，就会出现上面的问题。

二、解决方案

解决办法有两个：
1、在mysql里设置binlog_format为ROW，此时binlog会增大，但是一般来说对数据复制支持的更好，建议单机高性能环境下使用。
2、在activemq.xml的jdbcPersistenceAdapter里配置transactionIsolation=“4”，即TRANSACTION_REPEATABLE_READ，此时事务更严格，会影响性能，建议在集群、强实时一致、不强调单机性能的情况下使用。
可以参见源码里的说明：

/**
* set the Transaction isolation level to something other that TRANSACTION_READ_UNCOMMITTED
* This allowable dirty isolation level may not be achievable in clustered DB environments
* so a more restrictive and expensive option may be needed like TRANSACTION_REPEATABLE_READ
* see isolation level constants in {@link java.sql.Connection}
* @param transactionIsolation the isolation level to use
*/
public void setTransactionIsolation(int transactionIsolation) {
  this.transactionIsolation = transactionIsolation;
}