License: Attribution-NonCommercial-ShareAlike 4.0 International
本文出自 Suzf Blog。 如未注明，均为 SUZF.NET 原创。
转载请注明：http://suzf.net/post/144
问题描述
发现网站反应比较慢 ， 顺便查看了一下mysql的错误日志，发现产生了很多warning的日志：
150527 21:15:28 [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 'xxx'
问题原因
查了下原因，xxx 这个表上有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.
INSERT ... ON DUPLICATE KEY UPDATEstatement 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)
link : http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
binlog format : http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format
看官方解释，是server层把数据传给innodb引擎，innodb引擎检查key值比较敏感造成的。
问题解决
两种办法：
1.修改binlog_format格式为mixed；
登陆mysql，执行 set global binlog_format=MIXED; FLUSH LOGS;
注意：
如果是 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.'
2.不要使用这类sql；
