MySQL/MariaDB/Percona replication for tables without a primary key

Question

While creating tables without primary keys is definitely a bad practice, there are third-party applications with their own schemas, and altering third party's schema by adding a primary key might turn out to be even worse then the decision to use such an application was (because we can't predict if and how it breaks the application the schema of which is altered and it can bring a maintenance burden that barely can be known upfront).

However, I need to host such an application and have a highly available database for it; the application requires a modern-ish MySQL, so MySQL, MariaDB, and Percona are on the table. I need to set up master-master replication within a datacenter and between datacenter with a single active master and others just standing by. From what I've been able to gather so far, Group Replication in MySQL, and Galera, which MariaDB and Persona rely on, all require the tables to have a primary key.

Is there a way to untangle this knot? Is there a way to set up a highly available MySQL-like database that would not require the primary keys for cases when changing the schema is highly undesirable, or this is rather impossible?

Answer 1

I don't see a solution at the intersection of MySQL Group Replication and third party-designed tables that don't meet the requirements of Group Replication.

I think your options are:

• Don't use MySQL Group Replication. You can configure reflexive replication manually. That is, each source node is the source for the other. It takes several steps to do it, and it doesn't have the automatic failover features of MySQL Group Replication. But I worked at a job where we operated thousands of replication sets configured this way, using Percona Server but not Group Replication.

  You can do this immediately. It may be frustrating to know a Group Replication solution exists, but you can't use it for this third party application. At least you can use GR for other schemas under your control, or third party apps that are designed with proper tables.

• File a bug with the third party software developer and ask them to redesign their tables to have primary keys. I expect this will take years to get them to change (if ever).

• Fork the third party software and add primary keys yourself. This is probably a development burden you are not budgeted to take on.

• Don't use such applications in the first place. You have to wonder what other bad design decisions they made in their software, if they don't even make sure their tables have primary keys.