Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
C
Core
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 925
    • Issues 925
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Operations
    • Operations
    • Incidents
  • Analytics
    • Analytics
    • Repository
    • Value Stream
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Members
    • Members
  • Collapse sidebar
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
  • Development
  • Core
  • Issues
  • #367

Closed
Open
Opened Aug 28, 2018 by AllenShaw@AllenShawDeveloper

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:

  1. Start with a site having:
    1. 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".
    2. At least one group (smart group or static group).
  2. Perform an Advanced Search with these criteria:
    1. Relationships > Relationship Type: "Spouse of"
    2. Relationships > Target Contact(s) in Group: [any one or more groups]
    3. 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.

Edited Aug 29, 2018 by AllenShaw
To upload designs, you'll need to enable LFS and have admin enable hashed storage. More information
Assignee
Assign to
5.6
Milestone
5.6
Assign milestone
Time tracking
None
Due date
None
Reference: dev/core#367