Mysql syntax error with phpMyAdmin

[tazz]

Hi there,

 

I'm trying to import a database and kept getting ERROR 1044 Access denied for user 'username'@'%' to database 'databasename'

 

So, I've tried to grant permissions to the user by using the command below:

 

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

 

I get the following error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySql server Version for the right syntax to use near 'IDENTIFIED BY 'password' WITH GRANT OPTION' at line 1

 

I don't have a manual, the MySQL server version is 5.0.90 and phpMyAdmin is 2.11.9.6

 

Can anyone help point me in the right direction please?

[bossman]

@tazz:

 

Before importing the old database did you set up a new database with a username and password first?

[tazz]

@bossman:

 

I'm trying to help OH here - neither of us know what we're doing!

 

I'm accessing phpMyAdmin via a control panel called "plesk". In this I created a database and then a user with password, but there seems to be no means to set what privileges this user has. Anyway got as far as trying the following:

 

GRANT ALL PRIVILEGES ON databasename.* TO username@'localhost' IDENTIFIED BY 'password';

 

and got the original error, ERROR 1044 Access denied for user 'username'@'%' to database 'databasename'

 

So now I'm thinking I need to tell MySql that this user does have access. The info I can find (phpMyAdmin 3.5.0-dev - Documentation) tells me to the following:

 

phpMyAdmin does not apply any special security methods to the MySQL database server. It is still the system administrator's job to grant permissions on the MySQL databases properly. phpMyAdmin's "Privileges" page can be used for this.

 

Thing is I can't find the "Privileges" page

 

Hope you can help.

Edited July 30, 2011 by tazz

[kmount]

From memory in Plesk you use Plesk itself to grant the privileges as you possibly don't have access to the MySQL root account to do the GRANT statement stuff.

 

(you may well do if it's a dedicated system, but if it's shared I'd be surprised).

[rush_tech]

You shouldn't need to set permissions on the database user when you set up the database.

can you open your .sql file in say word and have a look to see if it trying to create a database usually be the lines

 

CREATE DATABASE `newdatabase`; 
USE user;

 

comment these out, then give it another shot

[webman]

As it's Plesk, and likely a shared hosting environment, your actual database/user names might need to be prefixed with the main Plesk account name, or similar, "behind the scenes".

 

Anyway, if you remove the blank database and user that you've created; you should be able to do this:

 

In phpMyAdmin, go to create a new user. In there, should be a box called 'Database for user'. Click the option that says 'Create database with same name and grant all privileges'. When done, you should have a database and user that are named the same with proper permissions granted.

[tazz]

Thanks for the tips guys, I'll give them a go and feed back in the morning.

[tazz]

@webman:

 

I deleted the user and database, but can't see how to create a user or database in myPhpAdmin- I'm not sure if this is a cut down version provided by the hosting provider.

 

I created a db and user both with the name of the main plesk account. Get No Privileges under Create new database. Please see pics.

 

 

When I try to import the sql file I get access denied

 

 

Further background information that may help:

 

We are using Parallels Plesk 9.5 which runs phpMyAdmin 2.11.9.6 and MySql Client Version 5.0.90, provided by the hosting provider.

 

Trying to follow the instructions for phpMyAdmin which suggests running /domains/databases/scripts/setup.php to configure config.inc.php

 

This brings up a GUI to configure the server, but there is a warning message of

 

 

I've tried creating a folder called config in the top level of the website, but get the error below - also, I'm not certain that's where it means.

 

Error: Unable to create directory /config: filemng failed: mkdir: cannot create directory `/var/www/vhosts/websitename.co.uk/config': Permission denied

filemng: Error occured during /bin/mkdir command.

 

I'd like to be able to actually manually edit config.inc.php, but can't locate it.

 

Hope you can help.

Edited July 31, 2011 by tazz

[webman]

If you're using the phpMyAdmin that is provided by Plesk, then you shouldn't need to do any extra configuration or editing of files. Also, you don't seem to have the option for creating users in there. Plesk probably has a recommended way of creating users and databases from its own control panel, rather than through phpMyAdmin.

 

From the screenshots, in phpMyAdmin, you don't have permission to create any databases or users - which is why the query is failing.

[mossj]

Okay I have plesk so I'll outline my steps...

 

 

Login to Plesk > Click Domains > Click Domain Concerned > Click Databases > Click Add New Database > Fill out the information and save it

 

Database Created

 

Click on the Database you just created > Click Add New User > Fill out the Details and save it

 

Username Created

 

Click on Webadmin and from there your back into phpmyadmin (make sure your script doesn't try to create any databases or use a specific username and you should be fine)

[tazz]

@mossj:

 

Maybe that's the problem. The sql database I'm trying to import is an export from another plesk managed site? The username and password I'm using is set to the same as the originating site.

[mossj]

@mossj:

 

Maybe that's the problem. The sql database I'm trying to import is an export from another plesk managed site? The username and password I'm using is set to the same as the originating site.

 

I think the main problem is the script your trying to import

 

 

When you go into phpadmin from plesk you are already logged in as a user and already inside a database

 

So you want your script to not bother with and database creation or user logging in/creation

 

I'd suggest making a back up of the import file then try removing references to databases and users

[Polski]

You appear to be trying to import a database, where you should be trying to import its tables. MySQLAdmin shows you do not have 'create database' permission (typical on free hosting sites), so the database you currently have, is what you will have to use.

 

Import the TABLES, not the database. (Correct me if I am wrong, I usually am!)