Skip to content
Snippets Groups Projects
New look: Off

Too many custom groups

    • Closed Issue created by kristinec @kristinec

    Two years ago, I reported an issue with "too many custom fields" Issue #1330. It seems that I have now surpass the limit for the custom groups instead of custom fields. If I add custom group number 94, accessing some cases produces an error "case_id is not valid : 1.". Keeping the number of custom groups at 93 removes the error.

    Edited
    To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information

    Child items ...

    • Activity

    • kristinec changed the description

      changed the description

    • eileen
      eileen @eileen ·
      Owner

      Unfortunately it is a sql limit on the number of joins

    • kristinec
      kristinec @kristinec ·
      Author

      Ah, correction. I'm currently at 77 custom groups which it seems to max out around 80 on my end. I'll explore some options, do a write-up, and come back to close the issue. Users seem to only be getting the error when viewing a case page which I wonder if it's because the case page loads all the activity types (see #3709 (closed)).

      Including the code here as future reference.

      Jun 27 22:15:17  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => 
	SELECT     [lists all custom_fields]
	FROM  (SELECT 161318 as entity_id ) as first 
	LEFT JOIN [lists all custom groups, repeat each line] USING (entity_id)
	   WHERE first.entity_id = 161318
     [nativecode=1116 ** Too many tables; MySQL can only use 61 tables in a join]
    [type] => DB_Error

Jun 27 22:15:17  [debug] $backTrace = #0 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(941): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#3 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#4 /home/admin/domains/cms.org/public_html/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_initial_form_12.id as civicrm_value_in...", "DB_Error", TRUE)
#5 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...", "1116 ** Too many tables; MySQL can only use 61 tables in a join")
#7 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#9 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#10 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#11 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(472): DB_DataObject->query("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#12 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(1637): CRM_Core_DAO->query("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...", TRUE)
#13 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(783): CRM_Core_DAO::executeQuery("\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...")
#14 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(732): CRM_Core_BAO_CustomGroup::buildTreeEntityDataFromQuery((Array:62), "\n      SELECT civicrm_value_initial_form_12.id as civicrm_value_in...", (Array:61))
#15 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomGroup.php(621): CRM_Core_BAO_CustomGroup::buildEntityTreeSingleFields((Array:62), 161318, (Array:840), (Array:61))
#16 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/v3/utils.php(1427): CRM_Core_BAO_CustomGroup::getTree("Activity", (Array:2), 161318, NULL, (Array:0), NULL, TRUE, NULL, TRUE, 2)
#17 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/v3/Activity.php(576): _civicrm_api3_custom_data_get((Array:11), 1, "Activity", 161318, NULL, "51")
#18 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/v3/Activity.php(307): _civicrm_api3_activity_get_formatResult((Array:11), (Array:1), (Array:5))
#19 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_activity_get((Array:11))
#20 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/Civi/API/Kernel.php(149): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#21 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#22 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/api.php(133): Civi\API\Kernel->runSafe("Activity", "Get", (Array:11))
#23 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php(643): civicrm_api3("Activity", "Get", (Array:11))
#24 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Activity/Selector/Activity.php(377): CRM_Activity_BAO_Activity::getActivities((Array:8))
#25 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(404): CRM_Activity_Selector_Activity->getRows(4, 0, 50, Object(CRM_Utils_Sort), 1, 2674)
#26 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(326): CRM_Core_Selector_Controller->getRows(Object(CRM_Core_Selector_Controller))
#27 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Case/Page/Tab.php(111): CRM_Core_Selector_Controller->run()
#28 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Case/Page/Tab.php(180): CRM_Case_Page_Tab->view()
#29 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(319): CRM_Case_Page_Tab->run((Array:4), NULL)
#30 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(69): CRM_Core_Invoke::runItem((Array:17))
#31 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#32 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/drupal/civicrm.module(471): CRM_Core_Invoke::invoke((Array:4))
#33 /home/admin/domains/cms.org/public_html/includes/menu.inc(527): civicrm_invoke("contact", "view", "case")
#34 /home/admin/domains/cms.org/public_html/index.php(21): menu_execute_active_handler()
#35 {main}

Jun 27 22:15:17  [error] 
$Fatal Error Details = array:3 [
  "message" => "case_id is not valid : 1"
  "code" => null
  "exception" => CiviCRM_API3_Exception {#142
    -extraParams: array:4 [
      "error_code" => "unknown error"
      "tip" => "add debug=1 to your API call to have more info about the error"
      "is_error" => 1
      "error_message" => "case_id is not valid : 1"
    ]
    #message: "case_id is not valid : 1"
    #code: 0
    #file: "/home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/api.php"
    #line: 135
    trace: {
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/api/api.php:135 {
        › if (is_array($result) && !empty($result['is_error'])) {
        ›   throw new CiviCRM_API3_Exception($result['error_message'], CRM_Utils_Array::value('error_code', $result, 'undefined'), $result);
        › }
      }
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php:643 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Activity/Selector/Activity.php:377 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php:404 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php:326 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Case/Page/Tab.php:111 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Case/Page/Tab.php:180 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:319 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:69 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:36 { …}
      /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/drupal/civicrm.module:471 { …}
      /home/admin/domains/cms.org/public_html/includes/menu.inc:527 { …}
      /home/admin/domains/cms.org/public_html/index.php:21 { …}
    }
  }
]

Jun 27 22:15:17  [debug] $backTrace = #0 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(433): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(39): CRM_Core_Error::handleUnhandledException(Object(CiviCRM_API3_Exception))
#2 /home/admin/domains/cms.org/public_html/sites/all/modules/civicrm/drupal/civicrm.module(471): CRM_Core_Invoke::invoke((Array:4))
#3 /home/admin/domains/cms.org/public_html/includes/menu.inc(527): civicrm_invoke("contact", "view", "case")
#4 /home/admin/domains/f/public_html/index.php(21): menu_execute_active_handler()
#5 {main}
      Edited by kristinec
    • eileen added triaged label

      added triaged label

    • ****
      **** @support_bot_dev ·
      Reporter

      Hi,

      Thank you for raising an issue to help improve CiviCRM. As you may know, this issue has not had any activity for quite some time, so we have closed it.

      We would like you to help us to determine if this issue should be re-opened:

      • If this issue was reporting a bug, can you attempt to reproduce it on a latest version of CiviCRM?
      • If this issue was proposing a new feature, can you verify if the feature proposal is still relevant? Did it get the concept-approved label? Have other people also shown interest? Could it be implemented as an extension?

      If the answer to either question is yes, please feel free to comment or re-open the issue. Please also consider:

      • Is it something that you could help implement, either by sending a patch or hiring someone who can?
      • Would you be willing to fund the work, either through a CiviCRM partner, or the Core Team?

      Thank you for your help and contributions to CiviCRM.

      P.S. This is an automated message, see infra/gitlab issue 20. We understand that automatic responses are annoying, but given the number of open issues as the project evolves, we need a bit of help to triage and prioritise the most relevant issues.

    • **** closed

      closed

    Please register or sign in to reply