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