首页 > 代码库 > JAVA调用mysql数据操作时出现错误：impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

JAVA调用mysql数据操作时出现错误：impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

2024-10-29 18:43:39   204人阅读

 使用mysql做持久化报错：Cannot execute statement: impossible to write to binary log since BINLOG_FORM             

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.

技术分享

这是因为，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      
  1. private int transactionIsolation = Connection.TRANSACTION_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，此时事务更严格，会影响性能，建议在集群、强实时一致、不强调单机性能的情况下使用。

可以参见源码里的说明：

 

/** 
  1.      * set the Transaction isolation level to something other that TRANSACTION_READ_UNCOMMITTED 
  2.      * This allowable dirty isolation level may not be achievable in clustered DB environments 
  3.      * so a more restrictive and expensive option may be needed like TRANSACTION_REPEATABLE_READ 
  4.      * see isolation level constants in {@link java.sql.Connection} 
  5.      * @param transactionIsolation the isolation level to use 
  6.      */  
  7.     public void setTransactionIsolation(int transactionIsolation) {  
  8.         this.transactionIsolation = transactionIsolation;  
  9.     }  
/**     * 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;    }

JAVA调用mysql数据操作时出现错误：impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'


联系
我们