0

Error: "Unknown column 'a_to_civicrm_value_reporting_doc_10.resource_2_67' in 'field list'"

This is a follow-up question to one that started over here. From that thread, I am clear that this is referring to a custom field that was created for CiviCase.

The error is popping up and causing database update problems in all sorts of places from contribution pages to membership update jobs.

Here is the CiviCRM log output for a failure on a contribution page:

Oct 21 12:15:08  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_type_id as `activity_type_id`, a.subject as `subject`, a.activity_date_time as `activity_date_time`, a.duration as `duration`, a.location as `location`, a.phone_id as `phone_id`, a.phone_number as `phone_number`, a.details as `details`, a.status_id as `status_id`, a.priority_id as `priority_id`, a.parent_id as `parent_id`, a.is_test as `is_test`, a.medium_id as `medium_id`, a.is_auto as `is_auto`, a.relationship_id as `relationship_id`, a.is_current_revision as `is_current_revision`, a.original_id as `original_id`, a.result as `result`, a.is_deleted as `is_deleted`, a.campaign_id as `campaign_id`, a.engagement_level as `engagement_level`, a.weight as `weight`, a.is_star as `is_star`, a.created_date as `created_date`, a.modified_date as `modified_date`, a_to_civicrm_value_reporting_doc_10.narrative_interim_report_59 as `custom_59`, a_to_civicrm_value_reporting_doc_10.financial_interim_report_60 as `custom_60`, a_to_civicrm_value_reporting_doc_10.interim_reciepts_61 as `custom_61`, a_to_civicrm_value_reporting_doc_10.narrative_final_report_62 as `custom_62`, a_to_civicrm_value_reporting_doc_10.financial_final_report_63 as `custom_63`, a_to_civicrm_value_reporting_doc_10.financial_audit_64 as `custom_64`, a_to_civicrm_value_reporting_doc_10.final_reciepts_65 as `custom_65`, a_to_civicrm_value_reporting_doc_10.resource_2_67 as `custom_67`, a_to_civicrm_value_reporting_doc_10.resource_3_68 as `custom_68`, a_to_civicrm_value_reporting_doc_10.resource_4_69 as `custom_69`, a_to_civicrm_value_reporting_doc_10.resource_5_70 as `custom_70`, a_to_civicrm_value_reporting_doc_10.photo_1_71 as `custom_71`, a_to_civicrm_value_reporting_doc_10.photo_2_72 as `custom_72`, a_to_civicrm_value_reporting_doc_10.photo_3_73 as `custom_73`, a_to_civicrm_value_reporting_doc_10.photo_4_74 as `custom_74`, a_to_civicrm_value_reporting_doc_10.photo_5_75 as `custom_75`, a_to_civicrm_value_payment_reque_13.payment_number_94 as `custom_94`, a_to_civicrm_value_payment_reque_13.payment_amount_95 as `custom_95`, a_to_civicrm_value_payment_12.payment_approval_96 as `custom_96`, a_to_civicrm_value_grant_payment_15.amount_100 as `custom_100`, a_to_civicrm_value_grant_payment_15.cheque_or_confirmation_number_101 as `custom_101`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_reporting_doc_10` `a_to_civicrm_value_reporting_doc_10` ON `a_to_civicrm_value_reporting_doc_10`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_reque_13` `a_to_civicrm_value_payment_reque_13` ON `a_to_civicrm_value_payment_reque_13`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_12` `a_to_civicrm_value_payment_12` ON `a_to_civicrm_value_payment_12`.entity_id = `a`.id
LEFT JOIN `civicrm_value_grant_payment_15` `a_to_civicrm_value_grant_payment_15` ON `a_to_civicrm_value_grant_payment_15`.entity_id = `a`.id
WHERE (a.source_record_id = "415") AND (a.activity_type_id = "6")
LIMIT 25
OFFSET 0
 [nativecode=1054 ** Unknown column 'a_to_civicrm_value_reporting_doc_10.resource_2_67' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_type_id as `activity_type_id`, a.subject as `subject`, a.activity_date_time as `activity_date_time`, a.duration as `duration`, a.location as `location`, a.phone_id as `phone_id`, a.phone_number as `phone_number`, a.details as `details`, a.status_id as `status_id`, a.priority_id as `priority_id`, a.parent_id as `parent_id`, a.is_test as `is_test`, a.medium_id as `medium_id`, a.is_auto as `is_auto`, a.relationship_id as `relationship_id`, a.is_current_revision as `is_current_revision`, a.original_id as `original_id`, a.result as `result`, a.is_deleted as `is_deleted`, a.campaign_id as `campaign_id`, a.engagement_level as `engagement_level`, a.weight as `weight`, a.is_star as `is_star`, a.created_date as `created_date`, a.modified_date as `modified_date`, a_to_civicrm_value_reporting_doc_10.narrative_interim_report_59 as `custom_59`, a_to_civicrm_value_reporting_doc_10.financial_interim_report_60 as `custom_60`, a_to_civicrm_value_reporting_doc_10.interim_reciepts_61 as `custom_61`, a_to_civicrm_value_reporting_doc_10.narrative_final_report_62 as `custom_62`, a_to_civicrm_value_reporting_doc_10.financial_final_report_63 as `custom_63`, a_to_civicrm_value_reporting_doc_10.financial_audit_64 as `custom_64`, a_to_civicrm_value_reporting_doc_10.final_reciepts_65 as `custom_65`, a_to_civicrm_value_reporting_doc_10.resource_2_67 as `custom_67`, a_to_civicrm_value_reporting_doc_10.resource_3_68 as `custom_68`, a_to_civicrm_value_reporting_doc_10.resource_4_69 as `custom_69`, a_to_civicrm_value_reporting_doc_10.resource_5_70 as `custom_70`, a_to_civicrm_value_reporting_doc_10.photo_1_71 as `custom_71`, a_to_civicrm_value_reporting_doc_10.photo_2_72 as `custom_72`, a_to_civicrm_value_reporting_doc_10.photo_3_73 as `custom_73`, a_to_civicrm_value_reporting_doc_10.photo_4_74 as `custom_74`, a_to_civicrm_value_reporting_doc_10.photo_5_75 as `custom_75`, a_to_civicrm_value_payment_reque_13.payment_number_94 as `custom_94`, a_to_civicrm_value_payment_reque_13.payment_amount_95 as `custom_95`, a_to_civicrm_value_payment_12.payment_approval_96 as `custom_96`, a_to_civicrm_value_grant_payment_15.amount_100 as `custom_100`, a_to_civicrm_value_grant_payment_15.cheque_or_confirmation_number_101 as `custom_101`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_reporting_doc_10` `a_to_civicrm_value_reporting_doc_10` ON `a_to_civicrm_value_reporting_doc_10`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_reque_13` `a_to_civicrm_value_payment_reque_13` ON `a_to_civicrm_value_payment_reque_13`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_12` `a_to_civicrm_value_payment_12` ON `a_to_civicrm_value_payment_12`.entity_id = `a`.id
LEFT JOIN `civicrm_value_grant_payment_15` `a_to_civicrm_value_grant_payment_15` ON `a_to_civicrm_value_grant_payment_15`.entity_id = `a`.id
WHERE (a.source_record_id = "415") AND (a.activity_type_id = "6")
LIMIT 25
OFFSET 0
 [nativecode=1054 ** Unknown column 'a_to_civicrm_value_reporting_doc_10.resource_2_67' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_type_id as `activity_type_id`, a.subject as `subject`, a.activity_date_time as `activity_date_time`, a.duration as `duration`, a.location as `location`, a.phone_id as `phone_id`, a.phone_number as `phone_number`, a.details as `details`, a.status_id as `status_id`, a.priority_id as `priority_id`, a.parent_id as `parent_id`, a.is_test as `is_test`, a.medium_id as `medium_id`, a.is_auto as `is_auto`, a.relationship_id as `relationship_id`, a.is_current_revision as `is_current_revision`, a.original_id as `original_id`, a.result as `result`, a.is_deleted as `is_deleted`, a.campaign_id as `campaign_id`, a.engagement_level as `engagement_level`, a.weight as `weight`, a.is_star as `is_star`, a.created_date as `created_date`, a.modified_date as `modified_date`, a_to_civicrm_value_reporting_doc_10.narrative_interim_report_59 as `custom_59`, a_to_civicrm_value_reporting_doc_10.financial_interim_report_60 as `custom_60`, a_to_civicrm_value_reporting_doc_10.interim_reciepts_61 as `custom_61`, a_to_civicrm_value_reporting_doc_10.narrative_final_report_62 as `custom_62`, a_to_civicrm_value_reporting_doc_10.financial_final_report_63 as `custom_63`, a_to_civicrm_value_reporting_doc_10.financial_audit_64 as `custom_64`, a_to_civicrm_value_reporting_doc_10.final_reciepts_65 as `custom_65`, a_to_civicrm_value_reporting_doc_10.resource_2_67 as `custom_67`, a_to_civicrm_value_reporting_doc_10.resource_3_68 as `custom_68`, a_to_civicrm_value_reporting_doc_10.resource_4_69 as `custom_69`, a_to_civicrm_value_reporting_doc_10.resource_5_70 as `custom_70`, a_to_civicrm_value_reporting_doc_10.photo_1_71 as `custom_71`, a_to_civicrm_value_reporting_doc_10.photo_2_72 as `custom_72`, a_to_civicrm_value_reporting_doc_10.photo_3_73 as `custom_73`, a_to_civicrm_value_reporting_doc_10.photo_4_74 as `custom_74`, a_to_civicrm_value_reporting_doc_10.photo_5_75 as `custom_75`, a_to_civicrm_value_payment_reque_13.payment_number_94 as `custom_94`, a_to_civicrm_value_payment_reque_13.payment_amount_95 as `custom_95`, a_to_civicrm_value_payment_12.payment_approval_96 as `custom_96`, a_to_civicrm_value_grant_payment_15.amount_100 as `custom_100`, a_to_civicrm_value_grant_payment_15.cheque_or_confirmation_number_101 as `custom_101`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_reporting_doc_10` `a_to_civicrm_value_reporting_doc_10` ON `a_to_civicrm_value_reporting_doc_10`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_reque_13` `a_to_civicrm_value_payment_reque_13` ON `a_to_civicrm_value_payment_reque_13`.entity_id = `a`.id
LEFT JOIN `civicrm_value_payment_12` `a_to_civicrm_value_payment_12` ON `a_to_civicrm_value_payment_12`.entity_id = `a`.id
LEFT JOIN `civicrm_value_grant_payment_15` `a_to_civicrm_value_grant_payment_15` ON `a_to_civicrm_value_grant_payment_15`.entity_id = `a`.id
WHERE (a.source_record_id = "415") AND (a.activity_type_id = "6")
LIMIT 25
OFFSET 0
 [nativecode=1054 ** Unknown column 'a_to_civicrm_value_reporting_doc_10.resource_2_67' in 'field list']"])


Oct 21 12:15:08  [debug] $backTrace = #0 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(954): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#3 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#4 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR::_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...", "DB_Error", TRUE)
#5 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-19, NULL, NULL, "SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...", "1054 ** Unknown column 'a_to_civicrm_value_reporting_doc_10.resource_2_67' in...")#7 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#9 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2696): DB_common->query("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#10 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#11 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(472): DB_DataObject->query("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#12 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1637): CRM_Core_DAO->query("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...", TRUE)
#13 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/Civi/API/SelectQuery.php(133): CRM_Core_DAO::executeQuery("SELECT a.id as `id`, a.source_record_id as `source_record_id`, a.activity_typ...")
#14 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/api/v3/utils.php(1283): Civi\API\SelectQuery->run()
#15 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/api/v3/Activity.php(302): _civicrm_api3_basic_get("CRM_Activity_BAO_Activity", (Array:3), FALSE, "Activity", Object(CRM_Utils_SQL_Select))
#16 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_activity_get((Array:3))
#17 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(149): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#18 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#19 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/api/api.php(133): Civi\API\Kernel->runSafe("Activity", "Get", (Array:3))
#20 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Activity/BAO/Activity.php(1607): civicrm_api3("Activity", "Get", (Array:3))
#21 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(1180): CRM_Activity_BAO_Activity::addActivity(Object(CRM_Contribute_BAO_Contribution), "Contribution", "33006", (Array:3))
#22 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(2758): CRM_Contribute_Form_Contribution_Confirm->processFormContribution((Array:62), NULL, (Array:30), Object(CRM_Financial_DAO_FinancialType), 5, NULL)
#23 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(1534): CRM_Contribute_Form_Contribution_Confirm->processConfirm((Array:57), "33006", "2", 1, NULL)
#24 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(1464): CRM_Contribute_Form_Contribution_Confirm->postProcessMembership((Array:57), "33006", (Array:41), (Array:0), (Array:21), (Array:1), TRUE, NULL, FALSE, "2", (Array:1))
#25 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(2567): CRM_Contribute_Form_Contribution_Confirm->processMembership((Array:48), "33006", (Array:0), (Array:41), (Array:1))
#26 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(2372): CRM_Contribute_Form_Contribution_Confirm->doMembershipProcessing("33006", (Array:48), (Array:41), (Array:1))
#27 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php(861): CRM_Contribute_Form_Contribution_Confirm->processFormSubmission("33006")
#28 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(573): CRM_Contribute_Form_Contribution_Confirm->postProcess()
#29 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#30 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(43): CRM_Core_StateMachine->perform(Object(CRM_Contribute_Form_Contribution_Confirm), "next", "Next")
#31 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contribute_Form_Contribution_Confirm), "next")
#32 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contribute_Form_Contribution_Confirm), "next")
#33 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(355): HTML_QuickForm_Page->handle("next")
#34 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(319): CRM_Core_Controller->run((Array:3), NULL)
#35 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(69): CRM_Core_Invoke::runItem((Array:18))
#36 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:3))
#37 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/civicrm.php(1199): CRM_Core_Invoke::invoke((Array:3))
#38 /var/www/civi.waccglobal.org/wordpress/wp-content/plugins/civicrm/includes/civicrm.basepage.php(380): CiviCRM_For_WordPress->invoke()
#39 /var/www/civi.waccglobal.org/wordpress/wp-includes/class-wp-hook.php(307): CiviCRM_For_WordPress_Basepage->basepage_handler(Object(WP))
#40 /var/www/civi.waccglobal.org/wordpress/wp-includes/class-wp-hook.php(331): WP_Hook->apply_filters(NULL, (Array:1))
#41 /var/www/civi.waccglobal.org/wordpress/wp-includes/plugin.php(524): WP_Hook->do_action((Array:1))
#42 /var/www/civi.waccglobal.org/wordpress/wp-includes/class-wp.php(787): do_action_ref_array("wp", (Array:1))
#43 /var/www/civi.waccglobal.org/wordpress/wp-includes/functions.php(1330): WP->main("")
#44 /var/www/civi.waccglobal.org/wordpress/wp-blog-header.php(16): wp()
#45 /var/www/civi.waccglobal.org/wordpress/index.php(17): require("/var/www/civi.waccglobal.org/wordpress/wp-blog-header.php")
#46 {main}

Some tasks just won't complete, others are only partial. I attempted to simply delete the offending field, but that failed with an error that says the column doesn't exist. Here's the most relevant line from the log after the failed attempt:

ALTER TABLE civicrm_value_reporting_doc_10 DROP COLUMN `resource_2_67`, DROP FOREIGN KEY FK_civicrm_value_reporting_doc_10_resource_2_67 [nativecode=1091 ** Can't DROP COLUMN `resource_2_67`; check that it exists]"

In the previous thread it was suggested that ACLs might be an issue. There are none set on that field, but I disabled the only ACL on the system and tried again without success.

I am at a loss as to how to resolve this situation, but it is becoming a serious issue. It likely requires some serious CiviCRM database foo, but I'm not sure what to do.

I'm currently running 5.52.2 on WordPress. I'm more than willing to upgrade to the latest, but it seems unlikely to resolve anything and I'm worried that just pushing further down that trail will make things worse until I resolve this.

I would be fine to even destroy the offending field (or even related ones in CiviCase), but I need the main Contacts, Membership, Mailings and Contributions to work properly and allow me to move ahead.

How can resolve this 'unknown column' error and get back to a good place again?

1 Answer 1

0

I've had something similar when a custom field wasn't fully created for some reason.

The resource_2_67 field of civicrm_value_reporting_doc_10 does not exist but Civi is expecting it to be there because of an inconsistency with other data. You should be able to clear that with:

DELETE FROM civicrm_custom_field WHERE column_name = 'resource_2_67'

Take a backup first before doing any direct SQL changes!

1
  • I wound up having to delete quite a few custom fields. I still have no idea what went wrong. I worked through one at a time. I would check the log for the error. Delete that field as suggested and then repeat until there were no errors.
    – kapn
    Commented Nov 1, 2022 at 11:44

Your Answer

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

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