Too many tables; MariaDB can only use 61 tables in a join

Closed Issue created 5 years ago by Pradeep Nayak

CiviCRM breaks when doing CRUDL actions on Activities with DB error ' Too many tables; MariaDB can only use 61 tables in a join' when you have system with more than 61 custom group for Activities. (See backtrace below)

The query generated by code joins all custom tables (which are active) to base table (i.e activity). As a partial fix we need to disable some custom group to make it working. But i think this should be fixed in core as this may occur for any entity if the system has more than 60 active custom group.

Approach:

Creating temp table for X set custom group if total number of custom group in system exceed 60.
Alter code to support temp table join

Aug 23 14:04:46  [info] $backTrace = #0 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/Error.php(952): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/drupal758civi5134/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#3 /var/www/drupal758civi5134/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#4 /var/www/drupal758civi5134/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...", "DB_Error", TRUE)
#5 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...", "1116 ** Too many tables; MariaDB can only use 61 tables in a join")
#7 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#9 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#10 /var/www/drupal758civi5134/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#11 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#12 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/DAO.php(1414): CRM_Core_DAO->query("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...", TRUE)
#13 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(831): CRM_Core_DAO::executeQuery("\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...")
#14 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(780): CRM_Core_BAO_CustomGroup::buildTreeEntityDataFromQuery((Array:62), "\n      SELECT civicrm_value_sla_acceptance_4.id as civicrm_value_sla_accepta...", (Array:61))
#15 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(669): CRM_Core_BAO_CustomGroup::buildEntityTreeSingleFields((Array:62), 28600, (Array:525), (Array:61))
#16 /var/www/drupal758civi5134/sites/all/modules/civicrm/api/v3/utils.php(1436): CRM_Core_BAO_CustomGroup::getTree("Activity", (Array:2), 28600, "117", (Array:0), NULL, TRUE, NULL, TRUE, 1)
#17 /var/www/drupal758civi5134/sites/all/modules/civicrm/api/v3/Activity.php(603): _civicrm_api3_custom_data_get((Array:13), 1, "Activity", 28600, NULL, "64")
#18 /var/www/drupal758civi5134/sites/all/modules/civicrm/api/v3/Activity.php(335): _civicrm_api3_activity_get_formatResult((Array:13), (Array:1), (Array:5))
#19 /var/www/drupal758civi5134/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(101): civicrm_api3_activity_get((Array:13))
#20 /var/www/drupal758civi5134/sites/all/modules/civicrm/Civi/API/Kernel.php(168): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))
#21 /var/www/drupal758civi5134/sites/all/modules/civicrm/Civi/API/Kernel.php(99): Civi\API\Kernel->runRequest((Array:9))
#22 /var/www/drupal758civi5134/sites/all/modules/civicrm/api/api.php(43): Civi\API\Kernel->runSafe("Activity", "Get", (Array:13))
#23 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php(721): civicrm_api3("Activity", "Get", (Array:13))
#24 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php(2498): CRM_Activity_BAO_Activity::getActivities((Array:10))
#25 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Activity/Page/AJAX.php(418): CRM_Activity_BAO_Activity::getContactActivitySelector((Array:10))
#26 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/Invoke.php(277): CRM_Activity_Page_AJAX::getContactActivity()
#27 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/Invoke.php(85): CRM_Core_Invoke::runItem((Array:12))
#28 /var/www/drupal758civi5134/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#29 /var/www/drupal758civi5134/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:3))
#30 /var/www/drupal758civi5134/includes/menu.inc(527): civicrm_invoke("ajax", "contactactivity")
#31 /var/www/drupal758civi5134/index.php(21): menu_execute_active_handler()