[MDEV-35347] Replication Primary randomly crashes on "FLUSH QUERY CACHE" command - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 11.4.3, 11.8.2
Fix Version/s: N/A
Component/s: Query Cache
Labels:
- crash
Environment:
Kernel Version: 5.14.0
OS Distribution: AlmaLinux 9.6 (Sage Margay)
MariaDB Version: 11.8.2-MariaDB, client 15.2

Bug Category:
Not for Release Notes

Description

Our Replication Primary occasionally crashes on the `FLUSH QUERY CACHE` command. The crash occurs unpredictably: sometimes the system remains stable for several weeks, while other times it crashes up to twice in a day. No specific sequence of actions has been identified to consistently reproduce the crash.

Replication Configuration:

Primary (Master) Status:
File: `master1-bin.000052`
Position: `4206398`
Binlog_Do_DB: None
Binlog_Ignore_DB: None

Query Cache Configuration:
`query_cache_size = 536870912`
`query_cache_limit = 5242880`
`query_cache_type = ON`

Additional Notes:

The issue occurs sporadically under standard workload conditions, without any clear correlation to specific queries or system loads.
Attempts to manually reproduce the crash by running `FLUSH QUERY CACHE` independently have not been successful.

Log:

Nov 05 12:40:01 server.dom.tld mariadbd[764]: 241105 12:40:01 [ERROR] mysqld got signal 11 ;

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Sorry, we probably made a mistake, and this is a bug.

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Your assistance in bug reporting will enable us to fix this for the next release.

Nov 05 12:40:01 server.dom.tld mariadbd[764]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs

Nov 05 12:40:01 server.dom.tld mariadbd[764]: We will try our best to scrape up some info that will hopefully help

Nov 05 12:40:01 server.dom.tld mariadbd[764]: diagnose the problem, but since we have already crashed,

Nov 05 12:40:01 server.dom.tld mariadbd[764]: something is definitely wrong and this may fail.

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Server version: 11.4.3-MariaDB-log source revision: 5ab81ffe0097a22a774957df28c5223cf0201de3

Nov 05 12:40:01 server.dom.tld mariadbd[764]: key_buffer_size=134217728

Nov 05 12:40:01 server.dom.tld mariadbd[764]: read_buffer_size=3145728

Nov 05 12:40:01 server.dom.tld mariadbd[764]: max_used_connections=342

Nov 05 12:40:01 server.dom.tld mariadbd[764]: max_threads=602

Nov 05 12:40:01 server.dom.tld mariadbd[764]: thread_count=127

Nov 05 12:40:01 server.dom.tld mariadbd[764]: It is possible that mysqld could use up to

Nov 05 12:40:01 server.dom.tld mariadbd[764]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4462294 K  bytes of memory

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Hope that's ok; if not, decrease some variables in the equation.

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Thread pointer: 0x7f1a28027068

Nov 05 12:40:01 server.dom.tld mariadbd[764]: Attempting backtrace. You can use the following information to find out

Nov 05 12:40:01 server.dom.tld mariadbd[764]: where mysqld died. If you see no messages after this, something went

Nov 05 12:40:01 server.dom.tld mariadbd[764]: terribly wrong...

Nov 05 12:40:01 server.dom.tld mariadbd[764]: stack_bottom = 0x7f1c16393cb8 thread_stack 0x49000

Nov 05 12:40:02 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x55a92f60067e]

Nov 05 12:40:02 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(handle_fatal_signal+0x478)[0x55a92f0ca168]

Nov 05 12:40:02 server.dom.tld mariadbd[764]: /lib64/libc.so.6(+0x3e6f0)[0x7f22fa3a96f0]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_ZN11Query_cache12move_by_typeEPPhPP17Query_cache_blockPmS3_+0x4a0)[0x55a92eddb2f0]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_ZN11Query_cache10pack_cacheEv+0x72)[0x55a92eddb652]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_ZN11Query_cache4packEP3THDmj+0x5a)[0x55a92eddb8ca]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z20reload_acl_and_cacheP3THDyP10TABLE_LISTPi+0x590)[0x55a92ef97220]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x1d6d)[0x55a92ee43d7d]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x55a92ee47797]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0xee5)[0x55a92ee49985]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z10do_commandP3THDb+0x134)[0x55a92ee4be94]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3bf)[0x55a92ef7761f]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(handle_one_connection+0x5d)[0x55a92ef7796d]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /usr/sbin/mariadbd(+0xd2cbb2)[0x55a92f305bb2]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /lib64/libc.so.6(+0x89c02)[0x7f22fa3f4c02]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: /lib64/libc.so.6(clone+0x44)[0x7f22fa478ed4]

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Trying to get some variables.

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Some pointers may be invalid and cause the dump to abort.

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Query (0x7f1a2801ce30): FLUSH QUERY CACHE

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Connection ID (thread ID): 212940

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Status: NOT_KILLED

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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,hash_join_cardinality=on,cset_narrowing=off,sargable_casefold=on

Nov 05 12:40:03 server.dom.tld mariadbd[764]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains

Nov 05 12:40:03 server.dom.tld mariadbd[764]: information that should help you find out what is causing the crash.

Nov 05 12:40:03 server.dom.tld mariadbd[764]: The "--memlock" argument, which was enabled, uses system calls that are

Nov 05 12:40:03 server.dom.tld mariadbd[764]: unreliable and unstable on some operating systems and operating-system versions (notably, some versions of Linux).

Nov 05 12:40:03 server.dom.tld mariadbd[764]: This crash could be due to use of those buggy OS calls.

Nov 05 12:40:03 server.dom.tld mariadbd[764]: You should consider whether you really need the "--memlock" parameter and/or consult the OS distributer about "mlockall" bugs.

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Writing a core file...

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Working directory at /var/lib/mysql

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Resource Limits:

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Limit                     Soft Limit           Hard Limit           Units

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max cpu time              unlimited            unlimited            seconds

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max file size             unlimited            unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max data size             unlimited            unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max stack size            8388608              unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max core file size        0                    unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max resident set          unlimited            unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max processes             62987                62987                processes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max open files            1048576              1048576              files

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max locked memory         unlimited            unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max address space         unlimited            unlimited            bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max file locks            unlimited            unlimited            locks

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max pending signals       769534               769534               signals

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max msgqueue size         819200               819200               bytes

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max nice priority         0                    0

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max realtime priority     0                    0

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Max realtime timeout      unlimited            unlimited            us

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h

Nov 05 12:40:03 server.dom.tld mariadbd[764]: Kernel version: Linux version 5.14.0 (mockbuild@builder5.eng.sw.ru) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-44) (GCC) ) #1 SMP Tue Jan 9 19:45:01 MSK 2024

Nov 05 12:40:04 server.dom.tld systemd[1]: mariadb.service: Main process exited, code=killed, status=11/SEGV

Nov 05 12:40:04 server.dom.tld systemd[1]: mariadb.service: Failed with result 'signal'.

Nov 05 12:40:04 server.dom.tld systemd[1]: mariadb.service: Unit process 37284 (addr2line) remains running after unit stopped.

Nov 05 12:40:09 server.dom.tld systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 1.

Nov 05 12:40:09 server.dom.tld systemd[1]: Stopped MariaDB 11.4.3 database server.

Nov 05 12:40:09 server.dom.tld systemd[1]: Starting MariaDB 11.4.3 database server...

Nov 05 12:40:09 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:09 0 [Note] Starting MariaDB 11.4.3-MariaDB-log source revision 5ab81ffe0097a22a774957df28c5223cf0201de3 server_uid yjSv0f1izstDJHjquB11XX1hPD8= as process 37430

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] mariadbd: Aria engine: starting recovery

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: recovered pages: 0% 10% 28% 46% 62% 80% 90% 100% (0.0 seconds); tables to flush: 100 99 98 97 96 95 94 93 92 91 90 89 88 87 86 85 84 83 82 81 80 79 78 77 76 75 74 73 72 71 70 69 68 67 66 65 64 63 62 61 60 59 58 57 56 55 54 53 52 51 50 49 48 47 46 45 44 43 42 41 40 39 38 37 36 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 0 (0.5 seconds);

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] mariadbd: Aria engine: recovery done

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Using transactional memory

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Number of transaction pools: 1

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Initializing buffer pool, total size = 2.000GiB, chunk size = 32.000MiB

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Completed initialization of buffer pool

Nov 05 12:40:10 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:10 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1265770

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: End of log at LSN=1315839

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: To recover: 79 pages

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Last binlog file './master1-bin.000050', position 668072927

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Opened 3 undo tablespaces

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: log sequence number 1315839; transaction id 2218

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Plugin 'FEEDBACK' is disabled.

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Plugin 'wsrep-provider' is disabled.

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Recovering after a crash using master1-bin

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Starting table crash recovery...

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Crash table recovery finished.

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] InnoDB: Buffer pool(s) load completed at 241105 12:40:11

Nov 05 12:40:11 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:11 0 [Note] Binary log 'master1-bin.000048' is not purged because less than 'slave_connections_needed_for_purge' slaves have processed it

Nov 05 12:40:18 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:18 0 [Note] Server socket created on IP: '0.0.0.0'.

Nov 05 12:40:18 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:18 0 [Note] Server socket created on IP: '::'.

Nov 05 12:40:18 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:18 0 [Note] mariadbd: Event Scheduler: Loaded 0 events

Nov 05 12:40:18 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:18 0 [Note] /usr/sbin/mariadbd: ready for connections.

Nov 05 12:40:18 server.dom.tld mariadbd[37430]: Version: '11.4.3-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

Nov 05 12:40:18 server.dom.tld systemd[1]: Started MariaDB 11.4.3 database server.

Nov 05 12:40:24 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:24 86 [Note] Start binlog_dump to slave_server(2), pos(master1-bin.000051, 366231804), using_gtid(1), gtid('0-1-20994207')

Nov 05 12:40:24 server.dom.tld mariadbd[37430]: 2024-11-05 12:40:24 87 [Note] Start binlog_dump to slave_server(3), pos(master1-bin.000051, 366231804), using_gtid(1), gtid('0-1-20994207')

Attachments

Issue Links

duplicates

MDEV-25365 Server hangs or crashes in Query_cache::move_by_type upon FLUSH QUERY CACHE

Confirmed

Activity

People

Assignee:: Unassigned

Reporter:: Andreas Schnederle-Wagner

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-11-05 12:27

Updated:: 2025-07-23 11:52

Resolved:: 2025-07-23 11:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.