MySQL Error: impossible to write to binary log

Question

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?

Answer 1

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

1. STATEMENT

   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.