Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.,mysqlbinlog

來源:互聯網
上載者:User

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.,mysqlbinlog
一.問題描述 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)

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html

 看官方解釋,是 server層把資料傳給innodb引擎,innodb引擎檢查key值比較敏感造成的。
個人理解,可能與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.'


相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.