[MDEV-18116] galera crashes BF-BF X lock conflict Created: 2019-01-02  Updated: 2021-12-23  Resolved: 2021-12-23

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.2.19
Fix Version/s: 10.2.42

Type: Bug Priority: Major
Reporter: TAO ZHOU Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 2
Labels: None
Environment:

FreeBSD11.2



 Description   

I am running mariadb102-server 10.2.19 with 3 nodes(two nodes + 1 garb).
The applications only write to the first node.
The second node crashed the other day. Error logs as below.

/var/log/mysql/error.log:

2018-12-27  5:38:52 45225640960 [Note] InnoDB: BF-BF X lock conflict,mode: 2563 supremum: 0
2018-12-27  5:38:52 45225640960 [Note] InnoDB: conflicts states: my 0 locked 0
RECORD LOCKS space id 1314 page no 248356 n bits 712 index history_uint_1 of table `zabbix`.`history_uint` trx id 32041102 lock_mode X
Record lock, heap no 249 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a8257; asc    W;;
 2: len 6; hex 0000094a448f; asc    JD ;;
 
Record lock, heap no 250 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a84af; asc     ;;
 2: len 6; hex 0000094a7a2a; asc    Jz*;;
 
Record lock, heap no 251 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a8707; asc     ;;
 2: len 6; hex 0000094aaf97; asc    J  ;;
 
Record lock, heap no 252 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a895f; asc    _;;
 2: len 6; hex 0000094ae6ec; asc    J  ;;
 
Record lock, heap no 253 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a8bb7; asc     ;;
 2: len 6; hex 0000094b1c8f; asc    K  ;;
 
Record lock, heap no 254 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000009707; asc         ;;
 1: len 4; hex dc1a8e0f; asc     ;;
 2: len 6; hex 0000094b5222; asc    KR";;
 
2018-12-27  5:38:52 45225640960 [Note] InnoDB:  SQL1: insert into history_uint (itemid,clock,ns,value) values (222203,1545849053,256005214,1545849048),(180428,1545849057,356958854,92),(192608,1545849057,361353668,104),(204482,1545849057,371455240,93),(204474,1545849057,371455240,1071644672),(180442,1545849058,382718477,104),(204550,1545849058,394405283,1071644672),(204558,1545849058,394405283,111),(204627,1545849059,417278815,545394168),(204634,1545849059,417278815,93),(180470,1545849060,425401728,74),(204702,1545849060,436984799,1071644672),(204710,1545849060,436984799,92),(204786,1545849061,466408819,83),(204779,1545849061,466408819,112071616),(180535,1545849062,475919429,94),(115675,1545849067,490699607,80),(115678,1545849070,535035512,85),(115680,1545849072,549328863,93),(115771,1545849074,558246023,93),(115785,1545849075,565169230,83),(199642,1545849075,569870618,94),(199652,1545849076,581432158,94),(199662,1545849077,587150393,93),(180420,1545849117,294322615,355772016),(180421,1545849117,294322615,377356288),(180422,1545849117,294322615,34
181227  5:38:52 [ERROR] mysqld got signal 11 ;
This could be because you hit 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.
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
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.
 
Server version: 10.2.19-MariaDB-log
key_buffer_size=67108864
read_buffer_size=131072
max_used_connections=2
max_threads=2002
thread_count=55
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 33164362 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
Thread pointer: 0xa8d8396c8
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 = 0x7fffdb89eb88 thread_stack 0x49000
0xc6287e <my_print_stacktrace+0x2e> at /usr/local/libexec/mysqld
0x7926a4 <handle_fatal_signal+0x294> at /usr/local/libexec/mysqld
0x8032f4946 <pthread_sigmask+0x536> at /lib/libthr.so.3
0x8032f3eb2 <pthread_getspecific+0xe12> at /lib/libthr.so.3
 
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x8486ea3f7): insert into history_uint (itemid,clock,ns,value) values (222203,1545849053,256005214,1545849048),(180428,1545849057,356958854,92),(192608,1545849057,361353668,104),(204482,1545849057,371455240,93),(204474,1545849057,371455240,1071644672),(180442,1545849058,382718477,104),(204550,1545849058,394405283,1071644672),(204558,1545849058,394405283,111),(204627,1545849059,417278815,545394168),(204634,1545849059,417278815,93),(180470,1545849060,425401728,74),(204702,1545849060,436984799,1071644672),(204710,1545849060,436984799,92),(204786,1545849061,466408819,83),(204779,1545849061,466408819,112071616),(180535,1545849062,475919429,94),(115675,1545849067,490699607,80),(115678,1545849070,535035512,85),(115680,1545849072,549328863,93),(115771,1545849074,558246023,93),(115785,1545849075,565169230,83),(199642,1545849075,569870618,94),(199652,1545849076,581432158,94),(199662,1545849077,587150393,93),(180420,1545849117,294322615,355772016),(180421,1545849117,294322615,377356288),(180422,1545849117,294322615,340941472),(180423,1545849117,294322615,121994304),(192604,1545849117,297603306,302536240),(192602,1545849117,297603306,347332520),(192605,1545849117,297603306,145134656),(192603,1545849117,297603306,321765376),(204481,1545849117,307240282,100),(204477,1545849117,307240282,184255160),(204476,1545849117,307240282,193609728),(204472,1545849117,307240282,0),(204473,1545849117,307240282,0),(179896,1545849117,349180735,98),(179890,1545849117,349183982,4368),(179893,1545849117,349188593,3081),(179891,1545849117,349192169,53),(179894,1545849117,349196992,3046),(179889,1545849117,349202411,0),(196014,1545849117,484813231,200),(195728,1545849117,486438141,0),(196020,1545849117,556714812,200),(195740,1545849117,558329977,0),(180434,1545849118,315782234,373500568),(180436,1545849118,315782234,524278416),(180437,1545849118,315782234,159594688),(180435,1545849118,315782234,746242048),(204549,1545849118,325028606,0),(204552,1545849118,325028606,251494400),(204553,1545849118,325028606,240496592),(204548,1545849118,325028606,1),(204557,1545849118,325028606,118),(192632,1545849118,527046222,0),(196029,1545849118,604212300,200),(195758,1545849118,605721013,0),(196035,1545849118,631309118,200),(195770,1545849118,632890639,0),(188233,1545849119,182649605,2234410),(188235,1545849119,204868146,14492931850),(188236,1545849119,226922145,277849035),(188237,1545849119,248556826,2606556848),(188238,1545849119,269672457,0),(188239,1545849119,280374143,1),(179901,1545849119,280400124,132294),(188271,1545849119,280416047,233),(179902,1545849119,280424149,0),(179903,1545849119,280433367,23934),(179904,1545849119,280440631,4827252),(188272,1545849119,280453202,235),(179905,1545849119,280461024,0),(179906,1545849119,280470522,23934),(39328,1545849119,300425696,1),(204633,1545849119,338455312,97),(204628,1545849119,338455312,142901248),(204625,1545849119,338455312,0),(204629,1545849119,338455312,137764320),(204624,1545849119,338455312,0),(179864,1545849119,994200833,111541),(179867,1545849119,994217889,3023),(179865,1545849119,994223108,0),(179868,1545849119,994228941,2962),(179869,1545849119,994235821,0),(179873,1545849119,994238688,90629),(179871,1545849119,994241814,88913),(179874,1545849119,994246176,23616),(179872,1545849119,994249743,0),(179875,1545849119,994254684,23281),(179876,1545849119,994260614,0),(180464,1545849120,348037591,147672408),(180463,1545849120,348037591,151453696),(180465,1545849120,348037591,54171200),(180462,1545849120,348037591,248243968),(204704,1545849120,357734511,237871104),(204700,1545849120,357734511,0),(204701,1545849120,357734511,0),(204705,1545849120,357734511,226321816),(204709,1545849120,357734511,96),(188370,1545849121,333903037,771986191993),(204785,1545849121,377949312,86),(204777,1545849121,377949312,0),(204780,1545849121,377949312,109346816),(204781,1545849121,377949312,104799184),(204776,1545849121,377949312,0),(196038,1545849121,898499103,200),(195776,1545849121,900244359,0),(180528,1545849122,387760658,414318592),(180527,1545849122,387760658,312769656),(180530,1545849122,387760658,146655168),(180529,1545849122,387760658,345976640),(206347,1545849123,362640397,1),(43275,1545849123,363936344,1),(192611,1545849123,368885189,262201),(192631,1545849123,373424773,0),(222166,1545849123,411802305,2613),(155284,1545849123,413652571,0),(188373,1545849123,457036789,0),(188374,1545849123,471390966,0),(188218,1545849125,412491761,3),(180611,1545849125,606306841,1086),(180612,1545849125,606311472,0),(180613,1545849125,606321644,334633),(180614,1545849125,606324214,831),(180615,1545849125,606327173,0),(180616,1545849125,606330622,334633),(180769,1545849125,606758631,1),(206352,1545849125,606993177,1),(192621,1545849125,608800902,0),(192641,1545849125,610322484,0),(196041,1545849126,11350617,200),(195782,1545849126,13416948,0),(196044,1545849126,267738464,200),(195788,1545849126,269552777,0),(192620,1545849126,290884990,160465),(155694,1545849126,292918314,4844767),(155695,1545849126,292936807,0),(155696,1545849126,292947538,0),(196053,1545849126,302833793,200),(195806,1545849126,304313484,0),(196062,1545849126,330705393,200),(195824,1545849126,332470569,0),(196065,1545849126,358278409,200),(195830,1545849126,360014629,0),(196068,1545849126,417455034,200),(195836,1545849126,419132723,0),(196071,1545849126,889475573,200),(195842,1545849126,891233652,0),(196074,1545849126,897250473,404),(195848,1545849126,897478722,0),(196077,1545849126,950722382,200),(195854,1545849126,952748310,0),(196026,1545849127,195571042,401),(195752,1545849127,197264093,0),(99650,1545849127,410226991,203889776),(173132,1545849127,410226991,71044736),(99652,1545849127,410226991,168864936),(99651,1545849127,410226991,174063616),(206357,1545849128,318550218,1),(206350,1545849129,646897624,1),(196080,1545849129,993935477,200),(195860,1545849129,995987029,0),(152411,1545849130,94388316,345361),(188556,1545849130,285368007,0),(179830,1545849130,285397241,0),(188487,1545849130,285406237,3773),(188488,1545849130,285415640,5798),(179821,1545849130,285431040,9572),(179831,1545849130,285441275,37343),(179842,1545849130,285449504,0),(179844,1545849130,285455262,1472),(179833,1545849130,285461767,983),(179832,1545849130,285474123,2455),(179843,1545849130,285484134,37343),(206349,1545849130,286436515,1),(188563,1545849130,287290968,0),(37916,1545849130,290652846,0),(192613,1545849130,293798996,1006104),(196092,1545849130,355241101,200),(195884,1545849130,357355475,0),(99689,1545849130,421294293,204394944),(173134,1545849130,421294293,101468928),(99691,1545849130,421294293,228295992),(99690,1545849130,421294293,237568000),(173136,1545849132,473719974,147516928),(99715,1545849132,473719974,342415120),(99717,1545849132,473719974,327034072),(99716,1545849132,473719974,366084096),(195963,1545849133,633851709,200),(195626,1545849133,635591624,0),(206300,1545849133,664747570,401),(206296,1545849133,666130521,0),(206312,1545849133,695244980,200),(206308,1545849133,696613752,0),(206324,1545849133,740581678,401),(206320,1545849133,741994382,0),(186816,1545849134,101650716,5517),(173138,1545849134,498387418,123425280),(115765,1545849134,498387418,210105520),(115766,1545849134,498387418,280641536),(115767,1545849134,498387418,267621704),(155285,1545849134,570846253,0),(196098,1545849135,466220794,200),(195896,1545849135,468322848,0),(173139,1545849135,513803009,99927808),(115779,1545849135,513803009,259759136),(115781,1545849135,513803009,221858520),(115780,1545849135,513803009,232325120),(199639,1545849135,519291894,148657024),(199637,1545849135,519291894,426704896),(199638,1545849135,519291894,357168816),(199636,1545849135,519291894,279328568),(199646,1545849136,529247476,473825528),(199647,1545849136,529247476,330039296),(199649,1545849136,529247476,132879616),(199648,1545849136,529247476,295498632),(179866,1545849137,470699436,41687),(188331,1545849137,475286586,1),(206346,1545849137,475463190,1),(192615,1545849137,476988592,0),(199657,1545849137,544354781,231194624),(199658,1545849137,544354781,215153008),(199659,1545849137,544354781,94601664),(199656,1545849137,544354781,250860064),(152401,1545849138,157177845,100),(152400,1545849138,158614296,360779),(152410,1545849138,158632598,10878),(152423,1545849138,158640155,64038),(152413,1545849138,158647477,0),(152412,1545849138,158670854,64038),(152422,1545849138,158680794,10879),(196101,1545849138,548226344,200),(195902,1545849138,550221058,0),(196104,1545849138,591413991,200),(195908,1545849138,593293927,0),(215010,1545849138,653532246,200),(215006,1545849138,655135423,0),(215016,1545849138,690568036,200),(215012,1545849138,692181994,0),(192635,1545849140,510382099,0),(206342,1545849141,782538749,200),(206338,1545849141,784206098,0),(188398,1545849242,457732760,1466),(188405,1545849247,611379721,16),(37574,1545849247,613406750,2),(37577,1545849247,614709122,114),(37642,1545849247,614723928,4872826880),(99657,1545849247,817543129,0),(188621,1545849249,831014106,88),(99696,1545849250,831673377,0),(99722,1545849252,850676504,0),(188622,1545849252,938145896,1),(115773,1545849254,863180362,0),(115787,1545849255,871005011,0),(186599,1545849263,959624070,0),(188085,1545849267,69733949,0),(188095,1545849268,104046820,0),(188147,1545849269,132413006,0),(188183,1545849271,188730562,0),(41885,1545849272,527482898,3),(41888,1545849272,528802013,113),(38661,1545849272,592869832,2),(38664,1545849272,593055438,55),(186887,1545849273,241756314,0),(188282,1545849273,246290827,0),(188300,1545849274,269189240,0),(37891,1545849275,932543660,2),(37895,1545849275,933349053,57),(37959,1545849275,933362268,16222945280),(186927,1545849277,333603843,0),(186937,1545849278,354677606,0),(185095,1545849279,362557381,0),(186947,1545849279,367058628,0),(41947,1545849280,633174610,6781366272),(188850,1545849281,388682540,0),(188414,1545849284,56195926,191),(177974,1545849285,439331251,0),(177988,1545849286,445891124,0),(178016,1545849288,470723793,0),(178030,1545849289,502427977,0),(178044,1545849290,513626148,0),(38723,1545849292,524943166,13798789120),(179490,1545849293,546032464,0),(188415,1545849294,541506245,24568),(180415,1545849296,594598441,0),(180429,1545849297,606482337,0),(192609,1545849297,610680459,0),(37656,1545849298,7930915,2),(180443,1545849298,631490892,0),(180471,1545849300,674405036,0),(180536,1545849302,705619529,0),(155687,1545849302,926207681,1627242496),(188416,1545849304,107300877,1),(188446,1545849304,107907519,2),(188450,1545849304,108319406,51),(188480,1545849304,108330347,1375813632),(155654,1545849315,164420960,3),(199643,1545849315,846004561,0),(42455,1545849316,206623301,2),(42458,1545849316,214016044,176),(199653,1545849316,852825881,0),(199663,1545849317,861790491,0),(155657,1545849318,215475920,68),(37659,1545849323,413864763,71)
 
Connection ID (thread ID): 16
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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.



 Comments   
Comment by MikaH [ 2019-10-30 ]

The same happened with 10.3.18, running on Centos 7.7.1908. MaxScales in use and crashed node was acting as slave.

2019-10-29 16:55:57 16 [Note] InnoDB: BF-BF X lock conflict,mode: 2563 supremum: 0conflicts states: my 0 locked 0
RECORD LOCKS space id 2484 page no 19638 n bits 184 index PRIMARY of table `webdb`.`fourpane_memo` trx id 790878587 lock_mode X locks gap before rec
Record lock, heap no 110 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 4; hex 802c403f; asc ,@?;;
1: len 6; hex 00002f23d97b; asc /# {;;
2: len 7; hex 4a001fc00425a5; asc J % ;;
3: len 8; hex 99a47aadf909b2e4; asc z ;;
4: len 4; hex 774fffb7; asc wO ;;
5: SQL NULL;
6: SQL NULL;
7: len 4; hex 6d61696e; asc main;;
8: len 8; hex 99a47aadf909241b; asc z $ ;;
9: len 8; hex 99a47aadf9000000; asc z ;;
10: SQL NULL;
11: len 9; hex 726571756573746564; asc requested;;
12: len 0; hex ; asc ;;
13: len 30; hex 663464316230656163356266346337393937626130663035626233626337; asc f4d1b0eac5bf4c7997ba0f05bb3bc7; (total 32 bytes);
14: len 11; hex 696e697469616c697a6572; asc initializer;;
15: len 19; hex 4f72646572203939374d503738333935313538; asc Order 997MP78395158;;
16: SQL NULL;
17: len 5; hex 8000000000; asc ;;
18: len 4; hex 8000008e; asc ;;
19: SQL NULL;
20: len 4; hex 8022870c; asc " ;;
 
2019-10-29 16:55:57 16 [Note] InnoDB: SQL1: INSERT INTO `fourpane_memo` (`last_modified`, `content_type_id`, `object_id`, `related_manager`, `related_id`, `memo_type`, `created_at`, `scheduled_at`, `executed_at`, `status`, `message`, `request_id`, `past_request_ids`, `handler_name`, `actor`, `fee_code`, `fee_amount`, `parent_memo_id`) VALUES ('2019-10-29 10:55:57.597194', 142, 2000025686, 'subscriptions', 2000024826, 'services', '2019-10-29 10:55:57.597272', '2019-10-29 10:55:57', NULL, 'new', '', NULL, '', 'services', 'POS', NULL, '0.000', NULL)<BD>^Z<B8>]^S<ED>^_ SQL2:

And nothing after that on the log

Generated at Thu Feb 08 08:41:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.