Today I just wanted to make some of our groups accessible by CiviMail by checking the mailinglist group type checkbox. But this took long time (more than 10 minutes) no matter if I did this for a group with about 10 or about 2k contacts. I also tried via group api, same same.

The ui ajax call responds with error and the corresponding log entry states:

"DB Error: unknown error", -1, 16, (Array:2), "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES (...")
Deadlock found when trying to get lock; try restarting transaction

We have somewhere around 180k contacts and 2k groups. Civi is 4.6.10. CMS is current Drupal 7, DB is Mysql 5.5.46.

Any ideas on how to solve it or investigate on?


This sounds like corrupt tables or a stuck MySQL process.

If you have access to phpMyAdmin, you can select all tables at the bottom and then choose "repair tables".

If you do not have phpMyAdmin, but you have access to ssh then you can use mysqlrepair (or mysqlcheck -r), see more at http://linuxcommand.org/man_pages/mysqlcheck1.html

If you have neither you should ask your hoster to do a repair for all tables.

Besides that, restarting MySQL makes sense.

If you need further MySQL tweaking, please look at http://mysqltuner.com/ Nothing I have seen beats that script.

  • Thanks a load! Besides that, do you know if changing the group type to include mailinglist should always take so long? What's happening behind the scenes?
    – nielo
    Jan 19 '16 at 7:55
  • no, should be a breaze, as far as I know it's only one field updated.
    – Catorghans
    Jan 19 '16 at 8:38
  • Hmm, lasts minutes for small groups. Do you think it would be fine to update this field in the db for sepecified groups?
    – nielo
    Jan 19 '16 at 8:41
  • Did repair tables and restart have an effect? If not then I guess we should have a look at mysql config. 180K Contacts and 2K Groups get to the scale that default config might not be optimal.
    – Catorghans
    Jan 19 '16 at 8:46
  • Yeah, effect was: no errors anymore. But changing to mailinglist keeps working incredibly slow. I did some optimizations in mysql server config but I'd appreciate some tips.
    – nielo
    Jan 19 '16 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.