Java error occurred while invoking MySQL data operation: Impossible to write to binary log since statement was in row format and Binlog_format = statement. '

persistent error using MySQL: Cannot execute statement:impossible to write to binary log since Binlog_form

ACTIVEMQ If you use MySQL InnoDB while the binlog is turned on, then in the ACK message, the log will be error: 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 while transaction isolation level is read COMMITTED or READ uncommitted.

This is because the default MySQL Binlog_format is statement, and InnoDB can only be used by the Binlog_format in the Read committed or READ UNCOMMITTED isolation level is row.

In the ACTIVEMQ store JDBC Implementation (TransactionContext), in order to improve concurrency performance, the use of READ UNCOMMITTED:

//A cheap dirty level, we can live with

1. Private int transactionisolation = connection.transaction_read_uncommitted;

     A cheap dirty level, we can live with         private int transactionisolation = Connection.transaction_read_uncommitte D

Therefore, the above problems will arise.

There are two solutions:

1, in MySQL set Binlog_format to row, at this time Binlog will increase, but generally to the data replication support better, recommended single-use high-performance environment.

2, in the Activemq.xml jdbcpersistenceadapter configuration transactionisolation= "4", that is, Transaction_repeatable_read, when the transaction is more stringent, will affect performance, It is recommended to use the cluster, strong real-time consistency, and no emphasis on single-machine performance.

You can see the instructions in the source code:

/** 

2. * Set the Transaction isolation level to something other that transaction_read_uncommitted
4. * This allowable dirty isolation level is not being achievable in clustered DB environments
6. * So-a more restrictive and expensive-option may is needed like Transaction_repeatable_read
8. * See Isolation Level constants in {@link java.sql.Connection}
10. * @param transactionisolation the isolation level
12. */
14. public void settransactionisolation (int transactionisolation) {
16. this.transactionisolation = transactionisolation;
18. }

/**     * Set the Transaction isolation level to something other, transaction_read_uncommitted     * This allowable di Rty isolation level is not being achievable in clustered DB environments     * So a more restrictive and expensive option MA Y is needed like transaction_repeatable_read     * See Isolation Level constants in {@link java.sql.Connection}     * @par AM Transactionisolation the isolation level to use     *    /public void settransactionisolation (int transactionisolation) {        this.transactionisolation = transactionisolation;    }

Java error occurred while invoking MySQL data operation: Impossible to write to binary log since statement was in row format and Binlog_format = statement. '