How-to: MySQL Import Error "1044 - Access Denied"

Time to upgrade a site's MySQL database from 5.5 to 5.7 on our web host. It should be simple. Except for the error "1044 - Access Denied" when importing the MySQL Database.

This is because the database backup file exported from the old MySQL database contains hard references to itself. The import routine for the new database rightly throws it out as the references are invalid for the new database. It would help if most hosting companies recognised this as a common occurrence (going back ten years) and included this in the basic instructions!

What do we mean?

In certain web hosting packages, the use of the MySQL commands CREATE DATABASE %DatabaseName% and USE %DatabaseName% are blocked for security reasons. Imports of SQL files that contain these commands fail with error 1044 - Access denied.

The accepted ten year-old solution (which probably won't work) to import a database backup to a new database, says to edit the database file before import.

The steps are simple enough:

  • Make a backup copy of the SQL file for import
  • Open the SQL file with a text editor
  • Search for strings containing CREATE DATABASE %OLD_Database_name% or USE %OLD_Database_name%.
    • where %OLD_Database_Name% is a placeholder for the actual database name in your SQL file.
  • Delete those commands and only those.
  • Save your changes.

The first issue is opening the file in a text editor capable of handling the full size file which could be tens or hundreds of megabytes in size. Good old fashioned command-line base text editors may be your best bet over GUI editors. You'll also need to have enough memory or memory paging to be able to edit the file, for the same reason.

You should then be able to import the edited SQL file into a database, most easily by Restoring a MySQL Database with phpMyAdmin.

The following extract from an SQL export file on our web host contains the CREATE DATABASE command in line 11 and the USE command in line 12. Since there are no other commands in either line, you can delete these lines completely.

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: db123456789.hosting-data.io
-- Creation Date: 01. Januar 2021 um 12:00
-- Server Version: 5.3.3-7+squeeze14
--
-- Database `db123456789`
--
CREATE DATABASE IF NOT EXISTS `db123456789` DEFAULT CHARACTER SET latin1 COLLATE lating1_english2_ci;
USE db123456789;


EXCEPT... simply deleting these doesn't work. You're likely to get a 1046 error, 'No Database selected'.

What you need to do instead is to edit the SQL file and amend the old database name in the CREATE and USE commands to be the new target database, for example:

CREATE DATABASE IF NOT EXISTS `db987654321` DEFAULT CHARACTER SET latin1 COLLATE lating1_english2_ci;
USE db987654321;


Now the import routine has the right references to bring in the old data to the new database instance. It works.





Comments