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

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 … Error: 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: 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.
• The AUTO_INCREMENT value for the table from the error message is equal or less than max PRIMARY KEY value of this table:
• plesk db -Ne’SELECT CONCAT(“Auto increment value: “, AUTO_INCREMENT) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = “psa” AND TABLE_NAME = “DomainServices“;’
  Auto increment value: 45
  
  # plesk db -Ne’SELECT CONCAT(“Primary key name: “, COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = “psa” AND TABLE_NAME = “DomainServices“;’
  Primary key name: id
  
  # plesk db -Ne’SELECT CONCAT(“Max value: “, MAX(id)) FROM DomainServices’
  Max value: 46

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

Download and use the script to automatically update the improper AUTO_INCREMENTS value: For Plesk on Linux

1. Connect to the Plesk server via SSH.
2. 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

1. Connect to the Plesk server via RDP.
2. Download the script to your Windows Server.
3. Extract the script from the zip file on C:\ disk.
4. Start a command prompt as an Administrator.
5. Create a backup of the Plesk database:

plesk db dump > C:\psa_dump.sql

Switch to the folder where extracted script on step 2 is located.

cd C:\

Run the script:

plesk php 115003292185_auto_increment_fix.php -v -y

Rerun the script until you see:

…
No inconsistencies were detected.