discuss
Threads by month
- ----- 2024 -----
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2023 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2022 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2021 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2020 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2019 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2018 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2017 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2016 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2015 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2014 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2013 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2012 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2011 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2010 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2009 -----
- December
- November
- October
- September
- August
- July
February 2021
- 9 participants
- 4 discussions
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
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
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
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