Turnoff mysql unsafe statement warning

mysql insert primary-key ignore composite-primary-key
user1640242 picture user1640242 · Sep 5, 2012 · Viewed 12.6k times · Source

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

Answer

user1978317 picture user1978317 · Jan 14, 2013

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.

Related questions

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either: ON DUPLICATE KEY UPDATE which implies an unnecessary update …

Read More
How to set initial value and auto increment in MySQL?

How do I set the initial value for an "id" column in a MySQL table that start from 1001? I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')"; Without specifying the initial …

Read More
How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More