mysql error while backing up virtual servers

Hello,

I have scheduled backups of all my virtual servers. Yesterday I followed mysql security guidelines and deleted the anonymous users ''@'%'

I see the following error in my backup. Looks like the mysql is using that user. Is there any way to fix this issue? Thanks

Dumping MySQL database arx_dev .. .. dump failed! mysqldump: Couldn't execute 'show table status like 'v_enhancement_tbl'': SELECT command denied to user ''@'%' for column 'account_key' in table 'enhancement_tbl' (1143)

\|/

Status: 
Active

Comments

Howdy -- hmm, that's an unusual looking error.

Are your websites working properly for you?

Do you happen to have a link to the MySQL security guidelines you followed? Seeing the link may help us figure out what isn't working properly.

I'm going to talk to Jamie a bit later today, as I'm a little confused as to why you're running into that issue... it makes it appear that the backups aren't using the root user or Virtual Server owner.

This particular backup though -- is this a Scheduled Backup setup by the Master Admin (root) user? Or was it setup by the Virtual Server owner?

This is a scheduled bkup setup by root via virtualmin console. Thanks much

\|/

Joe's picture
Submitted by Joe on Thu, 11/13/2014 - 19:17 Pro Licensee

Jamie thinks this has to do with the hostname MySQL thinks your box has and the hostname attached to the request (Eric and I had theorized about this, too, but the error message doesn't really seem to indicate that...but Jamie pointed out that MySQL goes through an odd fall-through process where it always ends up at the anonymous user @localhost, no matter what it started with, and that may end up being the user in the error, even if the request didn't have that user in it). Weird, I know.

Jamie's gonna chime in here with some troubleshooting or advice on fixing it when he gets a chance. I believe we'd want to make sure Webmin is connecting with a full/correct hostname that matches the hostname associated with your users.

Can you post the output of the SQL command select host,user from mysql.user so we can see what permissions exist? Also, the contents of the file /etc/webmin/mysql/config so I can see how Webmin is connecting to the database.

pls see below for results of your query.

+--------------------------+------------------+
| host                     | user             |
+--------------------------+------------------+
| %                        | dhakshin         |
| %                        | dramesh          |
| %                        | dyugandhar       |
| %                        | ksenthilkumar    |
| %                        | mprema           |
| %                        | vvelu            |
| 127.0.0.1           | root             |
| localhost            | arxchange        |
| localhost            | manthrasolutions |
| localhost            | root             |
| web3.mantrasolutions.com | root             |
+--------------------------+------------------+

I can give you root access to login to virtualmin if you wish. Let me know how i can give the root pwd to you securely \|/

[root@web3 aswinr]# cat vi /etc/webmin/mysql/config
cat: vi: No such file or directory
date_subs=0
max_text=1000
perpage=25
stop_cmd=/etc/rc.d/init.d/mysqld stop
mysqldump=/usr/bin/mysqldump
nodbi=0
mysql_libs=
max_dbs=50
start_cmd=/etc/rc.d/init.d/mysqld start
mysql_data=/var/lib/mysql
mysqlimport=/usr/bin/mysqlimport
access=*: *
webmin_subs=0
style=1
my_cnf=/etc/my.cnf
mysqlshow=/usr/bin/mysqlshow
mysql=/usr/bin/mysql
nopwd=0
add_mode=1
passwd_mode=0
blob_mode=0
mysqladmin=/usr/bin/mysqladmin
pass=****************

That looks OK I think.

I am happy to SSH into your system and take a look. You can email me the login details at jcameron@virtualmin.com

Hi, I have sent the pwd to Jamie's email.
ServerIP is 67.212.178.98

Thanks

\|/

Ok, I am looking now.

Which domain did the backup fail for?

dev.auctions.arxchange.com arxchange.com

THX \|/

So did you get this error from the scheduled backup email, or did it appear in the Virtualmin UI when a domain owner was doing a backup?

scheduled bkup in virtualmin UI.

Thx

\|/

So I looked into this, and it isn't really a Virtualmin bug at all. The problem happens on a single table, and is apparently caused by a bad VIEW definition in MySQL. Check out : http://stackoverflow.com/questions/6527599/mysql-forgets-who-is-logged-i...

You can re-produce the error even when logged into MySQL as root from the command line by running :

show table status like 'v\_enhancement\_tbl'

a comparable command on another table works fine :

show table status like 'account_fct'

Jamie, Eric, Thank you for your help. I am sorry if I have wasted your time.

Best Regards, |/