- discuss - lists.mariadb.org

[Maria-discuss] Is it possible to upgrade SHA-1 and MD5 algorithms in Mariadb-10.5?
by Lukas Javorsky 22 Mar '21

22 Mar '21

Hi, In RHEL-9 we are deprecating, old SHA-1 and MD5 and that's why I want to ask you if there is any chance that upstream is going to change it, or we should do it downstream. These algorithms are no longer considered as safe, so it may be a good thing to upgrade them. AFAIK mariadb uses these algorithms in *mariadb* and *mariadb-connector-c.* Also if you have no intention to change it, is there any chance you could help us somehow. Maybe point out what we should be aware of. Please let me know what you think Lukas -- S pozdravom/ Best regards Lukáš Javorský Associate Software Engineer, Core service - Databases Red Hat <https://www.redhat.com> Purkyňova 115 (TPB-C) 612 00 Brno - Královo Pole ljavorsk(a)redhat.com <https://www.redhat.com>

5 8

[Maria-discuss] Why does MariaDB needs SELinux capability for setuid/setgid?
by Lukas Javorsky 16 Mar '21

16 Mar '21

Hi guys, I'm looking into SELinux in Fedora's MariaDB package and I can see that we have two types in MariaDB that have setuid/setgid capability. 1st: https://github.com/fedora-selinux/selinux-policy/blob/rawhide/policy/module… 2nd: https://github.com/fedora-selinux/selinux-policy/blob/rawhide/policy/module… My question is, does mysqld_t need to have this capability? I found that setuid/setgid is used inside mysqld_safe_helper (mariadbd-safe-helper). Are there any other cases when MariaDB uses these functions? Thank you for letting me know Lukas -- S pozdravom/ Best regards Lukáš Javorský Associate Software Engineer, Core service - Databases Red Hat <https://www.redhat.com> Purkyňova 115 (TPB-C) 612 00 Brno - Královo Pole ljavorsk(a)redhat.com <https://www.redhat.com>

3 5

[Maria-discuss] Problem with InnoDB: Semaphore wait after upgrading to 10.3.27
by Conor Murphy 16 Mar '21

16 Mar '21

Hi, We have a handful of servers running MariaDB 10.3. We noticed issues on two of them where we were getting issues like "InnoDB: A long semaphore wait:--Thread 140602802394880 has waited at dict0stats.cc line 1969 for 241.00 seconds the semaphore:" and crashes like "ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.210215 7:14:24 [ERROR] mysqld got signal 6 ;" Turned out that these two servers were running 10.3.27 whereas the others were running 10.3.17. After downgrading the two problem servers to 10.3.17, all the issues with InnoDB semaphores stopped. Any ideas on how to track down what change between 10.3.17 to 10.3.27 might be causing this problem? Thanks, Conor

3 4

[Maria-discuss] RocksDB doubt
by Roberto Spadim 04 Mar '21

04 Mar '21

Hi folks! Could be possible write/read from rocksdb and at the same machine have a mariadb+myrocksdb running? -- Roberto Spadim

1 0

[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

[Maria-discuss] Streaming telemetry and ColumnStore - viable or not
by Richard Ellerbrock 19 Jan '21

19 Jan '21

Hi all, I am investigating the viability of using MariaDB as a datastore for streaming telemetry data sourced via gRPC. The data ingestion rate will be quite high (write once, read many with no change to the data), but the data can be buffered and bulk loaded in 10-60sec intervals. We are currently using MariaDB for OLTP purposes (Innodb) and also graphs (OQGRAPH) with no issues. My journey so far has been mainly focused on Kafka but the more I dig the more future trouble I uncover. So many components required to solve even a simple problem. Now I am investigating if using our current tooling that our team knows already will get the job done. Reading up on ColumnStore has also uncovered more gotchas and deprectations that I am comfortable with. Things that concern me are: 1. Is ColumnStore going to go the way of TokuDB? 2. The docs warn that data needs to be bulk inserted - not super ideal for streaming applications. Is streaming via cpimport viable? 3. The bulk write API's have been deprecated ( https://mariadb.com/kb/en/columnstore-bulk-write-sdk/) which then implies that streaming is not advised (Kafka connectors are thus also not viable). This slideshow talks about streaming with ColumnStore 1.1 https://www.slideshare.net/MariaDB/big-data-analytics-with-mariadb-columnst… 4. Streaming data is a combination of fixed columns and key / value pairs. How to deal with the dynamic nature of the key / value pairs (see below for sample data that was sourced via gRPC and converted to OpenTSDB format - we can convert the data into any required format, this is just one example). So some questions: What is the current recommended streaming telemetry data store solution from MariaDB, or should I rather look at a custom solution (different tooling, more skills/re-skilling required)? Is there a writeup or reference design? Any insights will be helpful. Thanks. put eos.intflagpktcounter.counter.pkt 1611032330 547181 intf=Port-Channel553 direction=in type=Multicast host=30.7.253.145 put eos.intflagcounter.counter.octets 1611032330 121113118 intf=Port-Channel553 direction=in host=30.7.253.145 put eos.intflagpktcounter.counter.pkt 1611032330 3649031 type=Ucast host=30.7.253.145 intf=Port-Channel553 direction=out put eos.intflagpktcounter.counter.pkt 1611032330 297600 host=30.7.253.145 intf=Port-Channel553 direction=in type=Ucast put eos.intflagcounter.counter.errors 1611032330 0 intf=Port-Channel553 direction=out host=30.7.253.145 put eos.totalcpu.cpu.utilization.total 1611032330 21 type=util host=30.7.253.145 put eos.totalcpu.cpu.utilization.total 1611032330 225275556 host=30.7.253.145 type=user put eos.corecpu.cpu.utilization.total 1611032330 42929390 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.total 1611032330 4005894390 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.0 1611032330 55264287 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.0 1611032330 10865672 host=30.7.253.145 type=system put eos.corecpu.cpu.utilization.cpu.0 1611032330 1002366784 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 20 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 50596732 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 10883378 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 1006988024 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 21 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 58921103 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 10405231 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 999355549 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 21 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 60493433 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 10775108 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 997184032 type=idle host=30.7.253.145 put eos.tempsensor.environment.temperature 1611032330 45.526831652731964 sensor=cell/1/TempSensor1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan2\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 host=30.7.253.145 fan=Fan2\/1 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan1\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan1\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan3\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan3\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan4\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan4\/1 host=30.7.253.145 put eos.sfpfixedtemperature.xcvr.temperature 1611032330 38.0390625 host=30.7.253.145 intf=Ethernet3 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -2.0398103065285085 intf=Ethernet3 powerdirection=tx host=30.7.253.145 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -1.92938760082761 powerdirection=rx host=30.7.253.145 intf=Ethernet3 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -1.92938760082761 intf=Ethernet3 powerdirection=rx host=30.7.253.145 -- Richard Ellerbrock

1 0

[Maria-discuss] Setting a System Global Variable
by brian＠smitherconsulting.com 15 Jan '21

15 Jan '21

Hello, Concerning: SET GLOBAL general_log_file='/path/to/general.log'; Some locations in the documentation suggests this can only be done by a super-user. https://mariadb.com/kb/en/general-query-log/ "A user with the SUPER privilege can disable logging to the general query log for a connection by setting the SQL_LOG_OFF system variable to 1." https://mariadb.com/kb/en/grant/#super SUPER: "Execute superuser statements: ..., SET global system variables,...." Q: Does setting the general_log_file, in fact, require super user privileges? Then, once the setting is made, and the task completed, the desire is to restore the setting back to what it was without having to know what it was. https://mariadb.com/kb/en/set/#global-session DEFAULT: "Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value." Q: By "server default" is this {host_name}.log or is it the value set in my.cnf? Thank you.

1 0