mysql error while backing up virtual servers

Submitted by username9 on Thu, 11/13/2014 - 01:26 Pro Licensee

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

Submitted by andreychek on Thu, 11/13/2014 - 09:42

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.

Submitted by andreychek on Thu, 11/13/2014 - 14:12

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?

Submitted by username9 on Thu, 11/13/2014 - 14:14 Pro Licensee

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.

Submitted by JamieCameron on Thu, 11/13/2014 - 19:23

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.

Submitted by username9 on Fri, 11/14/2014 - 09:13 Pro Licensee

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 \|/

Submitted by username9 on Fri, 11/14/2014 - 09:15 Pro Licensee

[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=****************

Submitted by JamieCameron on Fri, 11/14/2014 - 15:17

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

Submitted by username9 on Fri, 11/14/2014 - 17:29 Pro Licensee

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

Thanks

\|/

Submitted by JamieCameron on Fri, 11/14/2014 - 17:56

Ok, I am looking now.

Which domain did the backup fail for?

Submitted by username9 on Fri, 11/14/2014 - 17:58 Pro Licensee

dev.auctions.arxchange.com arxchange.com

THX \|/

Submitted by JamieCameron on Fri, 11/14/2014 - 18:09

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?

Submitted by username9 on Fri, 11/14/2014 - 18:10 Pro Licensee

scheduled bkup in virtualmin UI.

Thx

\|/

Submitted by JamieCameron on Fri, 11/14/2014 - 18:35

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'

Submitted by username9 on Sun, 11/16/2014 - 18:18 Pro Licensee

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

Best Regards, |/