CiviCRM Community Forums (archive)

I've got an interesting one that I ran into today...

I imported a few thousand contributions on a db a couple days ago. I have two custom field groups setup -- one is for all contributions, the second is unique to one contribution type. Import goes fine, but post-import when I try to access the CiviContribute dashboard page, I get the following error:

DB Error: unknown error
Database Error Code: Incorrect key file for table '/ramdisk/mysql/tmp/#sql_2d04_0.MYI'; try to repair it, 126
I'll make a long story short -- after a lot of hunting and searching, I arrive at the following as the problem:

Incorrect key file error, the cause of this issue has to do with the size of the JOINS, as well as ORDER BY and GROUP BY modifiers to the SQL queries. Typically these query modifiers create temporary tables in memory, and while the overall query is processing, the larger the temp table becomes, the more likely to have said temporary table written to disk, slowing query performance, and if the query is large enough, will fill the tmp/ directory, and cause the query to fail with the Invalid Key file Error. I've done a bit of research about this, and it has been suggested to take the large tables that are hit the most often with queries, and splitting them up in two. Some extremely technical information regarding this is found at:

http://www.petercooper.co.uk/archives/001006.html
http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

This seems to be verified by the fact that if I disable the second custom group (thus removing one left join; 10 fields) the page runs without a hitch.

So I ask, where is the "breaking point" when the table crashes. I re-enable the second custom group and begin enabling fields one by one. Again, making a long story short -- if I disabled the four fields that are standard alphanumeric text fields, the page runs. As soon as I enable one of them, the page crashes.

My other fields in this custom group consist of number, note, and date fields. Makes no sense to me why the simple text fields would create a problem -- I would expect note/memo and date fields to require more processing power.

So... I go into the actual custom field table and I change those four fields from a varchar type to a tinytext type --- and the page runs without a hitch (varchar fields are set to a 255 max, so the type change should be irrelevant).

The query generated by that page is pretty complex, so maybe it's not shocking that the limit was reached on the temp table. But why it suddenly becomes cooperative when the field type is changed is beyond me.

Happy to supply a db dump to the core team if they're interested.

-Brian