1

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}
  • @Aiden I've added the error log above. – user6052 May 25 '18 at 13:09
2

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.

  • Thanks Kevin, but I tried the small steps method before posting. Any upgrade to version 4.6.0 or higher fails at the same point: "Error: Upgrade DB to 4.6.alpha7". – user6052 May 24 '18 at 19:02
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

  • Thanks Aiden, but I backed our db up before upgrading and always restore it to 4.5.5 before trying again. I know the upgrade always fails when it reaches 4.6.alpha7 for any upgrade 4.6.0 or higher, But I can upgrade from 4.5.5 to 4.5.8, no problem. The problem seems to be with the upgrade script at 4.6.alpha7. – user6052 May 25 '18 at 0:52
  • Hi user6052 - it will help you be helped by others if you can grasp the Q+A concept. Please don't add Answers if they are just comments. If the issue is that you do not have the reputation yet to comment on Answers you should be able to comment / edit you Question to add more info. I have converted this to a comment on Aidan's post instead – petednz - fuzion May 25 '18 at 4:58
  • Restore from your backup to 4.5.5 and upgrade to 4.5.8. Then look at the structure of the civicrm_mailing_abtest table. Does it already have a created_id column? If not, attempt the upgrade to 4.6.0 and post the section of your log from successful completion of the 4.5.8 upgrade to the first error. – Aidan May 25 '18 at 8:30
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.