Upgraded mysql now no sites can connect to databases

Virtualmin
1

Hi guys. I just did a web upgrade via Virtualmin for the updated mysql packages that were showing, a few errors popped up which I have worked through and had to reinstall the mysql-server and mysql-server-5.5 packages but now mysql is showing as started and working.

However, none of my sites seem to be able to access the database and I am getting:

Database connection error (2): Could not connect to MySQL.

for all of them.

If I go into the virtual server and into edit databases they are all still showing correctly( the ones i have checked anyhow). Also propable related I cannot log into phpmyadmin at all as it is bringing up a #1045 Cannot log in to the MySQL server error.

All my sites are offline currently, I have no doubt there is something simple I am missing here so am hoping you can give some advise on what i should check to get them all working again.

Many Thanks

2

Howdy,

What distro/version are you using?

And are you able to access MySQL as the root user? Is it just the other users you’re having problems with?

-Eric

3

ps aux | grep mysql:

mysql 15399 3.1 6.0 6707368 1243760 ? Ssl 14:55 0:01 /usr/sbin/mysqld
1000 18269 0.0 0.0 9388 920 pts/0 S+ 14:56 0:00 grep --color=auto mysql

I can login to mysql using > mysql -u root -p

regards

4

Hi Eric, It is Ubuntu 12.04.

I just tried to log into mysql as root using : mysql -u root and just got: ** edited on the next post as I have been able to login afterall

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

When I did the mysql reconfigure however I did enter the password I was previously using.

regards

5

Howdy,

Okay, that’s good that you can access MySQL using root!

If you do that again from the command line, try running this MySQL command:

select user from mysql.user;

When you do that, do you see a full list of users, containing all the MySQL users who should be able to access your system?

-Eric

6

Thanks, it appears that all clients have lost their permissions in that case. There are only really root users showing in the output as well as one debian-sys-maint user.

How can I fix this - I assume that each user permission not being set also equates to each database under each account also not being associated properly.

I do have another thing too, if I go into manage database I am getting an error regarding InnoDB that I didnt have before:

SQL show index from jos_fulltextrss_feeds_url failed : Unknown storage engine ‘InnoDB’

regards

7

Howdy,

Hrm, so just to be clear – you’re saying that none of your users, except the root users, are listed in that table?

If that’s the case, that sounds like reinstalling MySQL may have removed all your users.

That’s a tricky problem :slight_smile:

One thing you could try is to go into Edit Virtual Server -> Enabled Features, and to disable then re-enable the MySQL Login feature. That may cause it to re-add your users… but I’m not sure if it’ll throw an error when it doesn’t see the user in there…

-Eric

8

Hi Eric, I actually looked at unticking the MySQL option > MySQL database enabled but it brings up a warning that the client and databases will be deleted. Is there a shortcut or easy way to download all databases on the server as a backup before I do this?

Also, what do you think to the idea if I disassociate the databases from the virtual server it should be able to disable the MySQL database and user permissions, then I enable it and import the database to the virtual server again?

regards

9

Nevermind on that last question Eric, I tried it myself and that procedure works.

Disassociate database from server > disable mysql in features > re-enable the mysql db feature > import database

all working on the first two I tested.

The question regarding InnoDB still stands though. It would appear that the Innodb engine is not working since i reinstalled mysql

regards

Leigh

10

The question regarding InnoDB still stands though. It would appear that the Innodb engine is not working since i reinstalled mysql

If you run this command, what output do you receive:

grep innodb /etc/mysql/my.cnf