MySQL Error: impossible to write to binary log

errorslogsMySQL

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?

Best Answer

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

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.
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.
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.

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.

Thesql “Unsafe statement”

You can set any of these three BINLOG-FORMAT :

STATEMENT causes logging to be statement based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.

The default binary logging format depends on the version of MySQL you are using:

For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

You have to make sure that:

Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.
When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication.

Also, this bug report is related about problem you have mentioned here :

http://bugs.mysql.com/bug.php?id=53259

Best Answer

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

Thesql “Unsafe statement”

Related Question