Replication fails after applying a Gateway Platform Update: You cannot 'ALTER' a log table

Products

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

Issue/Introduction

Solution

Background

The CA API Gateway utilizes MySQL replication to provide high-availability access to the Gateway configuration database. This database contains configuration data, published services, policy documents, and other components used to handle message traffic that may be configured via the Policy Manager by an administrator or operator. An issue may arise if an administrator attempts to apply a Platform Update against a Gateway cluster when replication is active and running between two database nodes. A Platform Update may result in a change in the schema of the metadata tables of the running MySQL server. If replication is active and running then these changes will be replicated--although such changes are not allowed by replication.?This article will prescribe the circumstances that cause this behavior and prescribe the action necessary to resolve the failed replication state.

Presentation

The following error message(s) may be visible when the status of replication is checked via the SHOW SLAVE STATUS query:

• 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log MODIFY start_time TIMESTAMP NOT NULL, MODIFY user_host MEDIUMTEXT NOT NULL, MODIFY query_time TIME NOT NULL, MODIFY lock_time TIME NOT NULL, MODIFY rows_sent INTEGER NOT NULL, MODIFY rows_examined INTEGER NOT NULL, MODIFY db VARCHAR(512) NOT NULL, MODIFY last_insert_id INTEGER NOT NULL, MODIFY insert_id INTEGER NOT NULL, MODIFY server_id INTEGER UNSIGNED NOT NULL, MODIFY sql_text MEDIUMTEXT NOT NULL'

• Last_SQL_Errno: 1580

Either or both of these error messages indicate that the native tables of the MySQL server are being modified. These tables are typically modified by a Platform Update while the update is in progress.

Resolution

This issue can be resolved by completing the installation of the Platform Update on all database nodes in the cluster and then reinitializing replication. Replication reinitialization is documented in the following article:?Reinitialize replication in a multi-node cluster

Environment

Release:
Component: APIGTW