Web Development Thread, Mysql syntax error with phpMyAdmin in Coding and Web Development; Hi there,
I'm trying to import a database and kept getting ERROR 1044 Access denied for user ' username '@'%' ...
30th July 2011, 12:05 PM #1
Mysql syntax error with phpMyAdmin
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 126.96.36.199
Can anyone help point me in the right direction please?
IDG Tech News
30th July 2011, 05:14 PM #2
Before importing the old database did you set up a new database with a username and password first?
30th July 2011, 05:59 PM #3
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.
Last edited by tazz; 30th July 2011 at 06:11 PM.
30th July 2011, 06:02 PM #4
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).
30th July 2011, 06:48 PM #5
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
comment these out, then give it another shot
CREATE DATABASE `newdatabase`;
30th July 2011, 07:36 PM #6
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.
30th July 2011, 07:40 PM #7
Thanks for the tips guys, I'll give them a go and feed back in the morning.
31st July 2011, 03:39 PM #8
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 188.8.131.52 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.
Last edited by tazz; 31st July 2011 at 03:43 PM.
31st July 2011, 04:16 PM #9
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.
31st July 2011, 06:13 PM #10
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
Click on the Database you just created > Click Add New User > Fill out the Details and save it
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)
31st July 2011, 06:37 PM #11
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.
3rd August 2011, 02:23 AM #12
I think the main problem is the script your trying to import
Originally Posted by tazz
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
1st November 2011, 04:39 PM #13
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!)
By glennda in forum Web Development
Last Post: 19th February 2011, 01:49 AM
By ICTNUT in forum Windows
Last Post: 15th November 2007, 09:35 AM
By philtomo-25 in forum Windows
Last Post: 27th June 2007, 11:45 AM
By mark80 in forum How do you do....it?
Last Post: 20th June 2007, 01:15 PM
By tarquel in forum Windows
Last Post: 14th June 2006, 10:07 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)