Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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 Slave
  • DELETE ... LIMIT may 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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.