apologies if my post

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello, apologies if my post is confusing or irrelevant I am new to the database/Percona world and just hoping to find any insight but I am running into an issue in our application and after searching the forums I am still having issues finding a solution/answer.

So just to give some background information I have a 3 node pxc setup sitting behind an haproxy lb. We set node1 as the primary node for HAProxy and if that node goes down it should failover to one of the other nodes.

Now on to the application so what it should do is create a record on Table A with a primary key (ID) then our application will create another record on Table B with a unique key with that same value, If there is already a record with that same Value in Table B our application will throw an error. When we delete the records on Table A our application does not clean up any records on Table B(which is done on purpose because we dont want to possibly reuse the values ever).

Now the issue we run into is for some reason (not sure if this is relevant or just pure coincidence but from what we noticed) sometimes after primary node goes down and recovers or service is rebooted, our Application tries to create a record on Table A but throws the error that there is already a value on Table B with that same value. From what I understand that should not happen due to Auto_Increment and the Increment_Offset . Its almost as if the increment on the Table goes backwards for some reason then tries to create the same value because records only get created on Table B when Table A creates a record.

Maybe I am misunderstanding something since I am new to this but any insight would be appreciated. I can provide configs if needed

2

Hello Jurick,
I’m just going to toss a couple things out there as I read over your post.

Firstly, are you aware that PXC auto-manages auto_increment_increment and auto_increment_offset based on the number of nodes in your cluster? Check these parameters on each node and you should see differences. If you want to disable this, set wsrep_auto_increment_control=OFF.

Next, are you running the insert into A and the follow-up insert into B within the same transaction? Remember that MySQL is autocommit=1 by default. So your application must explicitly call BEGIN/INSERT/INSERT/COMMIT in order for both to be within a transaction.

The primary going down/up and causing this situation is curious. All PXC nodes should always have the same data. If your primary goes down, and HAProxy moves operations to node2, that’s OK because we can write to any PXC node. But when node1 comes back online, it should start an increment-state-transfer (IST) and pickup all the delta changes that took place on other nodes and synchronize its data.

Please verify using SHOW CREATE TABLE that all tables involved are InnoDB.