An operation fails in Plesk: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘X’ for key ‘PRIMARY’

Dmitry Bystrov

10 months ago

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
1. Log in to Plesk.
2. Go to go to Tools & Settings > Diagnose & Repair.
3. Click Repair next to Plesk Database.
Repairing the database via a command-line interface
1. Connect to the Plesk server via SSH (Linux) / RDP (Windows Server).
2. Start the repair task:
  
  # plesk repair db

For older Plesk versions

Download and use the script to automatically update the improper AUTO_INCREMENTS value:

For Plesk on Linux

Connect to the Plesk server via SSH.
Create a backup of the Plesk database:

# plesk db dump > psa_dump.sql
Download the script:

# curl -LO https://support.plesk.com/hc/en-us/article_attachments/360009691780/115003292185_auto_increment_fix.php.tar.gz
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.

For Plesk on Windows Server

Connect to the Plesk server via RDP.
Download the script to your Windows Server.
Extract the script from the zip file on C: disk.
Start a command prompt as an Administrator.
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.

0 Shares