CiviCRM log_civicrm_* tables corrupted intermittently?

[Chris Burgess]

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

[Chris Burgess]

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

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.

[Eileen]

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).