[MDEV-25234] MariaDB Crashes when a query runs Created: 2021-03-23  Updated: 2021-11-19

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5.9
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Cosmin Boicu Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

mariadb Ver 15.1 Distrib 10.5.9-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Operating System: Ubuntu 20.04.2 LTS
Intel(R) Xeon(R) CPU E3-1275 v5 @ 3.60GHz
407G Storage - 299G Used (78%)
62 GB RAM - 2.1GB Used - 57GB Free

Attachments: File 50-server.cnf     PNG File SHOW INDEX FROM forum_forums_forums.png     PNG File SHOW INDEX FROM forum_forums_posts.png     PNG File SHOW INDEX FROM forum_forums_topics.png     Text File error.log     Text File error_using_backup_from_10_03_2021.log     Text File gdb.txt     Text File gdb_output.log    

 Description   

An IPB Forum is running on this database.
Some users reported that the forum is showing errors like:

  • MySQL server has gone away
  • Connection refused
    Weird for a forum and database that are on the same host (double-checked the firewall but nothing)
    Checked on MariaDB server, but the log_error was disabled by default.
    After enabling it found the following problem:
    For some queries the database seems to crash (had no problem until last few days)
    Most likely due to updating (had a 50-server.cnf.dpkg-old in /etc/mysql/mariadb.conf.d)

The Query crashes the server in terminal as well.

ERROR 2013 (HY000): Lost connection to MySQL server during query

The Query:

SELECT forums_posts.*, forums_topics.* FROM `forum_forums_posts` AS `forums_posts`  STRAIGHT_JOIN `forum_forums_topics` AS `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND ( forums_topics.approved=1 OR ( forums_topics.approved=0 AND forums_topics.starter_id=6158 ) ) 
AND forums_topics.approved!=-2 AND forums_topics.approved !=-3  LEFT JOIN `forum_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id AND forums_forums.password IS NULL AND forums_forums.can_view_others=1 AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(6,forums_forums.password_override) ) ) AND forums_forums.min_posts_view<='?' WHERE ( forums_forums.can_view_others='1' OR forums_topics.starter_id='?' )
 
 AND queued='?' AND forums_posts.queued!='-2' AND forums_posts.queued !='-3' AND 
 forums_topics.forum_id IN(1,14,15,17,21,5281,5279,5280,27,32,36,38,39,40,41,42,43,46,47,48,49,50,361,57,60,6126,6128,6130,67,6148,6151,82,90,92,5943,5944,97,99,101,151,176,2150,149,35,1667,150,4447,178,6164,128,129,134,135,162,4561,4562,6199,6132,6133,6359,4375,5983,1060,364,372,6240,6241,6145,362,365,366,367,369,370,373,6249,374,6352,375,6253,6254,376,5980,378,6251,6252,379,381,5982,6165,6166,6167,6176,6170,6171,6172,6173,6174,5893,6134,6135,6136,6137,6150,5284,497,5183,6142,6144,551,569,2325,605,6161,6162,654,9,4711,4432,1857,6179,6326,6196,6197,4713,1217,6178,2249,4712,1116,1117,1118,1119,1120,1121,1105,100,80,1239,1240,1241,1123,1124,1127,5277,5278,5108,5087,4082,1218,5964,1243,1244,1245,1247,1858,6255,2250,1248,1249,2172,1920,6152,1861,1317,4708,6263,1346,4710,4480,5987,4481,4515,6261,4709,1447,1448,1449,1917,2029,1475,1929,4704,4705,4706,1556,1928,1591,1585,1586,1855,5106,6131,5153,1859,5156,5157,5158,4748,4749,4750,4083,2032,2033,6347,2039,6284,5285,6243,5988,4479,2324,6101,2173,4486,5090,5089,2027,2201,2202,2203,2204,2205,2206,2207,1338,2236,2235,6245,2234,6244,5249,2238,2239,6246,5058,2251,2252,2253,6207,2255,2256,2257,2258,2260,5149,5102,5749,5100,5060,6163,2440,2441,5103,5104,5062,5000,2450,2537,2561,2829,5061,3029,3030,3031,3032,6262,3081,3157,6280,4999,3471,3472,3474,3475,3476,4377,4379,5984,6344,4909,4939,4938,4898,4899,4900,4901,4934,4935,4564,6259,2437,4880,4879,6201,5148,5146,3959,3960,3961,3962,6177,6157,6158,6160,6200,4220,4221,4222,4224,4560,4936,4892,4893,5118,5111,4394,4477,5985,4622,10,6206,12,554,6205,4992,4993,4251,4995,5059,4985,6124,4476,4922,4277,4278,4281,4620,4306,4618,4619,4309,4308,4307,4303,4304,4305,5184,4478,5986,132,1348,5247,5882,5145,5142,4279,4330,4331,4332,4333,4334,4335,5196,5057,5578,5577,4403,4402,4401,4400,4399,4398,4397,4396,4374,6123,4395,4373,1122,78,5120,5121,5091,5947,4764,5465,4675,4676,5283,5185,5187,5188,4707,5945,102,5113,5286,4996,6351,5182,5899,5147,4823,23,6266,5466,4910,5181,5143,5179,5154,6153,5250,5150,4862,6360,4882,4883,4884,4885,4886,4887,4888,4940,4941,4942,4943,4944,1343,5088,1345,5180,4891,4994,5946,6260,5105,5063,4812,5287,5302,5303,5304,5305,5306,5307,5308,6250,6230,6231,6232,6233,6234,6235,6236,5503,5502,6265,5979,6242,5390,5391,5392,5595,5594,5592,5591,5596,5597,5598,5599,6257,5883,5659,5660,5661,5662,5663,6256,5891,5695,5696,6346,6345,6339,6340,6341,6342,5720,5721,5722,5723,5724,5725,6279,6277,5812,5813,5814,5816,6343,6285,6286,6283,6258,6281,6282,6353,6354,6218,6229,6389,6357,6356,6355,6337,6084,6085,6086,6087,6088,6089,6090,6041,6042,6040,6037,6038,6209,6210,6212,6213,6214,6215,6216,6054,6055,6056,6057,6058,6059,6060,6061,6062,6064,6065,6066,6067,6068,6070,6071,6072,6073,6074,6093,6092,6082,6081,6080,6094,6095,6096,6097,6098,6102,6103,6104,6119,6120,6121,6325,6111,6112,6113,6114,6115,6116,6117,6298,6372,6300,6301,6302,6303,6304,6373,6387,6388,6349,6348,6330,6328,6329,6327,6365,6366,6367,6386,6385,6374,6375,6376,6377,6378,6390,6380,6381,6382,6383,6391,6392,6393,6394,6395,6397,6398,6399,6400,6401,6402,6403,6404,6405,6406,6407,6408) ORDER BY post_date DESC LIMIT 10


2021-03-24  0:25:32 0 [Note] InnoDB: Uses event mutexes
 
2021-03-24  0:25:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2021-03-24  0:25:32 0 [Note] InnoDB: Number of pools: 1
 
2021-03-24  0:25:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2021-03-24  0:25:32 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
 
2021-03-24  0:25:32 0 [Note] InnoDB: Using Linux native AIO
 
2021-03-24  0:25:32 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
 
2021-03-24  0:25:32 0 [Note] InnoDB: Completed initialization of buffer pool
 
2021-03-24  0:25:32 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2021-03-24  0:25:32 0 [Note] InnoDB: 128 rollback segments are active.
 
2021-03-24  0:25:32 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2021-03-24  0:25:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2021-03-24  0:25:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2021-03-24  0:25:32 0 [Note] InnoDB: 10.5.9 started; log sequence number 75840139118; transaction id 14134545
 
2021-03-24  0:25:32 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
 
2021-03-24  0:25:32 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2021-03-24  0:25:32 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2021-03-24  0:25:32 0 [Note] Reading of all Master_info entries succeeded
 
2021-03-24  0:25:32 0 [Note] Added new Master_info '' to hash table
 
2021-03-24  0:25:32 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
Version: '10.5.9-MariaDB-1:10.5.9+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
 
2021-03-24  0:25:32 0 [Note] InnoDB: Buffer pool(s) load completed at 210324  0:25:32
 
210324  0:32:12 [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.5.9-MariaDB-1:10.5.9+maria~focal
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=5
 
max_threads=153
 
thread_count=5
 
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.
 
 
 
Thread pointer: 0x7fa0e0000c58
 
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 = 0x7fa128667d58 thread_stack 0x49000
 
??:0(my_print_stacktrace)[0x562c029caaf2]
 
??:0(handle_fatal_signal)[0x562c024206f5]
 
??:0(__restore_rt)[0x7fa12c3733c0]
 
??:0(Type_handler_newdecimal::calc_pack_length(unsigned int) const)[0x562c020b3cae]
 
??:0(SEL_ARG::tree_delete(SEL_ARG*))[0x562c02567333]
 
??:0(SEL_ARG::tree_delete(SEL_ARG*))[0x562c02567c7f]
 
??:0(SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM*, SEL_TREE*, SEL_IMERGE*))[0x562c02568141]
 
??:0(SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM*, SEL_TREE*, SEL_IMERGE*))[0x562c025682a4]
 
??:0(SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM*, SEL_TREE*, SEL_IMERGE*))[0x562c0256849d]
 
??:0(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x562c025688e2]
 
??:0(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x562c0256f266]
 
??:0(JOIN::optimize_inner())[0x562c0227a043]
 
??:0(JOIN::optimize())[0x562c0227ab73]
 
??:0(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x562c0227ac37]
 
??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x562c0227b657]
 
??:0(LEX::mark_first_table_as_inserting())[0x562c02209f11]
 
??:0(mysql_execute_command(THD*))[0x562c022181a7]
 
??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x562c02204a9f]
 
??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x562c0221065f]
 
??:0(do_command(THD*))[0x562c02212a37]
 
??:0(do_handle_one_connection(CONNECT*, bool))[0x562c02318341]
 
??:0(handle_one_connection)[0x562c023187bd]
 
??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x562c02676c66]
 
??:0(start_thread)[0x7fa12c367609]
 
??:0(clone)[0x7fa12bf56293]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fa0e0010670): SELECT forums_posts.*, forums_topics.* FROM `forum_forums_posts` AS `forums_posts`  STRAIGHT_JOIN `forum_forums_topics` AS `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND ( forums_topics.approved=1 OR ( forums_topics.approved=0 AND forums_topics.starter_id=6158 ) ) 
AND forums_topics.approved!=-2 AND forums_topics.approved !=-3  LEFT JOIN `forum_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id AND forums_forums.password IS NULL AND forums_forums.can_view_others=1 AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(6,forums_forums.password_override) ) ) AND forums_forums.min_posts_view<='?' WHERE ( forums_forums.can_view_others='1' OR forums_topics.starter_id='?' )
 
 AND queued='?' AND forums_posts.queued!='-2' AND forums_posts.queued !='-3' AND 
 forums_topics.forum_id IN(1,14,15,17,21,5281,5279,5280,27,32,36,38,39,40,41,42,43,46,47,48,49,50,361,57,60,6126,6128,6130,67,6148,6151,82,90,92,5943,5944,97,99,101,151,176,2150,149,35,1667,150,4447,178,6164,128,129,134,135,162,4561,4562,6199,6132,6133,6359,4375,5983,1060,364,372,6240,6241,6145,362,365,366,367,369,370,373,6249,374,6352,375,6253,6254,376,5980,378,6251,6252,379,381,5982,6165,6166,6167,6176,6170,6171,6172,6173,6174,5893,6134,6135,6136,6137,6150,5284,497,5183,6142,6144,551,569,2325,605,6161,6162,654,9,4711,4432,1857,6179,6326,6196,6197,4713,1217,6178,2249,4712,1116,1117,1118,1119,1120,1121,1105,100,80,1239,1240,1241,1123,1124,1127,5277,5278,5108,5087,4082,1218,5964,1243,1244,1245,1247,1858,6255,2250,1248,1249,2172,1920,6152,1861,1317,4708,6263,1346,4710,4480,5987,4481,4515,6261,4709,1447,1448,1449,1917,2029,1475,1929,4704,4705,4706,1556,1928,1591,1585,1586,1855,5106,6131,5153,1859,5156,5157,5158,4748,4749,4750,4083,2032,2033,6347,2039,6284,5285,6243,5988,4479,2324,6101,2173,4486,5090,5089,2027,2201,2202,2203,2204,2205,2206,2207,1338,2236,2235,6245,2234,6244,5249,2238,2239,6246,5058,2251,2252,2253,6207,2255,2256,2257,2258,2260,5149,5102,5749,5100,5060,6163,2440,2441,5103,5104,5062,5000,2450,2537,2561,2829,5061,3029,3030,3031,3032,6262,3081,3157,6280,4999,3471,3472,3474,3475,3476,4377,4379,5984,6344,4909,4939,4938,4898,4899,4900,4901,4934,4935,4564,6259,2437,4880,4879,6201,5148,5146,3959,3960,3961,3962,6177,6157,6158,6160,6200,4220,4221,4222,4224,4560,4936,4892,4893,5118,5111,4394,4477,5985,4622,10,6206,12,554,6205,4992,4993,4251,4995,5059,4985,6124,4476,4922,4277,4278,4281,4620,4306,4618,4619,4309,4308,4307,4303,4304,4305,5184,4478,5986,132,1348,5247,5882,5145,5142,4279,4330,4331,4332,4333,4334,4335,5196,5057,5578,5577,4403,4402,4401,4400,4399,4398,4397,4396,4374,6123,4395,4373,1122,78,5120,5121,5091,5947,4764,5465,4675,4676,5283,5185,5187,5188,4707,5945,102,5113,5286,4996,6351,5182,5899,5147,4823,23,6266,5466,4910,5181,5143,5179,5154,6153,5250,5150,4862,6360,4882,4883,4884,4885,4886,4887,4888,4940,4941,4942,4943,4944,1343,5088,1345,5180,4891,4994,5946,6260,5105,5063,4812,5287,5302,5303,5304,5305,5306,5307,5308,6250,6230,6231,6232,6233,6234,6235,6236,5503,5502,6265,5979,6242,5390,5391,5392,5595,5594,5592,5591,5596,5597,5598,5599,6257,5883,5659,5660,5661,5662,5663,6256,5891,5695,5696,6346,6345,6339,6340,6341,6342,5720,5721,5722,5723,5724,5725,6279,6277,5812,5813,5814,5816,6343,6285,6286,6283,6258,6281,6282,6353,6354,6218,6229,6389,6357,6356,6355,6337,6084,6085,6086,6087,6088,6089,6090,6041,6042,6040,6037,6038,6209,6210,6212,6213,6214,6215,6216,6054,6055,6056,6057,6058,6059,6060,6061,6062,6064,6065,6066,6067,6068,6070,6071,6072,6073,6074,6093,6092,6082,6081,6080,6094,6095,6096,6097,6098,6102,6103,6104,6119,6120,6121,6325,6111,6112,6113,6114,6115,6116,6117,6298,6372,6300,6301,6302,6303,6304,6373,6387,6388,6349,6348,6330,6328,6329,6327,6365,6366,6367,6386,6385,6374,6375,6376,6377,6378,6390,6380,6381,6382,6383,6391,6392,6393,6394,6395,6397,6398,6399,6400,6401,6402,6403,6404,6405,6406,6407,6408) ORDER BY post_date DESC LIMIT 10
 
 
 
Connection ID (thread ID): 1665
 
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
 
 
 
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        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             256160               256160               processes 
Max open files            16384                16384                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       256160               256160               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: core

Attached 50-server.cnf, error.log

It may be related to database corruption.

 Comments   
Comment by Daniel Black [ 2021-03-23 ]

Probable duplicate MDEV-24953 which hasn't yet been released.

See workaround of setting the system variable optimizer_max_sel_arg_weight to a value less than 16000.

Comment by Cosmin Boicu [ 2021-03-24 ]

I've tried that but with no luck.
Also tried a backup from 10th of March 2021 and that one works just fine, so I assume it might be some database corruption (as the daily backups stopped in 11th).

Comment by Cosmin Boicu [ 2021-03-24 ]

Apparently, using the backup makes MariaDB Crash similarly.
I've attached the error error_using_backup_from_10_03_2021.log which is generated by the database backup from the 10th.
The server.cfg also contains optimizer_max_sel_arg_weight = 12000

Not sure why it keeps crashing.

Comment by Daniel Black [ 2021-03-25 ]

> Not sure why it keeps crashing.

Me neither yet. But its definitely a server fault.

Was the 10th -> 11th March when you installed MariaBD-10.5.9? What previous version where you running? Can I assume the same Forum was running on the previous MariaDB version?

Are you able to include EXPLAIN FORMAT=JSON SELECT ......?

Can you include {{

{SHOW CREATE TABLE tblname}

}} and {SHOW INDEX FROM tblname for the tables in your query.

If you can simplified the query down to the minimal query that cases a crash that minor changes from it that don't cause the crash that would be useful i you have time. Note the crash has something to do with the query execution, and a decimal type. So changes in query plan will affect this.

If you are able to gdb attach to a test instance and grab the backtrace per https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/#analyzing-a-core-file-with-gdb-on-linux that would be helpful.

Thanks.

Comment by Cosmin Boicu [ 2021-03-25 ]

So I was confused, because I ran some update command but apparently it did not update the MariaDB that day, my bad.

The same Forum was running on MariaDB yes.
The update took place on 25th February and went from 1:10.5.7+maria~focal to 1:10.5.9+maria~focal
But no issues were reported until the 20th of March.

I am not sure I can simplify the query, because it looks like some widget is running it and I don't want to change some things in the php file/files, even if it may fix it, too much of a risk for me.

Using the EXPLAIN FORMAT=JSON SELECT .... (the query)
returns ERROR 2013 (HY000): Lost connection to MySQL server during query

This is SHOW CREATE TABLE for forum_forums_posts

forum_forums_posts

| forum_forums_posts | CREATE TABLE `forum_forums_posts` (
 
  `pid` int(10) NOT NULL AUTO_INCREMENT,
 
  `append_edit` tinyint(1) DEFAULT 0,
 
  `edit_time` int(10) DEFAULT NULL,
 
  `author_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `author_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `ip_address` varchar(46) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `post_date` int(10) DEFAULT NULL,
 
  `post` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `queued` tinyint(1) NOT NULL DEFAULT 0,
 
  `topic_id` int(10) NOT NULL DEFAULT 0,
 
  `new_topic` tinyint(1) DEFAULT 0,
 
  `edit_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `post_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
 
  `post_htmlstate` smallint(1) NOT NULL DEFAULT 0,
 
  `post_edit_reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `post_bwoptions` int(10) unsigned NOT NULL DEFAULT 0,
 
  `pdelete_time` int(11) NOT NULL DEFAULT 0,
 
  `post_field_int` int(10) DEFAULT 0,
 
  `post_field_t1` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `post_field_t2` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  PRIMARY KEY (`pid`),
 
  KEY `topic_id` (`topic_id`,`queued`,`post_date`,`author_id`),
 
  KEY `author_id` (`author_id`,`post_date`,`queued`),
 
  KEY `post_date` (`post_date`),
 
  KEY `ip_address` (`ip_address`),
 
  KEY `post_key` (`post_key`),
 
  KEY `queued` (`queued`,`post_date`),
 
  KEY `first_post` (`topic_id`,`post_date`)
 
) ENGINE=MyISAM AUTO_INCREMENT=219572 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

And this one for forum_forums_topics

forum_forums_topics

 
 
| forum_forums_topics | CREATE TABLE `forum_forums_topics` (
 
  `tid` int(10) NOT NULL AUTO_INCREMENT,
 
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `state` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `posts` int(10) DEFAULT NULL,
 
  `starter_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `start_date` int(10) DEFAULT NULL,
 
  `last_poster_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `last_post` int(10) NOT NULL DEFAULT 0,
 
  `starter_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `last_poster_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `poll_state` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `last_vote` int(10) DEFAULT NULL,
 
  `views` int(10) unsigned DEFAULT 0,
 
  `forum_id` smallint(5) NOT NULL DEFAULT 0,
 
  `approved` tinyint(1) NOT NULL DEFAULT 0,
 
  `author_mode` tinyint(1) DEFAULT NULL,
 
  `pinned` tinyint(1) DEFAULT NULL,
 
  `moved_to` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `topic_firstpost` int(10) NOT NULL DEFAULT 0,
 
  `topic_queuedposts` int(10) NOT NULL DEFAULT 0,
 
  `topic_open_time` int(10) NOT NULL DEFAULT 0,
 
  `topic_close_time` int(10) NOT NULL DEFAULT 0,
 
  `topic_rating_total` smallint(5) unsigned NOT NULL DEFAULT 0,
 
  `topic_rating_hits` smallint(5) unsigned NOT NULL DEFAULT 0,
 
  `title_seo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `moved_on` int(11) NOT NULL DEFAULT 0,
 
  `topic_archive_status` int(1) NOT NULL DEFAULT 0,
 
  `last_real_post` int(10) NOT NULL DEFAULT 0,
 
  `topic_answered_pid` int(10) NOT NULL DEFAULT 0,
 
  `popular_time` int(10) DEFAULT NULL COMMENT 'Timestamp of when this topic will stop being popular.',
 
  `featured` tinyint(1) unsigned DEFAULT 0 COMMENT 'Topic is featured?',
 
  `question_rating` int(10) DEFAULT NULL COMMENT 'If this topic is a question, it''s current rating',
 
  `topic_hiddenposts` int(10) unsigned DEFAULT NULL,
 
  `topic_meta_data` bit(1) NOT NULL DEFAULT b'0',
 
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT '',
 
  PRIMARY KEY (`tid`),
 
  KEY `topic_firstpost` (`topic_firstpost`),
 
  KEY `last_post` (`forum_id`,`pinned`,`last_real_post`,`state`),
 
  KEY `forum_id` (`forum_id`,`pinned`,`approved`),
 
  KEY `starter_id` (`starter_id`,`forum_id`,`approved`,`start_date`),
 
  KEY `start_date` (`start_date`),
 
  KEY `last_x_topics` (`forum_id`,`approved`,`start_date`),
 
  KEY `approved` (`approved`),
 
  KEY `moved_redirects` (`moved_on`,`moved_to`,`pinned`),
 
  KEY `topic_archive_status` (`topic_archive_status`,`forum_id`),
 
  KEY `featured_topics` (`featured`,`start_date`),
 
  KEY `popular_topics` (`popular_time`,`approved`,`forum_id`,`start_date`),
 
  KEY `most_recent_post` (`forum_id`,`approved`,`last_post`),
 
  KEY `last_true_post` (`forum_id`,`pinned`,`last_post`)
 
) ENGINE=MyISAM AUTO_INCREMENT=46659 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

This one for the SHOW INDEX FROM forum_forums_posts

And those for the SHOW INDEX FROM forum_forums_topics

For the moment I've found the widget that runs that query and I disabled it and since then the server haven't crashed and the users are not experiencing/reporting any other issues.
Given that I will try to gdb attach to a test instance, but that would be done over the weekend as the problem is not that major anymore.

I am considering myself still a beginner in administrating those kinds of servers and stuffs, and sorry if I get things wrong/don't know how to do things. (98% I get them right)

And I would like to thank you a lot for your quick response and every thing! Appreciate it!
Have a good weekend! I'll come with the backtrace on Sunday/Monday.

Comment by Daniel Black [ 2021-03-26 ]

Thanks for the info. Missed the table information for `forum_forums_forums` (you must dislike whoever setup these names).

Was the widget that was running this query installed for when 10.5.7 was running?

Sorry to hear `EXPLAIN` crashed.

I'm not expecting any php code changes for query simplification. Just running the query on the mariadb command prompt like you did for `EXPLAIN`.

For getting a server core dump you need to ensure that the core limit is raised. (https://mariadb.com/kb/en/systemd/#configuring-the-core-file-size)

You're doing really well.

Off topic for this, however but recommend moving from MyISAM to Aria or InnoDB (will need innodb_buffer_pool_size= set). (ALTER TABLE x ENGINE=y)

Comment by Cosmin Boicu [ 2021-03-29 ]

Yeah, missed that one.

Here it is:

SHOW CREATE TABLE for forum_forums_forums

                                                                                                                                                            ------------------------------------------------------------------------+
 
| forum_forums_forums | CREATE TABLE `forum_forums_forums` (
 
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
 
  `topics` int(10) unsigned NOT NULL DEFAULT 0,
 
  `posts` int(10) unsigned NOT NULL DEFAULT 0,
 
  `last_post` int(10) DEFAULT NULL,
 
  `last_poster_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `last_poster_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''                                                                                                                                                             ,
 
  `position` int(5) unsigned DEFAULT 0,
 
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `password_override` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `last_title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `last_id` int(10) DEFAULT NULL,
 
  `sort_key` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `show_rules` tinyint(1) DEFAULT NULL,
 
  `preview_posts` tinyint(1) DEFAULT NULL,
 
  `allow_poll` tinyint(1) NOT NULL DEFAULT 1,
 
  `inc_postcount` tinyint(1) NOT NULL DEFAULT 1,
 
  `skin_id` int(10) DEFAULT NULL,
 
  `parent_id` mediumint(5) DEFAULT -1,
 
  `redirect_url` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT '',
 
  `redirect_on` tinyint(1) NOT NULL DEFAULT 0,
 
  `redirect_hits` int(10) NOT NULL DEFAULT 0,
 
  `sub_can_post` tinyint(1) DEFAULT 1,
 
  `permission_showtopic` tinyint(1) NOT NULL DEFAULT 0,
 
  `queued_topics` int(10) NOT NULL DEFAULT 0,
 
  `queued_posts` int(10) NOT NULL DEFAULT 0,
 
  `forum_allow_rating` tinyint(1) NOT NULL DEFAULT 0,
 
  `min_posts_post` int(10) unsigned NOT NULL DEFAULT 0,
 
  `min_posts_view` int(10) unsigned NOT NULL DEFAULT 0,
 
  `can_view_others` tinyint(1) NOT NULL DEFAULT 1,
 
  `name_seo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `seo_last_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `seo_last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `last_x_topic_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `forums_bitoptions` int(10) unsigned NOT NULL DEFAULT 0,
 
  `disable_sharelinks` int(1) NOT NULL DEFAULT 0,
 
  `tag_predefined` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `archived_topics` int(10) NOT NULL DEFAULT 0,
 
  `archived_posts` int(10) NOT NULL DEFAULT 0,
 
  `ipseo_priority` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT '',
 
  `viglink` tinyint(1) NOT NULL DEFAULT 1,
 
  `qa_rate_questions` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `qa_rate_answers` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `icon` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `conv_parent` bigint(20) DEFAULT 0,
 
  `club_id` bigint(20) unsigned DEFAULT NULL COMMENT 'The club ID if this forum                                                                                                                                                              belongs to a club, or NULL',
 
  `feature_color` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'F                                                                                                                                                             eature color',
 
  `last_prefix` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'AT                                                                                                                                                             &P: Last topic prefix',
 
  `tag_mode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'inherit' COMMENT '                                                                                                                                                             AT&P: Tag mode',
 
  `require_prefix` tinyint(4) DEFAULT 0 COMMENT 'AT&P: Require a prefix',
 
  `default_prefix` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT                                                                                                                                                              'AT&P: Default prefix',
 
  `default_tags` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'AT&                                                                                                                                                             P: Default tags',
 
  `show_prefix_in_desc` tinyint(4) DEFAULT 0 COMMENT 'AT&P: Show prefixes on ind                                                                                                                                                             ex',
 
  PRIMARY KEY (`id`),
 
  KEY `position` (`parent_id`,`position`),
 
  KEY `min_posts_view` (`min_posts_view`),
 
  KEY `can_view_others` (`can_view_others`),
 
  KEY `forum_password` (`password`),
 
  KEY `widget_performance` (`min_posts_view`,`password`(27),`password_override`(                                                                                                                                                             211),`can_view_others`)
 
) ENGINE=MyISAM AUTO_INCREMENT=6425 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unic                                                                                                                                                             ode_ci |
 
+---------------------+-------------

SHOW INDEX FROM forum_forums_forums

Yeah the widget was already running. The thing is it's not always crashing, because the widget is showing the posts/topics (but maybe is showing a cached version). For me it never crashed (as a logged user/admin), only for some users, so I assume some of the parameters (that depends on what permissions other users have) of that filtering that is applied is crashing. Will tinker with the query and try to run it from the command line and see if I can find some that does not crash.

For the core dump, I raised the core limit and enabled the core dumps by adding
core_file
in the [mariadb] section in the config.
Then ran the query, it crashed, then ran sudo gdb /usr/sbin/mysqld /var/lib/mysql/core
and set logging and bt and another run with:

set logging on
 
set pagination off
 
set print frame-arguments all
 
thread apply all bt full
 
set logging off

And those are the outpus:
gdb_output.log
gdb.txt

Moving to InnoDB would be possible without loosing data or anything else?
This database we have from 2017 since we started and this is how it was on the webhost we had, not sure if the forum installation was at fault for using MyISAM.
I'll search for how to do it and will give it a try on a backed up test db.

Let me know if you need anything else.

And thank you for everything, sir!

Comment by Alice Sherepa [ 2021-11-02 ]

Angrry Have you had a chance to try the new version if this still happens there?

Comment by Cosmin Boicu [ 2021-11-19 ]

Sorry for late reply, haven't had a chance to check. I will try next month (December) and come back with an update.

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