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?

  • If you're not stuck on MySQL and derivatives, you could do that with Postgres.
    – mustaccio
    Commented Jan 24 at 16:25
  • SHOW CREATE TABLE for one of the tables without a PK.
    – Rick James
    Commented Jan 24 at 16:50
  • @mustaccio I am, unfortunately, as the third party software requires MySQL (or a derivative).
    – kamazee
    Commented Jan 24 at 20:17

1 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.

  • There's Galera cluster for most of the schemas (of which there are plenty), and also I'd like to have a dedicated solution for the two outliers (which are really a fraction of percent of all the databases and all the data) that would make quick disaster recovery possible. Luckily, automated failover is not a must here, so it might be a way to go, really. Is there a searchable term for what you refer to as "reflexive replication" or maybe you have any links at hand that would shed more light on what it is?
    – kamazee
    Commented Jan 24 at 20:32
  • digitalocean.com/community/tutorials/… Commented Jan 24 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.