Error 1044 when moving database to new server

I am moving a site to a new server.
My old server had a prefix of kinectwe_ and the new server has a prefix of cashexch_ . Also my sql backup file (from old server) is called kinectwe_ingleburncashexchange.sql .
I am following these instructions https://www.zen-cart.com/content.php?142-i-want-to-move-my-zen-cart-installation-to-another-host-or-a-different-server
I have followed all steps ok, however when I get to step 5D (import into database) I encounter a 1044 error:

-- Database: kinectwe_ingleburncashexchange

CREATE DATABASE IF NOT EXISTS kinectwe_ingleburncashexchange DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;

MySQL said: Documentation

#1044 - Access denied for user 'cashexch'@'localhost' to database 'kinectwe_ingleburncashexchange'

In step 2a it says: IMPORTANT: when asked about the database-table-prefix during Database-Setup, use the same prefix that your old store uses.
I have this in my includes/configure.php and admin/includes/configure.php. Did I do that correctly?

// define our database connection
define('DB_TYPE', 'mysql');
define('DB_PREFIX', 'kinectwe');
define('DB_CHARSET', 'utf8');
define('DB_SERVER', 'localhost');
define('DB_SERVER_USERNAME', 'cashexch_ice');
define('DB_SERVER_PASSWORD', 'XXXXX');
define('DB_DATABASE', 'cashexch_ingleburncashexchange');

I am supposing that you have created a database already - if not do so in the account control panel

Then if your backup has the "CREATE DATABASE IF NOT EXISTS" function enabled
Create another backup without it

Then use phpmyadmin to import the backup into the DB you have created

kobra:

I am supposing that you have created a database already - if not do so in the account control panel

Then if your backup has the "CREATE DATABASE IF NOT EXISTS" function enabled
Create another backup without it

Then use phpmyadmin to import the backup into the DB you have created

Hi Kobra,
I have created a database via the CP.
I just did another sql backup without CREATE DATABASE checkmarked and it uploaded. But now I'm getting an 0071 error !!!
http://www.zen-cart.com/content.php?334-ERROR-0071-There-appears-to-be-a-problem-with-the-database-Maintenance-is-required
So now I'm trying to sort that one out.

Annie_zaz:

Hi Kobra,
I have created a database via the CP.
I just did another sql backup without CREATE DATABASE checkmarked and it uploaded. But now I'm getting an 0071 error !!!
http://www.zen-cart.com/content.php?334-ERROR-0071-There-appears-to-be-a-problem-with-the-database-Maintenance-is-required
So now I'm trying to sort that one out.

Kobra, do you think I should change all instances of cashexch_ to kinectwe_ in the configure files?

To manually figure out the correct prefix to use, look at the database using phpMyAdmin and see what common 2-4 letter prefix starts all the tablenames.
Check using phpmyadmin that tables do exist and what the prefix actually is

kobra:

Check using phpmyadmin that tables do exist and what the prefix actually is

I have cashexch_ I presume that's the table name?
The full name is cashexch_ingleburncashexchange

I would also suggest changing your database password as soon as you can ... :(

Ajeh:

I would also suggest changing your database password as soon as you can ... :(

Thanks I will do that now.

Annie_zaz:

I have cashexch_ I presume that's the table name?
The full name is cashexch_ingleburncashexchange

There is a difference between a prefix for the database name and the prefix that is prepended to "every" table within the database... Following kobra's suggestion, if you look at the first table in the database or at the sql that was uploaded, do you have a table named admin or one that has something before the word admin? Also how many tables are there? Somewhere around 100? Or 200? Or more?

I ended up making the site work. I still don't know what was wrong, maybe a table or configure.php file thing.
But what I did to correct it was upload a zc_install and reinstall it using the files from the old site and voila it worked.

Annie_zaz:

I ended up making the site work. I still don't know what was wrong, maybe a table or configure.php file thing.
But what I did to correct it was upload a zc_install and reinstall it using the files from the old site and voila it worked.

Aaah nuts. Spoke too soon.
Ok, so I did a fresh install using same version, uploaded my files and selected the template I'm using through the admin dashboard.
However it seems that all changes that I've made via the dashboard are not reflected. So I am presuming that the database didn't import.
I did an import with the CREATE DATABASE IF NOT EXISTS statement and I get an 1044 error.
If I do an import without the CREATE DATABASE IF NOT EXISTS statement it says it's imported successfully but no changes reflect.
I have a feeling this is because of the two different prefixes from each host.
So how do I overcome this?

Annie_zaz:

Aaah nuts. Spoke too soon.
Ok, so I did a fresh install using same version, uploaded my files and selected the template I'm using through the admin dashboard.
However it seems that all changes that I've made via the dashboard are not reflected. So I am presuming that the database didn't import.
I did an import with the CREATE DATABASE IF NOT EXISTS statement and I get an 1044 error.
If I do an import without the CREATE DATABASE IF NOT EXISTS statement it says it's imported successfully but no changes reflect.
I have a feeling this is because of the two different prefixes from each host.
So how do I overcome this?

Look at your old files:
Includes/configure.php
What is DB_PREFIX?

New files:
Same thing, waht is DB_PREFIX?
Are they the same.

By all this I mean there is a line:

define('DB_PREFIX', '');

Where on the right side there may or may not be something between the single quotes.

If they are the same... The next question is did the sql file being uploaded include a statement for each table such as

Drop if exists or something similar? Or for that matter any such drop statement in the file?

If not (or really if so), drop all of the ZC tables in this new database so that when importing the new file that there is no data to conflict.

Backup, backup, backup...

mc12345678:

There is a difference between a prefix for the database name and the prefix that is prepended to "every" table within the database... Following kobra's suggestion, if you look at the first table in the database or at the sql that was uploaded, do you have a table named admin or one that has something before the word admin? Also how many tables are there? Somewhere around 100? Or 200? Or more?

Hello,
On the left hand side I see half way down kinectwe infront of the tables. I have 194 tables.

mc12345678:

Look at your old files:
Includes/configure.php
What is DB_PREFIX?

New files:
Same thing, waht is DB_PREFIX?
Are they the same.

By all this I mean there is a line:

define('DB_PREFIX', '');

> Where on the right side there may or may not be something between the single quotes.
> 
> If they are the same... The next question is did the sql file being uploaded include a statement for each table such as 
> 
> Drop if exists or something similar? Or for that matter any such drop statement in the file?
> 
> If not (or really if so), drop all of the ZC tables in this new database so that when importing the new file that there is no data to conflict.
> 
> Backup, backup, backup...

On the old includes/configure.php in DB_PREFIX it is ' ' empty.
In the new config  DB_PREFIX it says 'kinectwe'
I do seem to have some DROP TABLE IF EXISTS `address_book`; for various things 'admin'  etc ....

Annie_zaz:

Hello,
On the left hand side I see half way down kinectwe infront of the tables. I have 194 tables.

That may be because of one of the above actions as well... Btw, in the future if going to use a prefix, it is suggested that it end with an underscore for example... It helps with reading it as well as for any type of table name manipulations.

But both appear to be present in that database, in particular by the total number of tables... It is as if two installs are present... One with a prefix of 'kinectwe' the other with a prefix of ''

Should be able to look at the sql of the file being iimported to see if it has the prefix of kinectwe on all (most) of the tables as well... If both sets of the tables are present in the sql file, then need to figure out which is more recent as it is likely the one that was active on the store being moved... There are dates that are logged for various things which should make that discovery relatively easy...

Annie_zaz:

On the old includes/configure.php in DB_PREFIX it is ' ' empty.
In the new config DB_PREFIX it says 'kinectwe'
I do seem to have some DROP TABLE IF EXISTS address_book; for various things 'admin' etc ....

So, based on that, if you make your new configure.php files (store and admin) have an empty DB_PREFIX then the new store should show the old store "stuff"..

The kinectwe prefix if following the above discussion correctly was added, and then a "install" was done which would have generated ~98 tables all having that prefix and have "new" data that does not reflect the old data...

mc12345678:

So, based on that, if you make your new configure.php files (store and admin) have an empty DB_PREFIX then the new store should show the old store "stuff"..

The kinectwe prefix if following the above discussion correctly was added, and then a "install" was done which would have generated ~98 tables all having that prefix and have "new" data that does not reflect the old data...

You are a bloody CHAMPION!!! I LOVE YOU. I edited the configure files and removed the kinectwe and it worked!
Thanks soooo much :hug:
So should I now remove all the tables that begin with kinectwe?

Annie_zaz:

You are a bloody CHAMPION!!! I LOVE YOU. I edited the configure files and removed the kinectwe and it worked!
Thanks soooo much :hug:
So should I now remove all the tables that begin with kinectwe?

To save your sanity in the future, yes. To reduce database bloat, yes. Affect on operation? None other than the bloat possibly "slightly" increasing database access time but no affect on the store.

When looking at the database view in phpmyadmin, should have a listing of all of the tables on the right side, with checkboxes... Can check each of those tables, then select the delete option at the bottom of the list... There are "automated" ways, that can be done as well... If you go with a sql statement method, be sure to try a select version of the sql to see what tables will be dropped before doing the drop...

mc12345678:

To save your sanity in the future, yes. To reduce database bloat, yes. Affect on operation? None other than the bloat possibly "slightly" increasing database access time but no affect on the store.

When looking at the database view in phpmyadmin, should have a listing of all of the tables on the right side, with checkboxes... Can check each of those tables, then select the delete option at the bottom of the list... There are "automated" ways, that can be done as well... If you go with a sql statement method, be sure to try a select version of the sql to see what tables will be dropped before doing the drop...

Ok I will delete them. Thanks again for your help you are an mysql angel.