Ensuring Schema Integrity on Upgrades

Introduction

When a CiviCRM site is upgraded to a new version, there are frequently changes to the structure of the database. This may include adding or dropping tables, columns, indexes and foreign keys. Generally, the SQL upgrade script provided with each new version does a decent job of modifying the existing database to match the new schema.

However, there are occassionally issues during the upgrade which prevent a full implementation of the new schema. These issues generally show up as error messages or warnings during the upgrade. The procedures below are designed to reload the data from the prior version into a new database which contains the exact schema for the new release. We recommend you follow these steps if you've experienced any unexpected database errors or warning during an upgrade. We also recommend following these steps if your database as been through several version upgrades.

After running these steps, you can be confident that your upgraded database schema matches the current production version exactly, with all indexes, foreign keys, defaults and other constraints.

Procedures

These procedures use upgrading from version 1.8 to 1.9 as an example - you should alter the database names and version references as needed for your situation.

Upgrade a Copy of Your Database

If you've already followed the verions upgrade procedures and upgraded your database, skip to the next section. Otherwise follow these steps first. (Complete version upgrade procedures can be found here).

Open your CiviCRM settings file (drupal_root/sites/xxx/civicrm.settings.php) in your favorite editor and verify the following information:
- Your installed MySQL version:
- Your MySQL DB Server Name, CiviCRM DB User Name and Password - as entered in the CIVICRM_DSN setting:
Load and run the SQL upgrade script corresponding to the version you are upgrading:
- You can do this via phpMyAdmin, OR from the command line:

Load Your Data into a Newly Built Database

Create a new empty database. We'll call it civicrm19New for this example.
Load the 1.9 table structure into this new database by sourcing civicrm/sql/civicrm_41.mysql.
Dump the data ONLY from your upgraded database to a file:
Populate the new empty database with your data by sourcing the dumpFile from step 3: