关闭mysql不安全语句警告 [英] Turnoff mysql unsafe statement warning

问题描述

我正在使用日志错误将警告/错误写入文件.当我执行INSERT IGNORE..SELECT语句时，它只会继续写此警告消息.

I am using log-error to write warning/errors into a file. When I perform INSERT IGNORE..SELECT statement, it just keep write this warning messages.

120905  3:01:23 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.

我想停止mysql logwriter不断重复以上错误. (我看不到其他日志，因为它们填写了整个日志文件...)

I want to stop mysql logwriter keep writing the error above over and over. (I can't see other log because they fillout the whole logfile...)

首先，我将(a，b，c)插入表中. c在表中应该是唯一的，并且使用a，b进行选择.查询将是这样

First of all, I insert (a, b, c) into a table. c should be unique in the table, and a, b are used to select. Query will be like this

SELECT c FROM table WHERE a=value1 AND value2<b AND b<value3

我的插入查询是

INSERT IGNORE INTO table VALUES (,,),(,,)...(,,)

我以为我可以更改查询而不产生警告，但是我的数据包含唯一字段，因此我需要保证该字段在表中是唯一的.每隔几秒钟应插入500〜2000行，因此我需要进行批量插入.

I thought I could change the query not to produce warning, but my data contain unique field and I need to guarantee that the filed is unique in the table. And 500~2000 rows should be inserted in every few seconds, so I need to do bulk insert.

如果已经插入了数千万行，那么我需要在几秒钟内再插入2000行.如何在不用警告填充日志文件的情况下将它们安全地插入表中?

If there are already dozen of millions row inserted, and I need to insert another 2000 rows in few seconds. How can I insert them safely into the table without filling the logfile with the warnings?

(由于需要使用mysql复制，因此我无法关闭二进制日志.)

(I can't turn off the binary log because I need to use mysql replication.)

推荐答案

您可以使用

SET GLOBAL LOG_WARNINGS = 0 

关闭日志记录和

SET GLOBAL LOG_WARNINGS = 1

将其重新打开.我个人的建议是将这些令人讨厌的查询包含在其中，而不是在全局范围内进行设置，以便您可以跟踪其他查询是否也引起了问题.

to turn it back on. My personal suggestion is to wrap the offending query with these rather than setting it globally so that you can track if other queries cause the problem as well.

请注意，较早版本的MySQL不支持此功能.

Note that older versions of MySQL do not support this.

这篇关于关闭mysql不安全语句警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！