I have a system (inherited from another development company) and I don't know all its features yet. Construction:
- web client/application (symfony 1.4)
- DB - MySQL.

Project is deployed on 2 servers:
1-st: nginx server + PHP + DB Master;
2-st: DB Slave.

When data are modificated by web-client at DB Master, DB Slave works good and data have normal replication process. When data are modificated by MySQL Management Client, I have an notification:

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED

And then, DB Slave falls down and replication process breaks (error insert... duplicate entry...).

The similar notification happens (at this situation - error) with the same text, when I try to insert data with java application/module (it runs inside transaction) and after exception, transaction rolls back and no modification happens.

How can I solve this problem and make system work and make it possible modificate data on DB Master and DB Slaves work in normal mode?

1 Answer 1


There are 3 methods that MySQL can use to write to the binary logs:


    This means that every SQL statement on the master is recorded in the log, and executed on the slave. This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic. This also requires support from the storage engine in use.

  2. ROW

    This means that every row that is changed by a statement is individually recorded in the binary log. This gives larger binary logs (generally) than statement based logging, but is almost always guaranteed to give the exact replication needed.

  3. MIXED

    This allows MySQL to choose between binary and row based logging as required.

If you're getting this error, one suggestion is to change the BINLOG_FORMAT variable to MIXED. This allows MySQL to automatically switch between ROW and STATEMENT based logging as required.

  • Right! Excellent! Commented Feb 19, 2014 at 20:04
  • 4
    An excellent suggestion, but HOW do you change BINLOG_FORMAT? Commented Sep 23, 2015 at 13:50
  • 2
    May be very late but, it can help for all those who wanna know how to change BINLOG_FORMAT dba.stackexchange.com/questions/6150/… Commented Apr 20, 2016 at 9:35
  • Trebor Rude: You should read the mysql documentation, which explains this in detail! dev.mysql.com/doc/refman/5.6/en/…
    – Lifeboy
    Commented Feb 17, 2017 at 9:48
  • This answer helps me resolve the problem. Thanks!
    – Tung
    Commented Oct 9, 2018 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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