[MDEV-25293] Can't connect to MySQL server on '192.168.239.83' (10061 "Unknown error") Created: 2021-03-30  Updated: 2021-04-05

Status: Open
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.5.9
Fix Version/s: None

Type: Bug Priority: Major
Reporter: starkhuang Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Linux localhost.localdomain 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux


 Description    

SELECT
 
	R.ROUTINE_SCHEMA,
 
	CONCAT( 'CREATE PROCEDURE ', R.ROUTINE_SCHEMA, '.', R.ROUTINE_NAME, ' () ', 'RETURNS ', P.DTD_IDENTIFIER, ' ', R.ROUTINE_DEFINITION ) TEXT,
 
	R.ROUTINE_NAME 
FROM
 
	information_schema.ROUTINES R,
 
	information_schema.PARAMETERS P 
WHERE
 
	R.ROUTINE_TYPE = 'FUNCTION' 
	AND R.ROUTINE_NAME = P.SPECIFIC_NAME 
	AND R.ROUTINE_NAME IN ( SELECT CASE SPECIFIC_name WHEN count( SPECIFIC_name ) = 1 THEN SPECIFIC_name END AS ROUTINE_NAME FROM information_schema.PARAMETERS ) 
	AND R.ROUTINE_SCHEMA NOT IN (
 
		'sys',
 
		'mysql',
 
		'performance_schema',
 
	'information_schema' 
	)

上述SQL查询报错，2003 - Can't connect to MySQL server on '192.168.239.83' (10061 "Unknown error")
> 时间: 3.294s
日志显示：

Mar 30 14:20:45 localhost mariadbd: 2021-03-30 14:20:45 121 [Warning] IP address '192.168.42.119' could not be resolved: Name or service not known
 
Mar 30 14:20:45 localhost mariadbd: 210330 14:20:45 [ERROR] mysqld got signal 11 ;
 
Mar 30 14:20:45 localhost mariadbd: This could be because you hit a bug. It is also possible that this binary
 
Mar 30 14:20:45 localhost mariadbd: or one of the libraries it was linked against is corrupt, improperly built,
 
Mar 30 14:20:45 localhost mariadbd: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Mar 30 14:20:45 localhost mariadbd: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
Mar 30 14:20:45 localhost mariadbd: We will try our best to scrape up some info that will hopefully help
 
Mar 30 14:20:45 localhost mariadbd: diagnose the problem, but since we have already crashed,
 
Mar 30 14:20:45 localhost mariadbd: something is definitely wrong and this may fail.
 
Mar 30 14:20:45 localhost mariadbd: Server version: 10.5.9-MariaDB
 
Mar 30 14:20:45 localhost mariadbd: key_buffer_size=134217728
 
Mar 30 14:20:45 localhost mariadbd: read_buffer_size=131072
 
Mar 30 14:20:45 localhost mariadbd: max_used_connections=1
 
Mar 30 14:20:45 localhost mariadbd: max_threads=153
 
Mar 30 14:20:45 localhost mariadbd: thread_count=1
 
Mar 30 14:20:45 localhost mariadbd: It is possible that mysqld could use up to
 
Mar 30 14:20:45 localhost mariadbd: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467864 K  bytes of memory
 
Mar 30 14:20:45 localhost mariadbd: Hope that's ok; if not, decrease some variables in the equation.
 
Mar 30 14:20:45 localhost mariadbd: Thread pointer: 0x7f4e900009b8
 
Mar 30 14:20:45 localhost mariadbd: Attempting backtrace. You can use the following information to find out
 
Mar 30 14:20:45 localhost mariadbd: where mysqld died. If you see no messages after this, something went
 
Mar 30 14:20:45 localhost mariadbd: terribly wrong...
 
Mar 30 14:20:45 localhost mariadbd: stack_bottom = 0x7f4ee00acc90 thread_stack 0x49000
 
Mar 30 14:20:45 localhost mariadbd: ??:0(my_print_stacktrace)[0x5651c7e7c7fe]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(handle_fatal_signal)[0x5651c7880a37]
 
Mar 30 14:20:45 localhost mariadbd: 2021-03-30 14:20:45 122 [Warning] Access denied for user 'dmcp'@'localhost' (using password: YES)
 
Mar 30 14:20:45 localhost mariadbd: sigaction.c:0(__restore_rt)[0x7f4ee5410630]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(optimize_schema_tables_memory_usage(List<TABLE_LIST>&))[0x5651c76f7f73]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(JOIN::optimize_inner())[0x5651c76d8e59]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(JOIN::optimize())[0x5651c76db9e5]
 
Mar 30 14:20:45 localhost mariadbd: ??: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*))[0x5651c76dbaa8]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5651c76dc694]
 
Mar 30 14:20:45 localhost mariadbd: /usr/sbin/mariadbd(+0x631909)[0x5651c7541909]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(mysql_execute_command(THD*))[0x5651c7682014]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5651c7684db5]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5651c7686f6c]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(do_command(THD*))[0x5651c768837b]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(do_handle_one_connection(CONNECT*, bool))[0x5651c7772ac2]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(handle_one_connection)[0x5651c7772d84]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x5651c7ace11d]
 
Mar 30 14:20:45 localhost mariadbd: pthread_create.c:0(start_thread)[0x7f4ee5408ea5]
 
Mar 30 14:20:45 localhost mariadbd: ??:0(__clone)[0x7f4ee376496d]
 
Mar 30 14:20:45 localhost mariadbd: Trying to get some variables.
 
Mar 30 14:20:45 localhost mariadbd: Some pointers may be invalid and cause the dump to abort.
 
Mar 30 14:20:45 localhost mariadbd: Query (0x7f4e90028ae0): SELECT
 
Mar 30 14:20:45 localhost mariadbd: R.ROUTINE_SCHEMA,
 
Mar 30 14:20:45 localhost mariadbd: CONCAT( 'CREATE PROCEDURE ', R.ROUTINE_SCHEMA, '.', R.ROUTINE_NAME, ' () ', 'RETURNS ', P.DTD_IDENTIFIER, ' ', R.ROUTINE_DEFINITION ) TEXT,
 
Mar 30 14:20:45 localhost mariadbd: R.ROUTINE_NAME
 
Mar 30 14:20:45 localhost mariadbd: FROM
 
Mar 30 14:20:45 localhost mariadbd: information_schema.ROUTINES R,
 
Mar 30 14:20:45 localhost mariadbd: information_schema.PARAMETERS P
 
Mar 30 14:20:45 localhost mariadbd: WHERE
 
Mar 30 14:20:45 localhost mariadbd: R.ROUTINE_TYPE = 'FUNCTION'
 
Mar 30 14:20:45 localhost mariadbd: AND R.ROUTINE_NAME = P.SPECIFIC_NAME
 
Mar 30 14:20:45 localhost mariadbd: AND R.ROUTINE_NAME IN ( SELECT CASE SPECIFIC_name WHEN count( SPECIFIC_name ) = 1 THEN SPECIFIC_name END AS ROUTINE_NAME FROM information_schema.PARAMETERS )
 
Mar 30 14:20:45 localhost mariadbd: AND R.ROUTINE_SCHEMA NOT IN (
 
Mar 30 14:20:45 localhost mariadbd: 'sys',
 
Mar 30 14:20:45 localhost mariadbd: 'mysql',
 
Mar 30 14:20:45 localhost mariadbd: 'performance_schema',
 
Mar 30 14:20:45 localhost mariadbd: 'information_schema'
 
Mar 30 14:20:45 localhost mariadbd: )
 
Mar 30 14:20:45 localhost mariadbd: Connection ID (thread ID): 121
 
Mar 30 14:20:45 localhost mariadbd: Status: NOT_KILLED
 
Mar 30 14:20:45 localhost kernel: mariadbd[7000]: segfault at 48 ip 00005651c76f7f73 sp 00007f4ee00ab1d0 error 4 in mariadbd[5651c6f10000+15f2000]
 
Mar 30 14:20:45 localhost mariadbd: 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
 
Mar 30 14:20:45 localhost mariadbd: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
Mar 30 14:20:45 localhost mariadbd: information that should help you find out what is causing the crash.
 
Mar 30 14:20:45 localhost mariadbd: Writing a core file...
 
Mar 30 14:20:45 localhost mariadbd: Working directory at /var/lib/mysql
 
Mar 30 14:20:45 localhost mariadbd: Resource Limits:
 
Mar 30 14:20:45 localhost mariadbd: Limit                     Soft Limit           Hard Limit           Units
 
Mar 30 14:20:45 localhost mariadbd: Max cpu time              unlimited            unlimited            seconds
 
Mar 30 14:20:45 localhost mariadbd: Max file size             unlimited            unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max data size             unlimited            unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max stack size            8388608              unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max core file size        0                    unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max resident set          unlimited            unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max processes             64065                64065                processes
 
Mar 30 14:20:45 localhost mariadbd: Max open files            16384                16384                files
 
Mar 30 14:20:45 localhost mariadbd: Max locked memory         65536                65536                bytes
 
Mar 30 14:20:45 localhost mariadbd: Max address space         unlimited            unlimited            bytes
 
Mar 30 14:20:45 localhost mariadbd: Max file locks            unlimited            unlimited            locks
 
Mar 30 14:20:45 localhost mariadbd: Max pending signals       64065                64065                signals
 
Mar 30 14:20:45 localhost mariadbd: Max msgqueue size         819200               819200               bytes
 
Mar 30 14:20:45 localhost mariadbd: Max nice priority         0                    0
 
Mar 30 14:20:45 localhost mariadbd: Max realtime priority     0                    0
 
Mar 30 14:20:45 localhost mariadbd: Max realtime timeout      unlimited            unlimited            us
 
Mar 30 14:20:45 localhost mariadbd: Core pattern: |/usr/libexec/abrt-hook-ccpp %s %c %p %u %g %t e %P %I %h
 
Mar 30 14:20:45 localhost abrt-hook-ccpp: Process 6971 (mariadbd) of user 27 killed by SIGSEGV - dumping core

mariadb数据库信息：Server version: 10.5.9-MariaDB MariaDB Server

 Comments   
Comment by Neil Skrypuch [ 2021-04-05 ]

I can reliably reproduce the segfault with the query in the original report in our setup. We are also seeing a very similar segfault with the following query:

SELECT `TABLE_NAME`, `COLUMN_NAME`
 
FROM `information_schema`.`COLUMNS`
 
WHERE `TABLE_NAME` IN (
 
        SELECT `TABLE_NAME`
 
        FROM `information_schema`.`COLUMNS`
 
        GROUP BY `TABLE_NAME` HAVING COUNT(*) = 1
 
);

Both queries can trigger a segfault even with a brand new datadir, so the issue does not appear to be data related and is quite easy to reproduce.

Here's our stack trace:

Apr  5 14:40:34 bm2 mysqld[3404645]: 210405 14:40:34 [ERROR] mysqld got signal 11 ;
 
Apr  5 14:40:34 bm2 mysqld[3404645]: This could be because you hit a bug. It is also possible that this binary
 
Apr  5 14:40:34 bm2 mysqld[3404645]: or one of the libraries it was linked against is corrupt, improperly built,
 
Apr  5 14:40:34 bm2 mysqld[3404645]: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Apr  5 14:40:34 bm2 mysqld[3404645]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
Apr  5 14:40:34 bm2 mysqld[3404645]: We will try our best to scrape up some info that will hopefully help
 
Apr  5 14:40:34 bm2 mysqld[3404645]: diagnose the problem, but since we have already crashed,
 
Apr  5 14:40:34 bm2 mysqld[3404645]: something is definitely wrong and this may fail.
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Server version: 10.5.9-MariaDB-log
 
Apr  5 14:40:34 bm2 mysqld[3404645]: key_buffer_size=16777216
 
Apr  5 14:40:34 bm2 mysqld[3404645]: read_buffer_size=1048576
 
Apr  5 14:40:34 bm2 mysqld[3404645]: max_used_connections=2
 
Apr  5 14:40:34 bm2 mysqld[3404645]: max_threads=1002
 
Apr  5 14:40:34 bm2 mysqld[3404645]: thread_count=3
 
Apr  5 14:40:34 bm2 mysqld[3404645]: It is possible that mysqld could use up to
 
Apr  5 14:40:34 bm2 mysqld[3404645]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2093927 K  bytes of memory
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Hope that's ok; if not, decrease some variables in the equation.
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Thread pointer: 0x7fbaa8000c58
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Attempting backtrace. You can use the following information to find out
 
Apr  5 14:40:34 bm2 mysqld[3404645]: where mysqld died. If you see no messages after this, something went
 
Apr  5 14:40:34 bm2 mysqld[3404645]: terribly wrong...
 
Apr  5 14:40:34 bm2 mysqld[3404645]: stack_bottom = 0x7fbb0c133bd8 thread_stack 0x49000
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(my_print_stacktrace)[0x5561873e9cce]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(handle_fatal_signal)[0x556186e7d055]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: sigaction.c:0(__restore_rt)[0x7fbb13be3b20]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(optimize_schema_tables_memory_usage(List<TABLE_LIST>&))[0x556186d01453]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(JOIN::optimize_inner())[0x556186ce3c27]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(JOIN::optimize())[0x556186ce6e73]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??: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*))[0x556186ce6f37]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x556186ce78bb]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(LEX::mark_first_table_as_inserting())[0x556186c83499]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(mysql_execute_command(THD*))[0x556186c8cb9d]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x556186c7e5c2]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x556186c89c40]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(do_command(THD*))[0x556186c8adbd]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(do_handle_one_connection(CONNECT*, bool))[0x556186d7cd91]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(handle_one_connection)[0x556186d7d12d]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: ??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x55618709e0ba]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: pthread_create.c:0(start_thread)[0x7fbb13bd914a]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: :0(__GI___clone)[0x7fbb11a42f23]
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Trying to get some variables.
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Some pointers may be invalid and cause the dump to abort.
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Query (0x7fbaa8012040): SELECT `TABLE_NAME`, `COLUMN_NAME`
 
Apr  5 14:40:34 bm2 mysqld[3404645]: FROM `information_schema`.`COLUMNS`
 
Apr  5 14:40:34 bm2 mysqld[3404645]: WHERE `TABLE_NAME` IN (
 
Apr  5 14:40:34 bm2 mysqld[3404645]:        SELECT `TABLE_NAME`
 
Apr  5 14:40:34 bm2 mysqld[3404645]:        FROM `information_schema`.`COLUMNS`
 
Apr  5 14:40:34 bm2 mysqld[3404645]:        GROUP BY `TABLE_NAME` HAVING COUNT(*) = 1
 
Apr  5 14:40:34 bm2 mysqld[3404645]: )
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Connection ID (thread ID): 1070
 
Apr  5 14:40:34 bm2 mysqld[3404645]: Status: NOT_KILLED
 
Apr  5 14:40:34 bm2 mysqld[3404645]: 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

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