Author Topic: Donation Summary Report Errors (Read 991 times)

thalemn · May 28, 2013, 09:18:36 am

I recently migrated CiviCRM 4.0 (Drupal 7) to a new hosting company (Bluehost), and now I'm getting errors running the Donation Summary Report. When I put in the two dates (I pick the dates), I get DB Error Unknown Error. The Page Source code shows the following (see below).

I also got this error when moving from CRM back to Drupal:

Quote

• Notice: Undefined variable: form in CRM_Utils_Date::relativeToAbsolute() (line 922 of /home2/nlcacom/public_html/dev/sites/all/modules/civicrm/CRM/Utils/Date.php).
• Notice: Undefined variable: form in CRM_Utils_Date::relativeToAbsolute() (line 932 of /home2/nlcacom/public_html/dev/sites/all/modules/civicrm/CRM/Utils/Date.php).

Page Source Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<base href="http://nlca.com/sites/all/modules/civicrm/" />
<style type="text/css" media="screen">@import url(http://nlca.com/sites/all/modules/civicrm/css/civicrm.css);</style>
<style type="text/css" media="screen">@import url(http://nlca.com/sites/all/modules/civicrm/css/extras.css);</style>
<script type="text/javascript" src="http://nlca.com/sites/all/modules/civicrm/js/jquery/jquery.crmaccordions.js"></script>
</head>
<body>
<div id="crm-container" lang="en" xml:lang="en">
<div class="messages status"> <div class="icon red-icon alert-icon"></div>
<span class="status-fatal">Sorry. A non-recoverable error has occurred.</span>
<div class="crm-section crm-error-message"></div>
<hr style="solid 1px" />
<div class="crm-section crm-error-message">DB Error: unknown error</div>
<div class="crm-accordion-wrapper crm-accordion_title-accordion crm-accordion-closed crm-fatal-error-details-block">
<div class="crm-accordion-header">
<div class="icon crm-accordion-pointer"></div>
Error Details </div>
<div class="crm-accordion-body">
<div class="crm-section">Database Error Code: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay, 1104</div>
<div class="crm-section">Additional Details: <p><pre>Array
(
[callback] => Array
(

=> CRM_Core_Error

Code: [Select]

 =&gt; -1
    [message] =&gt; DB Error: unknown error
    [mode] =&gt; 16
    [debug_info] =&gt; SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as contact_civireport_sort_name, contact_civireport.id as contact_civireport_id, 
contribution1_total_amount_count, contribution1_total_amount_sum, 
contribution2_total_amount_count, contribution2_total_amount_sum, CONCAT_WS('_', contact_civireport.id) AS uni  
FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_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

LEFT  JOIN (
   SELECT contribution1.contact_id,
          sum( contribution1.total_amount ) AS contribution1_total_amount_sum,
          count( * ) AS contribution1_total_amount_count
   FROM   civicrm_contribution contribution1
   WHERE  ( ( contribution1.receive_date &gt;= 20120701000000 ) AND ( contribution1.receive_date &lt;= 20130630235959 ) )  AND contribution1.contribution_status_id IN ( 1 ) AND contribution1.contribution_type_id IN ( 1 ) AND contribution1.is_test = 0
   GROUP BY contribution1.contact_id
) contribution1 ON contact_civireport.id = contribution1.contact_id

LEFT  JOIN (
   SELECT contribution2.contact_id,
          sum( contribution2.total_amount ) AS contribution2_total_amount_sum,
          count( * ) AS contribution2_total_amount_count
   FROM   civicrm_contribution contribution2
   WHERE  ( ( contribution2.receive_date &gt;= 20110701000000 ) AND ( contribution2.receive_date &lt;= 20120630235959 ) )  AND contribution2.contribution_status_id IN ( 1 ) AND contribution2.contribution_type_id IN ( 1 ) AND contribution2.is_test = 0
   GROUP BY contribution2.contact_id
) contribution2 ON contact_civireport.id = contribution2.contact_id
 WHERE !(contribution1_total_amount_count IS NULL AND contribution2_total_amount_count IS NULL) GROUP BY contact_civireport.id ORDER BY contact_civireport.sort_name  LIMIT 0, 50 [nativecode=1104 ** The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    [type] =&gt; DB_Error
    [user_info] =&gt; SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as contact_civireport_sort_name, contact_civireport.id as contact_civireport_id, 
contribution1_total_amount_count, contribution1_total_amount_sum, 
contribution2_total_amount_count, contribution2_total_amount_sum, CONCAT_WS('_', contact_civireport.id) AS uni  
FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_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

LEFT  JOIN (
   SELECT contribution1.contact_id,
          sum( contribution1.total_amount ) AS contribution1_total_amount_sum,
          count( * ) AS contribution1_total_amount_count
   FROM   civicrm_contribution contribution1
   WHERE  ( ( contribution1.receive_date &gt;= 20120701000000 ) AND ( contribution1.receive_date &lt;= 20130630235959 ) )  AND contribution1.contribution_status_id IN ( 1 ) AND contribution1.contribution_type_id IN ( 1 ) AND contribution1.is_test = 0
   GROUP BY contribution1.contact_id
) contribution1 ON contact_civireport.id = contribution1.contact_id

LEFT  JOIN (
   SELECT contribution2.contact_id,
          sum( contribution2.total_amount ) AS contribution2_total_amount_sum,
          count( * ) AS contribution2_total_amount_count
   FROM   civicrm_contribution contribution2
   WHERE  ( ( contribution2.receive_date &gt;= 20110701000000 ) AND ( contribution2.receive_date &lt;= 20120630235959 ) )  AND contribution2.contribution_status_id IN ( 1 ) AND contribution2.contribution_type_id IN ( 1 ) AND contribution2.is_test = 0
   GROUP BY contribution2.contact_id
) contribution2 ON contact_civireport.id = contribution2.contact_id
 WHERE !(contribution1_total_amount_count IS NULL AND contribution2_total_amount_count IS NULL) GROUP BY contact_civireport.id ORDER BY contact_civireport.sort_name  LIMIT 0, 50 [nativecode=1104 ** The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    [to_string] =&gt; [db_error: message=&quot;DB Error: unknown error&quot; code=-1 mode=callback callback=CRM_Core_Error::handle prefix=&quot;&quot; info=&quot;SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as contact_civireport_sort_name, contact_civireport.id as contact_civireport_id, 
contribution1_total_amount_count, contribution1_total_amount_sum, 
contribution2_total_amount_count, contribution2_total_amount_sum, CONCAT_WS('_', contact_civireport.id) AS uni  
FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_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

LEFT  JOIN (
   SELECT contribution1.contact_id,
          sum( contribution1.total_amount ) AS contribution1_total_amount_sum,
          count( * ) AS contribution1_total_amount_count
   FROM   civicrm_contribution contribution1
   WHERE  ( ( contribution1.receive_date &gt;= 20120701000000 ) AND ( contribution1.receive_date &lt;= 20130630235959 ) )  AND contribution1.contribution_status_id IN ( 1 ) AND contribution1.contribution_type_id IN ( 1 ) AND contribution1.is_test = 0
   GROUP BY contribution1.contact_id
) contribution1 ON contact_civireport.id = contribution1.contact_id

LEFT  JOIN (
   SELECT contribution2.contact_id,
          sum( contribution2.total_amount ) AS contribution2_total_amount_sum,
          count( * ) AS contribution2_total_amount_count
   FROM   civicrm_contribution contribution2
   WHERE  ( ( contribution2.receive_date &gt;= 20110701000000 ) AND ( contribution2.receive_date &lt;= 20120630235959 ) )  AND contribution2.contribution_status_id IN ( 1 ) AND contribution2.contribution_type_id IN ( 1 ) AND contribution2.is_test = 0
   GROUP BY contribution2.contact_id
) contribution2 ON contact_civireport.id = contribution2.contact_id
 WHERE !(contribution1_total_amount_count IS NULL AND contribution2_total_amount_count IS NULL) GROUP BY contact_civireport.id ORDER BY contact_civireport.sort_name  LIMIT 0, 50 [nativecode=1104 ** The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]&quot;]
)
</pre></p><p></p></div>
                     </div><!-- /.crm-accordion-body -->
        </div><!-- /.crm-accordion-wrapper -->
        <p><a href="http://nlca.com/" title="Main Menu">Return to home page.</a></p>
</div>
</div> 
<script type="text/javascript">
cj(function() {
   cj().crmaccordions(); 
});
</script>

</body>
</html>

Kurund Jalmi · May 28, 2013, 10:53:03 am

as mentioned in the error you should check below error with your hosting company. Error might be related to some mysql settings

Quote

Database Error Code: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE

Kurund

thalemn · May 28, 2013, 06:17:18 pm

Thanks Kurund.

Just to clarify, I should contact Bluehost about making a change to the mysql settings? I am relatively new to CiviCRM and need to get this issue resolved as quickly as I can. I have working knowledge of mysql, but may need further guidance.

thalemn · May 29, 2013, 03:56:42 am

I have heard back from Bluehost and they cannot help with this issue because of the shared hosting.

I see some recommendations to set option sql_big_selects=1 but I do not know if that will solve this issue. And if it will solve the issue, I would need to know where to put the code. Can someone please tell me where the following code would be placed? Thank you!

<?php
global $db_type;
if ($db_type == 'mysql' || $db_type == 'mysqli') {
db_query("SET OPTION SQL_BIG_SELECTS=1"); // added
}
?>

Hershel · May 29, 2013, 10:16:02 am

It may not be the ideal place, but you could try placing that code at the top of sites/all/modules/civicrm/drupal/civicrm.module and see if it helps.

If not, then I think trying another host would make sense.

CiviCRM Community Forums (archive)

News:

Author Topic: Donation Summary Report Errors (Read 991 times)

thalemn

Donation Summary Report Errors

Kurund Jalmi

Re: Donation Summary Report Errors

thalemn

Re: Donation Summary Report Errors

thalemn

Re: Donation Summary Report Errors

Hershel

Re: Donation Summary Report Errors