CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Unknown DB Error When Doing Export
Pages: [1]

Author Topic: Unknown DB Error When Doing Export  (Read 13347 times)

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Unknown DB Error When Doing Export
August 02, 2013, 08:33:20 am
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Unknown DB Error When Doing Export
August 02, 2013, 08:40:58 am

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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Re: Unknown DB Error When Doing Export
August 02, 2013, 01:07:19 pm
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Unknown DB Error When Doing Export
August 03, 2013, 08:21:09 pm

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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

civi5

  • I post occasionally
  • **
  • Posts: 39
  • Karma: 3
  • CiviCRM version: 4.1
  • CMS version: Drupal 7
  • MySQL version: MySQL 5
  • PHP version: 5.3.6
Re: Unknown DB Error When Doing Export
March 05, 2015, 12:31:27 pm
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.

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Unknown DB Error When Doing Export

This forum was archived on 2017-11-26.