CiviCRM Community Forums (archive)

I'm attempting to upgrade from 1.9 to 2.0.2. I successfully executed steps 1 through 6 of the upgrade process listed here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0

and got stuck part way through step 7 during the custom data upgrade. Here's the error sent back in the browser:

Database Error Code: Duplicate column name 'column_name', 1060

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_custom_field`
    ADD `column_name` varchar(255) NULL DEFAULT NULL AFTER note_rows,
    ADD `option_group_id` int(10) unsigned NULL DEFAULT NULL AFTER column_name,   
    MODIFY `data_type` enum('String','Int','Float','Money','Memo','Date','Boolean','StateProvince','Country','File','Link') NOT NULL,
    MODIFY `html_type` enum('Text','TextArea','Select','Multi-Select','Radio','CheckBox','Select Date','Select State/Province','Select Country','File','Link') NOT NULL,
    DROP FOREIGN KEY `FK_civicrm_custom_field_custom_group_id` [nativecode=1060 ** Duplicate column name 'column_name']
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_custom_field`
    ADD `column_name` varchar(255) NULL DEFAULT NULL AFTER note_rows,
    ADD `option_group_id` int(10) unsigned NULL DEFAULT NULL AFTER column_name,   
    MODIFY `data_type` enum('String','Int','Float','Money','Memo','Date','Boolean','StateProvince','Country','File','Link') NOT NULL,
    MODIFY `html_type` enum('Text','TextArea','Select','Multi-Select','Radio','CheckBox','Select Date','Select State/Province','Select Country','File','Link') NOT NULL,
    DROP FOREIGN KEY `FK_civicrm_custom_field_custom_group_id` [nativecode=1060 ** Duplicate column name 'column_name']
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_custom_field`
    ADD `column_name` varchar(255) NULL DEFAULT NULL AFTER note_rows,
    ADD `option_group_id` int(10) unsigned NULL DEFAULT NULL AFTER column_name,   
    MODIFY `data_type` enum('String','Int','Float','Money','Memo','Date','Boolean','StateProvince','Country','File','Link') NOT NULL,
    MODIFY `html_type` enum('Text','TextArea','Select','Multi-Select','Radio','CheckBox','Select Date','Select State/Province','Select Country','File','Link') NOT NULL,
    DROP FOREIGN KEY `FK_civicrm_custom_field_custom_group_id` [nativecode=1060 ** Duplicate column name 'column_name']"]
)

I've used PHPMyAdmin to browse through the civicrm_custom_field table to find duplicates in the 'column_name' column. No luck.

What could be causing this and how might I fix it.

Donald,

Yes, there is a field called column_name in that table.

Don't exactly know how it got there, but there are two possibilities I can think of.

First, because I couldn't get the db rebuild step to work, I attempted the upgrade process w/o rebuilding and got an error immediately. Would you expect a possible side effect of this to be creation of a column name 'column_name'?

Second, my Drupal tables and CiviCRM table are  housed in one database, so the db rebuild instructions didn't work as written. The dumpfile created of my original db included Drupal tables, so dumping this data back into the empty CiviCRM database failed. I had to customize the mysqldump command to include only the CiviCRM tables. Maybe my modified process caused this somehow. Here's the one command I modified:

mysqldump -u myuserid -ppassword -c -e -n -t -B uaprogre_uapaprod --tables civicrm_acl civicrm_acl_cache civicrm_acl_entity_role civicrm_activity civicrm_activity_history civicrm_address civicrm_contact civicrm_contribution civicrm_contribution_page civicrm_contribution_product civicrm_contribution_recur civicrm_contribution_type civicrm_country civicrm_county civicrm_currency civicrm_custom_field civicrm_custom_group civicrm_custom_option civicrm_custom_value civicrm_dedupe_rule civicrm_dedupe_rule_group civicrm_domain civicrm_dupe_match civicrm_email civicrm_email_history civicrm_entity_file civicrm_entity_tag civicrm_event civicrm_event_page civicrm_file civicrm_financial_trxn civicrm_geo_coord civicrm_group civicrm_group_contact civicrm_household civicrm_im civicrm_individual civicrm_line_item civicrm_location civicrm_location_type civicrm_log civicrm_mailing civicrm_mailing_bounce_pattern civicrm_mailing_bounce_type civicrm_mailing_component civicrm_mailing_event_bounce civicrm_mailing_event_confirm civicrm_mailing_event_delivered civicrm_mailing_event_forward civicrm_mailing_event_opened civicrm_mailing_event_queue civicrm_mailing_event_reply civicrm_mailing_event_subscribe civicrm_mailing_event_trackable_url_open civicrm_mailing_event_unsubscribe civicrm_mailing_group civicrm_mailing_job civicrm_mailing_spool civicrm_mailing_trackable_url civicrm_mapping civicrm_mapping_field civicrm_meeting civicrm_membership civicrm_membership_block civicrm_membership_log civicrm_membership_payment civicrm_membership_status civicrm_membership_type civicrm_msg_template civicrm_note civicrm_option_group civicrm_option_value civicrm_organization civicrm_participant civicrm_participant_payment civicrm_payment_processor civicrm_payment_processor_type civicrm_phone civicrm_phonecall civicrm_preferences civicrm_premiums civicrm_premiums_product civicrm_price_field civicrm_price_set civicrm_price_set_entity civicrm_product civicrm_project civicrm_relationship civicrm_relationship_type civicrm_saved_search civicrm_sms_history civicrm_state_province civicrm_subscription_history civicrm_tag civicrm_task civicrm_task_status civicrm_uf_field civicrm_uf_group civicrm_uf_join civicrm_uf_match civicrm_validation > dumpfile

I backed out 3 schema changes made to the custom data tables that caused that schema upgrade step not to work, restarted that step and everything worked. Two of the changes were added columns and one was additional tables (one per custom field group).

Thanks for your help. You helped get me pointed in the right direction.

$cott