Forwarded to devs Plesk Repair DB makes invalid attempt to repair

[Tozz]

TITLE:

Plesk Repair DB makes invalid attempt to repair​

PRODUCT, VERSION, OPERATING SYSTEM, ARCHITECTURE:

Plesk Onyx, latest version. Debian 8 or 9 (probably doesn't matter). MySQL is MariaDB​

PROBLEM DESCRIPTION:

If "plesk repair db" finds clients with a Pool ID 0 it returns a message that there is no IP Pool with ID 0. It asks the admin if it should create that IP Pool with ID 0.​

STEPS TO REPRODUCE:

Run "plesk repair db", and have atleast 1 client in the 'clients' table with a pool_id value of 0.​

ACTUAL RESULT:

New IP pool with an invalid value is created.

The 'ip_pool' table has the following layout:

MariaDB [psa]> describe ip_pool;    
+---------------+----------------------------+------+-----+---------+----------------+
| Field         | Type                       | Null | Key | Default | Extra          |
+---------------+----------------------------+------+-----+---------+----------------+
| id            | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |
| ip_address_id | int(10) unsigned           | NO   | PRI | NULL    |                |
| type          | enum('shared','exclusive') | NO   |     | shared  |                |
+---------------+----------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

A column with an auto_increment value CAN NOT have a value of 0, unless MySQL is explicitly configured to allow the value zero in an auto_increment column. Plesk doensn't set this parameter in MySQL.

Thus, as a result a new row with a new ID is created:

MariaDB [psa]> insert into ip_pool values(0, 1, 'shared');
Query OK, 1 row affected (0.00 sec)

MariaDB [psa]> delete from ip_pool where id = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [psa]> insert into ip_pool values(0, 999, 'exclusive');  
Query OK, 1 row affected (0.00 sec)

MariaDB [psa]> select * from ip_pool where ip_address_id = 999;
+------+---------------+-----------+
| id   | ip_address_id | type      |
+------+---------------+-----------+
| 1046 |           999 | exclusive |
+------+---------------+-----------+
1 row in set (0.00 sec)

As one can see, MySQL sees the value '0' as NULL, which means instead of saving the value 0 it just uses the next auto increment counter as value.​

EXPECTED RESULT:

plesk repair db should change the pool_id in the clients table, not create the pool as ID value of zero is invalid​

ANY ADDITIONAL INFORMATION:

​

YOUR EXPECTATIONS FROM PLESK SERVICE TEAM:

Confirm bug​

 

[IgorG]

STEPS TO REPRODUCE:
Run "plesk repair db", and have atleast 1 client in the 'clients' table with a pool_id value of 0.

Could you please clarify - what should be done before running this STR? Why did you need to run plesk repair utility? Now it looks like a consequence of the original problem, about which you did not say anything.

 

[Tozz]

Could you please clarify - what should be done before running this STR? Why did you need to run plesk repair utility? Now it looks like a consequence of the original problem, about which you did not say anything.

I have some issues with Plesk related to FPM, as I've explained here:
Issue - Issues with FPM

The "plesk repair db" did not solve any of these issues, but it did find some errors in the database. The cause of those errors is unknown, but we periodically run the Plesk repair tools about once a year, and it _always_ finds errors.

Now it looks like a consequence of the original problem

I _strongly_ disagree with this. This has nothing to do with the problem (if any) with my installation.

The issue is that "plesk repair db" makes a repair by inserting a value "0" on an auto_increment column, which MySQL translates to NULL.
The result is that Plesk did not repair the missing entry with ID 0, it created a new one with an auto incremented ID

As a result of this "plesk repair db" does not repair the actual issue, and thus will report the error every time you run the tool.

HOW I ended up with a client that had pool_id 0 in the psa database is a mystery to me, just as the mystery to why I almost always seem to have errors in Plesk's database. But again, regardless of the cause of the discrepancy, the attempt Plesk makes to restore the issue is WRONG and will NEVER work

It _should_ have updated the clients table, and remove the entries with ID 0

 

[IgorG]

From developer:

I do not confirm Plesk bug as it is not reproduced according to STR.
It is true that having an autoincremented value set to 0 results in saving next counter value and it is the expected behavior here as this next counter value will be saved as customer's pool_id.

These log lines:

MariaDB [psa]> insert into ip_pool values(0, 1, 'shared');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [psa]> delete from ip_pool where id = 0;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [psa]> insert into ip_pool values(0, 999, 'exclusive');
Query OK, 1 row affected (0.00 sec)

are clearly not produced by repair db utility as it sets all IP addresses as shared as a result of repair.

 

[Tozz]

I've tried to replicate this issue on one of our 'Plesk test boxes', but failed. The developer's response is correct on my test machine, but is wrong on my production machine.

- On my test machine the client table is updated with the correct newly created ip_pool value.
- On my production machine the client table _is not_ updated, and remains value 0. Thus, never actually fixing this issue.

Perhaps this issue has been resolved in the meantime, or there are other dependencies for this issue to trigger.

 

[Tozz]

Funny enough, I have the same exact issue again, on another machine, 3 years later:

Checking the consistency of the Plesk database

  Inconsistency in the table 'clients' for the column pool_id: There  
  is no IP pool with ID 0 ........................................... [ERROR]
    Creating a database dump ........................................ [OK]
    A dump of the database was created before an attempt to repair the database: /var/lib/psa/dumps/mysql.preresolve.20210312-154822.dump.gz
    Creating an IP pool ............................................. [FIXED]

Error messages: 1; Warnings: 0; Errors resolved: 1

It is true that having an autoincremented value set to 0 results in saving next counter value and it is the expected behavior here as this next counter value will be saved as customer's pool_id.

This is false. It is *not* expected behaviour. What the "plesk repair db" is doing is:

- Hey I see a pool_id with value 0 in the clients table. But there is no IP pool with ID 0
- I Shall fix this by creating an IP pool with ID 0, by doing a MySQL insert query with value '0' on an auto_increment column.
- MySQL sees the '0' as a value that should be replaced with the auto increment counter, and will create a new IP pool with the next available number.

At the end: Plesk did *NOT* create an IP pool with ID 0. So, the next "plesk repair db" run, it will see the same error and will try to implement the same non-working fix again. Over and over again.

This *IS* is a bug

 

[johnny_silverhand]

I can confirm that I faced exactly the same problem. Plesk repair tries to fix the problem, but rechecked with the same result.