1.9 to 2.0.2 Custom Data Upgrade: Duplicate column name; no table name - solved

[davej]

Error on CiviCRM 2.0 Upgrade: Step Five (Custom Data Upgrade). Had followed all steps in Upgrade Drupal Sites to 2.0, including rebuilding 1.9 db.

Here's the error message:

Code: [Select]

db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="        ALTER TABLE civicrm_value_1_Food_Providers_Details
        ADD COLUMN `Producing_Food__Organic_Certifi` varchar(255) [nativecode=1060 ** Duplicate column name 'Producing_Food__Organic_Certifi']"
The error occurs with two custom fields that have the same name, but not label, in the 1.9 db:

Code: [Select]

+----+---------------------------------+---------------------------------------------+
| id | name                            | label                                       |
+----+---------------------------------+---------------------------------------------+
| 68 | Producing_Food__Organic_Certifi | Producing Food - Organic Certification      |
| 74 | Producing_Food__Organic_Certifi | Producing Food - Organic Certification Type |
+----+---------------------------------+---------------------------------------------+
It appears that when these fields were set up, back in CiviCRM 1.4, the names were truncated. I have worked around this by changing the field names so that they're different, I just wanted to flag the issue in the hope that it might help someone. Perhaps the upgrade scripts could either check for this sort of issue at the outset, or check during Step Five and generate unique fieldnames where needed by appending digits.

Here's a SQL query that will spot duplicates:

Code: [Select]

select name, count(*) as number from civicrm_custom_field group by name having number > 1
I then took the field names returned by this query and used them in the following query to generate unique names using the field labels:

Code: [Select]

update civicrm_custom_field set name = replace(replace(replace(label, '/', ''), '-', ''), ' ', '_') where name in ('My_first_dup_field_name', 'My_second_dup_field_name', ...)
This solved the problem and moved me on to the next one, also in Step Five:

Code: [Select]

db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TABLE  (
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
        `domain_id` int unsigned NOT NULL COMMENT 'Default Domain that this data belongs to',
        `entity_id` int unsigned NOT NULL COMMENT 'Table that this extends',
        PRIMARY KEY ( id ),
        UNIQUE INDEX unique_domain_id_entity_id (  domain_id, entity_id ),
        CONSTRAINT FK__domain_id FOREIGN KEY ( `domain_id` ) REFERENCES civicrm_domain ( id ) ,
        CONSTRAINT FK__entity_id FOREIGN KEY ( `entity_id` ) REFERENCES civicrm_participant ( id ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL prima' at line 1]"- It was trying to create a table with no name. There was a NULL name in civicrm_custom_group. Fixing this got me through Step 5. :-)

Dave J