Some of our tables can’t have a Primary key due to application architecture, So we will be setting PXC_STRICT_mode to Permissive permanently, wanted to know can it cause any impact on replication or create any PXC cluster level issues or what all cautions required in permissive mode example: if bootstrap is required ?
Secondly, our env requires restoring data on PXC from standalone MySQL native env but when we restore it we get the below error and some data is lost so for that as well we need to set PXC_strict_mode to Permissive.
Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE WITH READ LOCK with pxc_strict_mode = ENFORCING when doing LOCK TABLES
I can’t fathom any reason why simply adding int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT to these tables would kill your application’s architecture. Your application simply ignores that column when doing SELECTs and does not provide any value on INSERT/UPDATE. MySQL handles the rest.
Yes it will. When PXC replicates transactions between nodes, the PK is used as the internal identifier of writesets. Without PKs, writesets must include far more data and therefore take longer to process.
Hi @matthewb,
Thanks for responding, we are using simple mysqldump command to take backup of databases one by one from mysql native stand alone system and restoring it by just doing “source backup-file.sql” on one of the pxc node and we get above mentioned error and some of the data is also lost in this process but when we set strict mode as permissive we are able to restore all data without error. Is it the correct way or how to proceed?
Regarding Primary key we are in discussion with application team as they are also using some other third party tool within the application which require third party consent before adding primary key and which is making this simple process much complicated.
But as you mentioned without PK it may take longer to process data but correct me if I am wrong that still data willl be consistent on all nodes?
Consider adding an INVISIBLE primary key. This way the application has absolutely no idea you added it and PXC writesets will be more optimal. Even if the application does ‘SELECT *’ the invisible key will not be returned.
Well, that’s your problem. You need to backup and restore as a single operation, not individually. For better performance, and better functionality, I suggest you look at mydumper. You can “snapshot” the entire mysql server in one go, then restore all at once to PXC. When you do this, you need to stop the other 2 PXC nodes. After you’ve completed the import on node1, start node2 which will SST from node1, and then start node3. Then configure replication from mysql → node1.
The delay doesn’t come from this setting. The delay comes from your writesets. If you don’t have many writes, you might not see much of a delay. If you are heavy insert/update, then you might feel it more. You can watch transaction latency in PMM.