Database Troubleshooting Tools

Warning: this tool does not work with the current stable version (3.2.3) of CiviCRM

INTRODUCTION

The CiviCRM DB Tools package is a set of tools to help system administrators deal with low-level database problems.
A system administrator facing a database problem has two basic questions:

What is wrong with this database?
What do I have to do to fix it and get back to normal operation?

To meet these two needs, the CiviCRM DB Tools package provides two distinct tools:

civicrm_db_integrity requires read-only access to the database. It tests the current state of the database and provides a diagnosis. This tool makes no changes to the database and is designed to be safe to run on any database at any time. The worst it can do is fail to produce a good diagnosis.
civicrm_db_repair requires read-write access to the database because it makes changes in an attempt to restore the database to normal operation. The worst it can do is to completely destroy the database; therefore it is important to make a backup of the database before running civicrm_db_repair

Both tools run from the command line and connect directly to the MySQL server, bypassing the CiviCRM software. They require the same versions of PHP and MySQL as CiviCRM.

INSTALLATION

To install the tools, download the package from the attachments tab or via the links below into any convenient directory and untar it. Additional documentation is in the docs directory of the package and available online.

civicrm_dbtools-1.2.tar.gz

RELEASE NOTES

1.2 Support for CiviCRM 3.0

Find non-existent tables referenced in saved searches

Check for bad Member* references in civicrm_activity

Add HTML unit test report

Start unit tests without an existing test_civicrm_db_tools db in MySQL

1.1.4

Fix more bugs left over from refactoring.

Support civicrm_membership_type schema in 1.5, 1.6

Improve speed of CRM_Core_EntityTag::find_duplicates()

1.1.3

Fix some bugs left over from refactoring.

1.1.2

Change STDIN to fopen('php://stdin','r') in an attempt to improve portability

Various bug fixes

1.1.1

Fix bug in Utils::repair_contact1()

Add section to unit tests for utility functions.

1.1

Major refactoring to facilitate unit testing. Now using thePDO Extension as a data abstraction layer because it is required by PHPUnit_Extensions_Database

1.0.2

Support for CiviCRM 2.2.2

1.0.1

Schema detection algorithm smarter after 1.9

Exit if schema detection fails

1.0.0

Support for CiviCRM 2.2.0

Add unit tests for the tools package

Document how to modify the tools package

Add checks and repairs for many fields

Use mysql_free_result() liberally to reduce the runtime memory requirements of the tools

1.0-beta-7

Check/repair contents of civicrm_worldregion

1.0-beta-6

Support for CiviCRM 2.1.2

Support the ON DELETE action of foreign keys

Include a customized PhpDocumentor package

1.0-beta-5

Repair damage caused by CRM-3749

Identify old mailing jobs, allow deletion

List tables in the database but not the schema

Improved documentation.

1.0-beta-4

Correct handling of multi-column keys and keys which do not match the schema.

Improved documentation.

1.0-beta-3

Ask user permission before updating or deleting any data. Output SQL to show the rows that violate the foreign key constraints, then offer several options, including no change.
Arrange the order in which tables are repaired to avoid breaking a table which was previously repaired.

1.0-beta-2

Fix problem in evaluating foreign key constraints on the same table that could produce false positive violations.
Partial support for multi-column keys.

civicrm_db_repair copies user-defined activity types into civicrm_option_values so the associated history won't be lost when upgrading to 2.0.

Refactoring to eliminate duplicate code.

Improve user documentation.

Repairs database damage caused by these bugs:

CRM-4191 Multi-level membership inheritance causes bin/UpdateMembershipRecord to produce duplicate membeships

CRM-3771 Upgrade to 2.0 or 2.1 deletes civicrm_group_contact.location_id but it remains in schema

CRM-3772 Upgrade to 2.0 adds region_id to civicrm_country but does not populate it

CRM-3776 Upgrade to 2.0 fails if civicrm_custom_group.name longer than 35 chars

CRM-2914 2.0 update routine destroys activity history records