MYSQL Permissions Grant problem

[Capere Spiritum]

Hi, as a complete newbie to MYSQL I'm having difficulty changing user priviliges in phpMYadmin. I cannot seem to find the correct syntax for setting the priviliges to 'grant all' to my user.

I'm setting up CMS Made Simple 1.10.2 and cannot get past stage five of the install. It reads;
'Could not connect to the database. Verify that username and password are correct, and that the user has access to the given database.'

I have entered the user and pass plus local host address correctly.

My Host is 1and1.co.uk who's help files are not helpful...

Anyone with experience of CMS Made Simple who can shed light?

Many thanks
Simon

[Talks_44]

Did you make sure to not only create a username for your database but also adding that username TO the database, before trying to access via username/pass?

[Capere Spiritum]

Hi Talks_44

I believe so, when creating the databse, my Host (1and1. co.uk) auto assigns a username. I assign a password and description.
I've been in the phpMYadmin panel for what seems like a week trying every permutation of the GRANT syntax to assign full permissions to the assigned username.

GRANT ALL ON *.* TO 'dbname'@'username' IDENTIFIED BY 'password'; and grant all privileges on dbname.* to dbname@userandfullpath identified by 'password';

I have tried swapping *.* to dbname.* and to dbdescription as well as 'username' to username.db.1and1.com

The result is either #1045 access denied or on occaision bad syntax...

It's driving me round the bend. Host support emails are just telling me to do what I've outlined above.

[a53mp]

If your database user account does not have the permissions to change user permissions, trying to GRANT permissions to itself will not work. Usually in that kind of set up, you will need to do it within your hosting control panel, or logging in with your root mysql account if you have one, or via SSH with the proper permissions

[computergenius]

Isn't it just
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

[aadhunik]

You can manage mysql database user permissions through the 1and1 co uk control panel.

Step 1 - Log in to the 1&1 Control Panel using your Customer ID OR domain name and your password at: http : / / admin . 1and1 . co . uk
Step 2 - Click the icon for MySQL Administration from the Web Space & Access section.

On the next screen, You will see the list of databases created by you.
List will be have the information - Database Name - User Name - Host Name - description - version - status - buttons for phpmyadmin, edit, delete.
If you don't remember the password, click the "Edit" button next to "phpMyAdmin" button

You need to use same Database Name, User Name, Host Name and Password in CMS for mysql connection.

Hope, this will help you to setup your CMS.

[NJ]

Access to a database to manage tables, fields, constraints and data is a different process than creating the database, setting up users and giving users permission to perform various functions. In a way, it's like your web host having to set up your server space for you to have an account, and then you can access it to add, update or delete files.

Another thing to consider is that your database security is very important. It's bad enough when an attacker can monkey with an existing database. Wouldn't it be worse, if it were just as easy to set up new databases!

[thunderpoet]

At 1and1 when you create a new database it will show you the following information, as an example:

Database name db192983999
Host name db392.perfora.net
Port 3306 (standard port)
User name dbo192983999
Password xTxKRPzY

you have to supply Database NAME: db192983999

the HOST NAME (IMPORTANT) is used instead of "localhost" (a very common default for a lot of hosting services but NOT 1and1) : db392.perfora.net

db392.perfora.net is the connection HOST (NOT "localhost"--you will probably have a similar address, such as, db374.perfora.net, etc, etc, etc.)


the USER NAME is : dbo192983999

(notice that it is just like the database name but with a "dbo" instead of "db".. do not swap these names with each other )


the password is : xTxKRPzY (whatever your setting shows you)

usually most php programs don't need you to fill in the Port number : 3306


NO QUOTES around any of those values when you write them in to your configuration file.

If you have been into phpmyadmin messing with permissions.... GIVE UP!

NIX that database. DO NOT TRY TO USE OR INSTALL TO IT

1and1 by DEFAULT gives you everything you need to connect to the database when you use the MYSQL ADMINISTRATION Tool

Also, CHOOSE PHP5 if your account gives you the choice between PHP4 or PHP5...make your database PHP5

Once you have created a new database you should see something similar to what I have showed above.

USE Those settings exactly the way I have described substituting your own settings. I usually drag the mouse across this information when it first shows up after creating a new table, copy all of that and paste it into Text and save.

Nothing else works. 1and1 will not connect you with anything else.

[Capere Spiritum]

Many thanks to all who contributed their valued assistance.

My CMS is now up and running. So now I've got to figure out how to use it

It'll be fun, but I'll be ready by 2012

Once again, my thanks.

[Refiner]

In addition to what thunderpoet says, here is something you may want to use for troubleshooting in the future.

If for any reason one database user you created does not work, create a second database user, associate it with the database with required permissions, then update your configuration file with the second user's name and password. Then test your page with the updated settings.

Also you should always make a note of the output setting of any creation of database or database user.