database out of memory error for SYBUNT, LYBUNT reports

[kindermaur]

I have a client running CiviCRM in a shared hosting environment, and it's running fine except for the SYBUNT and LYBUNT reports. Any time we try to run these reports, we get the following error.

==========
Sorry. A non-recoverable error has occurred.

DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Database Error Code: Out of memory (Needed 367200 bytes), 5
==========

(error details below). It looks like the database (not PHP) is running out of memory. Are there MySQL settings that could help these reports run? How much memory *should* our MySQL instance have to run CiviCRM well?

Thanks!

--Kurt

==========
Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                   
                   LEFT  JOIN civicrm_email  email_civireport
                           ON contact_civireport.id = email_civireport.contact_id 
                          AND email_civireport.is_primary = 1
                   LEFT  JOIN civicrm_phone  phone_civireport
                           ON contact_civireport.id = phone_civireport.contact_id AND
                              phone_civireport.is_primary = 1  WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2009 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 367200 bytes)]
    [type] => DB_Error
    [user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                   
                   LEFT  JOIN civicrm_email  email_civireport
                           ON contact_civireport.id = email_civireport.contact_id 
                          AND email_civireport.is_primary = 1
                   LEFT  JOIN civicrm_phone  phone_civireport
                           ON contact_civireport.id = phone_civireport.contact_id AND
                              phone_civireport.is_primary = 1  WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2009 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 367200 bytes)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                   
                   LEFT  JOIN civicrm_email  email_civireport
                           ON contact_civireport.id = email_civireport.contact_id 
                          AND email_civireport.is_primary = 1
                   LEFT  JOIN civicrm_phone  phone_civireport
                           ON contact_civireport.id = phone_civireport.contact_id AND
                              phone_civireport.is_primary = 1  WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2009 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 367200 bytes)]"]
)




[Donald Lobo]

1. how many contacts and contributions do you have in your DB?

2. you might want to try running those reports and not show the email/phone number since those are also expensive left joins

lobo

[kindermaur]

Thank you for the quick reply.

We have about 25,000 entries in the contacts table, and close to 60,000 in the contributions table.

I tried removing the phone and email fields from the SQL (creating modified versions of the reports), but I still get the same error (see details below).

Is there a way to rewrite this report to use less memory, perhaps at the price of some speed... maybe selecting into a temporary table first? Is that worth a try?

--Kurt

==========

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                     WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2010 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 252960 bytes)]
    [type] => DB_Error
    [user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                     WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2010 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 252960 bytes)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date   
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                     WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2010 AND contri.is_test = 0 ) AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50 [nativecode=5 ** Out of memory (Needed 252960 bytes)]"]
)


[kindermaur]

Running the same queries manually through phpMyAdmin (minus the phone and e-mail joins), I get the same 'out of memory' error, but running what I *think* is the equivalent query, first moving the subquery into a "CREATE TEMPORARY TABLE" (see below), it runs to completion. Unfortunately, I'm not familiar enough with how CiviReport puts together the query to be able to implement this in CiviCRM, so... would this work in CiviCRM? And if so, could someone help me to do this?

Thanks!

--Kurt

===== here's the modified SYBUNT query =====
CREATE TEMPORARY TABLE IF NOT EXISTS cont_ids
SELECT distinct cont.id as id FROM civicrm_contact cont, civicrm_contribution contri
 WHERE  cont.id = contri.contact_id AND YEAR (contri.receive_date) >= 2010 AND contri.is_test = 0;

SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, contribution_civireport.contact_id as civicrm_contribution_contact_id, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount, Year(contribution_civireport.receive_date ) as civicrm_contribution_receive_date
        FROM       civicrm_contribution  contribution_civireport
                   INNER JOIN civicrm_contact contact_civireport
                           ON contact_civireport.id = contribution_civireport.contact_id
                     WHERE contribution_civireport.is_test = 0 AND contribution_civireport.contact_id NOT IN
(select id from cont_ids)
AND ( contribution_civireport.contribution_status_id IN (1) ) Group BY contribution_civireport.contact_id  LIMIT 0, 50;

[Donald Lobo]

hey kurt:

thanx for taking the initiative and doing so. we'll take a look at the restructured query and most likely improve the code in 3.1.3

our method of using a temp table etc to restrict the set of contacts is a good strategy and a more efficient query. Can you please file an issue (http://issues.civicrm.org/) and point to this forum post

thanx

lobo

[kindermaur]

I've reported this as an issue: http://issues.civicrm.org/jira/browse/CRM-5816