Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Turnoff mysql unsafe statement warning

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

like image 969
user1640242 Avatar asked Sep 05 '12 03:09

user1640242


People also ask

How do I turn off warnings in MySQL?

To suppress warnings, set SQL_NOTES=0.

How do I view MySQL warnings?

The mysql client also has a number of options related to warnings. The \W command will show warnings after every statement, while \w will disable this.


2 Answers

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.

like image 142
user1978317 Avatar answered Sep 22 '22 11:09

user1978317


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.

like image 24
Laurent PELE Avatar answered Sep 24 '22 11:09

Laurent PELE