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 into a new database which contains the exact schema needed to upgrade to 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 database schema matches the current production version exactly, with all indexes, foreign keys, defaults and other constraints.

Procedure to rebuild schema for database versions  2.2.x or later

1. Take a DATA dump of existing database. Lets call it datafile.sql
2. Take a STRUCTURE only dump for all your custom data tables (civicrm_value_* tables or custom_value_* tables) of your database.

   The only option that is different than creating an entire backup is the -d switch, which tells mysqldump not to output the data. List all the relevant custom value tables before the > customfiedlsfile.sql.

3. Create a new database for your rebuilt data.
4. Import the original database structure from the same version of CiviCRM you are currently using: civicrm/sql/civicrm.sql (this file is found relative to your root civicrm directory)
5. Now import customfieldsfile.sql (taken from step2) in the new database.
6. Now import your datafile.sql (taken from step1) again in the new database.
7. Make sure you have the backup of your original working database and replace this with the new database created in step 3.

Browse through few civicrm pages to verify if civicrm is working fine along with all the custom data. Also make a few checks for e.g if number of contacts / custom-data are same in both the databases.

Now the new database is ready for upgrade to next higher version.

Procedure for 1.8 -> 1.9 upgrade

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:

1. Your installed MySQL version:
   • Your MySQL DB Server Name, CiviCRM DB User Name and Password - as entered in the CIVICRM_DSN setting:
2. 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

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