Just a note to mention a mishap I had with MySQL I had on a running server. It left me stunned and bruised.

First of all, I'm not a big MySQL user. I can do the very basic things. That's all. So, one day, I'm on my gentoo server as root because I'm creating up a database for a certain user. I use the command-line "mysql", and do the operation as mysqlroot user. That's something I've done several times.

This time however, I decide to test it from the command-line. SO I issue
and duly type in the password, but the password of the database I just created. Note however that I have not specified the database, which was clumsy, I admit.

Now with the "mysql>" prompt, "show databases;" gives me only the databases that that user can see. Then I leave.

Ok, but do you know what actually happened? That command I issued effectively changed the password to the user's _other_ databases as well! So the websites, which those databases fed, led to access errors and failure.

I still can't believe it. Now this may all due to some .mysqlrc config file in the the background (which I don't know about) that allows changing of passwords that easily, but somehow, a command like that, it doesn't seem conventional that it should change passwords, without looking for verification.

Any how, I just wanted to to get that off my chest. Another thing that I notice "mysql" has is a very trendy "man" page. It actually has the options listed out as a table very carefully. There's an artist at work there at MySQL for sure.

I can start to feel that it will only be myself on this thread.

Anyhow, I have started to do a bit of reading, and that command I issued is probably not the change of password command at all.

I burrow my way into the section of their ref book:
MySQL 5.1 Reference Manual :: 5 MySQL Server Administration :: 5.4 The MySQL Access Privilege System

And half way down it says
"A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine."

Nicely hidden away, that one.

So, my thinking has been always wrong on mysql. You don't set up a database for a particular user at all. You set up a database and separately you organise access to it.

This means that if you set up an additional database for a user, you need to know that user's mysql account password beforehand. Is that the way it is? That's a pain.

Anyhow, I'm going to read up some more.

Hello,

As far as I know, and as stated in your second post, a password is assigned to a user not to a database.

Did you use the grant command to identify the database, user, host, permissions and password to use for identification?

After creating the database you should execute something like this in order to give permissions:
The all means that all priviliges are granted. Check the MySQL manual for all options.

Kind regards,

Eric

Also you could use some tool like phpmyadmin that gives you a webGUI that is pretty handy.

Kind regards,

Eric

Hi EricTRA,

Thanks for your reply ... yes, I'm aware (especially now) of these things.

I've had to explain this error of mine to a number of people, and here is an extract of my email to them.

If I could underline that five times, I would!

Cheers.

No worries, glad you figured it out. Or is it still failing?

Kind regards,

Eric

To add to Eric's grant example, you only need to specify the password for the user when you issue the command the first time for that user. So you don't have to know the password when granting permissions on other databases.

Further you don't need to be system root to create databases (but you might have figured that one out by now).

Lastly I like to suggest that you start reading the MySql reference manual so you can understand how it exactly works; you should have done that long ago when you did set up your first database.


PS
I found your story very funny