CiviCRM Community Forums (archive)

We have a database of 1286 civicrm contacts, and each are organised into groups with tags and nested tags.  We have created Smart groups to pull contacts who match certain criteria.  From this we have created profiles for search listings to display pages with a google map of all the contacts from particular groups.

All goes well, and menu items are created using map links to the profile id, but then suddenly, some of the pages throw up a database error. 

The database error is attached.

I have been trailing forums etc to find the answer, and have had results for everything from mysql version, innoDB problems, large databases etc etc.

Can anyone please shed some light?

mySQL version 5.1.57, Civicrm 4.0.8, Joomla 1.7

Hi Donald,

Thanks for you speedy reply.  I've applied the patch to administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php

But still getting the same error - some pages are loading up fine, but others are producing the error - the url links are all the same, just with the profile id changed.

Any ideas?

Hi Donald,

Ran the query and get the following output:

=====================================
120123 18:11:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 921373, signal count 915241
Mutex spin waits 0, rounds 38463250, OS waits 45162
RW-shared spins 1567881, OS waits 568520; RW-excl spins 1310357, OS waits 303847
------------------------
LATEST FOREIGN KEY ERROR
------------------------
120123 13:51:47 Transaction:
TRANSACTION 0 6655688, ACTIVE 0 sec, process no 3219, OS thread id 1175124288 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 325913, query id 20189664 localhost admin update
REPLACE INTO civicrm_group_nesting (child_group_id, parent_group_id) VALUES (75,50)
Foreign key constraint fails for table `foodlinktest`.`civicrm_group_nesting`:
,
 CONSTRAINT `FK_civicrm_group_nesting_parent_group_id` FOREIGN KEY (`parent_group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `FK_civicrm_group_nesting_parent_group_id` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 00000032; asc    2;; 1: len 4; hex 00000021; asc    !;;

But in parent table `foodlinktest`.`civicrm_group`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 00000033; asc    3;; 1: len 6; hex 0000006587b0; asc    e  ;; 2: len 7; hex 000000193c13bc; asc     <  ;; 3: len 17; hex 466f6f646c696e6b5f4368657368697265; asc Foodlink_Cheshire;; 4: len 17; hex 466f6f646c696e6b204368657368697265; asc Foodlink Cheshire;; 5: len 30; hex 416c6c204f7267616e69736174696f6e7320616e6420427573696e657373; asc All Organisations and Business;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 81; asc  ;; 9: len 1; hex 01; asc  ;; 10: len 30; hex 202820606369766963726d5f67726f75705f636f6e746163742d3531602e; asc  ( `civicrm_group_contact-51`.;...(truncated); 11: len 30; hex 613a31323a7b733a31353a226369766963726d5f636f6e74616374223b69; asc a:12:{s:15:"civicrm_contact";i;...(truncated); 12: len 30; hex 613a323a7b733a31353a226369766963726d5f636f6e74616374223b693a; asc a:2:{s:15:"civicrm_contact";i:;...(truncated); 13: SQL NULL; 14: SQL NULL; 15: len 2; hex 3437; asc 47;; 16: SQL NULL; 17: len 1; hex 80; asc  ;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
120123 17:42:41
*** (1) TRANSACTION:
TRANSACTION 0 6980656, ACTIVE 0 sec, process no 3219, OS thread id 1179916608 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
MySQL thread id 332992, query id 21171369 localhost admin Updating
UPDATE civicrm_group g
SET    cache_date = null
WHERE  id IN ( '67' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4929 n bits 88 index `PRIMARY` of table `foodlinktest`.`civicrm_group` trx id 0 6980656 lock_mode X locks rec but not gap waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 00000043; asc    C;; 1: len 6; hex 0000006a841e; asc    j  ;; 2: len 7; hex 00000006f502bc; asc        ;; 3: len 30; hex 466f6f646c696e6b5f5f416e6f746865725f4e65775f47726f75705f4c61; asc Foodlink__Another_New_Group_La;...(truncated); 4: len 30; hex 466f6f646c696e6b2028416e6f74686572204e65772047726f757029204c; asc Foodlink (Another New Group) L;...(truncated); 5: len 30; hex 7468697320697320746865203372642073657420757020666f72204c616e; asc this is the 3rd set up for Lan;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 81; asc  ;; 9: len 1; hex 02; asc  ;; 10: len 30; hex 202820606369766963726d5f67726f75705f636f6e746163742d3637602e; asc  ( `civicrm_group_contact-67`.;...(truncated); 11: len 30; hex 613a31323a7b733a31353a226369766963726d5f636f6e74616374223b69; asc a:12:{s:15:"civicrm_contact";i;...(truncated); 12: len 30; hex 613a323a7b733a31353a226369766963726d5f636f6e74616374223b693a; asc a:2:{s:15:"civicrm_contact";i:;...(truncated); 13: len 3; hex 013101; asc  1 ;; 14: SQL NULL; 15: len 2; hex 3437; asc 47;; 16: len 2; hex 3735; asc 75;; 17: len 1; hex 80; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 0 6980655, ACTIVE 0 sec, process no 3219, OS thread id 1183111488 inserting, thread declared inside InnoDB 301
mysql tables in use 1, locked 1
55 lock struct(s), heap size 14320, 15222 row lock(s), undo log entries 200
MySQL thread id 332966, query id 21171365 localhost admin update
INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES (67,1320),(67,1322),(67,1334),(67,1340),(67,1341),(67,1344),(67,1345),(67,1347),(67,1350),(67,1353),(67,1355),(67,1356),(67,1357),(67,1360),(67,1373),(67,1379),(67,1383),(67,1384),(67,1385),(67,1389),(67,1391),(67,1394),(67,1395),(67,1398),(67,1401),(67,1403),(67,1404),(67,1407),(67,1410),(67,1412),(67,1803),(67,1804),(67,1805),(67,1806),(67,1807),(67,1808),(67,1809),(67,1811),(67,1812),(67,1813),(67,1814),(67,1815),(67,1816),(67,1817),(67,1818),(67,1820),(67,1821),(67,1822),(67,1823),(67,1824),(67,1825),(67,1826),(67,1827),(67,1828),(67,1829),(67,1830),(67,1831),(67,1832),(67,1833),(67,1834),(67,1835),(67,1836),(67,1837),(67,1838),(67,1839),(67,1840),(67,1841),(67,1842),(67,1843),(67,1844),(67,1845),(67,1846),(67,1847),(67,1848),(67,1849),(67,1850),(67,1851),(67,1852),(67,1853),(67,1854),(67,1855),(67,1856),(67,1857),(67,1858),(67,1860),(67,1861),(67,1862),(67,1863),(6
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4929 n bits 88 index `PRIMARY` of table `foodlinktest`.`civicrm_group` trx id 0 6980655 lock mode S locks rec but not gap
Record lock, heap no 18 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 00000043; asc    C;; 1: len 6; hex 0000006a841e; asc    j  ;; 2: len 7; hex 00000006f502bc; asc        ;; 3: len 30; hex 466f6f646c696e6b5f5f416e6f746865725f4e65775f47726f75705f4c61; asc Foodlink__Another_New_Group_La;...(truncated); 4: len 30; hex 466f6f646c696e6b2028416e6f74686572204e65772047726f757029204c; asc Foodlink (Another New Group) L;...(truncated); 5: len 30; hex 7468697320697320746865203372642073657420757020666f72204c616e; asc this is the 3rd set up for Lan;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 81; asc  ;; 9: len 1; hex 02; asc  ;; 10: len 30; hex 202820606369766963726d5f67726f75705f636f6e746163742d3637602e; asc  ( `civicrm_group_contact-67`.;...(truncated); 11: len 30; hex 613a31323a7b733a31353a226369766963726d5f636f6e74616374223b69; asc a:12:{s:15:"civicrm_contact";i;...(truncated); 12: len 30; hex 613a323a7b733a31353a226369766963726d5f636f6e74616374223b693a; asc a:2:{s:15:"civicrm_contact";i:;...(truncated); 13: len 3; hex 013101; asc  1 ;; 14: SQL NULL; 15: len 2; hex 3437; asc 47;; 16: len 2; hex 3735; asc 75;; 17: len 1; hex 80; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4088 n bits 864 index `UI_contact_group` of table `foodlinktest`.`civicrm_group_contact_cache` trx id 0 6980655 lock_mode X waiting
Record lock, heap no 80 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 000008f9; asc     ;; 1: len 4; hex 00000043; asc    C;; 2: len 4; hex 00590e02; asc  Y  ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 7014789
Purge done for trx's n:o < 0 7014783 undo n:o < 0 0
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3219, OS thread id 1175923008
MySQL thread id 333943, query id 21284509 localhost admin
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
103634 OS file reads, 425956 OS file writes, 335021 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.47 writes/s, 0.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5,
3692 inserts, 3692 merged recs, 406 merges
Hash table size 17393, node heap has 7 buffer(s)
0.00 hash searches/s, 0.53 non-hash searches/s
---
LOG
---
Log sequence number 1 1065269882
Log flushed up to   1 1065269882
Last checkpoint at  1 1065269627
0 pending log writes, 0 pending chkp writes
322062 log i/o's done, 0.20 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 24733090; in additional pool allocated 1047040
Dictionary memory allocated 3613624
Buffer pool size   512
Free buffers       0
Database pages     505
Modified db pages  3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 142837, created 36931, written 259180
0.00 reads/s, 0.00 creates/s, 0.20 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3219, id 1172461888, state: sleeping
Number of rows inserted 13808636, updated 309507, deleted 13759170, read 71699974
0.00 inserts/s, 0.13 updates/s, 0.00 deletes/s, 12.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Any ideas would be greatly appreciated - really need this sorting out asap.

Thanks Donald - I will have a look although writing patches is not my speciality.

I thought it was worth mentioning, that although the civicrm install is in development, it is installed as a multisite on 2 development domains - will this have an affect on the deadlock?

I would appreciate anybodies help on this - how would I implement a 'no lock' for the GroupContactCache.php to overcome this problem?

I am not sure if this might help but can you try increasing 'innodb_lock_wait_timeout' in my.cnf

I second that suggestion. See here http://forum.civicrm.org/index.php/topic,23089.msg97066.html#msg97066 for a success story using this method, albeit a slightly situation.