Resolved Plesk update: ERROR while trying to backup MySQL database

[Patashoow]

Server operating system version

Ubuntu 20.04

Plesk version and microupdate number

Plesk Obsidian 18.0.47

Hello,

I tried to update my Plesk version from 18.0.47 to the latest available one (18.0.60) from my Plesk panel (Tools & Settings>Plesk>Updates) and got the following error :

Trying to find Plesk database psa... version is 018000000
Trying to backup MySQL database... mysqldump: Got error: 1066: "Not unique table/alias: 'apirpccallsstat'" when using LOCK TABLES

ERROR while trying to backup MySQL database


**** Product prep-install for BASE failed.

mysqldump: Got error: 1066: "Not unique table/alias: 'apirpccallsstat'" when using LOCK TABLES
***** problem report *****
ERROR while trying to backup MySQL database

I didn't find anything about this specific issue on the internet. The only similar case I found didn't really resolve the problem and it led to an even worse situation.

So how could I safely fix that and be able to update my Plesk version?

Thank you for any help you could give.

 

[Kaspar]

I recommend contacting Plesk support so they can investigate the issue on your server directly.

To sign-in to support please go to https://support.plesk.com

If you experience login issues, please see this KB article:
https://support.plesk.com/hc/en-us/...rt-plesk-com-and-password-reset-does-not-work

If you bought your license from a reseller, your reseller should provide support for you. If the reseller does not provide support, here is an alternative:
https://support.plesk.com/hc/en-us/articles/12388090147095-How-to-get-support-directly-from-Plesk-

 

[Patashoow]

Thank you @Kaspar for your reply. My license was indeed not purchased from Plesk and I addressed my issue to my host but it seems that it's the least of their worries. I'll try to contact them again.

Anyway, I found an old post with the same problem and it's been suggested to backup the database and then delete the duplicated tables.

The problem is that I cannot even create a dump of the database as I have the exact same error message. It's really tricky. So is it still safe to perform the deletion of the duplicated tables (I found 123 duplicated names). And will that solve the problem or it's not even sure?

 

[Kaspar]

I am not sure to be honest

 

[Patashoow]

Yeah that's what I was worrying the most. So in fact I absolutely have to ask for an official support or it would be like rolling dice right?

The most frustrating part is that I haven't found this problem anywhere else on the internet and the only one I found didn't solve it and the OP ended reinstalling Plesk from scratch.

Maybe that someone will have a solution. Let's see.

Thank you again for your help.

 

[Kaspar]

Yes, hopefully some else knows and can help.

I do want to point that the first month of the Plesk support subscription is free. (As it's an monthly subscription, you can always cancel before the next month starts).

 

[Patashoow]

Yes thank you for the suggestion but I don't think that $10/month is a huge amount and if they did right their job then the 10 bucks would be deserved

 

[Patashoow]

So, I reached out to the Plesk customer service to ask if I could get support to upgrade my Plesk version and the fun(?) part is that my Plesk version is actually too old and not supported anymore.

So is there really no one who has any idea on how I could solve this problem?

 

[Dave W]

Youre on Plesk 18.0.47 correct?

That upgrade to 18.0.60 should be supported.

See here: Issue - Plesk not able to update | mysqldump cant dump psa database

 

[Patashoow]

Thank you @Dave W for your reply but as stated in a previous message, I've already checked that link and couldn't do a backup of the database before trying anything.

Thank you @Kaspar for your reply. My license was indeed not purchased from Plesk and I addressed my issue to my host but it seems that it's the least of their worries. I'll try to contact them again.

Anyway, I found an old post with the same problem and it's been suggested to backup the database and then delete the duplicated tables.

The problem is that I cannot even create a dump of the database as I have the exact same error message. It's really tricky. So is it still safe to perform the deletion of the duplicated tables (I found 123 duplicated names). And will that solve the problem or it's not even sure?

 

[Patashoow]

I'm wondering if the duplicate tables in the PSA database already existed in the original setup or is it a result of a bug or something. When I compared "ApiRpcCallsStat" and "apirpccallsstat" table, they are exactly the same and contain exactly the same data.
My OS is using ext4 file system but the value of "lower_case_table_names" in MariaDB configuration is set to "1" so technically "ApiRpcCallsStat" and "apirpccallsstat" is the exact same table. I don't know how the duplicate tables can even exist in the database.

But the main problem is that Plesk installer could not backup MySQL database with the error 1066.

 

[Kaspar]

So, I reached out to the Plesk customer service to ask if I could get support to upgrade my Plesk version and the fun(?) part is that my Plesk version is actually too old and not supported anymore.

That's a real bummer ...

So is there really no one who has any idea on how I could solve this problem?

Since you stated that both tables contain the same data I guess both are used. So deleting one of them probably isn't a option. What you could try however is to temporarily rename one table (I'd go for lower case table). Then manually create a database dump afterwards to see if there are still dump related issues. If not, you could try updating Plesk again. If the updated succeeded you can rename the table back. And afterwards contact support again to let them investigate the issue (you now on a supported version again after all).

A database dump can be made running plesk db dump psa /home/psa_backup.sql via command line. I am not completely sure of this approach works, but it's worth a try I think. Make sure to make a backup before you start (better safe than sorry).

If you run into any other issues post them here.

 

[Patashoow]

Temporarily renaming tables is exactly what I was about to do. I was just looking for a way to backup the database and I just did it through the webadmin panel. When I saw the tables directly via phpMyAdmin it's even clearer. Most of the tables are empty and all duplicate ones are exactly the same.
The interesting part is that when I exported the database and as I was expecting, all table names were in lowercase as my "lower_case_table_names" is set to 1. So I have in the result the exact same tables twice.

I'm still wondering how could that happen but anyway I'd update this post as I try to fix my issue.

However, I'm still curious to know if they are really used or not.

Also if I keep the duplicate tables, the same problem will occur again if a backup is triggered.

 

[Kaspar]

Temporarily renaming tables is exactly what I was about to do. I was just looking for a way to backup the database and I just did it through the webadmin panel. When I saw the tables directly via phpMyAdmin it's even clearer. Most of the tables are empty and all duplicate ones are exactly the same.
The interesting part is that when I exported the database and as I was expecting, all table names were in lowercase as my "lower_case_table_names" is set to 1. So I have in the result the exact same tables twice.

I'm still wondering how could that happen but anyway I'd update this post as I try to fix my issue.

Good, hopefully your update will go trough without any further issues.

However, I'm still curious to know if they are really used or not.

Also if I keep the duplicate tables, the same problem will occur again if a backup is triggered.

Probably, which is why it's is best to contact support again after (/if) the update succeeds. Hopefully thay can fix the issue. Otherwise you end up with the same problem again.

 

[Patashoow]

Ok so the problem was in fact related to MariaDB configuration. The value of "lower_case_table_names" in "/etc/mysql/my.cnf" was set to "1". In this situation, table names and database names are stored in lowercase and compared in a case-insensitive manner. The default value on Unix-based systems is "0" which indicates on the contrary that the comparison should be done in a case-sensitive manner.

What I didn't know is that the value of "lower_case_table_names" was modified! As our development environment runs under Windows (where the default value is "1") when we deployed our application we just wanted to make it easy by modifying that value as some bugs started to occur due to the table names we used in MySQL queries rather than rechecking the whole code as it was quite huge.

So at the very first setup, Plesk database is created with table names containing upper and lower case. I found the database schemas for those who are interested. But after switching the value of "lower_case_table_names" to "1", all the used tables became in lower case ("ApiRpcCallsStat" -> "apirpccallsstat" for e.g. and 123 more) hence the duplicates.
With "lower_case_table_names" set to "1" I couldn't even access tables such as "ApiRpcCallsStat" so I wasn't able to rename them nor delete them.

So now I switched back the "lower_case_table_names" to "0" and it's working just fine but with one downside: all the changes made in Plesk after modifying MariaDB configuration are lost. I still haven't verified if all the duplicate tables have exactly the same data but if it's the case, I guess that the lost data weren't stored in the PSA database but somewhere else into files.

I don't know if it's indicated somewhere and I missed it but be aware that changing "lower_case_table_names" when Plesk is already installed can lead to such issues.

 

[Dave W]

Sorry for the tangent but you were on Plesk 18.0.47 and the support team would not assist you in this issue?
Can someone from Plesk comment on this?

Its not like hes on Plesk 11.5

 

[Patashoow]

Sorry for the tangent but you were on Plesk 18.0.47 and the support team would not assist you in this issue?
Can someone from Plesk comment on this?

Its not like hes on Plesk 11.5

Yes it was quite frustrating for me but in fact the support is only available for the two latest releases. Just to make it clear, I didn't purchase my license from Plesk and what was even more frustrating is that my web host couldn't care less. But thankfully I could fix the issue so I gifted myself 10 bucks.

 

[Patashoow]

I finally fixed the issue and I don't really know if someone else will ever have this problem but I'll nonetheless share the solution.

So as stated in my previous message, the main problem was related to MySQL/MariaDB configuration. The value of lower_case_table_names in "/etc/mysql/my.cnf" was the culprit.
By default, the value is not even defined within the configuration file and depending on your OS, it will be considered to be "0" for Unix-based systems and "1" for Windows systems.
As my server is running under Ubuntu, when Plesk was first installed, the value was "0". But after deploying our application, we switched that value to "1" to fit our development environment which led to rename PSA tables and convert their names to lower-case (example: "ApiRpcCallsStat" -> "apirpccallsstat" ).
So it created over 124 duplicate tables and when Plesk installer tried to backup PSA database, the error (1066: "Not unique table/alias: 'apirpccallsstat'" when using LOCK TABLES) was triggered.

I was also fooled when I tried to compare duplicate tables such as "ApiRpcCallsStat" and "apirpccallsstat" because I was using MySQL command-lines and at that point, no matter how you write the table name, it will always be converted to lower-case. So for example the following code will always tell that the two tables are the same because indeed we are targeting the same table ("apirpccallsstat")

SELECT * FROM `ApiRpcCallsStat` EXCEPT SELECT * FROM `apirpccallsstat`;
SELECT * FROM `apirpccallsstat` EXCEPT SELECT * FROM `ApiRpcCallsStat`;

But in fact they didn't contain the same data and I could check that in phpMyAdmin.

So after switching back the value of "lower_case_table_names" to "0", I could finally treat the tables "ApiRpcCallsStat" and "apirpccallsstat" as two distinct tables.
From there I just renamed all tables containing upper and lower case in their name by adding the prefix "_old_" so for example "ApiRpcCallsStat" became "_old_ApiRpcCallsStat" . And then renamed all the duplicate tables with lower-case name to their original names ("apirpccallsstat" became "ApiRpcCallsStat").
And that's it.

I could after that upgrade my Plesk version and everything is working just fine so far.

So again, if you have already installed Plesk on a server running under Unix-based systems, you should be careful when modifying the value of lower_case_table_names.

 

[Kaspar]

Good you've managed to sort the issue yourself @Patashoow. Kudos!

 

[Patashoow]

Thanks @Kaspar and thank you for having tried to find solutions with me (I'll share the $10 with you if you want)