During the upgrade of a Magento shopping cart all of the sub categories were suddenly missing from the database. This article explains how I restored categories to Magento from a backup.
I had to find a way to update Magento’s database without affecting relationships to the other tables. Since I was only dealing with the categories I decided to try to replace the tables that correspond with categories and products tied to categories.
- catalog_category_entity
- catalog_category_entity_datetime
- catalog_category_entity_decimal
- catalog_category_entity_int
- catalog_category_entity_text
- catalog_category_entity_varchar
- catalog_category_flat
- catalog_category_flat_store_1
- catalog_category_product
- catalog_category_product_index
Since I had a backup of the database when the categories existed I exported those tables. To insure I wasn’t adding to the current data I dropped the tables first and then added then back. In PHPMyAdmin you can select drop tables as an option while exporting the tables. You can load the backup into a real database to let you use PHPMyAdmin.
I then backed up database to the live store that was missing the categories. Because Magento uses a relational database every time you update a table all the other tables update with it. I did not want this to occur because I have to drop the category tables to insert my new data. So to prevent this I had to disable foreign key checks and other relational database stuff.
I attached the following script to the categories SQL file.
Add this to the top of your script.
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=`NO_AUTO_VALUE_ON_ZERO`; SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
Add this to the bottom of your script. (This re-enables all the relationship stuff so Magento can run smoothly again)
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT; SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS; SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION; SET SQL_NOTES=@OLD_SQL_NOTES;
Then I imported the SQL file and cleared all my cache to insure everything was working. It seems to be cruising along great!
If you are having problems with the import and it is giving you errors. Try downloading notepad2 and switch the document to UTF8 mode (it’s at the bottom in the middle next to the size of the file, just click it.) I had to do this after trying this in notepad. For some reason it just didn’t like the notepad file. It said the doc-type was UTF-8 signature. I also had to fix a character issue there was a character that was suppose to be a ` and when copying from notepad it switched to a ‘ and made the script break. the ` is the character on the ~ key. After I switched everything worked peachy.
