1

After CiviCRM upgrade from v4.7.14. to v5.4.1. on Drupal 7 some reports that use AGE column are broken, e.g. Instance 8: Contribution Details.

The report works fine, if AGE column is disabled (checkbox unticked), but produce the error listed below, if AGE column is enabled.

The error appears specifically with the upgrade to v4.7.30, but does not disappear all they way up to v5.4.1.

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.
2

The issue is locked at https://lab.civicrm.org/dev/core/issues/325 and its fix is at https://github.com/civicrm/civicrm-core/pull/12660

You can either apply patch or wait for next release.

Your Answer

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

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