[MDEV-27387] Frequent DB crashes (InnoDB: Assertion failure in file buf0buf.cc) Created: 2021-12-30  Updated: 2022-01-04

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: zEvilz Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: crash
Environment:

Debian 10
MariaDB 10.6.5


 Description   

I have very frequent database crashes on my server. All crashes are associated with the same error (InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.6.5/storage/innobase/buf/buf0buf.cc line 2725). Crashes for different types of queries from different tables. Sometimes Systemd cannot restart MariaDB correctly. Similar behavior was before the complete reinstallation of the OS on the server (Debian 9, MariaDB 10.2/10.4)

 
 
2021-12-30 11:37:31 0x7f47841a8700  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.6.5/storage/innobase/buf/buf0buf.cc line 2725
 
InnoDB: We intentionally generate a memory trap.
 
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
 
InnoDB: If you get repeated assertion failures or crashes, even
 
InnoDB: immediately after the mariadbd startup, there may be
 
InnoDB: corruption in the InnoDB tablespace. Please refer to
 
InnoDB: https://mariadb.com/kb/en/library/innodb-recovery-modes/
 
InnoDB: about forcing recovery.
 
211230 11:37:31 [ERROR] mysqld got signal 6 ;
 
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.6.5-MariaDB-1:10.6.5+maria~buster-log
 
key_buffer_size=1073741824
 
read_buffer_size=131072
 
max_used_connections=2
 
max_threads=1002
 
thread_count=2
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5307089 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f45e0000c18
 
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 = 0x7f47841a7d98 thread_stack 0x49000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x5621c9175e2e]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x5621c8c4d3c5]
 
??:0(__restore_rt)[0x7f478f2f8730]
 
??:0(gsignal)[0x7f478ee4e7bb]
 
??:0(abort)[0x7f478ee39535]
 
/usr/sbin/mariadbd(+0x64a8be)[0x5621c890d8be]
 
/usr/sbin/mariadbd(+0xd9fb37)[0x5621c9062b37]
 
/usr/sbin/mariadbd(+0xd808d8)[0x5621c90438d8]
 
ut/ut0rbt.cc:460(rbt_eject_node)[0x5621c8fdf154]
 
buf/buf0buf.cc:2725(std::_Rb_tree<unsigned long, std::pair<unsigned long const, bool>, std::_Select1st<std::pair<unsigned long const, bool> >, std::less<unsigned long>, std::allocator<std::pair<unsigned long const, bool> > >::_M_erase(std::_Rb_tree_node<std::pair<unsigned long const, bool> >*))[0x5621c8fe1ac2]
 
btr/btr0cur.cc:1611(btr_cur_search_to_nth_level_func(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, srw_lock_impl<true>*, mtr_t*, unsigned long))[0x5621c8f0aedb]
 
include/page0page.h:216(page_offset(void const*))[0x5621c8c551d5]
 
row/row0sel.cc:5452(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x5621c8a7a36d]
 
handler/ha_innodb.cc:9274(ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int))[0x5621c8a6fccd]
 
sql/handler.cc:3587(handler::ha_index_next_same(unsigned char*, unsigned char const*, unsigned int))[0x5621c8a61066]
 
sql/sql_select.cc:21917(join_read_next_same)[0x5621c8a6fc87]
 
sql/sql_select.cc:21088(sub_select(JOIN*, st_join_table*, bool))[0x5621c8a61066]
 
sql/sql_select.cc:21075(sub_select(JOIN*, st_join_table*, bool))[0x5621c8a6fcfd]
 
sql/sql_select.cc:21106(sub_select(JOIN*, st_join_table*, bool))[0x5621c8a96ac4]
 
sql/sql_select.cc:20614(do_select)[0x5621c8a96ec7]
 
sql/sql_select.cc:4516(JOIN::exec())[0x5621c8a95124]
 
sql/sql_select.cc:4996(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*))[0x5621c8a9585e]
 
sql/sql_select.cc:545(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5621c8a301d1]
 
sql/sql_parse.cc:6256(execute_sqlcom_select)[0x5621c8a394e8]
 
sql/sql_parse.cc:5997(mysql_execute_command(THD*, bool))[0x5621c8a2b56f]
 
sql/sql_parse.cc:8047(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5621c8a3613e]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5621c8a377fd]
 
sql/sql_parse.cc:1404(do_command(THD*, bool))[0x5621c8b34597]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x5621c8b348dd]
 
sql/sql_connect.cc:1312(handle_one_connection)[0x5621c8e63635]
 
??:0(start_thread)[0x7f478f2edfa3]
 
??:0(clone)[0x7f478ef104cf]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f45e0010870): SELECT SQL_CALC_FOUND_ROWS  tlg_posts.ID FROM tlg_posts  LEFT JOIN tlg_term_relationships ON (tlg_posts.ID = tlg_term_relationships.object_id) INNER JOIN tlg_postmeta ON ( tlg_posts.ID = tlg_postmeta.post_id ) WHERE 1=1  AND ( 
  tlg_term_relationships.term_taxonomy_id IN (28)
 
) AND ( 
  tlg_postmeta.meta_key LIKE '_channel_members_count'
 
) AND tlg_posts.post_type = 'channel' AND (tlg_posts.post_status = 'publish') GROUP BY tlg_posts.ID ORDER BY tlg_postmeta.meta_value+0 DESC LIMIT 0, 12
 
 
 
Connection ID (thread ID): 36
 
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             62524                62524                processes 
Max open files            1048576              1048576              files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       62524                62524                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
 



 
 
2021-12-30 12:17:03 0x7f155c1c6700  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.6.5/storage/innobase/buf/buf0buf.cc line 2725
 
InnoDB: We intentionally generate a memory trap.
 
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
 
InnoDB: If you get repeated assertion failures or crashes, even
 
InnoDB: immediately after the mariadbd startup, there may be
 
InnoDB: corruption in the InnoDB tablespace. Please refer to
 
InnoDB: https://mariadb.com/kb/en/library/innodb-recovery-modes/
 
InnoDB: about forcing recovery.
 
211230 12:17:03 [ERROR] mysqld got signal 6 ;
 
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.6.5-MariaDB-1:10.6.5+maria~buster-log
 
key_buffer_size=1073741824
 
read_buffer_size=131072
 
max_used_connections=3
 
max_threads=1002
 
thread_count=3
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5307089 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f13bc000c18
 
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 = 0x7f155c1c5d98 thread_stack 0x49000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x55aacec16e2e]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x55aace6ee3c5]
 
??:0(__restore_rt)[0x7f1566d0a730]
 
??:0(gsignal)[0x7f15668607bb]
 
??:0(abort)[0x7f156684b535]
 
/usr/sbin/mariadbd(+0x64a8be)[0x55aace3ae8be]
 
/usr/sbin/mariadbd(+0xd9fb37)[0x55aaceb03b37]
 
/usr/sbin/mariadbd(+0xd808d8)[0x55aaceae48d8]
 
ut/ut0rbt.cc:460(rbt_eject_node)[0x55aaceaed4e7]
 
buf/buf0buf.cc:2725(std::_Rb_tree<unsigned long, std::pair<unsigned long const, bool>, std::_Select1st<std::pair<unsigned long const, bool> >, std::less<unsigned long>, std::allocator<std::pair<unsigned long const, bool> > >::_M_erase(std::_Rb_tree_node<std::pair<unsigned long const, bool> >*))[0x55aace9a74d4]
 
btr/btr0cur.cc:1611(btr_cur_search_to_nth_level_func(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, srw_lock_impl<true>*, mtr_t*, unsigned long))[0x55aace607b03]
 
include/page0page.h:216(page_offset(void const*))[0x55aace60a51e]
 
handler/ha_innodb.cc:14225(ha_innobase::records_in_range(unsigned int, st_key_range const*, st_key_range const*, st_page_range*))[0x55aace808a23]
 
sql/multi_range_read.cc:177(handler::multi_range_read_info_const(unsigned int, st_range_seq_if*, void*, unsigned int, unsigned int*, unsigned int*, Cost_estimate*))[0x55aace8141be]
 
sql/multi_range_read.cc:1713(DsMrr_impl::dsmrr_info_const(unsigned int, st_range_seq_if*, void*, unsigned int, unsigned int*, unsigned int*, Cost_estimate*))[0x55aace5354dc]
 
sql/opt_range.cc:11560(check_quick_select)[0x55aace536003]
 
sql/opt_range.cc:2931(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x55aace5360d3]
 
sql/sql_select.cc:5038(get_quick_record_count)[0x55aace53685e]
 
sql/sql_select.cc:1811(JOIN::optimize())[0x55aace4d11d1]
 
sql/sql_parse.cc:6256(execute_sqlcom_select)[0x55aace4da4e8]
 
sql/sql_parse.cc:5997(mysql_execute_command(THD*, bool))[0x55aace4cc56f]
 
sql/sql_parse.cc:8047(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55aace4d713e]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55aace4d87fd]
 
sql/sql_parse.cc:1404(do_command(THD*, bool))[0x55aace5d5597]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55aace5d58dd]
 
sql/sql_connect.cc:1312(handle_one_connection)[0x55aace904635]
 
??:0(start_thread)[0x7f1566cfffa3]
 
??:0(clone)[0x7f15669224cf]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f13bc0130c0): SELECT AVG(val) as views 
				FROM tlg_stat_views 
				WHERE 
					post_id="9748" AND 
					FROM_UNIXTIME(time) BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
 
 
 
Connection ID (thread ID): 614
 
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             62524                62524                processes 
Max open files            1048576              1048576              files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       62524                62524                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



 Comments   
Comment by Alice Sherepa [ 2022-01-03 ]

Could you please provide SHOW CREATE TABLE for tlg_posts,tlg_term_relationships,tlg_postmeta? Is the crash repeatable if you execute that query from the error log again?

Comment by zEvilz [ 2022-01-04 ]

Crashes not repeatable with this queries. Crashes with random queries.

tlg_posts

CREATE TABLE `tlg_posts` (
 
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_content` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `post_excerpt` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `post_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
 
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
 
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
 
  `post_password` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 
  `post_name` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 
  `to_ping` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `pinged` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
 
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `guid` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 
  `menu_order` int(11) NOT NULL DEFAULT 0,
 
  `post_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
 
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`ID`),
 
  KEY `post_name` (`post_name`(191)),
 
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
 
  KEY `post_parent` (`post_parent`),
 
  KEY `post_author` (`post_author`)
 
) ENGINE=InnoDB AUTO_INCREMENT=31143 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

tlg_term_relationships

CREATE TABLE `tlg_term_relationships` (
 
  `object_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `term_order` int(11) NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`object_id`,`term_taxonomy_id`),
 
  KEY `term_taxonomy_id` (`term_taxonomy_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

tlg_postmeta

CREATE TABLE `tlg_postmeta` (
 
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 
  `meta_value` longtext COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 
  PRIMARY KEY (`meta_id`),
 
  KEY `post_id` (`post_id`),
 
  KEY `meta_key` (`meta_key`(191))
 
) ENGINE=InnoDB AUTO_INCREMENT=6484157 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

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