Unable to create object in Plesk: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry for key 'PRIMARY'

Follow

Comments

11 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!

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

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

  • 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;"

  • Avatar
    Anton Maslov

    @Websavers fixed, thank you for noticing!

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

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

  • 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?

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

  • 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

  • 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?

Please sign in to leave a comment.

Have more questions? Submit a request