Unknown DB Error When Doing Export

[VRAZER]

Using CiviCRM 4.3.5 on Drupal 7.22 and we're getting errors when doing some exports we get an unknown db error.

Here is the backtrace

#0 /public_html/sites/all/modules/civicrm/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /public_html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /public_html/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: unknown error", -1, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#4 /public_html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#5 /public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...", "DB_Error", TRUE)
#6 /public_html/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-1, NULL, NULL, NULL, "126 ** Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it")
#7 /public_html/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#9 /public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#10 /public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#11 /public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(155): DB_DataObject->query("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#12 /public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(917): CRM_Core_DAO->query("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...", TRUE)
#13 /public_html/sites/all/modules/civicrm/CRM/Export/BAO/Export.php(621): CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`,...")
#14 /public_html/sites/all/modules/civicrm/CRM/Export/Form/Select.php(389): CRM_Export_BAO_Export::exportComponents(TRUE, (Array:0), (Array:1), "sort_name asc", NULL, NULL, 1, NULL, "civicrm_task_action_temp_6081b0af58abc90312a8d9e871b39460_6845", 0, 0, (Array:10), 1)
#15 /public_html/sites/all/modules/civicrm/CRM/Core/Form.php(246): CRM_Export_Form_Select->postProcess()
#16 /public_html/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#17 /public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(60): CRM_Core_StateMachine->perform(Object(CRM_Export_Form_Select), "next", "Next")
#18 /public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Export_Form_Select), "next")
#19 /public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Export_Form_Select), "next")
#20 /public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(316): HTML_QuickForm_Page->handle("next")
#21 /public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(292): CRM_Core_Controller->run((Array:3), (Array:0))
#22 /public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(70): CRM_Core_Invoke::runItem((Array:13))
#23 /public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#24 /public_html/sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:3))
#25 [internal function](): civicrm_invoke("contact", "search")
#26 /public_html/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#27 /public_html/index.php(21): menu_execute_active_handler()
#28 {main}

Any idea how we can resolve this. 

Whatever information you need to look into this, I'd be happy to supply.

Thanks!

[Donald Lobo]

seems like the error message is this:

"126 ** Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it" (from backtrace #6)

how large is the export? can u check your mysql error logs and see if u get any more information

lobo

[VRAZER]

Hey Lobo,

The export is only like 350-400 contacts.  Not very big.  The database does have a lot of contacts in it, though.  Over 20,000 or so.

As to the MySQL error logs, this is all I'm seeing.  Doesn't look very helpful.

Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
130719 14:08:10 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_1.MYI'; try to repair it
130719 14:08:10 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130719 14:08:10 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130719 14:08:10 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130719 14:08:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130719 14:08:33 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130723 12:04:30 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_2.MYI'; try to repair it
130723 12:04:30 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130723 12:04:30 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130723 12:04:30 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130723 12:04:30 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_1.MYI'; try to repair it
130723 12:04:30 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130723 12:04:38 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_1.MYI'; try to repair it
130723 12:04:38 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130724 16:49:47 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130724 16:49:47 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130726 12:21:50 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130726 12:21:50 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130729 10:50:10 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130729 10:50:10 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130729 10:51:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130729 10:51:17 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130729 10:52:35 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130729 10:52:35 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130729 11:00:39 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130729 11:00:39 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130730 17:17:04 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130730 17:17:04 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 10:51:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 10:51:33 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 11:36:16 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 11:36:16 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 11:38:12 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 11:38:12 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 11:38:35 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 11:38:35 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 11:41:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 11:41:41 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 11:43:46 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 11:43:46 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 14:25:23 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 14:25:23 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130731 14:38:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130731 14:38:33 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130801 11:12:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130801 11:12:28 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130801 11:13:15 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130801 11:13:15 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130801 13:52:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130801 13:52:14 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130801 14:18:55 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130801 14:18:55 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:39:23 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:39:23 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:40:53 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:40:53 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:43:10 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:43:10 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:46:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:46:41 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:56:10 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:56:10 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:57:20 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:57:20 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802  9:58:11 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802  9:58:11 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802 10:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802 10:57:29 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802 11:16:07 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802 11:16:07 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226
130802 11:16:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_4f3_0.MYI'; try to repair it
130802 11:16:26 [ERROR] Got an error from unknown thread, /home/rpmbuild/rpm-build/BUILD/mysql-5.5.32/mysql-5.5.32/storage/myisam/mi_write.c:226

[Donald Lobo]

sorry no idea, please do report back when you figure it out so others can benefit

if i had to guess, it seems like its hitting some mysql file / buffer limit

lobo

[civi5]

Late reply but I came across this thread as I ran into the same problem.  Lobo was correct in that there is a mysql limit that is causing the error.  I was able to get around this issue by increasing the following mysql constraints:

query_cache_size
tmp_table_size

I was running an export of only 282 contacts. The database has a total of 20,000 or so just for reference.  The temporary tables were getting in the 600MB range even though the final CSV export was only 545KB.  I was exporting data using the "Export Primary Fields" option so there must be some terribly bloated queries in the export function, including full tables when you just want a single column or such. 

Anyway increasing the MySQL limits in My.ini did the trick.

Just remember to restart the service: 
    sudo service mysql restart
If you are running debian based servers.