Articles in this section

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

Kuzma Ivanov
Updated
Plesk for Windows Plesk for Linux kb: technical ABT: Group B FR:PPM-2447

Applicable to:

  • Plesk for Linux
  • Plesk for Windows

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:

    PLESK_ERROR: Error: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'X' for key 'PRIMARY', query was: replace <table_name> set ...

    PLESK_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:
    "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

 

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

 

  1. Connect to the Plesk server via SSH.

  2. Create a backup of the Plesk database:

    # plesk db dump > psa_dump.sql

  3. Download the script:

    # curl -LO https://support.plesk.com/hc/en-us/article_attachments/360009691780/115003292185_auto_increment_fix.php.tar.gz

  4. Unpack the script:

    # tar xf 115003292185_auto_increment_fix.php.tar.gz

  5. Run the script to analyze the tables:

    # plesk php 115003292185_auto_increment_fix.php -v -y

  6. Rerun the script until you see:

    MYSQL_LIN: ...
    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:

    C:\> plesk db dump > C:\psa_dump.sql

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

    C:\> cd C:\

  7. Run the script:

    C:\> plesk php 115003292185_auto_increment_fix.php -v -y

  8. Rerun the script until you see:

    MYSQL_WIN: ...
    No inconsistencies were detected.

 

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.