ActiveMQ中使用mysql做持久化報錯：Cannot execute statement: impossible to write to binary log since BINLOG_FORM

標籤：style   blog   http   os   java   使用   io   strong   for   

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：

[java] view plaincopyprint?

1. // a cheap dirty level that we can live with    
2. 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，此時事務更嚴格，會影響效能，建議在叢集、強即時一致、不強調單機效能的情況下使用。

可以參見源碼裡的說明：

[java] view plaincopyprint?

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

ActiveMQ中使用mysql做持久化報錯：Cannot execute statement: impossible to write to binary log since BINLOG_FORM