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.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Right! Excellent! –  Chaki_Black Feb 19 at 20:04

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.