Author Topic: Upgrade to 3.4; Contribute Dashboard error (Read 821 times)

LoganBear · April 29, 2011, 01:39:52 pm

When I look at the CiviContribute Dashboard using a DB of about 11M Contacts with 33M Contributions on the new version, I get the following error:

Code: [Select]

Database Error Code: Incorrect key file for table '/tmp/#sql_4d93_0.MYI'; try to repair it, 126
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => SELECT  DISTINCT(civicrm_contribution.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, civicrm_contribution.currency as `currency`, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.non_deductible_amount as `non_deductible_amount`, civicrm_contribution.total_amount as `total_amount`, civicrm_contribution.fee_amount as `fee_amount`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.trxn_id as `trxn_id`, civicrm_contribution.invoice_id as `invoice_id`, civicrm_contribution.cancel_date as `cancel_date`, civicrm_contribution.cancel_reason as `cancel_reason`, civicrm_contribution.receipt_date as `receipt_date`, civicrm_contribution.thankyou_date as `thankyou_date`, civicrm_contribution.source as `contribution_source`, civicrm_contribution.amount_level as `amount_level`, civicrm_contribution.is_test as `is_test`, civicrm_contribution.is_pay_later as `is_pay_later`, civicrm_contribution.check_number as `check_number`, civicrm_contribution.campaign_id as contribution_campaign_id, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_product.id as product_id, civicrm_product.name as `product_name`, civicrm_product.sku as `sku`, civicrm_contribution_product.id as contribution_product_id, civicrm_contribution_product.product_option as `product_option`, civicrm_contribution_product.fulfilled_date as `fulfilled_date`, civicrm_contribution_product.start_date as `contribution_start_date`, civicrm_contribution_product.end_date as `contribution_end_date`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, contribution_status.value as contribution_status_id, contribution_status.label as contribution_status, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_sponsorship_options_1.id as civicrm_value_sponsorship_options_1_id, civicrm_value_sponsorship_options_1.i_want_to_be_anonymous_1 as custom_1, civicrm_value_sponsorship_options_1.sponsor_opportunity_2 as custom_2, civicrm_value_sponsorship_options_1.acknowledgement_name_22 as custom_22, civicrm_value_ubercart_purchases_4.id as civicrm_value_ubercart_purchases_4_id, civicrm_value_ubercart_purchases_4.sales_tax_20 as custom_20, civicrm_value_ubercart_purchases_4.shipping_cost_21 as custom_21  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id  
LEFT JOIN civicrm_value_sponsorship_options_1 ON civicrm_value_sponsorship_options_1.entity_id = civicrm_contribution.id  
LEFT JOIN civicrm_value_ubercart_purchases_4 ON civicrm_value_ubercart_purchases_4.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_note civicrm_note_contribution ON ( civicrm_note_contribution.entity_table = 'civicrm_contribution' AND
                                                        civicrm_contribution.id = civicrm_note_contribution.entity_id ) LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value 
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/tmp/#sql_4d93_0.MYI'; try to repair it]
    [type] => DB_Error
    [user_info] => SELECT  DISTINCT(civicrm_contribution.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, civicrm_contribution.currency as `currency`, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.non_deductible_amount as `non_deductible_amount`, civicrm_contribution.total_amount as `total_amount`, civicrm_contribution.fee_amount as `fee_amount`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.trxn_id as `trxn_id`, civicrm_contribution.invoice_id as `invoice_id`, civicrm_contribution.cancel_date as `cancel_date`, civicrm_contribution.cancel_reason as `cancel_reason`, civicrm_contribution.receipt_date as `receipt_date`, civicrm_contribution.thankyou_date as `thankyou_date`, civicrm_contribution.source as `contribution_source`, civicrm_contribution.amount_level as `amount_level`, civicrm_contribution.is_test as `is_test`, civicrm_contribution.is_pay_later as `is_pay_later`, civicrm_contribution.check_number as `check_number`, civicrm_contribution.campaign_id as contribution_campaign_id, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_product.id as product_id, civicrm_product.name as `product_name`, civicrm_product.sku as `sku`, civicrm_contribution_product.id as contribution_product_id, civicrm_contribution_product.product_option as `product_option`, civicrm_contribution_product.fulfilled_date as `fulfilled_date`, civicrm_contribution_product.start_date as `contribution_start_date`, civicrm_contribution_product.end_date as `contribution_end_date`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, contribution_status.value as contribution_status_id, contribution_status.label as contribution_status, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_sponsorship_options_1.id as civicrm_value_sponsorship_options_1_id, civicrm_value_sponsorship_options_1.i_want_to_be_anonymous_1 as custom_1, civicrm_value_sponsorship_options_1.sponsor_opportunity_2 as custom_2, civicrm_value_sponsorship_options_1.acknowledgement_name_22 as custom_22, civicrm_value_ubercart_purchases_4.id as civicrm_value_ubercart_purchases_4_id, civicrm_value_ubercart_purchases_4.sales_tax_20 as custom_20, civicrm_value_ubercart_purchases_4.shipping_cost_21 as custom_21  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id  
LEFT JOIN civicrm_value_sponsorship_options_1 ON civicrm_value_sponsorship_options_1.entity_id = civicrm_contribution.id  
LEFT JOIN civicrm_value_ubercart_purchases_4 ON civicrm_value_ubercart_purchases_4.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_note civicrm_note_contribution ON ( civicrm_note_contribution.entity_table = 'civicrm_contribution' AND
                                                        civicrm_contribution.id = civicrm_note_contribution.entity_id ) LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value 
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/tmp/#sql_4d93_0.MYI'; try to repair it]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT  DISTINCT(civicrm_contribution.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, civicrm_contribution.currency as `currency`, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.non_deductible_amount as `non_deductible_amount`, civicrm_contribution.total_amount as `total_amount`, civicrm_contribution.fee_amount as `fee_amount`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.trxn_id as `trxn_id`, civicrm_contribution.invoice_id as `invoice_id`, civicrm_contribution.cancel_date as `cancel_date`, civicrm_contribution.cancel_reason as `cancel_reason`, civicrm_contribution.receipt_date as `receipt_date`, civicrm_contribution.thankyou_date as `thankyou_date`, civicrm_contribution.source as `contribution_source`, civicrm_contribution.amount_level as `amount_level`, civicrm_contribution.is_test as `is_test`, civicrm_contribution.is_pay_later as `is_pay_later`, civicrm_contribution.check_number as `check_number`, civicrm_contribution.campaign_id as contribution_campaign_id, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_product.id as product_id, civicrm_product.name as `product_name`, civicrm_product.sku as `sku`, civicrm_contribution_product.id as contribution_product_id, civicrm_contribution_product.product_option as `product_option`, civicrm_contribution_product.fulfilled_date as `fulfilled_date`, civicrm_contribution_product.start_date as `contribution_start_date`, civicrm_contribution_product.end_date as `contribution_end_date`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, contribution_status.value as contribution_status_id, contribution_status.label as contribution_status, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_sponsorship_options_1.id as civicrm_value_sponsorship_options_1_id, civicrm_value_sponsorship_options_1.i_want_to_be_anonymous_1 as custom_1, civicrm_value_sponsorship_options_1.sponsor_opportunity_2 as custom_2, civicrm_value_sponsorship_options_1.acknowledgement_name_22 as custom_22, civicrm_value_ubercart_purchases_4.id as civicrm_value_ubercart_purchases_4_id, civicrm_value_ubercart_purchases_4.sales_tax_20 as custom_20, civicrm_value_ubercart_purchases_4.shipping_cost_21 as custom_21  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id  
LEFT JOIN civicrm_value_sponsorship_options_1 ON civicrm_value_sponsorship_options_1.entity_id = civicrm_contribution.id  
LEFT JOIN civicrm_value_ubercart_purchases_4 ON civicrm_value_ubercart_purchases_4.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_note civicrm_note_contribution ON ( civicrm_note_contribution.entity_table = 'civicrm_contribution' AND
                                                        civicrm_contribution.id = civicrm_note_contribution.entity_id ) LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value 
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/tmp/#sql_4d93_0.MYI'; try to repair it]"]
)

I even disabled the custom data and still get the error. What do I have to do to get the Dashboard to work?

Donald Lobo · April 29, 2011, 02:11:00 pm

wow, thats quite a large database. Would be great if you can write a blog post / case study on how/why/where u'll are using civicrm. the pros and the cons, whats working nicely, what can be improved etc

With regard to your current issue, i suspect you are hitting the limits of the query system. We have done quite a bit optimizing the contact queries for large data sets but have not done anything with the contribution (or participant/membership/pledges/cases etc) queries.

Would be great if your org can help push this forward and help optimize the component queries. This is not a trivial project, but i also think we can make pretty big improvements incrementally

For the short term, you might be able to work around the error by tweaking your mysql parameters (i dont know mysql well enough to be more specific)

lobo

ken · May 01, 2011, 02:34:30 am

@LoganBear,

Check you aren't running out of file space on /tmp .

Ken

LoganBear · May 01, 2011, 05:29:28 am

@Ken,

You got the problem. We recently moved to a new server and I config'd it for 1 Gb /tmp. Bumped it up to 5 Gb and no problem.

As to the rest of the stuff, @Lobo, we are still attempting to decide on which CRM to use. IT prefers CiviCRM, Operations leans toward SugarCRM. The Board, the final deciders, don't care either way. So, I really don't have anything to report.

Donald Lobo · May 01, 2011, 07:08:05 am

hey @loganbear

thanx for the response. do keep us updated.

would be great if you can elaborate a bit more on why IT prefers Civi and what are some of the reasons operations prefers Sugar over Civi

thanx

lobo

Hershel · May 01, 2011, 07:28:48 am

Would also be very interesting to hear what types of tests or criteria or demos etc. you are using to compare the tools. Even if you don't proceed with CiviCRM, we would be interested to hear any feedback how it runs with such a large DB.

Thanks.

CiviCRM Community Forums (archive)

News:

Author Topic: Upgrade to 3.4; Contribute Dashboard error (Read 821 times)

LoganBear

Upgrade to 3.4; Contribute Dashboard error

Donald Lobo

Re: Upgrade to 3.4; Contribute Dashboard error

ken

Re: Upgrade to 3.4; Contribute Dashboard error

LoganBear

Re: Upgrade to 3.4; Contribute Dashboard error

Donald Lobo

Re: Upgrade to 3.4; Contribute Dashboard error

Hershel

Re: Upgrade to 3.4; Contribute Dashboard error