Resolving Data Inconsistency in Percona XtraDB Cluster Caused by MyISAM Tables and Applying pt-online-schema-change

Overview

A developer reported that read/write nodes of an online PXC (Percona XtraDB Cluster) returned inconsistent data. Investigation revealed that the problematic table used the MyISAM storage engine, which is not supported by PXC replication, leading to unsynchronized operations.

Background

Percona XtraDB Cluster (PXC) integrates Percona Server, Percona XtraBackup, and the Galera library to provide multi‑master synchronous replication with strong consistency. All nodes are peers, allowing reads and writes on any node while keeping data identical across the cluster.

Problem Discovery

During a database migration, a table with the MyISAM engine showed a large discrepancy between the write node and the two replica nodes. Binlog positions were identical, indicating normal cluster synchronization, but the missing operations on replicas proved the table was not being replicated.

Root Cause

MyISAM is a non‑transactional engine; Galera replication works only with transactional engines such as InnoDB. Consequently, changes to the MyISAM table were never replicated, causing data divergence. When the engine was later altered to InnoDB, the previously unsynchronized data triggered errors during the APPLY phase, causing the two replica nodes to shut down themselves.

Operations and Consequences

The team first attempted a direct ALTER TABLE test.sbtest1 ENGINE=InnoDB conversion. Although the statement succeeded, the underlying data remained inconsistent, and the replicas crashed shortly after because their APPLY of pending writes failed.

Solution

To safely migrate the table while preserving data consistency, the team used pt-online-schema-change . This tool creates a new InnoDB copy of the table, copies rows, and atomically renames the tables, ensuring that all nodes receive identical data. Initial tests on a MyISAM table showed the tool rejected the operation because PXC disallows MyISAM tables. By commenting out the engine‑check code in the tool’s source, the conversion succeeded.

After successful testing, the remaining MyISAM tables were converted using the same approach, eliminating the risk of further inconsistencies.

Reflection

Assess the urgency of incidents and perform thorough root‑cause analysis in a test environment before applying changes in production.

Evaluate potential side effects of any change; prefer low‑risk, well‑understood methods.

Understand the inner workings of tools; reading source code can reveal ways to adapt them safely.

Maintain detailed operation records to aid future post‑mortems.

Study technical writing styles to improve clarity of documentation.

Conclusion

The core issue was the presence of MyISAM tables in a PXC cluster, which prevented replication and caused node self‑shutdown after engine conversion. Using pt-online-schema-change to rebuild tables as InnoDB ensured row‑level consistency across all nodes, restoring full cluster reliability.

References

MySQL Operations Handbook, Zhou Yanwei et al.

pt-online-schema-change Documentation – https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

Percona XtraDB Cluster 5.7 Documentation – https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html