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.
An example:
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. 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 ... LIMIT may update a row on the Master but a different row on the SlaveDELETE ... LIMIT may delete a row on the Master but a different row on the SlaveThe 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)
