MySql Access Issue

12 posts / 0 new

Topic locked

#1 Fri, 12/02/2011 - 11:38

ettolo

MySql Access Issue

Hi, after a fresh installation on a ubuntu 10.04 of virtualmin i can't get virtual servers users to work correctly.

The purpose is to act almost like a hosting webserver, but used for testing the websites

When i create a virtual server i add MySql and the db is createad and user is added with permission for that database, like it was in the previous installation.

The only user i can access with is the root. I tryed to manually extend privileges but i couldn'get it to work. This happens also in local lan and both with a tool like toad or from a php page.

The server has a fixed ip

Don't know what else to provide to help you helping me, so, please, feel free to ask!

Thanks! Nicola

#2 Fri, 12/02/2011 - 12:15

andreychek

Howdy,

Hmm, that's an unusual problem!

Do you see any errors when creating a Virtual Server?

What errors are you getting while trying to access the database?

Also, how much RAM do you have? You can determine by running the command "df -h".

-Eric

#3 Fri, 12/02/2011 - 14:09

ettolo

Thanks!

No error when creating Virtual Servers. User xxxx can't connect (or something similar... not in office now!) 2GB Ram... but I don't think this is a problem. That's the same pc i used before and worked perfectly.

#4 Mon, 12/05/2011 - 10:17

ettolo

Any other Ideas on what i can check? When i check configuration the only error i get is

"Default IP address is set to 192.168.1.100, but the detected external address is actually 83.XXX.XXX.XXX This is typically the result of being behind a NAT firewall, and should be corrected on the module configuration page."

and the config page is the one attached

S.O.S.!!!

#5 Mon, 12/05/2011 - 13:49

andreychek

Well, knowing the exact error you're seeing would help.

When configuring your application to connect to the database, if it asks you for what host to connect to, you'd want to use the local IP "127.0.0.1".

Also, in the Network Settings screen that you posted, you'd want to set "Default IP for DNS Records" to "Automatically Detected External Address", as it sounds like you're behind a NAT router.

-Eric

#6 Mon, 12/05/2011 - 14:50

ettolo

Thanks andreycheck, unfortunately it didn't solve my problem.

The exact error is "Database Error: Unable to connect to the database:Could not connect to MySQL"

I've tryed a joomla installation and tryed 127.0.0.1, localhost, and 192.168.1.100 but none of them is ok withe the virtual server administrator account. If I put the mySql root account, it can connect.

#7 Mon, 12/05/2011 - 16:29

ettolo

The user root can access to databases, so I think it's a user permission matter.

Can you tell me if these are correct?

Suppose i have a virtual server named myvirtualserver,with administrator myadministrator

the db file owner in var/lib/mysql is mysql and the group is myadministrator
mysql user is member of all groups
mysql user is not listed in "user permission" page of mysql module
mysql user is not listed in "database permission" page of mysql module
myadministrator user is listed in in "user permission" page of mysql module with permissions "none"
myadministrator user is listed in in "database permission" page of mysql module with permissions "all" for the database named "myvirtualserver"

#8 Mon, 12/05/2011 - 20:31

andreychek

Howdy,

Hmm, I'm getting confused between what you mean by the "mysql user" and "myadministrator user".

I'll offer that whenever you create a new Virtual Server, an admin user is created -- and that admin user can log into email, ftp, and, in theory, to mysql.

Is that the user that you're using to log into MySQL with? Or are you using a different user?

-Eric

#9 Tue, 12/06/2011 - 00:09

ettolo

Hmm, I'm getting confused between what you mean by the "mysql user" and "myadministrator user" "mysql user" is the default user named "mysql" created by virtualmin installation "myadministrator" user is the admin user created when I create the new virtal server

Is that the user that you're using to log into MySQL with? Yes, I try to log into MySql with myadministrator user but it doesn't work

#10 Tue, 12/06/2011 - 10:26

andreychek

Okay, one other troubleshooting step we can try, is to log into your server over SSH, and run this command on the command line:

mysql -u myadministrator -p

And then, when prompted for a password, enter the password for the user "myadministrator".

Do you get access to the MySQL shell, or do you get a password error of some kind?

If you're at the MySQL shell, try typing "use MY_DATABASE_NAME" -- when you do that, does it work (meaning no errors are returned), or do you get an error about not having rights to that DB?

-Eric

#11 Tue, 12/06/2011 - 12:12

bjb

We had an issue where if the encrypted password option is enabled (for new sites), the mysql user account would not work. It almost seemed if the password set in mysql was the encryped one (and thus it would not work).

So for all newly created sites we had to go and change the mysql password for the admin user of the site.

We turned off encryption on the password and the issue disappeared.

This is using Ubuntu server 10.04 LTS.

#12 Tue, 12/06/2011 - 15:45

ettolo

Great bjb and andreycheck! you are rigth! It was a password problem.

with mysql -u myadministrator -p and his password i get ERROR 1045 (28000)

So i went in Virtualmin, selected the virtual server, then edit databases, passwords, inserted THE SAME password and now it work. Is this what you ment?

Now, i don't know how to configure this setting for all the next virtual server i will create!

EDIT

Found in Virtualmin > system settings > server templates > default settings > administration user > Store clear text passwords? set to yes

Thanks again for the great support!

Topic locked