elgg_system_log tables get corrupted frequently

I frequently(once a month) get this issue. in 3  of my site based on Elgg2.1.1.   I then restart my site by runnig mysql repair command.

    repair table elgg_system_log;

 

SQLSTATE[HY000]: General error: 145 Table './pikesface/elgg_system_log' is marked as crashed and should be repaired

 

 QUERY: INSERT DELAYED into elgg_system_log

                (object_id, object_class, object_type, object_subtype, event,

                performed_by_guid, owner_guid, access_id, enabled, time_created, ip_address)

            VALUES

                ('36','ElggUser','user', '', 'login:before',

                0, 0, 2, 'yes', '1472527583', '162.158.54.103') in /.../vendor/elgg/elgg/engine/classes/Elgg/Database.php:424

Stack trace:

#0 /...vendor/elgg/elgg/engine/classes/Elgg/Database.php(242): Elgg\Database->executeQuery" while reading response header from upstream,  

  • What is used as database server program on your server? Is it MySQL or MariaDB or something else? And what version of the database server program is used?

    Does the time of the tables crashing correlate with execution of maintenance cronjobs (garbagecollection and/or logrotate)? By default these cronjobs run once a month (you can check in the plugin settings of these plugins for the interval set).

    I'm asking these questions because I sometimes have also tables crashing on my local test server that I have not found the real reason for. I think in my case it's might be due to usage of MariaDB at least to some extend (as the ariadb engine of MariaDB can't handle delayed queries). Therefore, I've turned of delayed queries by adding

    max_delayed_threads = 0

    in my my.cnf. Though this seems not to have solved the issue completely. While I got no crashes with any Elgg 1.x installation since then it happens once in a while now that the private_settings table is crashing on my 2.x test installation (and this seems to happen during execution of the maintanace cronjobs).

  • i was also seeing this for a while on my installations (i use mariadb on fedora). i haven't seen it in elgg 2.1 i think.. i don't know what the cause is.

  • I've made a note of this in our ticket to switch to InnoDB, and suggested we handle the exception around writing to the log instead of bringing down the site.

  • there is a 'no logging' plugin that you can use to disable logging until you install a fixed version of elgg. https://elgg.org/plugins/1441338

  • I had issues with crashing tables due to different reasons. Some I could resolve, but one issue seems to remain and I'm not sure it's caused due to delayed queries or usage of MariaDB as it happens only on Elgg 2.x installations.

    Problems so far:

    1. log table crashing on login (occured both on Elgg 1.x and 2.x). Resolved (https://github.com/Elgg/Elgg/issues/8741). Problem here was that after an upgrade of MariaDB the database server config had changed and the archive engine wasn't loaded. After changing the config and the archive engine getting loaded again the log table stopped crashing.
    2. log table crashing on logrotate cronjob run (or garbagecollection cronjob run) (occured both on Elgg 1.x and 2.x). Resolved by workaround (https://github.com/Elgg/Elgg/issues/8744). Problem seems to have been caused by delayed queries because the ariadb engine of MariaDB can't handle these. Workaround is to add the line "max_delayed_threads = 0" to my.cnf to turn of delayed queries.
    3. private_settings table crashing (on Elgg 2.x only!). Unresolved (https://github.com/Elgg/Elgg/issues/9057)! This seems very, very likely to happens on the garbagecollection cronjob run. Unfortunately, I haven't been able to reproduce as it never happens when triggering the corresponding cron interval manually. As delayed queries are turned off it can't happen due to such queries. One other reason might be that the site is set as a walled garden site. But I really have no good ideas here.

    I'm not sure if the No Logging plugin is of much use here to prevent the log table crashing. It would be necessary to test if the table can also crash with no entries.

    @Steve: is the exception occuring due to the table having crashed or makes the exception the table crashing?

  • i have never had my production site crash in this way and i have been running the no logging plugin on it, so that's why i made the suggestion.

  • Sorry guys , for replying late.( now a days I am not working on Elgg)

    On my server , I am not runnig any cronjob. and I am using MariaDB, on centos7. 

    And Yes I am using PHP7(You may not recommend it though)

  • If you are using MariaDB, add the line

    max_delayed_threads = 0

    to the [mysqld] section in my.cnf configuration file of the database server (on my server /etc.my.cnf).

    You can also check if the archive engine of MariaDB gets even loaded (after an upgrade of MariaDB this engine wasn't loaded anymore on my server). I had to uncomment the line

    plugin-load-add=archive=ha_archive.so

    in the [server] section in /etc/my.cnf.d/default_plugins.cnf.

    The archive engine is used by the system_log table. So, it's needed in any case. The problem is that the archive engine of MariaDB can't handle delayed queries and therefore setting max_delayed_threads to 0 is also necessary (at least for now - I think Elgg 2.3 won't use delayed queries for writing to the system_log table anymore).

    I don't think that the problem I had with the private_settings table will happen for anyone else. I could fix it in the meantime. It was caused by a zero-size *.TMM file for the private_settings table. I don't know when and how this file got created. It caused the table to crash whenever OPTIMIZE TABLE was executed on the private_settings table by garbagecollector. Deleting the TMM file solved this issue.