mysql error日志的一个问题

最新推荐文章于 2025-02-25 11:52:45 发布

原创最新推荐文章于 2025-02-25 11:52:45 发布 · 876 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

13 篇文章

订阅专栏

本文记录了MySQL中因使用特定SQL语句导致的复制警告问题，包括INSERT...ON DUPLICATE KEY UPDATE及带有LIMIT的DELETE语句，并给出了通过调整binlog_format为mixed来消除这些警告的方法。

2014-01-08 10:26:50 13761 [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 `tb_user_69`(`nick_name`,`user_id`) VALUES('滔哥test','900698') ON DUPLICATE KEY UPDATE `nick_name`='滔哥test',`user_id`='900698'
2014-01-08 10:28:43 13761 [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 `tb_user_69`(`sex`,`user_id`) VALUES('1','900698') ON DUPLICATE KEY UPDATE `sex`='1',`user_id`='900698'
2014-01-08 10:38:22 13761 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM `db_application`.`mine_car_info` WHERE `mine_car_info`.`mine_car_id` = 78 LIMIT 1

由于开启了replication, 且binlog日志的格式默认为STATEMENT。查看官方手册有以下解释：

When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.

For more information, see Section 16.4.1.1, “Replication and AUTO_INCREMENT”.

将数据库binlog_format 设置为mixed后，error日志不在提示该警告提示。