Answers.Body: <p>An extension was causing the issue. I disabled it, and it is working fine now.</p>
  <p><a href="https://i.sstatic.net/cWPBW4Ag.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/cWPBW4Ag.png" alt="Extension Details" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-22T14:34:55.100Z
Answers.Text: Can you share a bit more? Was the extension overriding the reports?
Answers.UserDisplayName: Parvez Saleh
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-23T13:20:14.777Z
Answers.Text: Report queries are affected by the extension, once I disabled it the temporary tables are not appeared again in the query
Answers.UserDisplayName: DSP
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-23T19:21:05.977Z
Answers.Text: Just putting the name of extension in as text as may help others when searching: Access Control by Financial Type for Reports - https://civicrm.org/extensions/access-control-by-financial-type-for-reports
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-23T22:02:19.553Z
Answers.Text: Thanks Both, useful to know
Answers.UserDisplayName: Parvez Saleh
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-22T11:28:01.933Z
Answers.LastActivityDate: 2024-11-22T11:28:01.933Z
Answers.UserDisplayName: DSP
Body: <p>I recently upgraded CiviCRM from 5.53.0 to 5.79.0. Since then, most of the reports have stopped showing records. For instance, I am posting the SQL Developer query of the Membership Summary Report below. Strangely, I found that it is accessing temporary tables that do not exist in the database.</p>
  <p>I have another instance with the same version of CiviCRM installed. I checked there and found that it is not accessing temporary tables and is working fine. By the way, that server was not upgraded manually but had version 5.79.0 directly installed during the setup. Please help resolve this issue.
  TIA.</p>
  <pre><code>
  Class used: CRM_Report_Form_Member_Summary
  
  SQL Modes: ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
  
  SELECT SQL_CALC_FOUND_ROWS COUNT( DISTINCT membership_civireport.id ) as civicrm_membership_member_count, DATE_SUB(membership_civireport.join_date, INTERVAL (DAYOFMONTH(membership_civireport.join_date)-1) DAY) as civicrm_membership_join_date_start, MONTH(membership_civireport.join_date) AS civicrm_membership_join_date_subtotal, MONTHNAME(membership_civireport.join_date) AS civicrm_membership_join_date_interval, membership_civireport.membership_type_id as civicrm_membership_membership_type_id, contact_civireport.contact_type as civicrm_contact_contact_type, contribution_civireport.currency as civicrm_contribution_currency  
          
  
  FROM  civicrm_membership membership_civireport
  
                
    LEFT JOIN civicrm_contact contact_civireport ON ( membership_civireport.contact_id = contact_civireport.id )
  
                
    LEFT JOIN civicrm_membership_status
                          ON (membership_civireport.status_id = civicrm_membership_status.id  )
                
    LEFT JOIN civicrm_membership_payment payment
                          ON ( membership_civireport.id = payment.membership_id )
                
    LEFT JOIN civicrm_contribution contribution_civireport
                           ON payment.contribution_id = contribution_civireport.id 
  
  WHERE membership_civireport.is_test = 0 AND
                                contact_civireport.is_deleted = 0 AND ( membership_civireport.owner_membership_id IS NULL ) AND ((`membership_civireport`.`membership_type_id` IN (1,2,3,4))) AND ((`contribution_civireport`.`id` NOT IN (SELECT id 
  
  FROM civicrm_contribution_financial_type_temp_1))) AND (`contribution_civireport`.`financial_type_id` IS NULL OR ((`contribution_civireport`.`financial_type_id` IN (7,3,17,20,1,4,12,11,14,13,15,10,6,8,2,19,16,5,9,18))))  
  
  GROUP BY YEAR(membership_civireport.join_date), MONTH(membership_civireport.join_date), membership_civireport.membership_type_id  WITH ROLLUP     
  
  LIMIT 0, 50
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-21T10:55:33.000Z
Comments.Text: Looking at the SQL query I'm not seeing any temporary tables being accessed?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-21T11:04:35.060Z
Comments.Text: It seems the table civicrm_contribution_financial_type_temp_1 is a temporary one as per the naming
Comments.UserDisplayName: DSP
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-21T19:04:51.080Z
Comments.Text: Ah yes, are these standard reports or have they been modified in anyway?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-21T19:13:05.170Z
Comments.Text: No, I'm directly using the templates with no modifications. Also tried different filters but no luck
Comments.UserDisplayName: DSP
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-22T12:44:50.710Z
Comments.Text: Are all reports effected or only some? And are you on Drupal or Wordpress?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-23T13:16:49.567Z
Comments.Text: I faced the issue for multiple reports especially related to membership. btw I'm running on wordpress
Comments.UserDisplayName: DSP
Tags: <upgrade><civireport><database>
Title: Reports stopped Showing Records after Civi Upgrade
ThreadText: # Reports stopped Showing Records after Civi Upgrade
  
  (Asked by: username_0 on 2024-11-21)
  
  I recently upgraded CiviCRM from 5.53.0 to 5.79.0. Since then, most of the reports have stopped showing records. For instance, I am posting the SQL Developer query of the Membership Summary Report below. Strangely, I found that it is accessing temporary tables that do not exist in the database.
  I have another instance with the same version of CiviCRM installed. I checked there and found that it is not accessing temporary tables and is working fine. By the way, that server was not upgraded manually but had version 5.79.0 directly installed during the setup. Please help resolve this issue.
  TIA.
  
  Class used: CRM_Report_Form_Member_Summary
  
  SQL Modes: ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
  
  SELECT SQL_CALC_FOUND_ROWS COUNT( DISTINCT membership_civireport.id ) as civicrm_membership_member_count, DATE_SUB(membership_civireport.join_date, INTERVAL (DAYOFMONTH(membership_civireport.join_date)-1) DAY) as civicrm_membership_join_date_start, MONTH(membership_civireport.join_date) AS civicrm_membership_join_date_subtotal, MONTHNAME(membership_civireport.join_date) AS civicrm_membership_join_date_interval, membership_civireport.membership_type_id as civicrm_membership_membership_type_id, contact_civireport.contact_type as civicrm_contact_contact_type, contribution_civireport.currency as civicrm_contribution_currency  
          
  
  FROM  civicrm_membership membership_civireport
  
                
    LEFT JOIN civicrm_contact contact_civireport ON ( membership_civireport.contact_id = contact_civireport.id )
  
                
    LEFT JOIN civicrm_membership_status
                          ON (membership_civireport.status_id = civicrm_membership_status.id  )
                
    LEFT JOIN civicrm_membership_payment payment
                          ON ( membership_civireport.id = payment.membership_id )
                
    LEFT JOIN civicrm_contribution contribution_civireport
                           ON payment.contribution_id = contribution_civireport.id 
  
  WHERE membership_civireport.is_test = 0 AND
                                contact_civireport.is_deleted = 0 AND ( membership_civireport.owner_membership_id IS NULL ) AND ((`membership_civireport`.`membership_type_id` IN (1,2,3,4))) AND ((`contribution_civireport`.`id` NOT IN (SELECT id 
  
  FROM civicrm_contribution_financial_type_temp_1))) AND (`contribution_civireport`.`financial_type_id` IS NULL OR ((`contribution_civireport`.`financial_type_id` IN (7,3,17,20,1,4,12,11,14,13,15,10,6,8,2,19,16,5,9,18))))  
  
  GROUP BY YEAR(membership_civireport.join_date), MONTH(membership_civireport.join_date), membership_civireport.membership_type_id  WITH ROLLUP     
  
  LIMIT 0, 50
  
  **Tags:** upgrade, civireport, database
  
  > **Comments:**
  > **username_1**: Looking at the SQL query I'm not seeing any temporary tables being accessed?  
  > **username_0**: It seems the table civicrm_contribution_financial_type_temp_1 is a temporary one as per the naming  
  > **username_1**: Ah yes, are these standard reports or have they been modified in anyway?  
  > **username_0**: No, I'm directly using the templates with no modifications. Also tried different filters but no luck  
  > **username_1**: Are all reports effected or only some? And are you on Drupal or Wordpress?  
  > **username_0**: I faced the issue for multiple reports especially related to membership. btw I'm running on wordpress  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2024-11-22. Score: 2 | Accepted answer
  
  An extension was causing the issue. I disabled it, and it is working fine now.
  
  [![Extension Details](https://i.sstatic.net/cWPBW4Ag.png)](https://i.sstatic.net/cWPBW4Ag.png)
  
  > **Comments:**
  > **username_1**: Can you share a bit more? Was the extension overriding the reports?  
  > **username_0**: Report queries are affected by the extension, once I disabled it the temporary tables are not appeared again in the query  
  > **username_2**: Just putting the name of extension in as text as may help others when searching: Access Control by Financial Type for Reports - https://civicrm.org/extensions/access-control-by-financial-type-for-reports  
  > **username_1**: Thanks Both, useful to know  
  
  ---
  
  