| Bug #105976 | Query replication_group_member_stats an empty result when there is WHERE clause | ||
|---|---|---|---|
| Submitted: | 25 Dec 2021 3:47 | Modified: | 30 Dec 2021 6:45 |
| Reporter: | xichao li (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Group Replication | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[28 Dec 2021 21:50]
MySQL Verification Team
Hi, I am having issues reproducing this?! Can you share your config and maybe write the steps to reproduce a bit clearer Thanks
[29 Dec 2021 1:54]
xichao li
1. Environmental information
mysql version: 8.0.27
architecture: 3 node group replication
configuration: 8 Core / 16 G
OS version: rhel 7.3
1. Set up the MGR synchronization cluster using the following configuration
[mysqld]
### MGR-Base ###
group_replication_start_on_boot = off
group_replication_bootstrap_group = off
group_replication_group_name = "e6d778cc-5d7d-11ec-8820-0050569be145"
group_replication_local_address = "10.0.135.75:23306"
group_replication_group_seeds = "10.0.135.75:23306,10.0.135.76:23306,10.0.135.77:23306"
group_replication_single_primary_mode = true
group_replication_enforce_update_everywhere_checks = false
group_replication_ip_allowlist= "127.0.0.1,10.0.135.75,10.0.135.76,10.0.135.77"
report_host="10.0.135.75"
### MGR-Tune ###
group_replication_consistency = EVENTUAL
group_replication_flow_control_mode = DISABLED
group_replication_exit_state_action = OFFLINE_MODE
group_replication_unreachable_majority_timeout = 13
group_replication_autorejoin_tries = 0
super_read_only = on
group_replication_member_expel_timeout = 5
### MGR-End ###
user=mysql
### General ###
server-id=13575
sql_require_primary_key=1
# default_authentication_plugin=mysql_native_password
transaction_isolation=READ-COMMITTED
basedir = /opt/mysql
datadir = /mysql/data
port = 3306
socket=/mysql/data/mysql.sock
tmpdir=/mysql/data/
pid-file=/mysql/data/mysqld.pid
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_time_zone = '+8:00'
log_error_verbosity = 3
core_file
local_infile= 1
log_timestamps= system
thread_stack= 192K
### innodb monitor ###
performance_schema = 1
# innodb_monitor_enable="module_innodb"
# innodb_monitor_enable="module_server"
# innodb_monitor_enable="module_dml"
# innodb_monitor_enable="module_ddl"
# innodb_monitor_enable="module_trx"
# innodb_monitor_enable="module_os"
# innodb_monitor_enable="module_purge"
# innodb_monitor_enable="module_log"
# innodb_monitor_enable="module_lock"
# innodb_monitor_enable="module_buffer"
# innodb_monitor_enable="module_index"
# innodb_monitor_enable="module_ibuf_system"
# innodb_monitor_enable="module_buffer_page"
# innodb_monitor_enable="module_adaptive_hash"
skip_name_resolve=ON
# host_cache_size=0
### CharacterSet ###
character_set_server=utf8mb4
# collation_server=utf8mb4_0900_ai_ci
collation_server=utf8mb4_general_ci
### Connection ###
max_allowed_packet=32M
max_connect_errors=100000
max_connections=2000
open_files_limit=65536
table_open_cache=2048
table_definition_cache=4096
### Log ###
log-error=/opt/logs/mysql_error.log
log-bin=/mysql/binlog/mysql-bin
log_bin_index=/mysql/binlog/mysql-bin.index
binlog_cache_size=1M
# expire_logs_days=7
binlog_transaction_dependency_tracking = WRITESET
binlog_expire_logs_seconds=604800
sync_binlog=1
binlog-format=ROW
slow_query_log=1
slow_query_log_file=/opt/logs/mysql_slow.log
#long_query_time=0 <debug mode>
long_query_time=0.1
### Memory ###
key_buffer_size=32M
sort_buffer_size=512K
read_buffer_size=256K
read_rnd_buffer_size=512K
### Innodb ###
innodb_thread_concurrency = 8
default-storage-engine=InnoDB
## Config innodb_buffer_pool_size=<physical memory>*0.4-0.6 ##
innodb_buffer_pool_size=11G
#After init db,can not modify: innodb_data_file_path
innodb_buffer_pool_instances=4
innodb_file_per_table=on
innodb_data_file_path=ibdata1:128M:autoextend
innodb_log_file_size=1G
innodb_log_files_in_group = 3
innodb_log_buffer_size=64M
innodb_flush_method=O_DIRECT
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
innodb_max_undo_log_size = 4G
# innodb_undo_tablespaces = 95 #deprecated param
innodb_undo_log_truncate = 1
# innodb_undo_directory = /mysql/data/undolog
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
# innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_sort_buffer_size = 67108864
### Others ###
lower_case_table_names=1
secure-file-priv=/mysql/bak/
explicit_defaults_for_timestamp=true
### Replication ###
binlog_order_commits=true
relay_log_recovery=1
relay_log_purge=0
skip-slave-start=1
# relay-log-info-repository=table # default value
# master-info-repository=table # default value
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
# slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' # default value
relay-log=/mysql/data/mysql-relay-bin
relay-log-index=/mysql/data/mysql-relay-bin.index
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = 1
slave_transaction_retries = 128
slave_pending_jobs_size_max = 2147483648
2. on the slave node executed stop group_replication;
stop group_replication;
3. Execute the following SQL1 or SQL2 on the slave node IMMEDIATELY(returns an empty result set):
-- SQL1
(root@127.0.0.1) [(none)] 20:56:36 66> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id= (select VARIABLE_VALUE from performance_schema.global_variables where variable_name in ('server_uuid'));
Empty set (4.09 sec)
-- SQL2
(root@127.0.0.1) [(none)] 21:05:35 122> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id='6945dd8f-5d7b-11ec-8263-0050569bdf16';
Empty set (2.15 sec)
4. Wait a few seconds, And execute the following SQL1 or SQL2 on the slave node(Reasonable results are returned):
(root@127.0.0.1) [(none)] 21:05:48 123> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id='6945dd8f-5d7b-11ec-8263-0050569bdf16';
+--------------------------------------+-------------------+------------------+
| member_id | CONFLICT_QUEUE_SZ | APPLIER_QUEUE_SZ |
+--------------------------------------+-------------------+------------------+
| 6945dd8f-5d7b-11ec-8263-0050569bdf16 | 0 | 0 |
+--------------------------------------+-------------------+------------------+
1 row in set (0.00 sec)
5. But if there is no condition, like following SQL3, the result can be queried every time.
-- SQL3
(root@127.0.0.1) [(none)] 21:13:56 170> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats;
+--------------------------------------+-------------------+------------------+
| member_id | CONFLICT_QUEUE_SZ | APPLIER_QUEUE_SZ |
+--------------------------------------+-------------------+------------------+
| 6945dd8f-5d7b-11ec-8263-0050569bdf16 | 0 | 0 |
+--------------------------------------+-------------------+------------------+
1 row in set (0.00 sec)
Description: Query replication_group_member_stats returns an empty result set when there is a WHERE clause How to repeat: -- slave node executed stop group_replication; (root@127.0.0.1) [(none)] 20:56:36 66> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id= (select VARIABLE_VALUE from performance_schema.global_variables where variable_name in ('server_uuid')); Empty set (4.09 sec) -- slave node executed stop_replication; select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id='6945dd8f-5d7b-11ec-8263-0050569bdf16'; (root@127.0.0.1) [(none)] 21:05:35 122> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id='6945dd8f-5d7b-11ec-8263-0050569bdf16'; Empty set (2.15 sec) (root@127.0.0.1) [(none)] 21:05:48 123> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats where member_id='6945dd8f-5d7b-11ec-8263-0050569bdf16'; +--------------------------------------+-------------------+------------------+ | member_id | CONFLICT_QUEUE_SZ | APPLIER_QUEUE_SZ | +--------------------------------------+-------------------+------------------+ | 6945dd8f-5d7b-11ec-8263-0050569bdf16 | 0 | 0 | +--------------------------------------+-------------------+------------------+ 1 row in set (0.00 sec) -- If there is no condition, the result can be queried every time. (root@127.0.0.1) [(none)] 21:13:56 170> select member_id,count_transactions_in_queue CONFLICT_QUEUE_SZ, count_transactions_remote_in_applier_queue APPLIER_QUEUE_SZ from performance_schema.replication_group_member_stats;