Magento Database Repair Tool

The Database Repair Tool compares 2 databases (reference [”corrupted”] and target), and updates the target database so it has the same structure as the reference database by doing the following:

• Adds missing tables, or repairs them to have the same engine type and charset
• Adds missing fields or repairs them
• Removes inconsistent data from tables that have broken foreign key references
• Adds missing foreign keys and indexes

A typical use case for this tool is to fix the database of an existing Magento installation that has some of the errors mentioned above.

Usage Instructions

Crash-course for the impatient

Install the same version of Magento you’re using into a clean database. Use the new database as “reference” and the current database as “corrupted”.

That’s it :)

Below come step-by-step instructions.

Test it before running on a Production Environment!

Warning! Before running the repair tool on a production environment, make sure you have tested it before on a development environment. Make sure to create a backup of your original database before running this tool

It is also highly encouraged to restrict access to your website while repairing the database. Here is an example of restricting your Magento instance to your IP address exclusively. Other visitors, including search spiders, will get the HTTP503 Service Unavailable error.

Create a file 503.php in your Magento installation root:

1. <?php
2. header('HTTP/1.1 503 Service Unavailable');
3. header('Content-Type: text/plain; charset=UTF-8');
4. echo "503 Service Unavailable";

In .htaccess or in Apache server configuration, add the following rewrite rule:

Where 127.0.0.1 (note the backslashes before dots) should be replaced with your IP-address.

Once you save this .htaccess file or reload Apache configuration, your site will be down until you restore the initial state.

Step-by step

1. Download Magento database repair tool archive from the download page
2. Uncompress the archive
3. Put the magento-db-repair-tool-1.0.php into any folder on your server
4. Backup your existing database to have ability to restore it if anything goes wrong
5. Clone it as new database on the same server. Let’s call it “database2”
6. Create an empty database (”database3”)
7. Either copy your entire Magento folder (without cache and sessions) into a new one and install there into “database3” (By editing local.xml with credentials for the newly created “database3”);
8. Or if you already restricted access to your Magento instance, you may just change your database credentials into “database3”, clean cache and launch Magento once: it will be installed automatically.

At this point you should have the clone of your original database in “database2” and a brand new “database3” with empty Magento installed.

1. Now run http://url-of-your-server/path-to-folder/magento-db-repair-tool-1.0.php
2. Enter access credentials to “database2” as “corrupted” database and to “database3” as “reference” database
3. Set table prefixes, if applicable
4. Press “Continue” and you will see result screen where you will see what was done to the “corrupted” database.

Explanation of the report:

1. If nothing was changed, then there is no need to fix your database
2. Only table charset was changed — usually there is no need to worry about it, especially if these tables don’t have text data
3. Table engine was changed from MyIsam to InnoDb — major issue. Needs developer for investigation
4. Added missing foreign key or field (or even a table!) — major/fatal issue. Ask a developer for help.

If you are satisfied with database repair report and need to fix your live database, you can either switch your installation to the “database2” (because it was repaired), or perform the repair directly on the live database.

Don’t forget to remove the magento-db-repair-tool-1.0.php and restore access to website when you are done.