2

After upgrading CiviCRM from v4.7.14 to 5.1 (or v4.7.31) which went seemingly well, CiviCRM encounters the following problem. UPDATE: Issue does not appear until 4.7.29 (including), and comes up only with the update to v4.7.30 (Drupal).

When I click on links in reports, they produce DB Error: no such field, e.g.

  • when clicking on a particular month in Contribution Summary report
  • trying to open Contribution Details report
  • clicking on contact link in Top Donors report

and more.

The DB errors seem to be produced by contact.birth_date field which used to cause no trouble.
I am not sure how to catch the situation when links just do not work.

Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
DB Error: no such field
Error Details
Database Error Code: Unknown column 'contact.birth_date' in 'field list', 1054
Additional 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_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   FROM civicrm_contact contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0
 LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                         ON contribution_soft_civireport.contribution_id = contribution_civireport.id
      LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1

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

            LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1) 
            LEFT JOIN civicrm_note note_civireport
                      ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                           contribution_civireport.id = note_civireport.entity_id )
LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   FROM civicrm_contact contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0
 LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                         ON contribution_soft_civireport.contribution_id = contribution_civireport.id
      LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1

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

            LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1) 
            LEFT JOIN civicrm_note note_civireport
                      ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                           contribution_civireport.id = note_civireport.entity_id )
LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' 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_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   FROM civicrm_contact contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0
 LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                         ON contribution_soft_civireport.contribution_id = contribution_civireport.id
      LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1

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

            LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1) 
            LEFT JOIN civicrm_note note_civireport
                      ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                           contribution_civireport.id = note_civireport.entity_id )
LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']"]
)
Return to home page.
  • Looks like something is wrong with the query and probably with the specific report. The column contact.birth_date shoudl be called contact_report.birth_date. Which report exactly is failing? And ant change the report is in an extension? – Jaap Jansma - CiviCooP May 25 '18 at 7:49
  • Do you get the same errors on dmaster.demo.civicrm.org ? – Aidan May 25 '18 at 8:34
  • Aidan No, the error is not present on sandbox. It just appeared after my upgrade. --- @JaapJansma-CiviCooP Multiple reports are failing - Contribution Details, Top Donors, Attendee list and more. --- What do you mean by "And ant change the report is in an extension?" – Aivars May 25 '18 at 14:30
  • I mean by "And ant change the report is in an extension" the question whether you have an extension which overrides those reports and which contains the bug. – Jaap Jansma - CiviCooP May 28 '18 at 7:21
  • 1
    Ok. Then you have to look in a different direction. what I am trying to help you with is deducting what causes the problem. We can the assume it is not in an extension. What we have to figure out where this query is generated and executed. And I have to say I have not a clue where to look next. – Jaap Jansma - CiviCooP Jun 2 '18 at 22:43

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.