2

I'm trying to generate a report using custom fields and I am getting

nativecode=1166 ** Incorrect column name

This happens when the report is trying to generate temp tables using an alias as the column name in my case civicrm_value_conference_membership_details_version_2_62_custom_469

This string is 67 characters and is why the table cannot be created; is there any possible solution to this issue? as I already have content generated, and it would not be easy to rename the original table.

Possible suggestions:

  • A way to easily rename custom field tables?
  • A better way of creating custom alias limiting to 64 characters - especially when they are used for creating temp tables.

Full error:

Mar 13 15:49:41  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, value_conference_membership_details_22_civireport.total_member_180 as civicrm_value_conference_membership_details_22_custom_180, value_conference_membership_details_22_civireport.total_full_181 as civicrm_value_conference_membership_details_22_custom_181, value_conference_membership_details_22_civireport.male_full_182 as civicrm_value_conference_membership_details_22_custom_182, value_conference_membership_details_22_civireport.female_full_183 as civicrm_value_conference_membership_details_22_custom_183, value_conference_membership_details_22_civireport.catholic_full_184 as civicrm_value_conference_membership_details_22_custom_184, value_conference_membership_details_22_civireport.total_aux_188 as civicrm_value_conference_membership_details_22_custom_188, value_conference_membership_details_22_civireport.male_aux_189 as civicrm_value_conference_membership_details_22_custom_189, value_conference_membership_details_22_civireport.female_aux_190 as civicrm_value_conference_membership_details_22_custom_190, value_conference_membership_details_22_civireport.catholic_aux_191 as civicrm_value_conference_membership_details_22_custom_191, value_conference_membership_details_22_civireport.total_others_honorary_young_reti_209 as civicrm_value_conference_membership_details_22_custom_209, value_conference_membership_details_version_2_62_civireport.membership_year_469 as civicrm_value_conference_membership_details_version_2_62_custom_469, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_conference_membership_details_22 value_conference_membership_details_22_civireport ON value_conference_membership_details_22_civireport.entity_id = activity_civireport.id
LEFT JOIN civicrm_value_conference_membership_details_version_2_62 value_conference_membership_details_version_2_62_civireport ON value_conference_membership_details_version_2_62_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_civireport.subject LIKE '%2014%' ) AND ( activity_civireport.activity_type_id IN (60) )  [nativecode=1166 ** Incorrect column name 'civicrm_value_conference_membership_details_version_2_62_custom_469']
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, value_conference_membership_details_22_civireport.total_member_180 as civicrm_value_conference_membership_details_22_custom_180, value_conference_membership_details_22_civireport.total_full_181 as civicrm_value_conference_membership_details_22_custom_181, value_conference_membership_details_22_civireport.male_full_182 as civicrm_value_conference_membership_details_22_custom_182, value_conference_membership_details_22_civireport.female_full_183 as civicrm_value_conference_membership_details_22_custom_183, value_conference_membership_details_22_civireport.catholic_full_184 as civicrm_value_conference_membership_details_22_custom_184, value_conference_membership_details_22_civireport.total_aux_188 as civicrm_value_conference_membership_details_22_custom_188, value_conference_membership_details_22_civireport.male_aux_189 as civicrm_value_conference_membership_details_22_custom_189, value_conference_membership_details_22_civireport.female_aux_190 as civicrm_value_conference_membership_details_22_custom_190, value_conference_membership_details_22_civireport.catholic_aux_191 as civicrm_value_conference_membership_details_22_custom_191, value_conference_membership_details_22_civireport.total_others_honorary_young_reti_209 as civicrm_value_conference_membership_details_22_custom_209, value_conference_membership_details_version_2_62_civireport.membership_year_469 as civicrm_value_conference_membership_details_version_2_62_custom_469, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_conference_membership_details_22 value_conference_membership_details_22_civireport ON value_conference_membership_details_22_civireport.entity_id = activity_civireport.id
LEFT JOIN civicrm_value_conference_membership_details_version_2_62 value_conference_membership_details_version_2_62_civireport ON value_conference_membership_details_version_2_62_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_civireport.subject LIKE '%2014%' ) AND ( activity_civireport.activity_type_id IN (60) )  [nativecode=1166 ** Incorrect column name 'civicrm_value_conference_membership_details_version_2_62_custom_469']
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, value_conference_membership_details_22_civireport.total_member_180 as civicrm_value_conference_membership_details_22_custom_180, value_conference_membership_details_22_civireport.total_full_181 as civicrm_value_conference_membership_details_22_custom_181, value_conference_membership_details_22_civireport.male_full_182 as civicrm_value_conference_membership_details_22_custom_182, value_conference_membership_details_22_civireport.female_full_183 as civicrm_value_conference_membership_details_22_custom_183, value_conference_membership_details_22_civireport.catholic_full_184 as civicrm_value_conference_membership_details_22_custom_184, value_conference_membership_details_22_civireport.total_aux_188 as civicrm_value_conference_membership_details_22_custom_188, value_conference_membership_details_22_civireport.male_aux_189 as civicrm_value_conference_membership_details_22_custom_189, value_conference_membership_details_22_civireport.female_aux_190 as civicrm_value_conference_membership_details_22_custom_190, value_conference_membership_details_22_civireport.catholic_aux_191 as civicrm_value_conference_membership_details_22_custom_191, value_conference_membership_details_22_civireport.total_others_honorary_young_reti_209 as civicrm_value_conference_membership_details_22_custom_209, value_conference_membership_details_version_2_62_civireport.membership_year_469 as civicrm_value_conference_membership_details_version_2_62_custom_469, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_conference_membership_details_22 value_conference_membership_details_22_civireport ON value_conference_membership_details_22_civireport.entity_id = activity_civireport.id
LEFT JOIN civicrm_value_conference_membership_details_version_2_62 value_conference_membership_details_version_2_62_civireport ON value_conference_membership_details_version_2_62_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_civireport.subject LIKE '%2014%' ) AND ( activity_civireport.activity_type_id IN (60) )  [nativecode=1166 ** Incorrect column name 'civicrm_value_conference_membership_details_version_2_62_custom_469']"]
)

Stack Trace:

Mar 13 15:49:41  [info] $backTrace = #0 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
    #1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
    #2 /var/www/sites/my_website/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
    #3 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #4 /var/www/sites/my_website/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...", "DB_Error", TRUE)
    #6 /var/www/sites/my_website/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
    #7 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
    #8 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...", "DB_Error", TRUE)
    #9 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1166 ** Incorrect column name 'civicrm_value_conference_membership_details_ve...")
    #10 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
    #11 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #12 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/DataObject.php(2446): DB_common->query("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #13 /var/www/sites/my_website/sites/all/modules/civicrm/packages/DB/DataObject.php(1635): DB_DataObject->_query("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #14 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/DAO.php(392): DB_DataObject->query("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #15 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/DAO.php(1348): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...", TRUE)
    #16 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Report/Form.php(3477): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...", (Array:0))
    #17 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Report/Form/Activity.php(817): CRM_Report_Form->executeReportQuery("CREATE TEMPORARY TABLE civireport_activity_temp_target  DEFAULT CHARACTER SET...")
    #18 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Report/Form/Activity.php(905): CRM_Report_Form_Activity->buildQuery(TRUE)
    #19 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Form.php(447): CRM_Report_Form_Activity->postProcess()
    #20 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php(74): CRM_Core_Form->mainProcess()
    #21 /var/www/sites/my_website/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Activity), "submit")
    #22 /var/www/sites/my_website/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Activity), "submit")
    #23 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("submit")
    #24 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
    #25 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(89): CRM_Utils_Wrapper->run("CRM_Report_Form_Activity", NULL, NULL)
    #26 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Report_Page_Instance->run((Array:4), NULL)
    #27 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
    #28 /var/www/sites/my_website/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
    #29 /var/www/sites/my_website/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:4))
    #30 [internal function](): civicrm_invoke("report", "instance", "61")
    #31 /var/www/sites/my_website/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:3))
    #32 /var/www/sites/my_website/index.php(21): menu_execute_active_handler()
    #33 {main}
  • 1
    You tagged this as 4.5 - are you really on 4.5? If so, time to upgrade! – Aidan Mar 2 '18 at 14:03
  • 1
    Will upgrading fix this issue? – Eli Stone Mar 2 '18 at 14:49
  • 1
    I don't know but there are numerous fixes and enhancements since 4.5 including security issues so I'd recommend upgrading anyway. You could try recreating the issue on one of the 4.7 demo sites. – Aidan Mar 2 '18 at 15:40
  • I have just spent all day setting a clients site locally on our staging servers, upgrading to 4.7 (which did not go to smoothly) only to find that this issue is still present! If I tag it as 4.7 can we get this looked in to? :D – Eli Stone Mar 13 '18 at 15:52

Your Answer

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

Browse other questions tagged or ask your own question.