Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-21491

CiviCRM Error Log: Too Many Tables MySQL 1116

          Details

          • Versioning Impact:
            Minor (add functionality in backwards-compatible manner)
          • Documentation Required?:
            None
          • Funding Source:
            Paid Issue Queue
          • Payment Status:
            Estimate Needed
          • Verified?:
            No
          • Overview:
            Hide
            I believe there is a problem with the CiviCRM API.

            In our example, we are loading contact and activity data into a webform. But the question is, why is the API trying to load all custom data sets relating to all activities, and not just exclusively, the custom data relating to the activity type and contact type in question.

            This issue causes : - "1116 ** Too many tables; MySQL can only use 61 tables in a join"
            Show
            I believe there is a problem with the CiviCRM API. In our example, we are loading contact and activity data into a webform. But the question is, why is the API trying to load all custom data sets relating to all activities, and not just exclusively, the custom data relating to the activity type and contact type in question. This issue causes : - "1116 ** Too many tables; MySQL can only use 61 tables in a join"
          • How it should work:
            My view is a smaller query, more efficient, with fewer joins
          • Acceptance Criteria:
            The webform can load relevant data into it when specifying existing contact and existing activity. The API should not try to query all unnecessary tables.

            Description

            When the CiviCRM API calls a get activity, or get contact, we have seen an error recorded to the ConfigAndLog file, such as: -

            #6 /home/xxx/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-1, NULL, NULL, NULL, "1116 ** Too many tables; MySQL can only use 61 tables in a join")

            This has been reported elsewhere, but I believe it is a problem with the API. In our example, we are loading contact and activity data into a webform. But the question is, why is the API trying to load all custom data sets relating to all activities, and not just exclusively, the custom data relating to the activity type and contact type in question.

            I sense this is a real problem for many user organisations. Can you provide an estimate on a fix for this please? We think it does affect CiviCRM reports as well as webforms. Otherwise, we have a real product limitation which will put people off using CiviCRM.

            Please can I get a developer view on this, with a quote on development (where it is determined it can be resolved in this manner),

              Attachments

                Activity

                  People

                  • Assignee:
                    adamw Adam Wight
                    Reporter:
                    jon-man Jon-man Cheung
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated: