1

I noticed that a couple of our reports had broken after the update to 4.7 (on Drupal), and trying to recreate the reports brings up the same error, with a message saying 'DB Error: no such field'

Mostly the activity report works fine, and most of the reports involving custom data are okay, but there's one particular custom data set that generates a fatal error. We're also using the Extended Reports extension, and the Activity reports from that don't have a problem with this data set.

From the error log (this is for just one custom field selected, but I get similar errors for other or multiple fields):

Feb 12 11:07:08  [info] $Fatal Error Details = Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target 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, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.country_id as civicrm_address_country_id, value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179 as civicrm_value_gwl_hears_me_equal_opps_form_33_custom_179, 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
  WHERE activity_civireport.is_test = 0 AND
                            activity_civireport.is_deleted = 0 AND
                            activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_type_id IN (23) )  [nativecode=1054 ** Unknown column 'value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179' in 'field list']
[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target 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, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.country_id as civicrm_address_country_id, value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179 as civicrm_value_gwl_hears_me_equal_opps_form_33_custom_179, 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
  WHERE activity_civireport.is_test = 0 AND
                            activity_civireport.is_deleted = 0 AND
                            activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_type_id IN (23) )  [nativecode=1054 ** Unknown column 'value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179' in 'field list']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_activity_temp_target 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, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.country_id as civicrm_address_country_id, value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179 as civicrm_value_gwl_hears_me_equal_opps_form_33_custom_179, 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
  WHERE activity_civireport.is_test = 0 AND
                            activity_civireport.is_deleted = 0 AND
                            activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_type_id IN (23) )  [nativecode=1054 ** Unknown column 'value_gwl_hears_me_equal_opps_form_33_civireport.what_is_your_postcode__179' in 'field list']"]
)


Feb 12 11:07:08  [info] $backTrace = #0 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Error.php(235): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB.php(976): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#4 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#5 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...", "DB_Error", TRUE)
#6 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\mysql.php(895): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'value_gwl_hears_me_equal_opps_form_33_civireport.what...")
#7 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\mysql.php(328): DB_mysql->mysqlRaiseError()
#8 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\common.php(1216): DB_mysql->simpleQuery("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#9 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\DataObject.php(2435): DB_common->query("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#10 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\DB\DataObject.php(1627): DB_DataObject->_query("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#11 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\DAO.php(334): DB_DataObject->query("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#12 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\DAO.php(1209): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...", TRUE)
#13 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Report\Form\Activity.php(775): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COL...")
#14 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Form.php(423): CRM_Report_Form_Activity->postProcess()
#15 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\QuickForm\Action\Submit.php(74): CRM_Core_Form->mainProcess()
#16 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\HTML\QuickForm\Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Activity), "submit")
#17 C:\xampper\htdocs\able\sites\all\modules\civicrm\packages\HTML\QuickForm\Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Activity), "submit")
#18 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Controller.php(351): HTML_QuickForm_Page->handle("submit")
#19 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Utils\Wrapper.php(113): CRM_Core_Controller->run()
#20 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Report\Page\Report.php(72): CRM_Utils_Wrapper->run("CRM_Report_Form_Activity", NULL, NULL)
#21 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Invoke.php(312): CRM_Report_Page_Report->run((Array:3), NULL)
#22 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Invoke.php(86): CRM_Core_Invoke::runItem((Array:14))
#23 C:\xampper\htdocs\able\sites\all\modules\civicrm\CRM\Core\Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))
#24 C:\xampper\htdocs\able\sites\all\modules\civicrm\drupal\civicrm.module(454): CRM_Core_Invoke::invoke((Array:3))
#25 [internal function](): civicrm_invoke("report", "activity")
#26 C:\xampper\htdocs\able\includes\menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#27 C:\xampper\htdocs\able\index.php(21): menu_execute_active_handler()
#28 {main}

(Just double-checked, and I'm also unable to filter the Activity Report with these custom fields)

Improve this question
1

2 Answers 2

Reset to default
1

I think the isssue might be based on the lenght of the table name of field name> I recollect we had an issue with that quite some time ago

Improve this answer
0

Inspect the DB and see if this table actually exists in your local and (if relevant) upstream DB copies?

If the table doesn't exist locally only but does elsewhere, that might point to a failed import or a limitation of your local MySQL environment (eg filename limit on windows MySQL).

If the table doesn't exist at all, perhaps try recreating the custom data group with a shorter name? According to https://stackoverflow.com/q/6868302/43034 the limit is 64 char so this may not be the cause.

Improve this answer
9
  • The table holding the data, civicrm_value_gwl_hears_me_equal_opps_form_33, is definitely there. The error log refers to value_gwl_hears_me_equal_opps_form_33_civireport, but I'd assumed this was referring to the temp table?
    – liblogger
    Feb 15, 2016 at 16:39
  • Yes. So either the query generator has generated a bad query, or CiviCRM thinks you have a table which you don't. Can you confirm that value_gwl_* table does NOT exist? I suspect you need some professional help to debug this.
    – Chris Burgess
    Feb 15, 2016 at 21:13
  • The value_gwl_..._civireport table doesn't exist (there's no table named in that form in the db).
    – liblogger
    Feb 16, 2016 at 10:25
  • I had another look at this and tried to replicate the error with a longer field set name, and was able to replicate it on both my installation and the 4.7 demo. I'm going to raise it as an issue - thank you for helping me sound this out.
    – liblogger
    Feb 16, 2016 at 16:05
  • @ChrisBurgess do you know the OP isn't a professional, or are you just assuming? Her/his comments and question seem pretty knowledgable to me!
    – naomi
    Feb 17, 2016 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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