Error 1044 when importing DATABASE

I'm in the process of moving my Zencart from one host to another. I have installed the same version zencart on the new host and am trying to import my database but am getting the following error...

Error

SQL query:

--
-- Database: `toysahoy_toysahoy`
--
CREATE DATABASE IF NOT EXISTS `toysahoy_toysahoy` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;



MySQL said: Documentation
#1044 - Access denied for user 'toy18870'@'localhost' to database 'toysahoy_toysahoy'


Any idea how to overcome this or what I have done wrong? I followed all the steps in...

http://www.zen-cart.com/content.php?144


Regards,
Kiz

Most hosts won't allow you to do a CREATE DATABASE statement from phpMyAdmin.
That's why the FAQ article tells you to click on the database you're doing a backup of, to select it, before doing the export, so that the create-database statement is not generated in the backup file. That's also why the __DB__ template is advised.

So, you'll need to use a (safe) text editor to remove that statement from your .sql file before attempting to restore it.

I've followed the instructions exactly as they were written in the instructions including selecting the database prior to exporting. As my old database is still on my old host, I have tried exporting again over and over and still experiencing the same issues.

Is it anything to do with my new database name being different to my old one? Old database was toysahoy_toysahoy and I'm trying to import onto new database on new host called toys18870_toystore.

The last host I attempted to upload the database onto worked fine but had to change host again due to not enough disk space on previous.

Both new and old server using phpMyadmin 4.0.5

Not sure if this helps but I had a similar issue, although not the exact same error messages, when moving to a new host, yesterday.

I found that exporting from within phpmyadmin and then importing to the new host, again within phpmyadmin, did not work (can't remember the errors I received).

I managed to successfully complete the transfer by exporting the database using cPanel database backup facility and then importing that into the new phpmyadmin. Seemed to work fine.

simon1066:

Not sure if this helps but I had a similar issue, although not the exact same error messages, when moving to a new host, yesterday.

I found that exporting from within phpmyadmin and then importing to the new host, again within phpmyadmin, did not work (can't remember the errors I received).

I managed to successfully complete the transfer by exporting the database using cPanel database backup facility and then importing that into the new phpmyadmin. Seemed to work fine.

Thanks Simon. I've tried this and it has solved the problem.

Glad you got it working.

As I said in my previous post, you could also have simply edited the exported .sql file to remove that problematic statement.

DrByte:

So, you'll need to use a (safe) text editor to remove that statement from your .sql file before attempting to restore it.

Hey DrByte Mate happily to read your answers... Same issue was with me.

"DrByte, As I said in my previous post, you could also have simply edited the exported .sql file to remove that problematic statement."

DrByte,
How do I remove it? In notepad I tried to, imported, queries were successful and YET failed several times as the fresh install was not populated with anything. It looks like this IN the database (without any line break. After copying to Word doc, it has the following line break.):

/*!40101 SET
--
-- Database: `blabla_zenc558`
--
CREATE DATABASE IF NOT EXISTS `blabla_zenc558` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `blabla_zenc558`; NAMES utf8 */;

Should I delete "CREATE DATABASE IF NOT EXISTS `blabla_zenc558` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;"
or everything between /*!40101 SET....................................*/;

some syntax messed up?
Thank you

To describe the "results" of the import please use more specific language. Failed? How? Error messages or just that there is no change seen on the store side, or is that from looking in phpmyadmin? Importing database data, is there already tables with the same complete table name present? What is the process used to import? Does the old store and new store have the same DB_PREFIX in the includes/configure.php files?

As for editing the file, should definitely use a text only editor (not word pad as it adds characters as well as messes with line endings).

As far as the text to remove for that particular "statement" would be everything between /* 40101 through the first occurrence of */ including each of those "symbols". As that complete statement, well, is a complete statement that if not to be processed, should not be there...

Thank you mc12345678 for replying.
Sorry for messed up writing. A week ago I backed up the database of an existing 1.5.4 store, erased zc, installed it back with automatic install, and restored the former db onto the new installed store through phpmyadmin. All done on the same server. So there's no old website, as this process was done repeatedly without problems, like 5 months ago I did the same and had no problem if I remember well. Then this time got the following error. The error shown by phpmyadmin is:

SQL query:

--
-- Database: `sainaasaguud_zenc558`
--
CREATE DATABASE IF NOT EXISTS `sainaasaguud_zenc558` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;


MySQL said: Documentation

#1044 - Access denied for user 'sainaasaguud'@'localhost' to database 'sainaasaguud_zenc558'

That's it for this install.

***********************************
Then onto another exactly the same fresh test install in different directory www.mongolianshop.com/testdb, I restored the same backedup db from which the following was deleted, by Notepad:

/*!40101 SET NAMES utf8 */;
--
-- Database: `sainaasaguud_zenc558`
--
CREATE DATABASE IF NOT EXISTS `sainaasaguud_zenc558` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `sainaasaguud_zenc558`;

Then my phpmyadmin screen showed this: Import has been successfully finished, 684 queries executed. (sainaasaguud_zenc561.sql)
No error message.
Looking at the db tables, the customers and products are populated.
On the outside, the website http://mongolianshop.com/testdb/ does not show products.

Its includes/configure shows this:
define('DB_TYPE', 'mysql');
define('DB_PREFIX', 'zenkg_');
define('DB_CHARSET', 'utf8');
define('DB_SERVER', 'localhost');
define('DB_SERVER_USERNAME', 'sainaasaguud_zenc564');
define('DB_SERVER_PASSWORD', 'XXXXXXXXXXXXXXXX');
define('DB_DATABASE', 'sainaasaguud_zenc564');

Prefix wise what is before underline is the same for the backedup db and the newly automatically created db for both installs.

-sainaasaguud_zenc564 is an automatically created db together with the new install.
-sainaasaguud_zenc561.sql is the backed up db.

Or zenkg_ is in conflict with sainaasaguud_ ?

What did I mess up?
Thank you.

When the db with edited or deleted statement as mentioned above went through without error message, I see there seem to double entries.
The newly, automatically created db had empty zenkg_products, zenkg_customers etc.
Then below those another section continues with znc_products, znc_customers etc, which are the real stuff.

Perhaps the website tries to show zenkg_products, which are empty, I guess.
I am not sure the way I edited the db is right or not.
These may shed some light.

Perfect timing for the thread to be resurrected.
Just this week, I had the issue where I received that error when importing the database backup created from our live site into the database of my sandbox site.

#1044 - Access denied for user 'MySandboxCpanelAccount'@'localhost' to database
> 'MyLiveDatabaseName'

I know that I have used the same process for creating exporting the zencart database on nearly a daily basis since created in 2009.
I know that I have imported the database into the sandbox whenever desired.

The host found that there was a CREATE/USE statement within the backup. Don't know what changed. Could it be differences in mySQL versions? I don't know.

I did find that the zencart database backup FAQ that I have been using for the process specifically states to Check the radio box for "Add CREATE DATABASE / USE statement"

I did find that when I Do Not check that radio button, no error message when importing the database into my sandbox everything works as expected.

Using phpMyAdmin v3.4 or higher:

The ideal settings to do a backup via phpMyAdmin are shown below. Settings not mentioned can be left to their defaults.
You will be presented with a choice of "Quick Backup" or "Custom", CHOOSE "Custom", and then make the following selections:
- Output: - Save output to a file, leave all the other settings under Output as defaults, except maybe Compression:
OPTIONAL: - Compression: "gzipped" (or whatever you prefer... gzip makes smaller files, but zip files are easier to read on Windows computers)
- Format: - SQL
- Format-specific options: - You ONLY need to check the "structure and data" option.
- Object creation options: - check ALL the boxes in this section
- Data dump options: - choose "INSERT" from the pulldown, and check the "both of the above" option. The rest of the defaults are fine.

WARNING ABOUT IMPORTING DATABASES!
I also found that even worse happens when you decide to create a sandbox/test install on the SAME server as your live site and "IMPORT" the data into the "live" db by mistake. Guess what, the import completes without an error. The test db is EMPTY. The LIVE database has just been overwritten with the backup. If your backup is 36 hours old, and broke your own rule of "Do nothing to the DB without making a backup immediately prior", you just lost 36 hours of important data that you will not realize until 48 hours later and have to manually recreate the orders.