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.

 

[enerspace]

Hi, thanks for your information.

In another thread I already read what this is about, and nevertheless I also agree with you here: The implementation and fixing of the bug cast a very poor light on the Plesk team. The licenses are becoming continuously more expensive, while the quality seems to be declining just as steadily. The published script also does not work correctly. Deletion suggestions are made even though the entries are not duplicates, and furthermore not all scripts have been adapted to the new database schema. The repair tool, for example, continues to compare against the old database schema.

 

[hschramm]

The repair tool, for example, continues to compare against the old database schema.

This is my major concern. There is a so called "repair tool" and it does not what it says. It does not detect the error nor does it help in any way. This should taken into account asap, so there is no need to hand out a wonky bash / awk script which needs to be manually intervented to get this issue fixed correctly.

 

[Sebahat.hadzhi]

@enerspace , thank you for the report. According to our developers the repair utility cannot fix the issue with the duplicate entries. At this point, the root cause of their appearance remains unclear. They should not exist in the first place. Plesk didn't create them. They existed before upgrading to 18.0.74 version when the change for the unescaped entries in mysql.db table was introduced.

The published script also does not work correctly.

Could you please provide more details regarding what doesn't work correctly so I can further consult with our team? Thank you in advance.

 

[Hangover2]

Some of our Plesk servers also have faulty entries in the mysql.db table.
We are affected by two types of inconsistencies:

a) Duplicate entries where the database name exists both with and without escaping the underscore (e.g. db_name and db\_name), leading to the duplicate entry error as mentioned:



b) Entries that were not converted/escaped and are therefore still vulnerable to unintended access, because an underscore (_) is treated as a single-character wildcard in LIKE pattern matching: