The one place for your designs
To enable design management, you'll need to meet the requirements. If you need help, reach out to our support team for assistance.
Fatal error / SQL timeout in A-Z pager for advanced search by relationship with target group for reciprocal relationship types
Pull request: https://github.com/civicrm/civicrm-core/pull/12740
Similar issue but not directly related: https://issues.civicrm.org/jira/browse/CRM-21811
On many sites, this is not an issue, but the more "spouse" relationships a site has, the more likely it happens (and not just spouse; this applies for "partner of" and any other reciprocally named relationship type).
Steps to reproduce:
- Start with a site having:
- Several thousand active relationships of a reciprocal type (where the name for both related positions is the same – e.g., "spouse of" <=> "spouse of"; "sibling of" <=> "sibling of"; "partner of" <=> "partner of"). This recipe uses "Spouse of".
- At least one group (smart group or static group).
- Perform an Advanced Search with these criteria:
- Relationships > Relationship Type: "Spouse of"
- Relationships > Target Contact(s) in Group: [any one or more groups]
- Submit the search form and observe a very long page load; depending on the number of relationships, server configuration, and server resources, this search may end in a WSOD, or take dozens of minutes to complete.
Analysis:
- When this actually times out, you get a fatal error from MySQL: "Query execution was interrupted (max_statement_time exceeded)"
- Backtrace indicates that the long-running query is the one used to generate the A-Z pager controls in search results; this query includes as join to a temporary table which is created without indices.
Proposed solution:
Add indices and column definitions to the temporary table, so that the join can use the indices and run in a timely manner.