Warning: this tool does not work with the current stable version of CiviCRM
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.
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.
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
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()
Fix some bugs left over from refactoring.
Change STDIN to fopen('php://stdin','r') in an attempt to improve portability
Various bug fixes
Fix bug in Utils::repair_contact1()
Add section to unit tests for utility functions.
Support for CiviCRM 2.2.2
Schema detection algorithm smarter after 1.9
Exit if schema detection fails
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
Check/repair contents of civicrm_worldregion
Support for CiviCRM 2.1.2
Support the ON DELETE action of foreign keys
Include a customized PhpDocumentor package
Repair damage caused by CRM-3749
Identify old mailing jobs, allow deletion
List tables in the database but not the schema
Correct handling of multi-column keys and keys which do not match the schema.
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.
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