Node has dropped from cluster error in Galera Cluster

Kedar

10 months ago

Recently I noted a strange MySQL replication error in a Percona XtraDB Cluster (PXC) node replicating from a standard MySQL instance. “Node has dropped from cluster”. This is a quick blog showing you what happens when you use Galera Cluster for an unsupported feature.

Node has dropped from cluster

As the MySQL Architecture image above notes, we have a 3 node Percona XtraDB Cluster (Galera Cluter) replicating from a standard MySQL (Percona Server). And this is our SHOW REPLICA STATUS:

pxc-node-1 (none)> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: prod_mysql_master
                   Master_User: rpl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysqld-bin.008582
           Read_Master_Log_Pos: 97094409
                Relay_Log_File: mysqld-relay-bin.000013
                 Relay_Log_Pos: 322994461
         Relay_Master_Log_File: mysqld-bin.008582
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
...
                    Last_Errno: 1047
                    Last_Error: Node has dropped from cluster

This was surely a strange replication error and it was interesting to look into what might have caused this. One way to identify what’s the replica is executing is by looking into binary log on master (or relay log on replica). I checked the binary log on master using a mysqlbinlog command:

mysqlbinlog --base64-output=decode-rows -vvv --start-position EXEC_LOG_POS | more

Quick tip: Here’s how you can understand REPLICA STATUS output.

… and the replication was stuck on this DDL with following definition

CREATE TABLE a_naughty_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  status INT DEFAULT 0,
  recordid INT,
  taskname varchar(255),
  someid varchar(250),
  taskstatus varchar(100),
  order_status varchar(100),
  technology varchar(255)
) ENGINE=MyISAM;

a MyISAM table!! This is a known limitation of Galera, that it does not support the MyISAM engine.

Even the Percona Documentation clearly states this as a first point: Replication works only with InnoDB storage engine. Any writes to tables of other types are not replicated.

I had to manually create the table on the cluster node and skip the errors using Percona’s pt-slave-restart tool as there were many records.

What requires here is to fix the MyISAM table (convert to InnoDB) at source and resync the cluster. The learning here is, always remember limitation of certain products before using it in production. It is a risk of data loss especially when that a feature is explicitly mentioned as “unsupported”.

Some Galera Cluster Limitations

Following are a few of the limitations, you should review full detail before choosing PXC as your clustering solution.

Limitation	Description
Replication Engine	Only supports InnoDB storage engine.
Primary Key Requirement	All tables must have a primary key for consistent row order.
Unsupported Writes	Writes to tables of other types are not replicated.
Unsupported Queries	– LOCK TABLES and UNLOCK TABLES in multi-source setups. – Lock functions like GET_LOCK(), RELEASE_LOCK(), etc. – Query log cannot be directed to a table.
LOAD DATA INFILE	Processing commits every 10,000 rows, large transactions are split. Some such transactions issuing COMMIT may still be aborted.
XA Transactions	Not supported due to possible rollback on commit.
Write Throughput	Limited by the weakest node, slowing down the entire cluster.
Storage Engine Compatibility	enforce_storage_engine=InnoDB not compatible with wsrep_replicate_myisam=OFF.
ALTER TABLE Workloads	Avoid ALTER TABLE IMPORT/EXPORT workloads for consistency.
Temporary Table Names	Avoid reusing names of persistent tables for temporary tables.
INPLACE ALTER TABLE	No longer allows LOCK=NONE for INPLACE ALTER TABLE queries.
Dot Characters	Avoid dot characters (.) in values for log_bin and log_bin_index variables. I wrote on this causing replication issues as well.

It is recommended read for the complete list of limitations on the following documentation page for Galera Cluster

https://docs.percona.com/percona-xtradb-cluster/8.0/limitation.html
https://galeracluster.com/library/training/tutorials/differences.html

Conclusion

Ofcourse, “Bros don’t let Bros use MyISAM table, especially in Galera Cluster. “
This blog highlighted a particular scenario in a Percona XtraDB Cluster (PXC) node, replicating from a standard MySQL instance, facing this issue due to an unsupported feature of a MyISAM table. That said, it goes beyond conveying the importance of verifying Galera or any product limitations before deployment.