URGENT ERROR, Cannot restore MySQLDB from backup - Moving to new domain host.

Hello all,

I have recently moved domain hosting from godaddy to ipage so I'm migrating my existing 1.5.1 store to the new host with the plan of later upgrading to 1.5.4 if migration goes well :ohmy:.

I have followed the migration document which has been perfect but I've finally run into an issue when restoring my only DB Backup of my store. https://www.zen-cart.com/content.php?142-i-want-to-move-my-zen-cart-installation-to-another-host-or-a-different-server

Fyi, the DB server has version MySQL - 5.5.32-log - phpMyAdmin - 2.8.0.1

I was preparing for the last stage of the migration in the doc of running fix_cache_key.php but ran into the following when restoring my live backup.

Error

SQL query:

/*!40101 SET character_set_client = @saved_cs_client */;

MySQL said: Documentation
#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 'ALTER TABLE products_to_categories DISABLE KEYS */;
INSERT INTO `products_to_' at line 5

As it stands I cannot access my site, admin or run the fix_cache_key as they all result in the following when visiting the site.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

My DB Skills are very limited but can follow most instructions without issue if anyone has any ideas?

Any help would be greatly apreciated, many thanks to anyone who can help.

Kind regards

This is also in my LOGS folder

[03-Jun-2015 08:48:28 UTC] PHP Fatal error: 1146:Table 'ibdb01.project_version' doesn't exist :: select * from project_version WHERE project_version_key = 'Zen-Cart Database' in /hermes/bosnawe***/b429/ipg.him****/itkshop/zencart/includes/classes/db/mysql/query_factory.php on line 120

As per troubleshooting tips, I've just checked DB_PREFIX in old and new configure.php and they're both the same.

define('DB_PREFIX', '');

Internal server error may have nothing to do with your DB backup

https://www.zen-cart.com/content.php?105-why-am-i-getting-500-internal-server-error

If you still have access to your GoDaddy account suggest that you follow this tutorial in creating a proper backup

https://www.zen-cart.com/content.php?144-how-can-i-do-a-site-backup-or-use-phpmyadmin-for-database-backup

kobra:

Internal server error may have nothing to do with your DB backup

https://www.zen-cart.com/content.php?105-why-am-i-getting-500-internal-server-error

If you still have access to your GoDaddy account suggest that you follow this tutorial in creating a proper backup

https://www.zen-cart.com/content.php?144-how-can-i-do-a-site-backup-or-use-phpmyadmin-for-database-backup

Hello Kobra, thanks for the tips. I unfortunately dont have access to them anymore. I've always backed up the DB using their MySQL Backup button in the cpanel under MySQL. Used it and restored most recently without issue when previously testing an upgrade to 1.5.4.

Btw, I do still have access to the same Website Store DB version which is 1.5.4, think I may be short a few customers but if it restores will it be fine to run with core 1.5.1 files until ready to upgrade fully?

if it restores will it be fine to run with core 1.5.1 files until ready to upgrade fully?
Unfortunately no - as there are changes to the DB between those versions

If you have 1.5.4 installed why are you reverting back to 1.5.1

kobra:

Unfortunately no - as there are changes to the DB between those versions

If you have 1.5.4 installed why are you reverting back to 1.5.1

It was a test to a clean build of 1.5.4 with no templates or plugins, images installed, just wanted to make sure DB would upgrade which it did fine. Guess maybe I should take that route now and invest the time with 1.5.4 and cleanly install plugins, templates etc from scratch.

While it is a matter of time trading, hopefully/maybe assistance can be provided to address the database restore issue. Before vesting too much time into the immediate upgrade, I would recommend attempting to do the transfer of your "clean" test site intoan alternate location of your final site to see what issues you may need to first overcome.

When that is good, then have a test location to verify the final product before moving it to your final location.

himmon:

...
I was preparing for the last stage of the migration in the doc of running fix_cache_key.php but ran into the following when restoring my live backup.

Error

SQL query:

/*!40101 SET character_set_client = @saved_cs_client */;

MySQL said: Documentation
#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 'ALTER TABLE products_to_categories DISABLE KEYS */;
INSERT INTO `products_to_' at line 5
Have you looked at the .sql file created by your backup? Can you see the issue that mySQL is complaining about?

himmon:

This is also in my LOGS folder

[03-Jun-2015 08:48:28 UTC] PHP Fatal error: 1146:Table 'ibdb01.project_version' doesn't exist :: select * from project_version WHERE project_version_key = 'Zen-Cart Database' in /hermes/bosnawe***/b429/ipg.him****/itkshop/zencart/includes/classes/db/mysql/query_factory.php on line 120
This is the very first database request made by Zen Cart's initialization; so this error indicates that the project_version table doesn't exist.

lat9:

Have you looked at the .sql file created by your backup? Can you see the issue that mySQL is complaining about?

This is the very first database request made by Zen Cart's initialization; so this error indicates that the project_version table doesn't exist.

Thanks all of you guys for your input, it turns out it was a Database corruption when exporting, loads of tables were missing. My own fault with staying with godaddy so long, expensive, poor performance, and bad backup functionality. They wanted $170 to recover my DB from backup, a 2MB file... I've moved to ipage now, cant believe how fast the site is, wow.

Anyway, stroke of luck is I forgot I took another backup from 2 weeks ago on my other pc with intact copy of database so I just went straight ahead to a clean build of 1.54, DB upgraded perfectly. Spent the rest of the day configuring template, paypal, shipping, editors, plugins etc and extremely happy now.

Was worried at 1st about the plugin side of things but it has worked out great. Have a fast clean secure SSL enabled site, would recommend this route so long as your calm and patient :) Thanks all.

lat9:

Have you looked at the .sql file created by your backup? Can you see the issue that mySQL is complaining about?

This is the very first database request made by Zen Cart's initialization; so this error indicates that the project_version table doesn't exist.

You're correct, everything below and including project_version table was missing when I checked against very old backups.

I decided to install MYSQL Backup plugin from DrByte, tested it heavily yesterday on 1.5.4, restores and backups, it works brilliantly! Would be great if there was a feature within it to schedule & autodump the DB backups to Dropbox, Onedrive, Jottacloud etc.

Anyway, here's the link for anyone else who may be interested instead of having to login to your Mysql/php hosting co to backup the site.

https://www.zen-cart.com/downloads.php?do=file&id=7