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

Follow

Comments

16 comments

  • 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

Please sign in to leave a comment.

Have more questions? Submit a request