I’m trying to setup PXC, but I’m having trouble because many tables don’t have a PK.
While I’m trying to fix the missing PK, the problem is that they are missing even in popular CMS tables that users are going to use. Plus at the moment I should fix over 2000 tables without PKs.
Since I’m going to have only 1 writer, can I just set PXC to permissive? Or it’s not related to the number of writers?
Or otherwise what other replication options do I have to avoid adding thousand of PKs (and anyway new cms setups will still have problems) and just copy the whole mariadb server into a new percona istance with mydumper?
You can use sql_generate_invisible_primary_key=1 to automatically create a Primary key on new tables that don’t have one. Set this before you do a mydumper import into PXC and all tables will get a PK.
The PKs help with the certification process which allows transactions to be processed in parallel. Without a PK, PXC has to use more/all columns to calculate collisions. You can go permissive, but depending on write volume, it might slow down txn apply phase.
Yea, I was reading about invisible PK right now.
I don’t get one thing: PKs are unique by default and throw erros if try to insert duplicate values, how is that handled with invisible PK?
Does it create a PK using all the columns directly to avoid duplicates or is a non-blocking type of PK?
I also read a reply of your in another thread about the slow down using permissive. That’s why I wanted to fix it. But trying to change thousand of tables by hand, even if a did some small scripting, is a pain.
Please read up on what it means to be an invisible column. Nothing changes in the behavior except if the column is returned in SELECT * The documentation link I provided earlier explains the PK is an unsigned auto increment integer.