Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.7
-
None
-
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.4.1708 (Core)
Release: 7.4.1708
Codename: Core
Kernel version: 3.10.0-693.5.2.el7.x86_64
##################################
#### DB configuration:
[mysqld]
########## Server Level ##########
port=3306
basedir=/opt/maria10.1
datadir=/opt/maria10.1/data/db
tmpdir=/opt/maria10.1/data/tmp
socket=/opt/maria10.1/data/mysql.sock
pid-file=/opt/maria10.1/data/mysqld.pid
lc-messages-dir=/opt/maria10.1/share
lc-messages=en_US
lower-case-table-names=1
##### Character #####
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
##### Security/Mode #####
#skip_name_resolve
bind_address=0.0.0.0
explicit_defaults_for_timestamp=1
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
skip-ssl
##### Connection #####
max_connections=1000
max_connect_errors=65536
max_user_connections=2900
max_allowed_packet=128M
connect_timeout=20
back_log=65535
net_read_timeout=30
net_write_timeout=60
net_buffer_length=2K # default 16K
##### per-thread Buffer #####
#read_buffer_size=128K
#read_rnd_buffer_size=256K
sort_buffer_size=32M
#join_buffer_size=2M
optimizer_switch="index_merge_sort_intersection=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,optimize_join_buffer_size=on"
join_cache_level=8
join_buffer_size=4M
join_buffer_space_limit=250M
mrr_buffer_size=4M
binlog_cache_size=16M
thread_stack=292K
##### Global Buffer #####
key_buffer_size=10M
query_cache_type = 0
query_cache_size = 0
##### THREAD POOL #####
thread_handling=pool-of-threads
thread_pool_max_threads=30000
##### TABLE CACHE #####
table_definition_cache=16500
table_open_cache=2000
table_open_cache_instances=8
##### IO #####
open_files_limit=65000
##### OTHERS #####
tmp_table_size=500M
max_heap_table_size=16M # Memory Engine
max_prepared_stmt_count=1310560
########## Server LOG ##########
##### general log #####
general_log=0
##### err log #####
log_error=/opt/maria10.1/log/alert_TMTEST.log
log_warnings=1
##### bin log #####
server_id=14285
log_bin=/opt/maria10.1/binlog/BINLOG
binlog_format=ROW
binlog_row_image=FULL
sync_binlog=1
log_slave_updates=1
expire_logs_days=2
max_binlog_size=128M
max_binlog_cache_size=1G
##### GTID #####
gtid_domain_id=14282
gtid_strict_mode=1
gtid_ignore_duplicates=on
##### slow log #####
slow_query_log=1
slow_query_log_file=/opt/maria10.1/log/slow.log
log_output='FILE'
long_query_time=1
log_slow_admin_statements=0
log_slow_rate_limit=1
log_slow_verbosity='query_plan,explain'
##### audit log #####
plugin-load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_logging=1
server_audit_file_path=/opt/maria10.1/log/server_audit.log
server_audit_events='CONNECT,QUERY'
server_audit_file_rotate_size=524288000 # 500M
server_audit_file_rotations=9
server_audit_output_type='file'
server_audit_excl_users='event_scheduler'
server_audit_query_log_limit=10240
########## INNODB ##########
default_storage_engine=InnoDB
##### Tablespace #####
innodb_file_per_table=1
##### Buffer Pool #####
#innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
innodb_buffer_pool_size=6G # innodb data cache. 70% of total RAM
#innodb_buffer_pool_instances=2
innodb_max_dirty_pages_pct=60
##### Redo #####
innodb_log_buffer_size=200M
innodb_log_file_size=1G
#innodb_log_files_in_group=2
##### Undo #####
innodb_undo_directory=/opt/maria10.1/data/db
#innodb_undo_logs=128
innodb_undo_tablespaces=4
##### Locking #####
innodb_print_all_deadlocks=1
innodb_lock_wait_timeout=50 # 50s
innodb_autoinc_lock_mode=2
innodb_rollback_on_timeout=1
##### Data Security #####
innodb_doublewrite=1
innodb_strict_mode=1
innodb_flush_log_at_trx_commit=1
transaction-isolation=READ-COMMITTED
##### IO #####
innodb_flush_method=O_DIRECT
innodb_purge_threads=1
innodb_io_capacity=1000 # if SSD, set 4000
innodb_flush_neighbors=1 # if SSD, set 0
innodb_read_io_threads=14
innodb_write_io_threads=14
innodb_open_files=60000
innodb_use_native_aio=1
########## Performance Schema ##########
performance_schema=ON
performance_schema_instrument='stage/%=ON'
performance_schema_consumer_events_stages_current=ON
performance_schema_consumer_events_statements_current=ON
performance_schema_consumer_events_waits_current=ON
performance_schema_consumer_events_statements_history_long=ON
########## Rpl_Slave ##########
##### only on slave node #####
relay_log=/opt/maria10.1/binlog/RELAY
max_relay_log_size=1024M
relay_log_recovery=1
relay_log_purge=1
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=test.%
replicate_wild_ignore_table=jnxdba.%
replicate_wild_ignore_table=mysql.slow_log
read_only=1
skip_slave_start=1
########## Rpl_Semi_Sync ##########
rpl_semi_sync_master_wait_point=AFTER_SYNC
##### master #####
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=18446744073709551615 # 1s
##### slave #####
rpl_semi_sync_slave_enabled=1
LSB Version: :core-4.1-amd64:core-4.1-noarch Distributor ID: CentOS Description: CentOS Linux release 7.4.1708 (Core) Release: 7.4.1708 Codename: Core Kernel version: 3.10.0-693.5.2.el7.x86_64 ################################## #### DB configuration: [mysqld] ########## Server Level ########## port=3306 basedir=/opt/maria10.1 datadir=/opt/maria10.1/data/db tmpdir=/opt/maria10.1/data/tmp socket=/opt/maria10.1/data/mysql.sock pid-file=/opt/maria10.1/data/mysqld.pid lc-messages-dir=/opt/maria10.1/share lc-messages=en_US lower-case-table-names=1 ##### Character ##### character-set-client-handshake=FALSE character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci ##### Security/Mode ##### #skip_name_resolve bind_address=0.0.0.0 explicit_defaults_for_timestamp=1 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION skip-ssl ##### Connection ##### max_connections=1000 max_connect_errors=65536 max_user_connections=2900 max_allowed_packet=128M connect_timeout=20 back_log=65535 net_read_timeout=30 net_write_timeout=60 net_buffer_length=2K # default 16K ##### per-thread Buffer ##### #read_buffer_size=128K #read_rnd_buffer_size=256K sort_buffer_size=32M #join_buffer_size=2M optimizer_switch="index_merge_sort_intersection=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,optimize_join_buffer_size=on" join_cache_level=8 join_buffer_size=4M join_buffer_space_limit=250M mrr_buffer_size=4M binlog_cache_size=16M thread_stack=292K ##### Global Buffer ##### key_buffer_size=10M query_cache_type = 0 query_cache_size = 0 ##### THREAD POOL ##### thread_handling=pool-of-threads thread_pool_max_threads=30000 ##### TABLE CACHE ##### table_definition_cache=16500 table_open_cache=2000 table_open_cache_instances=8 ##### IO ##### open_files_limit=65000 ##### OTHERS ##### tmp_table_size=500M max_heap_table_size=16M # Memory Engine max_prepared_stmt_count=1310560 ########## Server LOG ########## ##### general log ##### general_log=0 ##### err log ##### log_error=/opt/maria10.1/log/alert_TMTEST.log log_warnings=1 ##### bin log ##### server_id=14285 log_bin=/opt/maria10.1/binlog/BINLOG binlog_format=ROW binlog_row_image=FULL sync_binlog=1 log_slave_updates=1 expire_logs_days=2 max_binlog_size=128M max_binlog_cache_size=1G ##### GTID ##### gtid_domain_id=14282 gtid_strict_mode=1 gtid_ignore_duplicates=on ##### slow log ##### slow_query_log=1 slow_query_log_file=/opt/maria10.1/log/slow.log log_output='FILE' long_query_time=1 log_slow_admin_statements=0 log_slow_rate_limit=1 log_slow_verbosity='query_plan,explain' ##### audit log ##### plugin-load=server_audit=server_audit.so server_audit=FORCE_PLUS_PERMANENT server_audit_logging=1 server_audit_file_path=/opt/maria10.1/log/server_audit.log server_audit_events='CONNECT,QUERY' server_audit_file_rotate_size=524288000 # 500M server_audit_file_rotations=9 server_audit_output_type='file' server_audit_excl_users='event_scheduler' server_audit_query_log_limit=10240 ########## INNODB ########## default_storage_engine=InnoDB ##### Tablespace ##### innodb_file_per_table=1 ##### Buffer Pool ##### #innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend innodb_buffer_pool_size=6G # innodb data cache. 70% of total RAM #innodb_buffer_pool_instances=2 innodb_max_dirty_pages_pct=60 ##### Redo ##### innodb_log_buffer_size=200M innodb_log_file_size=1G #innodb_log_files_in_group=2 ##### Undo ##### innodb_undo_directory=/opt/maria10.1/data/db #innodb_undo_logs=128 innodb_undo_tablespaces=4 ##### Locking ##### innodb_print_all_deadlocks=1 innodb_lock_wait_timeout=50 # 50s innodb_autoinc_lock_mode=2 innodb_rollback_on_timeout=1 ##### Data Security ##### innodb_doublewrite=1 innodb_strict_mode=1 innodb_flush_log_at_trx_commit=1 transaction-isolation=READ-COMMITTED ##### IO ##### innodb_flush_method=O_DIRECT innodb_purge_threads=1 innodb_io_capacity=1000 # if SSD, set 4000 innodb_flush_neighbors=1 # if SSD, set 0 innodb_read_io_threads=14 innodb_write_io_threads=14 innodb_open_files=60000 innodb_use_native_aio=1 ########## Performance Schema ########## performance_schema=ON performance_schema_instrument='stage/%=ON' performance_schema_consumer_events_stages_current=ON performance_schema_consumer_events_statements_current=ON performance_schema_consumer_events_waits_current=ON performance_schema_consumer_events_statements_history_long=ON ########## Rpl_Slave ########## ##### only on slave node ##### relay_log=/opt/maria10.1/binlog/RELAY max_relay_log_size=1024M relay_log_recovery=1 relay_log_purge=1 replicate_wild_ignore_table=sys.% replicate_wild_ignore_table=test.% replicate_wild_ignore_table=jnxdba.% replicate_wild_ignore_table=mysql.slow_log read_only=1 skip_slave_start=1 ########## Rpl_Semi_Sync ########## rpl_semi_sync_master_wait_point=AFTER_SYNC ##### master ##### rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=18446744073709551615 # 1s ##### slave ##### rpl_semi_sync_slave_enabled=1
Description
I'm doing test about MDEV-21117, deployed master to master with semi-sync replication enabled, and found that:
stop all slaves; -- doing this in one of the two master-master node;
|
-- do the steps below in the left master node
|
start transaction:
|
insert into t1(1);
|
commit; -- due to slave stopped, will be hanged here. And issue `control + c` signal.
|
Repeatedly doing the above operation, and found that this will keep the gtid increasing every time.
Is it right logic for semi-sync produce this behavior?
Attachments
Issue Links
- relates to
-
MDEV-31596 Client connection loss makes MariaDB commit a non semi-sync ACKed transaction
- Open