February 2021 - discuss - lists.mariadb.org

[Maria-discuss] GTID and missing domain
by mariadb＠Biblestuph.com 27 Feb '21

27 Feb '21

Greetings, I've had this issue before and never quite got to the bottom of it. It keeps biting me and I'm hoping I can figure out how to definitively solve it. In brief, my replica server has this for gtid_slave_pos: 0-303-67739600,1-303-7360639083,100-303-4337869 And my primary server has: gtid_binlog_pos 0-303-67739600,1-303-7363061243,100-303-4338582 gtid_binlog_state 0-302-67690294,0-301-67719794,0-303-67739600,1-301-7350472534,1-302-7350381758,1-303-7363061243,100-302-4242958,100-301-4332195,100-303-4338582 That's all well and good and I can connect that way. But if I do this on the replica server: stop slave; select @@global.gtid_slave_pos; 0-303-67739600,1-303-7360639083,100-303-4337869 set global gtid_slave_pos = '1-303-7360639083,100-303-4337869'; start slave; I get: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged. Even though I'm positive there are no domain 0 transactions (again, hasn't been in service for years). If I add the `0-303-67739600` back in to gtid_slave_pos I can reconnect. At this point, I have only one server of a four server chain that I can actually connect to with gtid (as above). So I'm a little reluctant to do too much experimenting. But just to ask the question, if I: FLUSH BINARY LOGS DELETE_DOMAIN_ID=(0) on the master, would I then be able to connect to it via set global gtid_slave_pos = '1-303-7360639083,100-303-4337869'; from the replica? At this point, I cannot connect any of my other servers (which currently are all replicating from the same master) to each other, even the ones that don't show the 0- domain in the gtid-binlog vars. I'm hoping if I can figure out the above scenario it might help me deal with the rest, but I just keep feeling like I'm missing something. TIA, Dan

2 2

[Maria-discuss] Odd errors with TIMESTAMP columns
by David Buckley 26 Feb '21

26 Feb '21

Hello, I hope this is the correct list for this sort of question, apologies if not. I am working on importing data from a MySQL 5.1 database into a new MariaDB database (Server version: 10.4.14-MariaDB-1:10.4.14+maria~focal-log mariadb.org binary distribution), and am having trouble with a number of values in TIMESTAMP columns. For example: MariaDB [x]> create table y ( z timestamp ); MariaDB [x]> insert into y values ('2016-03-13 02:05:32'); ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:05:32' for column `x`.`y`.`z` at row 1 MariaDB [x]> insert into y values ('2016-03-13 03:05:32'); Query OK, 1 row affected (0.091 sec) I have tried turning off mysql56_temporal_format but that doesn't seem to change anything. As far as I can tell the values should be valid, in the above example simply changing the hour from 2 to 3 allowed the insert to succeed. But I am at a loss as to why '2016-03-13 02:05:32' is not a valid value. There are a few hundred rows at least affected by this, with many different values. Any ideas as to what could be causing this? Or ways to resolve it? Thanks, -David

3 2

[Maria-discuss] Maximum possible memory usage: 123.3G
by Reindl Harald 19 Feb '21

19 Feb '21

hmmmm - i thought i had the per-thread buffers down to a value which would be reasonable in case the max number of connections is reached i guess that two are guilty but with low "myisam_sort_buffer_size" optimize large tables fail from my expierience and a small "join_buffer_size" don't work well too are these *really* allocated unconditional per-threaf or only in case it's needed? myisam_sort_buffer_size = 15M join_buffer_size = 1M ----------------------------------------------- [--] Up for: 19h 10m 52s (174K q [2.520 qps], 688 conn, TX: 65M, RX: 11M) [--] Reads / Writes: 69% / 31% [--] Binary logging is enabled (GTID MODE: ON) [--] Physical Memory : 31.2G [--] Max MySQL memory : 123.3G [--] Other process memory: 0B [--] Total buffers: 450.0M global + 251.7M per thread (500 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 4.9G (15.57% of installed RAM) [!!] Maximum possible memory usage: 123.3G (394.87% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory ----------------------------------------------- max_allowed_packet = 250M max_tmp_tables = 100 max_connect_errors = 500 max_delayed_threads = 30 max_connections = 500 back_log = 1000 query_cache_limit = 512K query_cache_min_res_unit = 1024 query_cache_size = 32M query_cache_type = 1 table_cache = 5000 table_definition_cache = 512 thread_cache_size = 100 tmp_table_size = 256M max_heap_table_size = 256M key_buffer_size = 32M sort_buffer_size = 128K myisam_sort_buffer_size = 15M join_buffer_size = 1M preload_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128K innodb_buffer_pool_size = 96M innodb-defragment = 1 innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_instances = 1 innodb_checksum_algorithm = NONE innodb_doublewrite = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 1000 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_max_dirty_pages_pct = 60 innodb_max_purge_lag = 200000 innodb_open_files = 300 innodb_page_cleaners = 1 innodb_purge_threads = 1 innodb_read_io_threads = 4 innodb_stats_on_metadata = 0 innodb_strict_mode = 1 innodb_table_locks = 0 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 10 innodb_write_io_threads = 4 transaction-isolation = READ-COMMITTED server-id = 1 expire_logs_days = 21 max_binlog_size = 256M binlog-format = ROW binlog_stmt_cache_size = 256K binlog_cache_size = 256K sync_binlog = 30 log_bin_compress = 1 binlog-ignore-db = autotest replicate-ignore-db = autotest low-priority-updates skip-symbolic-links skip-name-resolve safe-user-create [mariadb] aria_pagecache_buffer_size = 32m aria_sort_buffer_size = 32m aria_page_checksum = 0 key_cache_segments = 8 thread_handling = pool-of-threads thread_pool_idle_timeout = 900 ----------------------------------------------- MariaDB [(none)]> show variables where variable_name!='optimizer_switch' and variable_name!='log_slow_filter' and variable_name!='session_track_system_variables' and variable_name not like '%ssl%' and variable_name!='version_source_revision' and variable_name not like 'log_bin%' and value not like '/var/log%' and value not like '%/data/%' and value not like '/var/%' and value not like '/usr%'; +---------------------------------------------+------------------------+ | Variable_name | Value | +---------------------------------------------+------------------------+ | alter_algorithm | DEFAULT | | aria_block_size | 8192 | | aria_checkpoint_interval | 30 | | aria_checkpoint_log_activity | 1048576 | | aria_encrypt_tables | OFF | | aria_force_start_after_recovery_failures | 0 | | aria_group_commit | none | | aria_group_commit_interval | 0 | | aria_log_file_size | 1073741824 | | aria_log_purge_type | immediate | | aria_max_sort_file_size | 9223372036853727232 | | aria_page_checksum | OFF | | aria_pagecache_age_threshold | 300 | | aria_pagecache_buffer_size | 33554432 | | aria_pagecache_division_limit | 100 | | aria_pagecache_file_hash_size | 512 | | aria_recover_options | BACKUP,QUICK | | aria_repair_threads | 1 | | aria_sort_buffer_size | 33554432 | | aria_stats_method | nulls_unequal | | aria_sync_log_dir | NEWFILE | | aria_used_for_temp_tables | ON | | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 1000 | | big_tables | OFF | | bind_address | | | binlog_annotate_row_events | ON | | binlog_cache_size | 262144 | | binlog_checksum | CRC32 | | binlog_commit_wait_count | 0 | | binlog_commit_wait_usec | 100000 | | binlog_direct_non_transactional_updates | OFF | | binlog_file_cache_size | 16384 | | binlog_format | ROW | | binlog_optimize_thread_scheduling | ON | | binlog_row_image | FULL | | binlog_stmt_cache_size | 262144 | | bulk_insert_buffer_size | 8388608 | | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | check_constraint_checks | ON | | collation_connection | utf8_general_ci | | collation_database | latin1_german1_ci | | collation_server | latin1_german1_ci | | column_compression_threshold | 100 | | column_compression_zlib_level | 6 | | column_compression_zlib_strategy | DEFAULT_STRATEGY | | column_compression_zlib_wrap | OFF | | completion_type | NO_CHAIN | | concurrent_insert | ALWAYS | | connect_timeout | 10 | | core_file | OFF | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | deadlock_search_depth_long | 15 | | deadlock_search_depth_short | 4 | | deadlock_timeout_long | 50000000 | | deadlock_timeout_short | 10000 | | debug_no_thread_alarm | OFF | | default_master_connection | | | default_regex_flags | | | default_storage_engine | MyISAM | | default_tmp_storage_engine | | | default_week_format | 0 | | delay_key_write | ALL | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | encrypt_binlog | OFF | | encrypt_tmp_disk_tables | OFF | | encrypt_tmp_files | OFF | | enforce_storage_engine | | | eq_range_index_dive_limit | 0 | | error_count | 0 | | event_scheduler | OFF | | expensive_subquery_limit | 100 | | expire_logs_days | 21 | | explicit_defaults_for_timestamp | OFF | | external_user | | | extra_max_connections | 1 | | extra_port | 0 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | group_concat_max_len | 1048576 | | gtid_binlog_pos | 0-1-2075216 | | gtid_binlog_state | 0-1-2075216 | | gtid_current_pos | 0-1-2075216 | | gtid_domain_id | 0 | | gtid_ignore_duplicates | OFF | | gtid_pos_auto_engines | | | gtid_seq_no | 0 | | gtid_slave_pos | | | gtid_strict_mode | OFF | | have_compress | YES | | have_crypt | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_profiling | NO | | have_query_cache | YES | | have_rtree_keys | YES | | have_symlink | DISABLED | | histogram_size | 0 | | histogram_type | SINGLE_PREC_HB | | host_cache_size | 628 | | hostname | srv-rhsoft.rhsoft.net | | identity | 0 | | idle_readonly_transaction_timeout | 0 | | idle_transaction_timeout | 0 | | idle_write_transaction_timeout | 0 | | ignore_builtin_innodb | OFF | | ignore_db_dirs | | | in_predicate_conversion_threshold | 1000 | | in_transaction | 0 | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10.000000 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_parts | 8 | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_background_scrub_data_check_interval | 3600 | | innodb_background_scrub_data_compressed | OFF | | innodb_background_scrub_data_interval | 604800 | | innodb_background_scrub_data_uncompressed | OFF | | innodb_buf_dump_status_frequency | 0 | | innodb_buffer_pool_chunk_size | 100663296 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 100663296 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | none | | innodb_checksums | ON | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compression_algorithm | zlib | | innodb_compression_default | OFF | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_deadlock_detect | ON | | innodb_default_encryption_key_id | 1 | | innodb_default_row_format | dynamic | | innodb_defragment | ON | | innodb_defragment_fill_factor | 0.900000 | | innodb_defragment_fill_factor_n_recs | 20 | | innodb_defragment_frequency | 40 | | innodb_defragment_n_pages | 7 | | innodb_defragment_stats_accuracy | 0 | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_encrypt_log | OFF | | innodb_encrypt_tables | OFF | | innodb_encrypt_temporary_tables | OFF | | innodb_encryption_rotate_key_age | 1 | | innodb_encryption_rotation_iops | 100 | | innodb_encryption_threads | 0 | | innodb_fast_shutdown | 1 | | innodb_fatal_semaphore_wait_threshold | 600 | | innodb_file_format | | | innodb_file_per_table | ON | | innodb_fill_factor | 100 | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbors | 1 | | innodb_flush_sync | ON | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_primary_key | OFF | | innodb_force_recovery | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_idle_flush_pct | 100 | | innodb_immediate_scrub_data_uncompressed | OFF | | innodb_instant_alter_column_allowed | add_last | | innodb_io_capacity | 1000 | | innodb_io_capacity_max | 2000 | | innodb_large_prefix | | | innodb_lock_schedule_algorithm | fcfs | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 2097152 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 33554432 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_optimize_ddl | OFF | | innodb_log_write_ahead_size | 8192 | | innodb_lru_scan_depth | 1024 | | innodb_max_dirty_pages_pct | 60.000000 | | innodb_max_dirty_pages_pct_lwm | 0.000000 | | innodb_max_purge_lag | 200000 | | innodb_max_purge_lag_delay | 0 | | innodb_max_purge_lag_wait | 4294967295 | | innodb_max_undo_log_size | 10485760 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 300 | | innodb_optimize_fulltext_only | OFF | | innodb_page_cleaners | 1 | | innodb_page_size | 16384 | | innodb_prefix_index_cluster_optimization | OFF | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_scrub_log | OFF | | innodb_scrub_log_speed | 256 | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 4 | | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_modified_counter | 0 | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_traditional | ON | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | innodb_strict_mode | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | OFF | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10 | | innodb_tmpdir | | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | OFF | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | | innodb_use_atomic_writes | ON | | innodb_use_native_aio | ON | | innodb_version | 10.3.26 | | innodb_write_io_threads | 4 | | insert_id | 0 | | interactive_timeout | 86400 | | join_buffer_size | 1048576 | | join_buffer_space_limit | 2097152 | | join_cache_level | 2 | | keep_files_on_create | OFF | | key_buffer_size | 33554432 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | key_cache_file_hash_size | 512 | | key_cache_segments | 8 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | last_gtid | | | last_insert_id | 0 | | lc_messages | en_US | | lc_messages_dir | | | lc_time_names | en_US | | license | GPL | | local_infile | OFF | | lock_wait_timeout | 86400 | | locked_in_memory | OFF | | log_disabled_statements | sp | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | | | log_tc_size | 24576 | | log_warnings | 1 | | long_query_time | 1.000000 | | low_priority_updates | ON | | lower_case_file_system | OFF | | lower_case_table_names | 1 | | master_verify_checksum | OFF | | max_allowed_packet | 262144000 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 268435456 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 500 | | max_connections | 500 | | max_delayed_threads | 30 | | max_digest_length | 1024 | | max_error_count | 64 | | max_heap_table_size | 268435456 | | max_insert_delayed_threads | 30 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 262144000 | | max_prepared_stmt_count | 16382 | | max_recursive_iterations | 4294967295 | | max_relay_log_size | 268435456 | | max_seeks_for_key | 4294967295 | | max_session_mem_used | 9223372036854775807 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_statement_time | 0.000000 | | max_tmp_tables | 100 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | metadata_locks_cache_size | 1024 | | metadata_locks_hash_instances | 8 | | min_examined_row_limit | 0 | | mrr_buffer_size | 262144 | | multi_range_count | 256 | | myisam_block_size | 1024 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | FORCE | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 15728640 | | myisam_stats_method | NULLS_UNEQUAL | | myisam_use_mmap | ON | | mysql56_temporal_format | ON | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | old | OFF | | old_alter_table | DEFAULT | | old_mode | | | old_passwords | OFF | | open_files_limit | 500000 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_selectivity_sampling_limit | 100 | | optimizer_use_condition_selectivity | 1 | | pid_file | /dev/null | | plugin_maturity | gamma | | port | 3306 | | preload_buffer_size | 131072 | | progress_report_time | 5 | | protocol_version | 10 | | proxy_protocol_networks | | | proxy_user | | | pseudo_slave_mode | OFF | | pseudo_thread_id | 727 | | query_alloc_block_size | 16384 | | query_cache_limit | 524288 | | query_cache_min_res_unit | 1024 | | query_cache_size | 33554432 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 24576 | | rand_seed1 | 577605648 | | rand_seed2 | 1011664119 | | range_alloc_block_size | 4096 | | read_binlog_speed_limit | 0 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 131072 | | relay_log | | | relay_log_basename | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | replicate_annotate_row_events | ON | | replicate_do_db | | | replicate_do_table | | | replicate_events_marked_for_skip | REPLICATE | | replicate_ignore_db | autotest | | replicate_ignore_table | | | replicate_wild_do_table | | | replicate_wild_ignore_table | | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rowid_merge_buff_size | 8388608 | | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | | secure_auth | ON | | secure_file_priv | | | secure_timestamp | NO | | server_id | 1 | | session_track_schema | ON | | session_track_state_change | OFF | | session_track_transaction_info | OFF | | skip_external_locking | ON | | skip_name_resolve | ON | | skip_networking | OFF | | skip_parallel_replication | OFF | | skip_replication | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_ddl_exec_mode | IDEMPOTENT | | slave_domain_parallel_threads | 0 | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 60 | | slave_parallel_max_queued | 131072 | | slave_parallel_mode | conservative | | slave_parallel_threads | 0 | | slave_parallel_workers | 0 | | slave_run_triggers_for_rbr | NO | | slave_skip_errors | OFF | | slave_sql_verify_checksum | ON | | slave_transaction_retries | 10 | | slave_transaction_retry_errors | 1213,1205 | | slave_transaction_retry_interval | 0 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | ON | | sort_buffer_size | 131072 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_mode | STRICT_ALL_TABLES | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | standard_compliant_cte | ON | | storage_engine | MyISAM | | stored_program_cache | 256 | | strict_password_validation | ON | | sync_binlog | 30 | | sync_frm | OFF | | sync_master_info | 10000 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | | system_time_zone | CET | | system_versioning_alter_history | ERROR | | system_versioning_asof | DEFAULT | | table_definition_cache | 512 | | table_open_cache | 5000 | | table_open_cache_instances | 8 | | tcp_keepalive_interval | 0 | | tcp_keepalive_probes | 0 | | tcp_keepalive_time | 0 | | thread_cache_size | 100 | | thread_concurrency | 10 | | thread_handling | pool-of-threads | | thread_pool_idle_timeout | 900 | | thread_pool_max_threads | 65536 | | thread_pool_oversubscribe | 3 | | thread_pool_prio_kickup_timer | 1000 | | thread_pool_priority | auto | | thread_pool_size | 8 | | thread_pool_stall_limit | 500 | | thread_stack | 299008 | | time_format | %H:%i:%s | | time_zone | Europe/Vienna | | timed_mutexes | OFF | | timestamp | 1613721594.324895 | | tmp_disk_table_size | 18446744073709551615 | | tmp_memory_table_size | 268435456 | | tmp_table_size | 268435456 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | READ-COMMITTED | | tx_read_only | OFF | | unique_checks | ON | | updatable_views_with_limit | YES | | use_stat_tables | NEVER | | userstat | OFF | | version | 10.3.26-MariaDB-log | | version_comment | thelounge | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | system | | wait_timeout | 86400 | | warning_count | 0 | +---------------------------------------------+------------------------+ 535 rows in set (0.002 sec)

2 2

[Maria-discuss] Help needed. Replication slave hangs on 'stop slave; '
by Thomas 01 Feb '21

01 Feb '21

Hello, we have upgraded our MySQL Master/Slave server vom 10.1.24 to 10.1.48 on 25.01. Backups worked as usual, but since friday (29.01.) our backups do not work anymore. I have seen that entering a 'stop slave;' on mysql cli hangs forever, while in the background (on a second console, I see the replication going on, but the gtid is far away from the master. According to MaxScale, on the slave it is '0-1-8696229561 and on the master '0-1-8705275411' and not moving forward on the slave. Slave 'show slave status': MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.1 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.005595 Read_Master_Log_Pos: 581894706 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 693 Relay_Master_Log_File: mysql-bin.005587 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 967647646 Relay_Log_Space: 8205026277 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-8705427292 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic 1 row in set (0.00 sec) Master status is this: MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.005595 Position: 581594839 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) If I try to stop the server it hangs also. Waited half an hour after I saw the backups hanging on the 'stop slave' command, then killed the server with kill -9. It restarted without complaining, but the hang is still occuring when doing a 'stop slave'. Can anybody help me diagnosing and possibly resolving this issue? Server is a baremetal with battery backed raid10. OS is Centos 7 all updated. Thanks, Thomas

2 7