Db Upgrade from 4.5.5 to 5.1.2 fails with [Error: Upgrade DB to 4.6.alpha7]

Question

Upgrading CiviCRM 4.5.5 on Wordpress 4.9.6 to CiviCRM 5.1.2...

I tried upgrading database from 4.5.5 to 5.1.2 and got the error below. Any version above 4.6.0 gives the same error (tried them all!). However, upgrading from 4.5.5 to 4.5.8 works, but upgrade from 4.5.8 to 5.1.2 fails with the same error. What happens in 4.6.alpha7 that breaks the upgrade?? How to get past this?

Error: Upgrade DB to 4.6.alpha7

Details are:

ALTER TABLE civicrm_mailing_abtest ADD COLUMN created_id int unsigned COMMENT 'FK to Contact ID', ADD COLUMN created_date datetime COMMENT 'When was this item created', ADD COLUMN testing_criteria varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, ADD COLUMN winner_criteria varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, ADD CONSTRAINT FK_civicrm_mailing_abtest_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL [nativecode=1060 ** Duplicate column name 'created_id']

Any help much appreciated. Here is the error log:

May 24 11:47:23  [info] $CRM_Queue_Page_AJAX_runNext_error = PEAR_Exception: "DB Error: unknown error"
 * ERROR TYPE: DB_Error
 * ERROR CODE: -1
 * ERROR MESSAGE: DB Error: unknown error
 * ERROR MODE: 16
 * ERROR USERINFO: ALTER TABLE civicrm_mailing_abtest
  ADD COLUMN `created_id` int unsigned    COMMENT 'FK to Contact ID',
  ADD COLUMN `created_date` datetime    COMMENT 'When was this item created',
  ADD COLUMN `testing_criteria` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  ADD COLUMN `winner_criteria` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  ADD CONSTRAINT FK_civicrm_mailing_abtest_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL [nativecode=1060 ** Duplicate column name 'created_id']
 * ERROR DEBUGINFO: ALTER TABLE civicrm_mailing_abtest
  ADD COLUMN `created_id` int unsigned    COMMENT 'FK to Contact ID',
  ADD COLUMN `created_date` datetime    COMMENT 'When was this item created',
  ADD COLUMN `testing_criteria` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  ADD COLUMN `winner_criteria` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  ADD CONSTRAINT FK_civicrm_mailing_abtest_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL [nativecode=1060 ** Duplicate column name 'created_id']
#0 [internal function](): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#1 [path to content]/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#2 [path to content]/wp-content/plugins/civicrm/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: unknown error", -1, 16, (Array:2), "ALTER TABLE civicrm_mailing_abtest\n  ADD COLUMN `created_id` int unsigned   ...")
#3 [path to content]/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "ALTER TABLE civicrm_mailing_abtest\n  ADD COLUMN `created_id` int unsigned   ...")
#4 [path to content]/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "ALTER TABLE civicrm_mailing_abtest\n  ADD COLUMN `created_id` int unsigned   ...", "DB_Error", TRUE)
#5 [path to content]/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(899): DB_common->raiseError(-1, NULL, NULL, NULL, "1060 ** Duplicate column name 'created_id'")
#6 [path to content]/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#7 [path to content]/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("ALTER TABLE civicrm_mailing_abtest\n  ADD COLUMN `created_id` int unsigned   ...")
#8 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Utils/File.php(303): DB_common->query("ALTER TABLE civicrm_mailing_abtest\n  ADD COLUMN `created_id` int unsigned   ...")
#9 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(197): CRM_Utils_File::sourceSQLFile("mysql://[database access stuff]?new_link...", "-- location_type_id should have default NULL, not invalid id 0\nALTER TABLE c...", NULL, TRUE)
#10 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(394): CRM_Upgrade_Form->source("-- location_type_id should have default NULL, not invalid id 0\nALTER TABLE c...", TRUE)
#11 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(428): CRM_Upgrade_Form->processLocales("[path to content]/wp-content/plugins...", "4.6.alpha7")
#12 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(686): CRM_Upgrade_Form->processSQL("4.6.alpha7")
#13 [internal function](): CRM_Upgrade_Form::doIncrementalUpgradeStep(Object(CRM_Queue_TaskContext), "4.6.alpha7", "4.5.5", "4.6.0", "/tmp/civicrm-post-upgrade3KkVLN")
#14 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Queue/Task.php(88): call_user_func_array((Array:2), (Array:5))
#15 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Queue/Runner.php(213): CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))
#16 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Queue/Page/AJAX.php(52): CRM_Queue_Runner->runNext(TRUE)
#17 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Queue/ErrorPolicy.php(106): CRM_Queue_Page_AJAX::{closure}()
#18 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Queue/Page/AJAX.php(54): CRM_Queue_ErrorPolicy->call(Object(Closure))
#19 [internal function](): CRM_Queue_Page_AJAX::runNext()
#20 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(278): call_user_func((Array:2))
#21 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#22 [path to content]/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:5))
#23 [path to content]/wp-content/plugins/civicrm/civicrm.php(1133): CRM_Core_Invoke::invoke((Array:5))
#24 [internal function](): CiviCRM_For_WordPress->invoke("")
#25 [path to content]/wp-includes/class-wp-hook.php(286): call_user_func_array((Array:2), (Array:1))
#26 [path to content]/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
#27 [path to content]/wp-includes/plugin.php(453): WP_Hook->do_action((Array:1))
#28 [path to content]/wp-admin/admin.php(224): do_action("toplevel_page_CiviCRM")
#29 {main}

Answer 1

When I do upgrades from older versions, I do it in small steps. In this case I would do:

4.5.5 --> 4.5.8 If that succeeds I would then do:

4.5.8 --> 4.6.36

If that succeeds I'd go from:

4.6.36 -->5.1.2

If it fails, I would then look at the Altering the table that caused the error before running the upgrade script. I've found that not all fixes to the update scripts make it to the next version.

Answer 2

The error is Duplicate column name 'created_id' so your civicrm_mailing_abtest table already has that column. Trying to rerun the upgrade and will just attempt to do the same thing unsuccessfully. It sounds like you have tried multiple upgrades but they will all fail to get past this step. When you upgrade, Civi applies all the upgrade steps from the current version to the target one, so if you fail getting to 4.6.0, attempts to go further won't work.

The failure to focus on is the first one. Try looking at the log under files/civicrm/ConfigAndLog. You should see messages from the upgrade steps followed by the error.

You have two basic choices: either restore the database as it was prior to your upgrades and try again, or try to figure out went wrong and correct it. The first option is safer and easier, but no guarantee that it will work since there may already be a mismatch between your database and the state the upgrade steps expect to encounter. You can compare the log messages with the files in CRM/Upgrade/Incremental and look at your database to see which step it initially failed at. From there you can complete the remaining steps manually and update the version in the database so that later upgrades work. See After a failed upgrade, is updating the table structure enough? for more details.

If that seems daunting, try https://civicrm.org/partners-contributors

Answer 3

In fact, the table civicrm_mailing_abtest shouldn't even exists in the CiviCRM 4.5.5 database.

When doing the upgrade the first time, CiviCRM has created it (introduced in 4.6.alpha1 and then updated in 4.6.alpha7).

Unfortunately, your upgrade has crashed (my guess is : because of a timeout) and a mysql backup doesn't contains any queries to delete the newly created tables so you have to clean your database (delete all the tables) prior restoring the data if you really want to have the same database as before.

However, you should ensure that your backup contains all the structure of the tables - i.e. queries like "CREATE TABLE ...".

When looking at the source, it seems that only 2 tables are created between 4.5.5 and 4.6.alpha7 : civicrm_mailing_abtest and civicrm_recurring_entity. So you might be able to simply delete those 2 tables and restart from there.