Plesk repair / constraint violation: 1062 Duplicate entry

[enerspace]

Username:

TITLE

Plesk repair / constraint violation: 1062 Duplicate entry

PRODUCT, VERSION, OPERATING SYSTEM, ARCHITECTURE

Plesk Obsidian 18.0.75, CloudLinux 8.10

PROBLEM DESCRIPTION

When you enter the command: plesk repair all -y the following error message appears:

- 0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46
    - ERROR: Plesk\Upgrade\Exception: SQLSTATE[23000]: Integrity
      constraint violation: 1062 Duplicate entry
      'localhost-****\_****' for key
      'PRIMARY' (upgrade.php:46)
    - 0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46
    -
    - Some steps of upgrade failed. Run upgrade with option --repair
      to rerun failed steps.

I was able to narrow down the problem further. When a new database and a new database user are created, the following entry is generated in the mysql.db table:

db=admin\_testoooo
user=admin_testoooo

However, the entry should be created as follows:

db=admin_testoooo
user=admin_testoooo

STEPS TO REPRODUCE

1. Create a new database and a new database user.
2. Run the command plesk repair mysql -y.
3. The newly created database is listed as well. Every time the command is executed again.
   Attaching the user admin_testoooo to the database admin_testoooo ...... [FIXED]

ACTUAL RESULT

Because of this error, a comparison error also occurs when running the command plesk repair mysql -y. It checks whether db=admin_testoooo exists. If not, it is created. In this case, however, it is created incorrectly as db=admin\_testoooo. As a result, the Repair Toolkit keeps trying to create this entry over and over again.

EXPECTED RESULT

It should always create the entries using the following schema:

db=admin_testoooo
user=admin_testoooo

The Repair Toolkit should also check whether the entry admin_testoooo exists. If not, it should be newly created. In addition, the toolkit should also delete the admin\_testoooo variants so that the error constraint violation: 1062 Duplicate entry does not occur.

ANY ADDITIONAL INFORMATION

I do not know at which step the Plesk Panel creates a new entry:

db=admin\_testoooo
user=admin_testoooo

even though an entry already exists:

db=admin_testoooo
user=admin_testoooo

This causes the previously described constraint violation: 1062 Duplicate entry error.

YOUR EXPECTATIONS FROM PLESK SERVICE TEAM

Confirm bug

 

[enerspace]

Please check this: Resolved - Upgrade to 18.0.75 Integrity constraint violation: 1062 Duplicate entry 'localhost-database\_-user' for key 'PRIMARY':

My report should extend the ticket accordingly. The problem is still occurring!

 

[hschramm]

db=admin\_testoooo
user=admin_testoooo

This is correct. In MySQL only database names and table names allow '_' (underscore) as a wildcard character. So for security reasons they have to be escaped. For example:

A grant like GRANT ALL ON db_1.* TO 'user'@'%' could match databases such as db01, dba1, or db-1, resulting in giving more permissions than actually intended or expected. In cases with multiple underscores (e.g., db_1_1), the risk escalates dramatically because of the double wildcard.

That Plesk fixes this bug is correct, but how they did it show a poor quality management / testing. So are additional database servers untouched and do not get fixed overall, even if they are added as database servers in plesk and usable via the hosting.

I also condemn the fact that a script was only provided upon request. I am certain that there are many servers out there that have not been adequately fixed, meaning that these systems are likely to remain vulnerable.