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
- 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:
/**
- * Set the Transaction isolation level to something other that transaction_read_uncommitted
- * This allowable dirty isolation level is not being achievable in clustered DB environments
- * So-a more restrictive and expensive-option may is needed like Transaction_repeatable_read
- * See Isolation Level constants in {@link java.sql.Connection}
- * @param transactionisolation the isolation level
- */
- public void settransactionisolation (int transactionisolation) {
- this.transactionisolation = transactionisolation;
- }
/** * 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. '