Turnoff mysql unsafe statement warning

Question

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.

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...)

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

And my insert query is

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

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.

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?

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

Solution 1

You can use

SET GLOBAL LOG_WARNINGS = 0 

to turn off logging and

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.

Note that older versions of MySQL do not support this.

Solution 2

I think this question is still open. Use set max_error_count=0; your unsafe queries; set max_error_count=64;

before the queries you want to run it is a session variable, so you don't need to be super user to use. Not only, no warnings will be issued when you call show warnings limit 5; but such warnings are not reporting in mysql log files. you can also use it in stored procedures. After your queries you know that are unsafe, you can put back this variable to its original value.