MySQL Crash on Archive

XN-Matt · March 22, 2021, 11:21am

We’ve recently upgraded to 4.2.1 from 4.1.0.

Since then, we’ve been seeing our MySQL Galera cluster die regularly and we’ve finally been able to pin point this to Matomo as the cause from the archive task.

/usr/bin/php /var/www/m/console core:archive --url=https://x.x.x.x/

If we run manually, it also causes this.

The last query that errored was:

Mar 22 11:05:04 db02 mariadbd[7332]: Query (0x7fd79c01c3b0): UPDATE matomo_archive_numeric_2020_12 SET value = 4 WHERE name LIKE 'done%'
Mar 22 11:05:04 db02 mariadbd[7332]:                    AND idsite IN (1, 2, 3, 4, 5, 6, 8, 10, 11, 13, 16, 22, 25, 31, 34, 46, 49, 70)
Mar 22 11:05:04 db02 mariadbd[7332]:                    AND ((period = 5 AND ((date1 <= '2020-12-01' AND '2020-12-01' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-01' AND '2020-12-31' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-02' AND '2020-12-02' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-03' AND '2020-12-03' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-04' AND '2020-12-04' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-05' AND '2020-12-05' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-06' AND '2020-12-06' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-07' AND '2020-12-07' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-07' AND '2020-12-13' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-08' AND '2020-12-08' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-09' AND '2020-12-09' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-10' AND '2020-12-10' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-11' AND '2020-12-11' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-12' AND '2020-12-12' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-13' AND '2020-12-13' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-14' AND '2020-12-14' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-14' AND '2020-12-20' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-15' AND '2020-12-15' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-16' AND '2020-12-16' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-17' AND '2020-12-17' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-18' AND '2020-12-18' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-19' AND '2020-12-19' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-20' AND '2020-12-20' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-21' AND '2020-12-21' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-21' AND '2020-12-27' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-22' AND '2020-12-22' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-23' AND '2020-12-23' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-24' AND '2020-12-24' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-25' AND '2020-12-25' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-26' AND '2020-12-26' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-27' AND '2020-12-27' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-28' AND '2020-12-28' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-28' AND '2021-01-03' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-29' AND '2020-12-29' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-30' AND '2020-12-30' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-31' AND '2020-12-31' <= date2))))
Mar 22 11:05:04 db02 mariadbd[7332]: Connection ID (thread ID): 1794

We’ve also ruled out a single node as the issue, when db02 was down. We ran again and then it took db01 down.

This has been running well for over a year now but to the hour when the upgrade was done, this has been happening. The only solution we’ve had is to stop the archive task but longer term, a workable solution is needed.

Lukas · March 22, 2021, 12:27pm

Hi,

If MySQL crashes on an SQL query then this has to be a bug in MySQL (or your setup).

For example there was recently a bug in one version of MariaDB that caused it to crash in some Matomo queries.

github.com/matomo-org/matomo

mysqld got signal 11 every hour

opened 04:06PM - 26 Feb 21 UTC

closed 09:01PM - 28 Feb 21 UTC

aboettger-tuhh

answered

``` 210226 15:49:51 [ERROR] mysqld got signal 11 ; This could be because you h…it a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=2 max_threads=153 thread_count=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467864 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: Thread pointer: 0x7f0104000c58 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f0144c81d58 thread_stack 0x49000 mysqld(my_print_stacktrace+0x32)[0x5556d6cc0af2] Printing to addr2line failed mysqld(handle_fatal_signal+0x485)[0x5556d67166f5] /lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f015ac793c0] mysqld(+0x5fecae)[0x5556d63a9cae] mysqld(+0xab2333)[0x5556d685d333] mysqld(+0xab20c3)[0x5556d685d0c3] mysqld(+0xab2333)[0x5556d685d333] mysqld(+0xab20c3)[0x5556d685d0c3] mysqld(+0xab2333)[0x5556d685d333] mysqld(+0xab2c7f)[0x5556d685dc7f] mysqld(+0xab34c5)[0x5556d685e4c5] mysqld(_ZN13Item_cond_and11get_mm_treeEP15RANGE_OPT_PARAMPP4Item+0x92)[0x5556d685e8e2] mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyybbbb+0xf56)[0x5556d6865266] mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_orderybPySA_+0xa6d)[0x5556d65c841d] mysqld(_Z21mysql_execute_commandP3THD+0x3d1f)[0x5556d650d42f] mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1ef)[0x5556d64faa9f] mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x134f)[0x5556d650665f] mysqld(_Z10do_commandP3THD+0x117)[0x5556d6508a37] mysqld(_Z24do_handle_one_connectionP7CONNECTb+0x411)[0x5556d660e341] mysqld(handle_one_connection+0x5d)[0x5556d660e7bd] mysqld(+0xbc1c66)[0x5556d696cc66] /lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f015ac6d609] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f015a85c293] Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f0104010480): UPDATE piwik_archive_numeric_2020_12 SET value = 4 WHERE name LIKE 'done%'#015 AND idsite IN (1, 2, 4, 5, 6, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 28)#015 AND ((period = 5 AND ((date1 <= '2020-12-01' AND '2020-12-01' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-01' AND '2020-12-31' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-02' AND '2020-12-02' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-03' AND '2020-12-03' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-04' AND '2020-12-04' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-05' AND '2020-12-05' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-06' AND '2020-12-06' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-07' AND '2020-12-07' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-07' AND '2020-12-13' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-08' AND '2020-12-08' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-09' AND '2020-12-09' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-10' AND '2020-12-10' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-11' AND '2020-12-11' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-12' AND '2020-12-12' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-13' AND '2020-12-13' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-14' AND '2020-12-14' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-14' AND '2020-12-20' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-15' AND '2020-12-15' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-16' AND '2020-12-16' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-17' AND '2020-12-17' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-18' AND '2020-12-18' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-19' AND '2020-12-19' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-20' AND '2020-12-20' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-21' AND '2020-12-21' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-21' AND '2020-12-27' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-22' AND '2020-12-22' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-23' AND '2020-12-23' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-24' AND '2020-12-24' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-25' AND '2020-12-25' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-26' AND '2020-12-26' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-27' AND '2020-12-27' <= date2))) OR (period =5 AND ((date1 <= '2020-12-28' AND '2020-12-28' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-28' AND '2021-01-03' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-29' AND '2020-12-29' <=date2))) OR (period = 5 AND ((date1 <= '2020-12-30' AND '2020-12-30' <= date2))) OR (period = 5 AND ((date1 <= '2020-12-31' AND '2020-12-31' <= date2)))) Connection ID (thread ID): 72 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off Feb 26 16:49:51 fizban docker/matomo_u03980ef286m_db_1[1965]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size unlimited unlimited bytes Max resident set unlimited unlimited bytes Max processes unlimited unlimited processes Max open files 1048576 1048576 files Max locked memory 67108864 67108864 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 63984 63984 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E ``` ## Your Environment * Matomo Version: 4.2.0 * PHP Version: 7.4.15 * Server Operating System: Docker Images * Additionally installed plugins: * Browser: * Operating System: mariadb:10, ubuntu:20.04

Maybe check if you are using the latest patch version of MySQL. I unfortunatly don’t know MySQL well enough to be able to help more.

XN-Matt · March 22, 2021, 12:48pm

Thank you.

Looks like we’ve had nothing running that kind of query to trigger it. I did have a search but couldn’t see anything.

Anyway, for now, I’ve applied that limit and it seems to be happier.