Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.9
-
None
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.
Attachments
Activity
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)
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!
Angrry Have you had a chance to try the new version if this still happens there?
Sorry for late reply, haven't had a chance to check. I will try next month (December) and come back with an update.
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 | CREATE TABLE `forum_forums_posts` (`post` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,`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`)And this one for forum_forums_topics
| forum_forums_topics | CREATE TABLE `forum_forums_topics` (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`)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.