plesk backup fails on databases since MU#44

[azurro]

Plesk Version 10.4.4, Centos 5.8

my backup cronjobs suddenly started to fail after the latest microupdates. (first occurence 9.Oct.2012)

CL utilitiy finishes with error: Runtime error: The backup failed with errors!
GUI backup finishes without an error message, but on examining log files there is a problem with dumping the databases

/usr/local/psa/PMM/sessions/sessionXXXXXX/migration.result:

<?xml version="1.0" encoding="UTF-8"?>
<execution-result status="error" log-location="/usr/local/psa/PMM/sessions/2012-10-11-002932.78/migration.result">
<object name="domain.at" type="domain">
<object name="domain_phpwcms" type="mysql">
<message id="7ac2a9cf-4219-40f3-8405-283367726a02" severity="error" code="msgtext">
<description>Failed to execute backup database</description>


I also noticed many files with short random names fileXXXXX showing up in /tmp containing

[mysqldump]
password=cleartextadminpassword,

I found a temporary solution for the failing backups by providing a my.cnf for root with passwords
but i'd prefer to have it working again as it did before.

Kind Regards
Thomas

 

[IgorG]

As for database backup error - please try to do the following:

1) Find out credentials for database in question:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "select d.name,dbs.host, dbs.type, db.name,du.login,a.password from domains d left join data_bases db on db.dom_id=d.id left join DatabaseServers dbs on db.db_server_id=dbs.id left join db_users du on db.default_user_id=du.id left join accounts a on du.account_id=a.id where d.name='DOMAINNAME.TLD'"

You will get list of credentials like one:

+----------------+-----------------+------------+----------------------------+---------+----------+
| name | host | type | name | login | password |
+----------------+-----------------+------------+----------------------------+---------+----------+
| DOMAINNAME.TLD | HOSTNAME.MYSQL | mysql | DBNAME | USERLOG | PASSWD |
+----------------+-----------------+------------+----------------------------+---------+----------+
2) Verify it is possible to backup the database:

#mysqldump DBNAME -uUSERLOG -pPASSWD -hHOSTNAME.MYSQL > testdump

If it is not possible - verify MySQL settings.

 

[azurro]

seems there are no credentials in the database

Dear Igor,

following your advice i get no credentials.

result:

+---------------------+-----------+-------+-----------------+-------+----------+
| name | host | type | name | login | password |
+---------------------+-----------+-------+-----------------+-------+----------+
| xxxxxxdomain.at | localhost | mysql | xx_phpwcms | NULL | NULL |
+---------------------+-----------+-------+-----------------+-------+----------+

EDIT: Solved this part, see http://forum.parallels.com/showpost.php?p=643393&postcount=7
I get credentials now and can successfully dump the databases, but pleskbackup still fails.

 

[YSA Techs Y]

Hello,

we have exactly the same issue, the only difference is we have CentOS 6.3 installed(plesk 10.4.4 Update #44, last updated at Oct 7, 2012 03:20 AM), in addition we see the following error in the log:

mysqldump: Got error: 1045: Access denied for user 'admin'@'localhost' (using password: YES) when trying to connect

but login info for 'admin' account is correct, we verified it by log in into mysql. It seems there is a bug in this microupdate, we'd like this to be fixed asap.

Thanks

 

[IgorG]

Hello,

we have exactly the same issue, the only difference is we have CentOS 6.3 installed(plesk 10.4.4 Update #44, last updated at Oct 7, 2012 03:20 AM), in addition we see the following error in the log:

mysqldump: Got error: 1045: Access denied for user 'admin'@'localhost' (using password: YES) when trying to connect

but login info for 'admin' account is correct, we verified it by log in into mysql. It seems there is a bug in this microupdate, we'd like this to be fixed asap.

Thanks

There is absolutely nothing changed regarding MySQL in MU#44.
Try to use search in KB articles. AFAIR there were several articles about this issue.

 

[YSA Techs Y]

There is absolutely nothing changed regarding MySQL in MU#44.
Try to use search in KB articles. AFAIR there were several articles about this issue.

thank you for your reply. We already tried to search KB but found nothing related. The weird thing backup works when you run it from web interface, but it won't work when you run it from console. Again, there was no such problem before update, according to the logs this started to happen on Oct 8, right after update, which forces us to think that update was the reason of this problem.

 

[azurro]

Error occured suddenly and unattended!

1. I found solution for the missing credentials, when running the sql query from this post:
On most of my databases no default user is set (default_user_id in table data_bases is 0).

Solved this with:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "UPDATE data_bases db set db.default_user_id=(select du.id from db_users du where du.db_id=db.id) where db.default_user_id=0"

I can manually dump the databases now with the credentials from the sql query.

2. But pleskbackup still fails with the same error.

3. I can confirm that the problem occured just after one of the last microupdates (in my case on oct. 9).

i run several servers with unattendend automatic plesk upgrade enabled and the problem occured suddenly, and there were no other changes on these systems.
on one machine automatic upgrade was not applied and the error occured just after i forced the upgrade manually.
also the problem is not bound to centos os, as one of these servers runs on suse.


Regards,
Thomas

 

[azurro]

Backup Error MU#44 SOLVED! (Solution included)

Finally found the root cause and a solution for this problem.
It is caused by changes made by MU#44 in file $PRODUCT_ROOT_D/PMM/agents/shared/Db/MysqlShellBackend.pm

Required password for the mysqldump command is now written to an option file with random name like '/tmp/fileL7lLKQ', but without quotes.
This causes the acess denied failure, at least if the password contains special characters like '#'.


SOLUTION:

1. open file $PRODUCT_ROOT_D/PMM/agents/shared/Db/MysqlShellBackend.pm

2. find line 52 containing:
print OPTSFILE "[mysqldump]\npassword=" . $self->{password};

3. replace with:
print OPTSFILE "[mysqldump]\npassword=\"" . $self->{password} ."\"";

(notice added quotes)


REMARKS:

I personally do neither know nor understand the security improvement intended with the change made by MU#44.
Though the option files are readable for root only, they are created in world readable /tmp, have very short random names, are not deleted after backup and contain the admin password in clear text.
This way one is just one failure away from compromising the password by changing permissions in /tmp.

It is also possible to revert back to the previous solution handling passwords with the mysqldump parameter -p.
(using the file MysqlShellBackend.pm from /root/parallels/PSA_10.4.4/microupdates/MU16/common)

 

[IgorG]

Finally found the root cause and a solution for this problem.
It is caused by changes made by MU#44 in file $PRODUCT_ROOT_D/PMM/agents/shared/Db/MysqlShellBackend.pm

Required password for the mysqldump command is now written to an option file with random name like '/tmp/fileL7lLKQ', but without quotes.
This causes the acess denied failure, at least if the password contains special characters like '#'.


SOLUTION:

1. open file $PRODUCT_ROOT_D/PMM/agents/shared/Db/MysqlShellBackend.pm

2. find line 52 containing:
print OPTSFILE "[mysqldump]\npassword=" . $self->{password};

3. replace with:
print OPTSFILE "[mysqldump]\npassword=\"" . $self->{password} ."\"";

(notice added quotes)


REMARKS:

I personally do neither know nor understand the security improvement intended with the change made by MU#44.
Though the option files are readable for root only, they are created in world readable /tmp, have very short random names, are not deleted after backup and contain the admin password in clear text.
This way one is just one failure away from compromising the password by changing permissions in /tmp.

It is also possible to revert back to the previous solution handling passwords with the mysqldump parameter -p.
(using the file MysqlShellBackend.pm from /root/parallels/PSA_10.4.4/microupdates/MU16/common)

Thank you!
But we can't reproduce this problem with backup. Could you please inform me what is symbols in your password?

 

[azurro]

# interpreted as comment

Thank you!
But we can't reproduce this problem with backup. Could you please inform me what is symbols in your password?

the password contains a character '#' (NUMBER SIGN).
according to mysql option file docs this is read as beginning of a '#comment, ;comment' if not enclosed in quotes.

As far as i have tested plesk allows all characters in passwords, among them numbersign '#' , semicolon ';' and double quote '"', all of them will cause problems with the option file, double quotes in the password even with my solution.

 

[MerlijnH]

While trying to refrain from "Me too" replies, I still want to say that this problem is heavily affecting us as well. For what it's worth, this MU also creates trouble when using the Plesk migration manager, very likely due to the same cause.

The solution provided by azurro is the way it should work, and I suggest this patch is merged as new MU to resolve the regressions caused by MU#44 on Plesk 10 (and the related MUs for Plesk 9/11).

I'm also highly surprised how a change like this can make it through the Parallels Quality Assurance team. You're supposed to catch these bugs before they end up on our production systems, we are not your guinea pigs.

 

[mooiesite]

our root passwords do not contain #, but our tmp dir gets flooded with the fileXXXX.tmp files with the clear admin passwords in it!

Found it on plesk 9.5.4 en plesk 10 after daily backups and after using migrationmanager (both on centos)

 

[Binici]

The fix described by azzuro (post #8) also worked for us. Our password contains a #.

We are running Plesk Panel 11.0.9 Update #22 on Debian 6.0.1.

 

[LloydD]

The fix works fine on Ubuntu 8.04 LTS, but it came back after installing MU47.
I just re-did the fix and all seems good.
Thanks

 

[GiorgosI]

azurro you saved us!
We didn't know that our backups didn't contain db backups until we accidentally found out today!

The problem still exists in Plesk 9.5.4 (CentOS5) with latest microupdates installed (which is 27)!
I wonder, shouldn't parallels have provided another microupdate which fixes the problem, since they know it for so long time?
Our admin passwd includes a '#' sign.

The fix that azurro provided applies to Plesk 9.5.4 too, with the only difference that the line to fix is at line 48 of the file.

Has this been fixed in newer versions of Plesk or we should examine our servers one by one to check if the daily backup contains db backups?

 

[CDWweb]

DB Backup Failed for 4 months - FIXED

Thank you, thank you!
Yes - my Admin password starts with #, and yes # is the comment character in MySQL.

Excellent fix for plesk backups that have been failing due to MySQL database for the past 4 months.
It really would be best practice to "quote" everything anyway, right?

Thanks again - I now have working backups again on 10.4.4

 

[asfik]

Hello,

I have same issue. I noticed the password is in $AES ... format and trying using command line produce the 'Access denied' message. Please advice. Thanks.

Regards,
Asfihani

 

[FredericF]

Same Here...

Hi all,

I've the same problem, my root MSQL password contains a # char... The fix provided by azurro works for me like a charm. But each time PLESK is auto updated itself, i need to re-apply this patch !

What the hell are the plesk dev team waiting for to fix this ?
Unescaping password is a rookie error in a script like this... Or you just have to tell us : "DONT USE THE # CHAR IN YOUR MSQL PASSWORD".

Thanks to Azurro for the Fix.

 

[tanatonaut]

double message, sorry

 

[tanatonaut]

Hi, Igor!
My /tmp path mount with "noexec", and i have this problem on "backup sheduled". But backup is sucessful, if i run it on panel.
I have no "#" in my pass.
any ideas?