Magento Database Repair Tool

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

This is an old revision of the document!

Database Repair Tool compares 2 databases and makes target database to have the same structure, as reference. It does 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.

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.

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:

header('HTTP/1.1 503 Service Unavailable');
header('Content-Type: text/plain; charset=UTF-8');
echo "503 Service Unavailablen";

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 entire your 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.

To this point you should have the cloned existing database “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 “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 no need to worry about it, especially if these tables don’t tave 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 satisified 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.