Plesk Update fails with errors: Integrity constraint violation: 1062 Duplicate entry db_name db\_name' for key 'PRIMARY'

Symptoms

• The following error show up in Plesk panel when trying to upgrade Plesk:

  PLESK_ERROR: ERR [panel] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'localhost-db\_name for key 'PRIMARY'`

• No error is displayed when running plesk repair db -n command.
• Database tables do not have misconfigured AUTO_INCREMENT that can lead to duplicate primary keys when inserting new records.
• mysql.db database table includes two entries, where only Db field differs by escaping underscore with backslash, for example db_name and db\_name

Cause

In Plesk Obsidian 18.0.74, an upgrade step was introduced to address a security issue where database names containing underscores were not properly escaped in the mysql.db table. This could allow unintended access to databases.

The process attempts to fix this by converting names like db_name to db\_name. However, if an entry with the escaped name already exists, the upgrade fails during the FixGrantPrivileges task.

Resolution

In order to resolve it, delete one of the entries:

1. Connect to the Plesk server via SSH.

2. Back up MySQL database:

   # plesk db dump mysql > /root/mysql_backup.sql

3. Confirm the existing entries:

   # plesk db "SELECT Host, Db, User FROM mysql.db WHERE Db LIKE 'db_name%';"

4. Delete one of the entries from the mysql.db table based on the details from the error:

   # plesk db "DELETE FROM mysql.db WHERE Host = 'localhost' AND Db ='db_name' AND User = 'db_name';

5. Reload the grant tables with the following command:

   # plesk db "FLUSH PRIVILEGES;"

6. Proceed to complete the upgrade
7. If the error appears again for a different database, repeat steps 3. to 6.