6635人阅读
JMS/MQ（39）
作者同类文章X
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
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 = transactionI
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：736675次
积分：9288
积分：9288
排名：第944名
原创：142篇
转载：21篇
评论：504条
文章：30篇
阅读：210149
(5)(5)(1)(1)(1)(2)(2)(3)(3)(2)(2)(2)(11)(3)(1)(2)(1)(5)(1)(2)(45)(10)(1)(3)(10)(9)(4)(2)(5)(6)(1)(1)(2)(9)(6)(3)[MySQL复制异常]'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' - Master HaKu - 博客园
随笔 - 409, 文章 - 1, 评论 - 45, 引用 - 0
MySQL复制错误]Last_Errno: 1666 Last_Error: Error executing row event: 'Cannot execute statement: imposs
收到email报警，&Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
看到貌似是statement模式不足以应付应用，换成mixed试试看吧：mysql& STOP SLAVE; Query OK, 0 rows affected (0.02 sec)&mysql& &SET GLOBAL binlog_format=MIXED; Query OK, 0 rows affected (0.00 sec)&mysql& START SLAVE; Query OK, 0 rows affected (0.00 sec)
但是这样只会一次性
为了永久生效，需要修改my.ini
# Remove leading
# to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin = E:/mysql56/log_bin/log_bin.log
#relay_log = E:/mysql56/log_bin/relay_log.log
#read_only = 1
# binary logging format - mixed recommended
binlog_format=mixedStack Overflow is a community of 4.7 million programmers, just like you, helping each other. J it only takes a minute:
Full error:
Warning: Unsafe statement written to the binary log using statement format
since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an
auto-increment column after selecting from another table are unsafe because
the order in which rows are retrieved determines what (if any) rows will be
written. This order cannot be predicted and may differ on master and the
I only seem to get this error when trying to run raw SQL from Django. If I run the SQL from MySQL CLI client I do not get the same error. Here's the SQL:
UPDATE picture p
SELECT @inc := @inc + 1 AS new_weight, id
FROM (SELECT @inc := 0) temp, (
SELECT id FROM picture
WHERE album_id = 5
ORDER BY taken_date ASC
ON p.id = pw.id
SET p.weight = pw.new_
The purpose of this is to order the records, and apply a sequential number to the weight in order to persist this ordering in the database.
I've tried running this command within the client to see if I can replicate the issue however it still runs successfully:
mysql& SET GLOBAL binlog_format = 'STATEMENT';
Also, it's important that I either solve or rewrite the SQL to work with this constraint as the final application is almost certainly going to run across a master-slave database setup.
*EDIT: After reading a bit more binlog_format, it seems as though ROW or MIXED would be perfectly acceptable, however my main concern is not being able to replicate this issue in the MySQL CLI in order to test whether MIXED/ROW can infact solve this problem?
There is no way to rewrite the sql to not throw that error message. Any sort of order will throw that message as the slave might have different rows then the master. mixed will solve it, as it will only switch to row based replication when a statement is not safe to replicate with statement.
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Upcoming Events
Stack Overflow works best with JavaScript enabled}