2

I loaded a new 4.6.0 database with data dumped from a 4.3.4 database. The update ran okay to a certain point, when I got this message:

*[Error: Upgrade DB to 4.6.alpha7]
Error Field Error Value
Type    DB_Error
Code    -1
Message DB Error: unknown error
Mode    16
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']
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']
PEAR_Exception: DB Error: unknown error in unknown on line unknown
- DB_Error: DB Error: unknown error in unknown on line unknown*
  • You'll get better answers if you give more details. Can you list exactly what steps you took, on what server, with which cms, and which version of php, mysql, etc. – Coleman Apr 7 '15 at 0:27
  • How can an issue be closed? I have completed this successfully. – Vietyank May 13 '15 at 3:00
  • Questions on StackExchange do not have an open or closed status. The most useful thing you could do to resolve this would be to post an answer saying how you solved the problem, then mark it as the accepted answer. – Coleman May 13 '15 at 23:01
  • I had a similar issue, almost exactly the same. I had been upgrading from a very old version with repeated failures until I went slowly. I suspect the mailing_abtest snuck in there. I just dropped the civicrm_mailing_abtest table, and it breezed by the 4.6.alpha7 step. – Harold Shinsato Jan 5 '16 at 6:40
5

If I understand correctly, you first installed 4.6.0 and then loaded a DB dump for 4.3.4 on top of the 4.6.0 system. Doing so would put the database in an inconsistent state — some elements from 4.6.0 (e.g. the table civicrm_mailing_abtest) are still present, but other elements are replaced by the 4.3.4 version (e.g. the table civicrm_domain).

When loading an old database dump, ensure that you are loading into a pristine database. The easiest way is to drop+recreate the database before loading.

If for some reason that's not possible (eg the database is shared with another app whose data must be preserved), then you might drop all civicrm_* tables (and preserve the other tables). The exact steps will depend on your tooling. I can't speak about any GUI tools, but if you're comfortable with scripting, then you could ripoff this snippet from setup.sh.

echo; echo "Dropping civicrm_* tables from database $DBNAME"
echo "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='${DBNAME}' AND TABLE_TYPE = 'VIEW'" \
    | $MYSQLCMD \
    | grep '^\(civicrm_\|log_civicrm_\)' \
    | awk -v NOFOREIGNCHECK='SET FOREIGN_KEY_CHECKS=0;' 'BEGIN {print NOFOREIGNCHECK}{print "drop view " $1 ";"}' \
    | $MYSQLCMD
echo "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='${DBNAME}' AND TABLE_TYPE = 'BASE TABLE'" \
    | $MYSQLCMD \
    | grep '^\(civicrm_\|log_civicrm_\)' \
    | awk -v NOFOREIGNCHECK='SET FOREIGN_KEY_CHECKS=0;' 'BEGIN {print NOFOREIGNCHECK}{print "drop table " $1 ";"}' \
    | $MYSQLCMD
3

Try importing the "drop everything" file from the CiviCRM tarball in sql/civicrm_drop.mysql to remove all 4.6.0 tables from your new database. Then re-import your 4.3.4 database. Then try the upgrade.

  • Based on your error message: "Duplicate column name 'created_id'" this sounds like a reasonable approach. – DaveD - BackOffice Thinking Apr 5 '15 at 20:32
  • Thanks but when I ran the upgrade script, I got no upgrade screen. ?<mysitename>q=civicrm/upgrade&reset=1 – Vietyank Apr 7 '15 at 0:01
  • note that civicrm_drop,mysql is not complete since it does not include any custom tables that have been created (from custom groups/fields) – Donald Lobo Apr 14 '15 at 4:40

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.