Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
来源:互联网 发布:直销软件 编辑:程序博客网 时间:2024/05/22 00:36
一.问题描述
014-12-15 20:00:29 4398 [Warning] Unsafe statement written to the binary log using statement format since
BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than
one UNIQUE KEY is unsafe Statement: INSERT INTO `longxibendi` (`lxid`, `l_id`, `l_aplply`, `l_types`, `ctimes`) VALUES
(75692, 218, 8, 2, 1418644829) ON DUPLICATE KEY
UPDATE `ctimes`=1418644829
一个mysql master 的err log 报上面的错误。
二.问题原因
查了下原因,longxibendi这个表上有2个唯一键。则使用 INSERT... ON DUPLICATE KEY UPDATE ,且当前数据库binlog_format是statement格式,这种sql语句就会报unsafe。
查了下手册
INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys. When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends.
An
INSERT ... ON DUPLICATE KEY UPDATE
statement against a table having more than one unique or primary key is marked as unsafe for statement-based replication beginning with MySQL 5.6.6. (Bug #11765650, Bug #58637)
看官方解释,是 server层把数据传给innodb引擎,innodb引擎检查key值比较敏感造成的。
个人理解,可能与server与innodb每次只传1行数据有关。
个人理解,可能与server与innodb每次只传1行数据有关。
三.问题解决
两种办法:
1.修改binlog_format格式为mixed;
登陆mysql,执行 set global binlog_format=MIXED;
2.不要使用这类sql;
其中,第1种方法,需要注意下。
如果是 master->slave 结构的数据库架构。并且 slave上开启了 log_slave_updates。那么在master上修改完binlog格式,开启了log_slave_updates的从库,会同布中断。
所以,需要先在 slave上,设置 binlog_format=mixed,之后再在master上设置。slave报错信息如下:
Last_SQL_Errno: 1666
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
0 0
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. S
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. S
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
- [Warning] Unsafe statement written to the binary log using statement format since 错误
- impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT
- error code 1592 Unsafe statement written to the binary log using statement...
- Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT…
- 开启binlog后异常:impossible to write to binary log since BINLOG_FORMAT = STATEMENT
- ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT
- [MySQL复制异常]Cannot execute statement: impossible to write to binary log since statement is in row for
- [MySQL复制异常]Cannot execute statement: impossible to write to binary log since statement is in row for
- 15.13 The using statement
- Using the Switch Statement
- ActiveMQ中使用mysql做持久化报错:Cannot execute statement: impossible to write to binary log since BINLOG_FORM
- ActiveMQ中使用mysql做持久化报错:Cannot execute statement: impossible to write to binary log since BINLOG_FORM
- - Statement
- textView画横线需求
- poj 2778 DNA Sequence (ac自动机+矩阵快速幂优化dp)
- QUARTUS ii中IP核破解
- 绩效管理
- Quartz参数说明
- Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
- Android Spinner控件 显示数据和下拉选中数据分离
- 从零开始学习嵌入式 - 什么是Endianness?
- C++中的多态性
- domino百度文库功能
- 如何搭建SSH+JPA+mysql项目
- 交换、路由与防火墙手记(3)
- 【转】Android之apk文件签名——keytool 和 jarsigner
- C++11 CPP11 学习