CiviCRM Community Forums (archive)

New Joomla 1.5.9 CiviCRM 2.2 (stable) running on Bluehost.com generates an error whenever CiviContribute is selected.

"We apologize for the trouble you've been having, if you are getting that error it means that the script is attempting a query which creates a temporary file with a size that exceeds what we allow on our servers. We unfortunately can't raise the mysql temporary file size limit, so this component will not work on our servers unless you hire a programmer to modify the queries so it doesn't use such large joins."

Here is the error:

Error Details:

Array
(
    [callback] => Array
        (
            

• => CRM_Core_Error
[1] => handle
        )

    

=> -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, 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.currency as `currency`, 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`, contribution_status.name as contribution_status_id, civicrm_contribution.check_number as `check_number`, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, 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`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_1_grtl_contributions.id as civicrm_value_1_grtl_contributions_id, civicrm_value_1_grtl_contributions.check_number as custom_15, civicrm_value_1_grtl_contributions.check_date as custom_17, civicrm_value_1_grtl_contributions.contribution_credit 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_1_grtl_contributions ON civicrm_value_1_grtl_contributions.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  ( 1 )    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/ramdisk/mysql/tmp/#sql_f9c_0.MYI'; try to repair it]
    [type] => DB_Error
    [user_info] => SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, 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.currency as `currency`, 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`, contribution_status.name as contribution_status_id, civicrm_contribution.check_number as `check_number`, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, 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`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_1_grtl_contributions.id as civicrm_value_1_grtl_contributions_id, civicrm_value_1_grtl_contributions.check_number as custom_15, civicrm_value_1_grtl_contributions.check_date as custom_17, civicrm_value_1_grtl_contributions.contribution_credit 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_1_grtl_contributions ON civicrm_value_1_grtl_contributions.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  ( 1 )    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/ramdisk/mysql/tmp/#sql_f9c_0.MYI'; try to repair it]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_contribution.id as contribution_id, 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.currency as `currency`, 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`, contribution_status.name as contribution_status_id, civicrm_contribution.check_number as `check_number`, civicrm_contribution_type.id as contribution_type_id, civicrm_contribution_type.name as contribution_type, civicrm_contribution_type.accounting_code as `accounting_code`, 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`, payment_instrument.id as instrument_id, payment_instrument.label as `payment_instrument`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_note_contribution.note as contribution_note, payment_instrument.name as contribution_payment_instrument, civicrm_contribution.check_number as contribution_check_number, civicrm_value_1_grtl_contributions.id as civicrm_value_1_grtl_contributions_id, civicrm_value_1_grtl_contributions.check_number as custom_15, civicrm_value_1_grtl_contributions.check_date as custom_17, civicrm_value_1_grtl_contributions.contribution_credit 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_1_grtl_contributions ON civicrm_value_1_grtl_contributions.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  ( 1 )    ORDER BY receive_date desc  LIMIT 0, 10  [nativecode=126 ** Incorrect key file for table '/ramdisk/mysql/tmp/#sql_f9c_0.MYI'; try to repair it]"]
)


We have a large database. Is there a work-around?

about 250K records
119K contributions

I ran into a similar problem. The breakdown happens with the inclusion of the custom fields, particular those with option groups (select, multi-select, radio button fields) as that adds two more joins, and basically pushes the query "over the edge".

Lobo -- I think we had a discussion about this at some point. Can't remember if it was on the forums or elsewhere. Part of a larger discussion about creating query efficiencies.

Anyway, as Lobo pointed out, for a db of that size and scope, you definitely should be looking at a VPS solution. That will give you more control over resources and config settings for php/mysql.