Symptoms
When adding a customer, subscription, mail account, mailing list, IP address, changing domain hosting type or installing a Let's Encrypt SSL certificate in Plesk, the operation fails with one of the following error messages:
Error: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'X' for key 'PRIMARY', query was: replace <table_name> set ...
Could not issue a Let's Encrypt SSL/TLS certificate for example.com.
Details
Failed to execute XML-RPC operation 'install/certificate': DB query failed:
"replace certificates set id='0', name='Lets Encrypt example.com', csr='-----BEGIN+CERTIFICATE+REQUEST----When installing a custom instance of WordPress at Domains > example.com > WordPress > Install > Install (Custom), the operation loads continuously and no errors appear.
Cause
The AUTO_INCREMENT value for the table is lower than the real maximum PRIMARY KEY value.
This issue could be caused when InnoDB and XtraDB use an auto-increment counter stored in the memory. When the server restarts, the counter is re-initialized. This issue is mentioned for a certain MariaDB version.
Resolution
For Plesk Obsidian 18.0.28 Update 3
Error messages related to an incorrect AUTO_INCREMENT value in the Plesk database now show a suggestion to fix the error using the Repair utility (either in the Plesk interface or in the CLI).
Repairing the database via Plesk
Go to go to Tools & Settings > Diagnose & Repair.
Click Repair next to Plesk Database.
Repairing the database via a command-line interface
Connect to the Plesk server via SSH (Linux) / RDP (Windows Server).
Start the repair task:
# plesk repair db
For older Plesk versions
Download and use the script to automatically update the improper AUTO_INCREMENTS value:
Connect to the Plesk server via SSH.
Create a backup of the Plesk database:
# plesk db dump > psa_dump.sql
Download the script:
Unpack the script:
# tar xf 115003292185_auto_increment_fix.php.tar.gz
Run the script to analyze the tables:
# plesk php 115003292185_auto_increment_fix.php -v -y
Rerun the script until you see:
...
No inconsistencies were detected.
Connect to the Plesk server via RDP.
Download the script to your Windows Server.
Extract the script from the zip file on C: disk.
Create a backup of the Plesk database:
C:> plesk db dump > C:psa_dump.sql
Switch to the folder where extracted script on step 2 is located.
C:> cd C:
Run the script:
C:> plesk php 115003292185_auto_increment_fix.php -v -y
Rerun the script until you see:
...
No inconsistencies were detected.
