We are currently using a Master-Master-Slave replication setup for our MySQL databases, with ProxySQL acting as the load balancer. The setup is used in an eCommerce environment where the application (an OXID eShop) processes user orders. However, we are encountering an issue related to failover and data consistency.
The problem occurs when one of the masters (Master2) is down. During this time, when we attempt to place an order or interact with the database, we receive the following error:
Integrity constraint violation: 1062 Duplicate entry ‘9872187e89ebebf364de00b756f33e6c’ for key ‘PRIMARY’ at /var/www/daily/auna/eshop/releases/20241022070743/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:74
Doctrine\DBAL\Driver\PDO\Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘9872187e89ebebf364de00b756f33e6c’ for key ‘PRIMARY’ at /var/www/daily/auna/eshop/releases/20241022070743/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
PDOException(code: 23000)
It appears that ProxySQL is not properly handling the failover in certain scenarios, as the error arises when one of the masters is unavailable. This leads to issues with the data integrity and results in duplicate key violations when trying to insert records.
Could you kindly provide guidance on the following:
Failover Configuration: Recommendations on configuring ProxySQL to seamlessly point to the other master (Master1) when Master2 is down, without causing transaction issues.
Data Consistency: Best practices to avoid duplicate key violations during the failover, especially in a Master-Master replication setup.
Transaction Safety: Advice on ensuring that in-flight transactions do not cause inconsistency or deadlocks when one master is down and ProxySQL switches the traffic.
Your assistance would be greatly appreciated in resolving this issue as it directly impacts the order flow on our eCommerce platform.
@rahul_ambekar,
Configure ProxySQL so that sourceA has a weight of 1000000 and sourceB has a weight of 1. This will help ensure that proxysql always sends WRITE traffic to sourceA unless A is down, then send to B.
Also, 9872187e89ebebf364de00b756f33e6c is a horrible PRIMARY KEY. String-based primary keys are the worst performing as they generate random writes.
I am still facing the same issue:
: Integrity constraint violation: 1062 Duplicate entry ‘3ddda7ce305fea8f54118efa4c631f65’ for key ‘PRIMARY’ at /var/ww
w/daily/auna/eshop/releases/20241022070743/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 23000): S
QLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3ddda7ce305fea8f54118efa4c631f65’ for key ‘PRIMARY’ at /var/www/daily
/auna/eshop/releases/20241022070743/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:55)\n[stacktrace]\n#0 /var/www/daily/auna/eshop/releases/20241022070743/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php(797): OxidEsales\EshopCommunity\Core\Database\Adapter\Doctrine\Database->convertException()\n#1 /var/www/daily/auna/eshop/releases/20241022070743/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php(566):
ProxySQL is not a replication manager. It will not fix issues within the database. You need to manually fix this dup primary key issue and restart replication.
I HIGHLY RECOMMEND that you enable GTID-based replication (many blogs on our website), which can help with duplicate issues.
You should not have anything in slave-skip-errors to make sure database is consistent on master and replica.
If there are any errors that should be resolved.
Your application is trying to enter duplicate values for primary key which is not allowed on database. It should be fixed on application side or change the database table structure if that is required.
I wanted to update you on the recent changes we’ve made to the MySQL setup and the application logic. In our current Master-Master-Slave replication setup with ProxySQL, I’ve configured the following weights:
Master1: 10,000,000
Master2: 1
This ensures that all write requests are routed to Master1 unless it fails, in which case ProxySQL sends the write request to Master2 to achieve failover.
On the application side, we were previously encountering error 1062 (duplicate entry for primary key) during inserts. To resolve this, I made the following code changes:
Before:
php
Copy code
$insertSql = "INSERT INTO {$this->getCoreTableName()} SET ";
After:
php
Copy code
$insertSql = "REPLACE INTO {$this->getCoreTableName()} SET ";
This change has effectively addressed the duplicate key error and ensures that ProxySQL handles failover correctly. Now, when Master1 fails, the application is able to send write requests to Master2 without encountering the duplicate entry issue.
From my understanding:
INSERT will throw an error if the primary key already exists in the database.
INSERT IGNORE will bypass the insert statement if the primary key already exists.
REPLACE will delete any existing record with the same primary key and then insert the new data.
Could you please confirm if it is safe to use REPLACE instead of INSERT from the application’s point of view, especially considering that REPLACE deletes any existing data with the same primary key before inserting a new one? I want to ensure this approach is reliable and won’t lead to any unexpected data loss or integrity issues.
Now, your application runs REPLACE with the following: (1234, ‘Bob’, ‘Status G’)
The data for ‘Alice’ is deleted, and replaced with ‘Bob’. You have now lost data.
You need to continue using INSERT, and fix the mechanism that you are using for generating primary keys. Since you are not using AUTO_INCREMENT, you must have some other system that is generating PKs, and this system is out of sync with the database.
After you fix that, do some research on using pt-table-checksum, and pt-table-sync to fix your source/replicas so they have the same data.