Hello Here,
I think this will answer your question, the risk with MyISAM Tables. CLICK HERE
Failure Modes and Data Divergence :
When MyISAM tables are used in a PXC cluster, several failure scenarios can occur:
• Writes to MyISAM tables are not replicated: Data inserted, updated, or deleted on one node is not propagated to other nodes. This leads to immediate data divergence.
• Cluster certification cannot be enforced: Since MyISAM does not support transactions, there is no way to roll back or certify changes across nodes.
• Node restarts or crashes can cause further divergence: MyISAM’s lack of crash recovery means that after a crash, tables may be corrupted or out of sync with other nodes.
• Administrative operations (CHECK, REPAIR, OPTIMIZE) are not replicated: Running these commands on one node does not affect other nodes, leading to inconsistent table states.
• State Snapshot Transfer (SST) and Incremental State Transfer (IST) do not guarantee MyISAM consistency: When a node joins the cluster and receives a full data copy, only InnoDB tables are guaranteed to be consistent. MyISAM tables may be copied as-is, but any changes made while the node was offline are lost.
Identifying MyISAM Tables
Before converting MyISAM tables to InnoDB, it is essential to identify all tables using the MyISAM engine. This can be done using queries against the information_schema database:
SELECT table_schema, table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') ORDER BY table_schema, table_name;
This query lists all user tables (excluding system schemas) that are currently using MyISAM. For a specific database, filter by table_schema = 'your_database'.
Converting MyISAM Tables to InnoDB
The recommended method to convert a MyISAM table to InnoDB is:
ALTER TABLE table_name ENGINE=InnoDB;
This command changes the storage engine in place. For large databases or many tables, you can generate a list of ALTER statements: