Bug #91548 LOCK_grant and LOCK_open can deadlock on a gtid slave
Description:
Two threads deadlock on the slave, their stacks are :
1. Slave SQL Thread running a CREATE USER command and trying to save gtid:
# Call Site
00 ntdll!ZwWaitForSingleObject+0xa
01 ntdll!RtlpWaitOnCriticalSection+0xe8
02 ntdll!RtlEnterCriticalSection+0xd1
03 mysqld!native_mutex_lock+0x10 [.\include\thr_mutex.h @ 81]
04 mysqld!my_mutex_lock+0x10 [.\include\thr_mutex.h @ 182]
05 mysqld!inline_mysql_mutex_lock+0x3e [.\include\mysql\psi\mysql_thread.h @ 711]
06 mysqld!check_if_table_exists+0x66 [.\sql\sql_base.cc @ 2655]
07 mysqld!open_table+0x4a0 [.\sql\sql_base.cc @ 3238]
08 mysqld!open_and_process_table+0x1dd [.\sql\sql_base.cc @ 5144]
09 mysqld!open_tables+0x221 [.\sql\sql_base.cc @ 5753]
0a mysqld!open_and_lock_tables+0x44 [.\sql\sql_base.cc @ 6475]
0b mysqld!open_n_lock_single_table+0x3a [.\sql\sql_base.cc @ 6317]
0c mysqld!open_n_lock_single_table+0x27 [.\sql\sql_base.h @ 469]
0d mysqld!System_table_access::open_table+0x145 [.\sql\rpl_table_access.cc @ 57]
0e mysqld!Gtid_table_access_context::init+0xd5 [.\sql\rpl_gtid_persist.cc @ 192]
0f mysqld!Gtid_table_persistor::save+0xf4 [.\sql\rpl_gtid_persist.cc @ 406]
10 mysqld!Gtid_state::save+0x22 [.\sql\rpl_gtid_state.cc @ 714]
11 mysqld!commit_owned_gtids+0x61 [.\sql\handler.cc @ 1592]
12 mysqld!commit_owned_gtid_by_partial_command+0x1a [.\sql\handler.cc @ 1622]
13 mysqld!mysql_create_user+0x571 [.\sql\auth\sql_user.cc @ 1491]
14 mysqld!mysql_execute_command+0x21ed [.\sql\sql_parse.cc @ 3950]
15 mysqld!mysql_parse+0x296 [.\sql\sql_parse.cc @ 5560]
16 mysqld!Query_log_event::do_apply_event+0x966 [.\sql\log_event.cc @ 4790]
17 mysqld!Log_event::apply_event+0x2a3 [.\sql\log_event.cc @ 3473]
18 mysqld!apply_event_and_update_pos+0x17e [.\sql\rpl_slave.cc @ 4719]
19 mysqld!exec_relay_log_event+0x1f1 [.\sql\rpl_slave.cc @ 5268]
1a mysqld!handle_slave_sql+0x975 [.\sql\rpl_slave.cc @ 7425]
1b mysqld!pfs_spawn_thread+0x132 [.\storage\perfschema\pfs.cc @ 2193]
1c mysqld!win_thread_start+0x1c [.\mysys\my_thread.c @ 38]
2. end-user connection querying i_s:
# Call Site
00 ntdll!ZwWaitForKeyedEvent+0xa
01 ntdll! ?? ::FNODOBFM::`string'+0x9837
02 mysqld!native_rw_rdlock+0xc [.\include\thr_rwlock.h @ 76]
03 mysqld!inline_mysql_rwlock_rdlock+0x37 [.\include\mysql\psi\mysql_thread.h @ 895]
04 mysqld!Partitioned_rwlock::rdlock+0x4f [.\sql\auth\partitioned_rwlock.h @ 79]
05 mysqld!Partitioned_rwlock_read_guard::{ctor}+0x15 [.\sql\auth\partitioned_rwlock.h @ 113]
06 mysqld!LOCK_grant_read_guard::{ctor}+0x1b [.\sql\auth\sql_authorization.cc @ 548]
07 mysqld!get_column_grant+0x48 [.\sql\auth\sql_authorization.cc @ 2705]
08 mysqld!get_schema_column_record+0x238 [.\sql\sql_show.cc @ 5372]
09 mysqld!fill_schema_table_from_frm+0x466 [.\sql\sql_show.cc @ 4408]
0a mysqld!get_all_tables+0x4da [.\sql\sql_show.cc @ 4713]
0b mysqld!do_fill_table+0x89 [.\sql\sql_show.cc @ 8148]
0c mysqld!get_schema_tables_result+0x240 [.\sql\sql_show.cc @ 8279]
0d mysqld!JOIN::prepare_result+0x91 [.\sql\sql_select.cc @ 908]
0e mysqld!JOIN::exec+0x15c [.\sql\sql_executor.cc @ 124]
0f mysqld!handle_query+0x202 [.\sql\sql_select.cc @ 185]
10 mysqld!execute_sqlcom_select+0x226 [.\sql\sql_parse.cc @ 5128]
11 mysqld!mysql_execute_command+0x707 [.\sql\sql_parse.cc @ 2814]
12 mysqld!mysql_parse+0x296 [.\sql\sql_parse.cc @ 5560]
13 mysqld!dispatch_command+0x6e4 [.\sql\sql_parse.cc @ 1486]
14 mysqld!do_command+0x1ea [.\sql\sql_parse.cc @ 1026]
15 mysqld!handle_connection+0x12c [.\sql\conn_handler\connection_handler_per_thread.cc @ 300]
16 mysqld!pfs_spawn_thread+0x132 [.\storage\perfschema\pfs.cc @ 2193]
17 mysqld!win_thread_start+0x1c [.\mysys\my_thread.c @ 38]
18 ucrtbase!crt_at_quick_exit+0x7d
Here's a sample cut processlist, also showing new connections are stalling ....
| Time | State | Info
+------+----------------------+----------------------------------------
| 496 | | NULL
| 867 | Opening tables | NULL
| 519 | Opening tables | CREATE USER IF NOT EXISTS 'myuser'@'%'
| 519 | checking permissions | select * from information_schema.column
| 488 | starting | SET NAMES utf8
| 488 | starting | SET NAMES utf8
| 488 | starting | SET NAMES utf8
| 488 | starting | SET NAMES utf8
| 458 | starting | SET NAMES utf8
| 458 | starting | SET NAMES utf8
| 458 | starting | SET NAMES utf8
| 427 | starting | SET NAMES utf8
How to repeat:
Configuration
-------------
Setup ordinary gtid master.
Slave runs with options:
--sync_master_info=1 --sync_relay_log=1 --sync_relay_log_info=1
--loose-enforce-gtid-consistency=ON --loose-master_info_repository=TABLE
--loose-relay_log_info_repository=TABLE --loose-gtid-mode=ON
To repeat:
----------
Execute on master in a loop:
drop user if exists myuser;
create user if not exists myuser;
Create some fake tables as needed.. the more the better.
On slave, connect and query the i_s in a loop, multiple threads if needed:
select * from information_schema.columns order by table_schema,table_name;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Two threads deadlock on the slave, their stacks are : 1. Slave SQL Thread running a CREATE USER command and trying to save gtid: # Call Site 00 ntdll!ZwWaitForSingleObject+0xa 01 ntdll!RtlpWaitOnCriticalSection+0xe8 02 ntdll!RtlEnterCriticalSection+0xd1 03 mysqld!native_mutex_lock+0x10 [.\include\thr_mutex.h @ 81] 04 mysqld!my_mutex_lock+0x10 [.\include\thr_mutex.h @ 182] 05 mysqld!inline_mysql_mutex_lock+0x3e [.\include\mysql\psi\mysql_thread.h @ 711] 06 mysqld!check_if_table_exists+0x66 [.\sql\sql_base.cc @ 2655] 07 mysqld!open_table+0x4a0 [.\sql\sql_base.cc @ 3238] 08 mysqld!open_and_process_table+0x1dd [.\sql\sql_base.cc @ 5144] 09 mysqld!open_tables+0x221 [.\sql\sql_base.cc @ 5753] 0a mysqld!open_and_lock_tables+0x44 [.\sql\sql_base.cc @ 6475] 0b mysqld!open_n_lock_single_table+0x3a [.\sql\sql_base.cc @ 6317] 0c mysqld!open_n_lock_single_table+0x27 [.\sql\sql_base.h @ 469] 0d mysqld!System_table_access::open_table+0x145 [.\sql\rpl_table_access.cc @ 57] 0e mysqld!Gtid_table_access_context::init+0xd5 [.\sql\rpl_gtid_persist.cc @ 192] 0f mysqld!Gtid_table_persistor::save+0xf4 [.\sql\rpl_gtid_persist.cc @ 406] 10 mysqld!Gtid_state::save+0x22 [.\sql\rpl_gtid_state.cc @ 714] 11 mysqld!commit_owned_gtids+0x61 [.\sql\handler.cc @ 1592] 12 mysqld!commit_owned_gtid_by_partial_command+0x1a [.\sql\handler.cc @ 1622] 13 mysqld!mysql_create_user+0x571 [.\sql\auth\sql_user.cc @ 1491] 14 mysqld!mysql_execute_command+0x21ed [.\sql\sql_parse.cc @ 3950] 15 mysqld!mysql_parse+0x296 [.\sql\sql_parse.cc @ 5560] 16 mysqld!Query_log_event::do_apply_event+0x966 [.\sql\log_event.cc @ 4790] 17 mysqld!Log_event::apply_event+0x2a3 [.\sql\log_event.cc @ 3473] 18 mysqld!apply_event_and_update_pos+0x17e [.\sql\rpl_slave.cc @ 4719] 19 mysqld!exec_relay_log_event+0x1f1 [.\sql\rpl_slave.cc @ 5268] 1a mysqld!handle_slave_sql+0x975 [.\sql\rpl_slave.cc @ 7425] 1b mysqld!pfs_spawn_thread+0x132 [.\storage\perfschema\pfs.cc @ 2193] 1c mysqld!win_thread_start+0x1c [.\mysys\my_thread.c @ 38] 2. end-user connection querying i_s: # Call Site 00 ntdll!ZwWaitForKeyedEvent+0xa 01 ntdll! ?? ::FNODOBFM::`string'+0x9837 02 mysqld!native_rw_rdlock+0xc [.\include\thr_rwlock.h @ 76] 03 mysqld!inline_mysql_rwlock_rdlock+0x37 [.\include\mysql\psi\mysql_thread.h @ 895] 04 mysqld!Partitioned_rwlock::rdlock+0x4f [.\sql\auth\partitioned_rwlock.h @ 79] 05 mysqld!Partitioned_rwlock_read_guard::{ctor}+0x15 [.\sql\auth\partitioned_rwlock.h @ 113] 06 mysqld!LOCK_grant_read_guard::{ctor}+0x1b [.\sql\auth\sql_authorization.cc @ 548] 07 mysqld!get_column_grant+0x48 [.\sql\auth\sql_authorization.cc @ 2705] 08 mysqld!get_schema_column_record+0x238 [.\sql\sql_show.cc @ 5372] 09 mysqld!fill_schema_table_from_frm+0x466 [.\sql\sql_show.cc @ 4408] 0a mysqld!get_all_tables+0x4da [.\sql\sql_show.cc @ 4713] 0b mysqld!do_fill_table+0x89 [.\sql\sql_show.cc @ 8148] 0c mysqld!get_schema_tables_result+0x240 [.\sql\sql_show.cc @ 8279] 0d mysqld!JOIN::prepare_result+0x91 [.\sql\sql_select.cc @ 908] 0e mysqld!JOIN::exec+0x15c [.\sql\sql_executor.cc @ 124] 0f mysqld!handle_query+0x202 [.\sql\sql_select.cc @ 185] 10 mysqld!execute_sqlcom_select+0x226 [.\sql\sql_parse.cc @ 5128] 11 mysqld!mysql_execute_command+0x707 [.\sql\sql_parse.cc @ 2814] 12 mysqld!mysql_parse+0x296 [.\sql\sql_parse.cc @ 5560] 13 mysqld!dispatch_command+0x6e4 [.\sql\sql_parse.cc @ 1486] 14 mysqld!do_command+0x1ea [.\sql\sql_parse.cc @ 1026] 15 mysqld!handle_connection+0x12c [.\sql\conn_handler\connection_handler_per_thread.cc @ 300] 16 mysqld!pfs_spawn_thread+0x132 [.\storage\perfschema\pfs.cc @ 2193] 17 mysqld!win_thread_start+0x1c [.\mysys\my_thread.c @ 38] 18 ucrtbase!crt_at_quick_exit+0x7d Here's a sample cut processlist, also showing new connections are stalling .... | Time | State | Info +------+----------------------+---------------------------------------- | 496 | | NULL | 867 | Opening tables | NULL | 519 | Opening tables | CREATE USER IF NOT EXISTS 'myuser'@'%' | 519 | checking permissions | select * from information_schema.column | 488 | starting | SET NAMES utf8 | 488 | starting | SET NAMES utf8 | 488 | starting | SET NAMES utf8 | 488 | starting | SET NAMES utf8 | 458 | starting | SET NAMES utf8 | 458 | starting | SET NAMES utf8 | 458 | starting | SET NAMES utf8 | 427 | starting | SET NAMES utf8 How to repeat: Configuration ------------- Setup ordinary gtid master. Slave runs with options: --sync_master_info=1 --sync_relay_log=1 --sync_relay_log_info=1 --loose-enforce-gtid-consistency=ON --loose-master_info_repository=TABLE --loose-relay_log_info_repository=TABLE --loose-gtid-mode=ON To repeat: ---------- Execute on master in a loop: drop user if exists myuser; create user if not exists myuser; Create some fake tables as needed.. the more the better. On slave, connect and query the i_s in a loop, multiple threads if needed: select * from information_schema.columns order by table_schema,table_name;