CiviCRM Community Forums (archive)

A customer VPS is spending long periods doing queries of the form

REPLACE INTO civicrm_temp_custom_entityID_9ac331fb0291ef73735f5a3847aee985 ( entity_id )
       SELECT ca.id
         FROM civicrm_activity ca, civicrm_activity_target cat, civicrm_contact c, civicrm_email e
    LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
    LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )
        WHERE cat.activity_id = ca.id
          AND cat.target_contact_id = c.id
          AND ( c.display_name LIKE '%fulltext%' OR
              ( e.email LIKE '%fulltext%' AND cat.target_contact_id = e.contact_id AND
                ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email') ) )
        LIMIT 10 ;

These queries persist for a long time, during which time attempts to write to civicrm_activity will cause CiviCRM to die with a fatal error because the table civicrm_activity is locked. Note that this table isn't being written to, but apparently is still locked while the SELECT against it is writing to a temporary table.

mysql> EXPLAIN SELECT ca.id  FROM   civicrm_activity ca, civicrm_activity_target cat, civicrm_contact c, civicrm_email e LEFT JOIN civicrm_option_group og ON og.name = 'activity_type' LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )  WHERE  cat.activity_id = ca.id AND    cat.target_contact_id = c.id AND    ( c.display_name LIKE '%social styx%' OR          ( e.email LIKE '%social styx%' AND cat.target_contact_id = e.contact_id AND             ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email') ) )  LIMIT 10;
+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                                                        | key                           | key_len | ref                             | rows  | Extra       |
+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------------+-------+-------------+
|  1 | SIMPLE      | e     | ALL    | FK_civicrm_email_contact_id                                          | NULL                          | NULL    | NULL                            |  2299 |             |
|  1 | SIMPLE      | og    | const  | UI_name                                                              | UI_name                       | 194     | const                           |     1 | Using index |
|  1 | SIMPLE      | ov    | ref    | index_option_group_id_name                                           | index_option_group_id_name    | 4       | exampledb.og.id                 |     2 |             |
|  1 | SIMPLE      | cat   | index  | UI_activity_target_contact_id,FK_civicrm_activity_target_activity_id | UI_activity_target_contact_id | 8       | NULL                            | 48553 | Using index |
|  1 | SIMPLE      | ca    | eq_ref | PRIMARY                                                              | PRIMARY                       | 4       | exampledb.cat.activity_id       |     1 |             |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                                                              | PRIMARY                       | 4       | exampledb.cat.target_contact_id |     1 | Using where |
+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------------+-------+-------------+
6 rows in set (0.02 sec)

If MySQL was return a plain resultset rather than a temporary table, I think this wouldn't lock _activity, and thus wouldn't throw a fatal error. Is that correct? Is it a plausible way of handling fulltext search results? We could retrieve the resultset and then insert it into the same entityID table; it would be slightly slower, but wouldn't require locking the other tables.

This is happening in CRM/Contact/Form/Search/Custom/FullText.php in CRM_Contact_Form_Search_Custom_FullText::buildTempTable(). CiviCRM is 2.2.9.

Spoke to lobo on IRC - this issue had already been reported at http://issues.civicrm.org/jira/browse/CRM-5179 and a fix (by way of optimised SQL to build the table) already applied.

The updated SQL from 3.0 is much faster, which should prevent the long table locks. Instead of the SELECT above, I now see
SELECT     ca.id
FROM       civicrm_activity ca
INNER JOIN civicrm_activity_target cat ON cat.activity_id = ca.id
INNER JOIN civicrm_contact c ON cat.target_contact_id = c.id
LEFT  JOIN civicrm_email e ON cat.target_contact_id = e.contact_id
LEFT  JOIN civicrm_option_group og ON og.name = 'activity_type'
LEFT  JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )
WHERE c.display_name LIKE '%fulltext%' OR
       ( e.email LIKE '%fulltext%'    AND
         ca.activity_type_id = ov.value AND
         ov.name IN ('Inbound Email', 'Email') )

which gives the following EXPLAIN result

+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                                        | key                           | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | c     | ALL    | PRIMARY                                                              | NULL                          | NULL    | NULL                      | 3690 |             |
|  1 | SIMPLE      | cat   | ref    | UI_activity_target_contact_id,FK_civicrm_activity_target_activity_id | UI_activity_target_contact_id | 4       | exampledb.c.id            |    9 | Using index |
|  1 | SIMPLE      | ca    | eq_ref | PRIMARY                                                              | PRIMARY                       | 4       | exampledb.cat.activity_id |    1 |             |
|  1 | SIMPLE      | e     | ref    | FK_civicrm_email_contact_id                                          | FK_civicrm_email_contact_id   | 5       | exampledb.c.id            |    1 | Using where |
|  1 | SIMPLE      | og    | const  | UI_name                                                              | UI_name                       | 194     | const                     |    1 | Using index |
|  1 | SIMPLE      | ov    | ref    | index_option_group_id_name                                           | index_option_group_id_name    | 4       | exampledb.og.id           |    2 | Using where |
+----+-------------+-------+--------+----------------------------------------------------------------------+-------------------------------+---------+---------------------------+------+-------------+

The SVN diff for 3.x series applies cleanly to v2.2 - you can just use svn diff -r24059:24060 http://svn.civicrm.org/civicrm/branches/v3.0/CRM/Contact/Form/Search/Custom/FullText.php to get the patch for your 2.2.x install.