Magento Database Repair Tool

Last modified by FerdiUSA on Thu, June 24, 2010 15:16
Source|Old Revisions  

This is an old revision of the document!

Database Repair Tool compares 2 databases (reference 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 indices

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

Usage Instructions

Test it before running on a Production Environment!

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

Also, it is highly encouraged to restrict access to your website while repairing database. Here is an example of restricting your Magento instance to your IP address exclusively. Other visitors, including search spiders, will get the HTTP 503 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:

RewriteEngine On
RewriteCond %{REMOTE_ADDR} !^$
RewriteRule !503.php$ /503.php [L]

Where 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 initial state.

Step-by step

  1. Put the Repairdb.php into tools/db folder inside your Magento instance root folder
  2. Backup your existing database to have ability to restore it if anything goes wrong
  3. Clone it as new database on the same server. Let’s call it “database2”
  4. Create an empty database (”database3”)
  5. Either copy your entire Magento folder (without cache and sessions) into new one and install there into “database3”;
  6. 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. Enter access credentials to “database2” as “corrupted” database and to “database3” as “reference” database
  2. Set table prefixes, if applicable
  3. Press “Continue” and you’ll see result screen where you’ll see what was done to the “corrupted” database.

What does the report mean:

  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.

Eventually, 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 to perform repair directly on live database.

Don’t forget to remove the tools/db/Repairdb.php and restore access to website when you finish.