What is the meaning of the following MySQL warning:
Statement may not be safe to log in statement format.
I have some tens of thousands of these statements every day in my
mysqld.err file, and I'm not sure where should I begin my investigation.
This means that your binary log format
binlog_format is STATEMENT, which is the default binary log format.
STATEMENT-based format logs the statement issued, while
ROW-based format logs how individual tables were changed. The problem with
STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the slave.
INSERT INTO audit (CURRENT_USER());
You can get a good idea of different types of these non-deterministic statements by looking at how the
MIXED format handles switching to
ROW-based format writes a lot more data to the binary log, but is much safer.
There is another reason for
“Statement may not be safe to log in statement format” warning
My employer has a DB Client who is always running a query like this
DELETE FROM mydb.mytable WHERE ... LIMIT 1;
This query will succeed on a DB Server. However, if you are using MySQL Replication, this could present data inconsistency problems because when either
UPDATE ... LIMIT or
DELETE ... LIMIT statement is executed, the row order for the
LIMIT is never guaranteed to be the same.
In other words :
UPDATE ... LIMITmay update a row on the Master but a different row on the Slave
DELETE ... LIMITmay delete a row on the Master but a different row on the Slave
The way around this would be to do what @DTest just recommended : Change the binlog_format to ROW. (+1 for @DTest for going straight to the solution)