Recently we successfully upgraded one of our client CiviCRM install from v2.1 to v3.1.2. Over all I was pretty happy with the CiviCRM upgrade process considering client’s db was in bad shape. This client is one of the early adopters of CiviCRM, ( since v1.8 ) that made things bit worse.
Few things you should remember before starting upgrade:
1. Backup your working CiviCRM database
If you have demo / test server try to upgrade it first rather than live. Always keep backup of your current working CiviCRM database so that you can revert back if needed.
2. Ensure Database Integrity
Never assume client’s database structure is correct, it might be upgraded and hence might be missing foreign key constraints etc, especially if it is been upgraded from CiviCRM v1.x. This might lead to more errors and will take more time to fix / result in more issues during upgrade process.
3. Understand upgrade errors
Try to figure out why you get errors during upgrade process rather than commenting sql or php code in CiviCRM.
4. Fix current database issues
If you get any sql errors during upgrade try to fix your current database. Most of the time database might have stale data.
So based on above, we followed these steps:
1. Backup CiviCRM database. I usually do it via command prompt.( you can also use phpmyadmin or any other database qui’s )
mysqldump -u DBUSER -p PASSWD DBNAME > civicrm_backup.mysql
2. We followed these steps to ensure database integrity before upgrade:
– Export only data from current CiviCRM database. Before exporting I would suggest you should empty cache tables in CiviCRM.
civicrm_cache, civicrm_group_contact_cache, civicrm_acl_cache, civicrm_acl_contact_cache
Then take database backup:
mysqldump5 -c -e -t -n -u DBUSER -p PASSWDS DBNAME > civicrm_only_data.mysql
– Drop current database
mysqladmin -f -u DBUSER -p $PASSWD drop DBNAME
– Create new database and import structure from sql/civicrm.mysql ( from your current install, so in this case creating structure for CiviCRM v2.1 )
mysqladmin -f -u DBUSER -p $PASSWD create DBNAME
mysql -u DBUSER -p PASSWD DBNAME < civicrm.mysql
– Now import data which you exported
mysql -u DBUSER -p PASSWD DBNAME < civicrm_only_data.mysql
After these steps make sure your CiviCRM is working without any errors.
Now you are ready for actual upgrade process 🙂
1. Upgrade CiviCRM v2.1 to latest 2.2.x release using following instructions:
2. Make sure everything is working fine. If yes than take database backup using instructions mentioned above. ( so that you have db to revert if next upgrade fails )
3. Upgrade CiviCRM v2.2.x to CiviCRM v3.1.x using these instructions:
During this upgrade we got few sql errors:
– We got foreign key contraint failure error when following SQL was fired by CiviCRM
ALTER TABLE `civicrm_activity`
ADD CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
We got this error because civicrm_activity table in client’s db had wrong values. For few records “source_contact_id” value didn’t exist in civicrm_contact table. So we did source_contact_id = NULL for those activity records.
– Error
DB Error: constraint violation Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`database_civicrm/civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452
This was because civicrm_group_contact table had entries for contacts that didn’t exits in civicrm_contact table. So we deleted those entries from civicrm_group_contact table.
Note that after each failed step you should rollback to previous working copy / fixed copy.
I agree this process looks bit complicated 🙂 but trust me it might help you to save lot of time in debugging upgrade errors and ensure smooth future upgrade. It took me around 3-4 hours for upgrading this install from CiviCRM v2.1 to CiviCRM v3.1
I hope this helps someone 🙂