XtraDB Cluster, 2 nodes and missing data

mygeeknc · December 30, 2020, 4:21pm

I have two nodes running both with master-master XtraDB Cluster 5.5 with HAProxy as a frontend. Everything was running fine for a couple of days but I noticed some issues with the data, which turns out the issue was because the tables were MyISM instead of InnoDB.

A couple of days later, I noticed that I’m missing some entries in one of the tables. As a workaround, I pointed our web app to just one of the DB’s instead of the HAProxy server until I could get the tables converted to InnoDB. Is it possible that rows are deleted in this case?

And just to prefix this all, i’m still learning the inner workings of Percona. Thanks for any help.

Oliver · December 30, 2020, 4:31pm

percona xtradb cluster will sync myisam table structure but not the data as far as i know … do you have all tables as innodb?

mygeeknc · December 30, 2020, 4:31pm

I don’t currently, but they will be soon. Would it delete the data though? Like if a row was in node A but not node B, would it delete the row in A?

Oliver · December 30, 2020, 4:31pm

If you would have 2 different states (and all tables innodb) that means your cluster is out of sync. If you would stop node 2, change the tables on node 1 to innodb and then start node 2 again… node 2 will copy the current state from node 1 … so yes … it will probably delete stuff on node 2 or at least be out of sync.

Easiest way would be in my eyes to make sure you have everything straight on one node … than let the second node replicate from first one.

przemek · December 30, 2020, 4:31pm

You can also enable myisam replication, on you own risk:
http://www.percona.com/doc/percona-xtradb-cluster/5.5/wsrep-system-index.html#wsrep_replicate_myisam

Ideal situation: all tables InnoDB (except the ones in ‘mysql’ database) and all tables have primary key. Don’t update mysql.* tables directly - use DCL commands instead, like GRANT, REVOKE, ADD USER, etc.