When importing database, I get the error "#1044 - Access denied for user x to database y"

When you import a database using phpMyAdmin, normally you do so by importing a text file with a .sql extension. Here is a section of code that may be in a .sql database backup. In your example, the database you are trying to import is named database.

-- phpMyAdmin SQL Dump-- version 2.11.9.5-- http://www.phpmyadmin.net---- Host: localhost-- Generation Time: Apr 02, 2010 at 08:01 AM-- Server version: 5.0.81-- PHP Version: 5.2.6SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";CREATEDATABASEdatabase;-- ------------------------------------------------------------ Table structure for table `table`--CREATETABLEIFNOTEXISTS`table`(`column1` text NOTNULL,`column2` text NOTNULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When using phpMyAdmin to attempt to import such a file, you will receive an error message similar to:

Error

SQL query:CREATEDATABASEdatabase;

MySQL said: Documentation
#1044- Access denied foruser'user'@'localhost'todatabase'database'

In this scenario, the cPanel username is user. Because of cPanel's database naming conventions, all database names must begin with the cPanel username followed by _. Using this format you can only creat a database named user_database.

The reason this import failed is because of the following line in the .sql file...

CREATEDATABASEdatabase;

Again, you cannot create a database named database, however you can create a database nameduser_database.

If you change the line that says: CREATE DATABASE so that it creates: user_database instead ofdatabase it will again fail with the following message:

Error

SQL query:CREATEDATABASE user_database;

MySQL said: Documentation
#1044- Access denied foruser'user'@'localhost'todatabase'user_database'

When using cPanel, databases must be created within the cPanel itself.

Here are the steps to correct thi sissue:

  1. Create the user_database database within cPanel

  2. Comment out the CREATE DATABASE command in my .sql file

    To do this, simply change:

    CREATE DATABASE database;

    to

    -- CREATE DATABASE database;

    You are simply adding dash-dash-space to the front of the line to comment it out so that it will not be executed.

  3. Log into phpMyAdmin, access the user_database database, and then import as normal.

Was this answer helpful?

 Print this Article