+ Post New Thread
Results 1 to 13 of 13
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 '@'%' ...
  1. #1
    tazz's Avatar
    Join Date
    May 2007
    Location
    Notts
    Posts
    224
    Thank Post
    48
    Thanked 16 Times in 9 Posts
    Rep Power
    17

    Mysql syntax error with phpMyAdmin

    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?

  2. #2

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,905
    Thank Post
    1,186
    Thanked 1,057 Times in 749 Posts
    Rep Power
    328
    @tazz:

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

  3. #3
    tazz's Avatar
    Join Date
    May 2007
    Location
    Notts
    Posts
    224
    Thank Post
    48
    Thanked 16 Times in 9 Posts
    Rep Power
    17
    @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.
    Last edited by tazz; 30th July 2011 at 05:11 PM.

  4. #4


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,687
    Thank Post
    352
    Thanked 794 Times in 714 Posts
    Rep Power
    346
    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).

  5. #5

    rush_tech's Avatar
    Join Date
    Jul 2006
    Location
    Nottingham
    Posts
    1,408
    Thank Post
    111
    Thanked 265 Times in 201 Posts
    Rep Power
    194
    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

    Code:
    CREATE DATABASE `newdatabase`; 
    USE user;
    comment these out, then give it another shot

  6. #6

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    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.

  7. #7
    tazz's Avatar
    Join Date
    May 2007
    Location
    Notts
    Posts
    224
    Thank Post
    48
    Thanked 16 Times in 9 Posts
    Rep Power
    17
    Thanks for the tips guys, I'll give them a go and feed back in the morning.

  8. #8
    tazz's Avatar
    Join Date
    May 2007
    Location
    Notts
    Posts
    224
    Thank Post
    48
    Thanked 16 Times in 9 Posts
    Rep Power
    17
    @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.

    phpMYAdmin02.JPG

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

    phpMYAdmin01.JPG

    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

    phpMYAdmin03.JPG

    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 02:43 PM.

  9. #9

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    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.

  10. #10
    mossj's Avatar
    Join Date
    Dec 2008
    Location
    Leicester
    Posts
    1,466
    Thank Post
    157
    Thanked 189 Times in 174 Posts
    Rep Power
    52
    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)

  11. #11
    tazz's Avatar
    Join Date
    May 2007
    Location
    Notts
    Posts
    224
    Thank Post
    48
    Thanked 16 Times in 9 Posts
    Rep Power
    17
    @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.

  12. #12
    mossj's Avatar
    Join Date
    Dec 2008
    Location
    Leicester
    Posts
    1,466
    Thank Post
    157
    Thanked 189 Times in 174 Posts
    Rep Power
    52
    Quote Originally Posted by tazz View Post
    @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

  13. #13

    Join Date
    May 2007
    Location
    Suffolk
    Posts
    145
    Thank Post
    2
    Thanked 19 Times in 17 Posts
    Rep Power
    18
    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!)

SHARE:
+ Post New Thread

Similar Threads

  1. [PHP] MySQL syntax error
    By glennda in forum Web Development
    Replies: 1
    Last Post: 19th February 2011, 12:49 AM
  2. Error with NEW SSL Cert in OWA
    By ICTNUT in forum Windows
    Replies: 3
    Last Post: 15th November 2007, 08:35 AM
  3. Cachepilot error with myLGFL
    By philtomo-25 in forum Windows
    Replies: 2
    Last Post: 27th June 2007, 10:45 AM
  4. MySQL Backup with phpMyAdmin
    By mark80 in forum How do you do....it?
    Replies: 5
    Last Post: 20th June 2007, 12:15 PM
  5. Stupid CMD error with logon script
    By tarquel in forum Windows
    Replies: 37
    Last Post: 14th June 2006, 09:07 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •