Articles in this section

See more

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

Avatar
Kuzma Ivanov
Updated
Follow
Plesk for Windows Plesk for Linux FR:PPM-2447 kb: technical ext: le

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

    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

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.

 

Comments

18 comments

Sort by Date Votes
  • Avatar
    Stéphan Schamp

    Can a check for this be added to plesk repair db?

    We've had this issue on dns_zone table.

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Stéphan Schamp

    As a sidenote, we suspect that an upgrade we did to MariaDB 10.2 (from 5.5 to 10.1 and then to 10.2) might be the cause of this, but this upgrade was done over a week ago.
    We asked our customer if they had added any other domains since the upgrade, they did not.

    2
    Comment actions Permalink
  • Avatar
    Alisa Kasyanova

    @Stéphan Schamp
    Hi! Our developers are aware of such issue and already implementing such functionality in plesk db utility. You may use the ID PPM-2625 to track when it in Plesk Change log here: https://docs.plesk.com/release-notes/onyx/whats-new/
    The root cause of AUTO_INCREMENT corruption is still inclear, however, it seems that such upgrade may cause it. The development team is checking it.

    1
    Comment actions Permalink
  • Avatar
    Websavers Inc

    We, too, have encountered this issue after upgrading to MariaDB 10.2 from 10.0. In our case it was the certificates table.

    Also, your example for the certificates table, step 4 is missing a close quote. It should be this:

    # plesk db "ALTER TABLE certificates AUTO_INCREMENT = 82;"

    0
    Comment actions Permalink
  • Avatar
    Anton Maslov

    @Websavers fixed, thank you for noticing!

    0
    Comment actions Permalink
  • Avatar
    Giannis Zacharioudakis (Edited )

    This time i made the migration, i had more than one errors.
    I update the AI of tables:

    • DomainServices
    • domains
    • certificates
    • dns_zone


    to check (replace DomainServices table_name below to check the other tables too)

    SELECT (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "psa" AND table_name = "DomainServices") AS AI, MAX(id) + 1 FROM DomainServices;


    if the values are the same, then the table is ok
    else set AI the same as MAX(id)+1 (the AI is wrong and should be updated with MAX(id)+1)

    ALTER TABLE DomainServices AUTO_INCREMENT = 72;

    to detect
    The warning has the error with duplicate id=20 (eg.) so this time the AI is set (possibly false) to 21 for that table and for the next inserted row.
    We do not know the table (it does not provided in the error message)
    So, executing

    SELECT table_schema as 'Database', AUTO_INCREMENT AS 'AI', table_name AS 'Table' FROM information_schema.TABLES WHERE TABLE_SCHEMA = "psa" AND AUTO_INCREMENT=21 ORDER BY (table_schema) ASC;

    we might have the candidates tables to check for non-aligned values.

    Please follow the above solution with caution and i am not responsible for any damage it may cause. This worked for me.

    PS. I am also curious about why the plesk repair db do not fix errors like these. I think that it would be some conflict issues.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @GIannis,

    Thank you for sharing your experience, it may be useful for other Pleskians.

    > I am also curious about why the plesk repair db do not fix errors like these. I think that it would be some conflict issues.

    Thank you for the idea, I have created a feature request at Plesk User Voice, feel free to vote for this feature as popular suggestions are likely to be implemented.

    1
    Comment actions Permalink
  • Avatar
    Sebastian Lange

    Hello, same problem here after updating to MariaDB 10.3.X

    After having fixed the dns_zone table (as proposed by Giannis) I was able to add new domains in Plesk.

    But: when trying to add domains which were registered (in the past) and removed (in the past), following error message is returned:

    Error: mailmng-outgoing failed: ERROR:outgoing:column name is not unique

    Any idea?

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Sebastian,

    Try this article https://support.plesk.com/hc/en-us/articles/213371469-Domain-or-mail-account-creation-error-mailmng-outgoing-failed but, please, make sure that you have created a database backup before applying a solution.

    0
    Comment actions Permalink
  • Avatar
    TomBob

    Same error here. domains table.

    plesk repair db does not repair the issue.

    Provided script works, but leaves errors for sys_user table

    0
    Comment actions Permalink
  • Avatar
    Nikita Nikushkin

    Hello @TomBob,

    The script has to resolve the issue for the "sys_user" table as well

    What is the error message for the "sys_user" table and which actions lead to its appearing?

    0
    Comment actions Permalink
  • Avatar
    Mitchell van Bijleveld (Edited )

    This error also occurs when resetting a DNS zone (because it shows as empty within the domain > DNS settings). Can you confirm? The solution provided works perfectly.

    0
    Comment actions Permalink
  • Avatar
    Maxim Krasikov

    Hello @Mitchell van Bijleveld,

    The error may be related to any table in Plesk database and occur in using different Plesk functionality, including DNS settings.
    The solution is universal and fixed the inconsistency in all tables automatically.

    0
    Comment actions Permalink
  • Avatar
    METANET Support

    MariaDB fixed a bug regarding this problem: https://jira.mariadb.org/browse/MDE-6076

    But it seems it is not completely fixed https://jira.mariadb.org/browse/MDEV-20892

    0
    Comment actions Permalink
  • Avatar
    Alexandr Nikolaenko

    Hello,

    Thank you for the information.

    The mentioned MariaDB jira items are referenced to different versions and confirm that the issue is specific for MariaDB server 10.2.3 and lower.

    0
    Comment actions Permalink
  • Avatar
    Brian (Edited )

    Hello,

    I'm the guy that made https://jira.mariadb.org/browse/MDEV-20892

    I updated the issue yesterday since i'm still busy figuring out what this problem causes. In the meantime we've tried multiple mariadb versions: 10.1.38, 10.2.27, 10.2.28 and 10.3.20. This problem happended to me in all these versions. Can someone here confirm that this problem is also happening in their environment? For me this problem caused major data corruption and I'm looking for a way to fix it for almost 2 months now. For now i have contained this problem with a script that fixes the auto_increments based on the max id of the primary key but obviously thats not a long term solution.

    I think this problem is caused by wrong evicted cache from innodb where the auto_increment gets flushed to disk. Im currently testing different situations with different innodb_autoinc_lock_mode's and the combination with IODKU statements.

    If someone has any information about a possible fix or bug in mariadb. Please let me know.

    0
    Comment actions Permalink
  • Avatar
    burnleyvic

    Just encountered this error on a Windows 2012 R2 running Plesk Obsidian Version 18.0.23 with MariaDB 10.3.17 when trying to clone a service plan. In our case the tables affected were clients, dns_zone, domains, log_rotation and mail_resp. Plesk would err on log_rotation.

    0
    Comment actions Permalink
  • Avatar
    TomP

    I just had this same problem on my CentOS server after adding a new website

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles