CiviCRM Community Forums (archive)

Running a CiviCRM query at http://pastebin.com/6M1emH14, I get

ERROR 5 (HY000): Out of memory (Needed 1640964 bytes)

This query is run after attempting to save data for any contact for one user in particular, but not all.

Though I can submit a query change recommendation to CiviCRM, I'm hoping this can be fixed by adding an index or changing a parameter in the my.cnf file.   

Should I increase key_buffer_size or table_cache or is it another parameter?  An InnoDB parameter perhaps?

+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+
| id | select_type        | table                                   | type            | possible_keys                                                                      | key              | key_len | ref                       | rows   | Extra                        |
+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+
|  1 | PRIMARY            | contact_a                               | index           | NULL                                                                               | index_is_deleted | 1       | NULL                      | 149732 | Using index; Using temporary |
|  1 | PRIMARY            | civicrm_group_contact-605               | ref             | UI_contact_group                                                                   | UI_contact_group | 4       | step2_crm_db.contact_a.id |      1 | Distinct                     |
|  1 | PRIMARY            | civicrm_group_contact-608               | ref             | UI_contact_group                                                                   | UI_contact_group | 4       | step2_crm_db.contact_a.id |      1 | Distinct                     |
|  1 | PRIMARY            | civicrm_group_contact-612               | ref             | UI_contact_group                                                                   | UI_contact_group | 4       | step2_crm_db.contact_a.id |      1 | Using where; Distinct        |
|  7 | DEPENDENT SUBQUERY | civicrm_group_contact                   | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id                                 | UI_contact_group | 8       | func,const                |      1 | Using where                  |
|  6 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref          | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4       | func                      |      1 | Using where; Using temporary |
|  6 | DEPENDENT SUBQUERY | contact_a                               | eq_ref          | PRIMARY                                                                            | PRIMARY          | 4       | func                      |      1 | Using where; Using index     |
|  5 | DEPENDENT SUBQUERY | civicrm_group_contact                   | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id                                 | UI_contact_group | 8       | func,const                |      1 | Using where                  |
|  4 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref          | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4       | func                      |      1 | Using where; Using temporary |
|  4 | DEPENDENT SUBQUERY | contact_a                               | eq_ref          | PRIMARY                                                                            | PRIMARY          | 4       | func                      |      1 | Using where; Using index     |
|  3 | DEPENDENT SUBQUERY | civicrm_group_contact                   | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id                                 | UI_contact_group | 8       | func,const                |      1 | Using where                  |
|  2 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref          | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4       | func                      |      1 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | contact_a                               | eq_ref          | PRIMARY                                                                            | PRIMARY          | 4       | func                      |      1 | Using where; Using index     |
+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+


Some my.cnf settings:

#
# * Fine Tuning
#
key_buffer              = 256M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
max_connections        = 256
table_cache            = 2048
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    =8M
myisam_sort_buffer_size = 64M
thread_concurrency     = 16

tmp_table_size = 500M
max_heap_table_size = 500M



# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
innodb_buffer_pool_size = 1500M
#innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_flush_method=O_DIRECT

Thanks for your time lobo.  You are right in guessing its a user with restricted access.

I applied the patch but we are still getting the error.

I'm persuing the issue on the MySQL forums (http://forums.mysql.com/read.php?20,429609,429776#msg-429776).  Let me know though if you have any other thoughts on if there's a solution at the application layer or if you need additional info.   Merci encore.

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] =>
SELECT DISTINCT(contact_a.id) as id
        FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-605` ON contact_a.id = `civicrm_group_contact-605`.contact_id    LEFT JOIN civicrm_group_contact `civicrm_group_contact-608` ON contact_a.id = `civicrm_group_contact-608`.contact_id    LEFT JOIN civicrm_group_contact `civicrm_group_contact-612` ON contact_a.id = `civicrm_group_contact-612`.contact_id 
WHERE  (  ( ( ( `civicrm_group_contact-605`.group_id IN (605) AND `civicrm_group_contact-605`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a
LEFT JOIN civicrm_value_1_riding__circonscription ON civicrm_value_1_riding__circonscription.entity_id = contact_a.id  WHERE  ( civicrm_value_1_riding__circonscription.riding = 47001 )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 605 AND civicrm_group_contact.status = "Removed" ) ) ) ) )  OR  ( ( ( `civicrm_group_contact-608`.group_id IN (608) AND `civicrm_group_contact-608`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a
LEFT JOIN civicrm_value_1_riding__circonscription ON civicrm_value_1_riding__circonscription.entity_id = contact_a.id  WHERE  ( civicrm_value_1_riding__circonscription.riding = 47004 )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 608 AND civicrm_group_contact.status = "Removed" ) ) ) ) )  OR  ( ( ( `civicrm_group_contact-612`.group_id IN (612) AND `civicrm_group_contact-612`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a
LEFT JOIN civicrm_value_1_riding__circonscription ON civicrm_value_1_riding__circonscription.entity_id = contact_a.id  WHERE  ( civicrm_value_1_riding__circonscription.riding = 47008 )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 612 AND civicrm_group_contact.status = "Removed" ) ) ) ) )  )
 [nativecode=5 ** Out of memory (Needed 2000180 bytes)]

1. can you describe in words your ACL structure (which i suspect is a wee bit complex)

2. Might want to consider implementing your ACL scheme as a hook rather than via the UI. Is a lot more scalable that way

3.Is the query pasted below AFTER the patch was applied? Can you double check that the patch was applied

1. There are 3 Canadian electoral ridings for which this user has editorial access, represented by group 605, 608 and 612 (also the numbers 47001, 47008 and 47012).  Each riding has its seperate ACL.  Hence 3 left joins and 3 ORed sub-clauses.  If I'm reading the query correctly, for each riding, it is pulling the contacts that are in the riding, plus contacts that have been manually added (AND `civicrm_group_contact-605`.status IN ( "Added" ))  minus contacts that have been manually removed (contact_a.id NOT IN ... civicrm_group_contact.status = "Removed")

2. I will pass on this recommendation to the team and let you know the results.

3. I had actually patched the wrong copy of the file.  But when I patched the right file, I get no records when masquerading as the restricted user and searching contacts in group id 612 when I should get 193.   The ACL.php file is from CiviCRM version 3.3 . For what version of CiviCRM was the patch meant?

Thank you for your time,

Julien