Impossible to write to binary log error

Post Reply
User avatar
Daniel
Support
Posts: 200
Joined: 02 Aug 2013, 17:50

07 Nov 2015, 01:08

If SoluteDNS returns a error similar to the below shown error you might want to change your MySQL/MariaDB server configuration to allow mixed binary logging.

Code: Select all

Error: GSQLBackend unable to store key: Failed to execute mysql_query, perhaps connection died? 

Err=1: 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. 
Solution:
The most likely reason you encounter this error is because you are using MySQL database replication together with STATEMENT based binary logging. This may cause some issues in some situation. To fix this you could use ROW based binary logging instead, which is more reliable but results in larger log files.

Another option is to use MIXED binary logging which basically allows MySQL to choose between binary and row based logging when whatever suits best for the entry logged.

How to setup:
Open the /etc/my.cnf configuration file on all MySQL servers in your replication cluster.

After:

Code: Select all

[mysqld]
Add or change if existent:

Code: Select all

binlog_format = MIXED
Restart the slave MySQL servers first and when ready the master MySQL server. When finished please make sure your replication is running correctly. You may need to reset database replication.The error should now have been solved.

Please note depending on your situation a ROW binlog format may be favourable over MIXED. When using this binlog format we do recommend setting the --binlog-row-event-max-size option as well.

Please also see:
Post Reply