Replication fails to start: Cannot execute statement: impossible to write to binary log

Products

• STARTER PACK-7
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

Solution

Background

The Layer 7 Gateway uses replication to ensure that there are multiple instances of the Gateway database available in case a Gateway node, database application, or network segment fails or is unavailable. A certain level of operational continuity is maintained by maintaining the Gateway database on two separate Gateway nodes. Replication using MySQL seeks to accomplish this goal. In some circumstances, replication will fail or may fail to start when one of the MySQL servers in the Gateway cluster is improperly configured. MySQL has its own internal reporting and logging method to handle replication issues and the status of replication can be checked using the SHOW SLAVE STATUS?query.

By default, MySQL will engage in "statement-based" transaction logging. The Gateway appliance is configured by default to use "row-based" transaction logging. When the Gateway is configured to use statement-based logging instead of row-based logging, replication will fail to start. For more information on this subject, please consult the MySQL documentation pertinent to this configuration directive.

Presentation

Replication may fail to start and will present the following error message when?the?SHOW SLAVE STATUS?query is executed on a particular node:

Error '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.' on query.

Additionally, the other database node in the cluster will not report a specific error as this is a client-specific configuration problem.

Resolution

The MySQL server configuration file (/etc/my.cnf) must be changed to configure the Gateway database node correctly. To resolve this issue, perform the following:

1. Log into the Gateway appliance of the impacted node as the?ssgconfig user.
2. Select Option #3: Use a privileged shell (root).
3. Open?/etc/my.cnf in a text editor.
4. Verify the?binlog_format directive?is set to?MIXED.
5. Save the file and exit the text editor.
6. Restart the MySQL server (service mysql restart).

Please note that it is possible that the configuration directive may not be present at all. This should be considered improperly configured and the?binlog_format?directive will need to be added manually. If this is necessary then the configuration directive should be added as follows and the MySQL service restarted:?binlog_format = MIXED

Once the directive is set correctly in the server configuration and the MySQL server restarted, MySQL replication should resolve itself. It may be necessary to reinitialize replication. The process to do so is documented in the following article:?Reinitialize replication in a multi-node cluster
?

Environment

Release:
Component: APIGTW