CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • CiviCRM log_civicrm_* tables corrupted intermittently?
Pages: [1]

Author Topic: CiviCRM log_civicrm_* tables corrupted intermittently?  (Read 856 times)

Chris Burgess

  • Ask me questions
  • ****
  • Posts: 675
  • Karma: 59
CiviCRM log_civicrm_* tables corrupted intermittently?
March 17, 2015, 05:40:22 pm
Over the space of several months I've observed some instances where CiviCRM logging tables became corrupted.

The symptom was that nightly MySQL backups would report inability to dump the DB (MySQL reports "out of memory", which is deceptive). Investigating revealed that the dumps would get part way through a log_civicrm_* table and die. A SELECT against the table would give the same symptoms (SELECT * FROM log_civicrm_foo LIMIT 1 worked, SELECT * FROM log_civicrm_foo ORDER BY ID DESC LIMIT 1 failed).

Code: [Select]
mysql> select * from log_civicrm_contact order by id desc limit 1 \G
ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

mysql> repair table log_civicrm_contact;
+------------------------------------------+--------+----------+----------+
| Table                                    | Op     | Msg_type | Msg_text |
+------------------------------------------+--------+----------+----------+
| exampleorg.log_civicrm_contact           | repair | error    | Corrupt  |
+------------------------------------------+--------+----------+----------+
1 row in set (1.03 sec)

Our MySQL configuration is not far from a stock Debian configuration (see local.conf below).

We've observed the corruption on various tables (log_civicrm_account_invoice, log_civicrm_contact, log_civicrm_uf_match and at least one other instance which I haven't got records of yet) so I don't think it's a particular behaviour within CiviCRM (except, "logging updates from other tables").

Workaround: Dropping and recreating the table resolves the issue, but I've seen it recur on the same site at least once.

Speculation: I've seen suggestions on the internet that ARCHIVE tables tend to corrupt more frequently than other table types (and by their archive nature, they'd be less visible when corrupted too). I do wonder if CiviCRM's usage of ARCHIVE is somehow triggering the corruption, but since CiviCRM is the only tool we use that uses ARCHIVE format, that's as likely to be a correlation as a cause. Of course, it could be our MySQL configuration that's doing something edgy (can you see it?).

Are other CiviCRM hosting providers observing similar behaviour?

Code: [Select]
[client]
default-character-set   = utf8

[mysql]
default-character-set   = utf8

[mysqld]
collation-server        = utf8_general_ci
character-set-server    = utf8
init-connect            = 'SET NAMES utf8'

max_allowed_packet      = 50M
tmp_table_size          = 396M
max_heap_table_size     = 128M
join_buffer_size        = 2M
query_cache_size        = 64M

innodb_buffer_pool_size = 500M
innodb_log_file_size    = 64M
@xurizaemon ā— www.fuzion.co.nz

Chris Burgess

  • Ask me questions
  • ****
  • Posts: 675
  • Karma: 59
Re: CiviCRM log_civicrm_* tables corrupted intermittently?
March 17, 2015, 07:06:27 pm
Discussed with a couple of people on IRC - the general recommendation seems to be "stay away from ARCHIVE table format". It offers limited space savings (I'll investigate this) and is not ACID. One person's response (not in a CiviCRM channel) replied "yep CiviCRM has that problem" so ... glad to know it's not just us anyway :D

From here I'll probably look at converting all log_civicrm_* tables on some sites to InnoDB (possibly with compression where available), to confirm if we see a change in behaviour.
@xurizaemon ā— www.fuzion.co.nz

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: CiviCRM log_civicrm_* tables corrupted intermittently?
March 21, 2015, 01:58:44 am
Note that we have frequently been 'saved' by the fact archive tables are non-transactional. CiviCRM does transaction rollback in various instances when something fails. Over the years we have hit several cases where a bug has caused transaction rollback resulting in lost financial data. Because archive tables can't be rolled back this data has left a footprint in the archive tables which has allowed us to do both forensics & data retrieval.

Also note that we have agreement in principal to fail rather than rollback contributions in some or all of the cases they are currently rolled back but no financial backing to implement this (which would be in the realm of 30-40 hours I expect).
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • CiviCRM log_civicrm_* tables corrupted intermittently?

This forum was archived on 2017-11-26.