- discuss - lists.mariadb.org

[Maria-discuss] How to Easily Set Up a Full-Featured Linux Mail Server on Ubuntu 18.04.5 LTS with iRedMail 1.4.0
by Turritopsis Dohrnii Teo En Ming 26 Apr '21

26 Apr '21

Subject: How to Easily Set Up a Full-Featured Linux Mail Server on Ubuntu 18.04.5 LTS with iRedMail 1.4.0 Good day from Singapore, I followed linuxbabe.com's Xiao Guoan's guide and successfully setup a full featured Linux mail server on Ubuntu 18.04.5 LTS with IRedMail 1.4.0. Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 25 April 2021 Sunday Type of Publication: PDF Manual Document Version: 20210425.01 (1st release) ***IMPORTANT NOTICE*** Please note that Turritopsis Dohrnii Teo En Ming’s guide is based on Xiao Guoan’s guide at linuxbabe.com. Reference Guide Used by Teo En Ming: How to Easily Set Up a Full-Featured Mail Server on Ubuntu 18.04 with iRedMail Link: https://www.linuxbabe.com/mail-server/ubuntu-18-04-iredmail-email-server Original Author: Xiao Guoan The following is a list of open-source software that will be automatically installed and configured by iRedMail. • Postfix SMTP server • Dovecot IMAP server • Nginx web server to serve the admin panel and webmail • OpenLDAP, MySQL/MariaDB, or PostgreSQL for storing user information • Amavised-new for DKIM signing and verification • SpamAssassin for anti-spam • ClamAV for anti-virus • Roundcube webmail • SOGo groupware, providing webmail, calendar (CalDAV), contacts (CardDAV), tasks and ActiveSync services. • Fail2ban for protecting SSH • mlmmj mailing list manager • Netdata server monitoring • iRedAPD Postfix policy server for greylisting Redundant Download Links for Teo En Ming's PDF Manual: [1] https://drive.google.com/file/d/1un8sLLmNSMIt7V6blWCvJEgwGvxMbd4B/view?usp=… [2] https://drive.google.com/file/d/1i0vY7kfYkobu563qoI3_qCZg7G7BFoYR/view?usp=… [3] https://drive.google.com/file/d/1U9MFN1EklLbA8TMweLV5ntiSJuBBVkpQ/view?usp=… [4] https://www.docdroid.net/dW70KtS/iredmail-setup-1st-release-pdf [5] https://www.mediafire.com/file/evar7j28knqyoj6/IRedMail+Setup+1st+Release.p… [6] https://www.scribd.com/document/504932780/IRedMail-Setup-1st-Release Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 26 April 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] pt-online-schema-change vs alter with alter_algorithm='NOCOPY'
by Jeff Dyke 24 Apr '21

24 Apr '21

Hi. In previous jobs, i have used pt-online-schema-change quite a bit, normally testing the change the night before it went live. I have a very good understanding about how pt-online-schema-change works and i would like to understand the inner workings of alter_algorithm='NOCOPY'. If i can trust that as much as the percona script, i'm going to be happy happy person. Thanks. This is my reddit post. https://www.reddit.com/r/mariadb/comments/mx4e60/ptonlineschemachange_vs_al… . I know we have a lot of great responders here, and if the answer comes here or on reddit, i will copy and paste for everyone's knowledge. Thanks all!

1 0

[Maria-discuss] Galera cluster will not bootstrap with SST set to use TLS
by lejeczek 23 Apr '21

23 Apr '21

Hi Guys. I wonder if anybody sees the same or similar issues which I do experience with SST, or maybe could try to reproduce it simply by: [sst] tkey = /etc/my.cnf.d/certs/sst/sst.key tcert = /etc/my.cnf.d/certs/sst/sst.crt Yes, by having SST to use TLS. If I do have those two params in my config then 'galera_new_cluster' fails to bootstrap new cluster. (naturally all other bits to required are set with TLS) Suffices to take those out and cluster bootstrap okey, then, on such up&running cluster, I can on each node put those back in and, one by one, restart the service/server and... it works!? I filled a bug report - https://jira.mariadb.org/browse/MDEV-25435 - and will appreciate if somebody can comment. many thanks, L.

1 0

[Maria-discuss] Building mariadb without PCRE?
by Chris Ross (cross2) 21 Apr '21

21 Apr '21

Good day. We have been using MariaDB in our product for a while, and are upgrading to 10.5.8 at the moment. I’ve just noticed that it’s no longer using the PCRE on our system, it’s grabbing its own from outside since the upgrade to PCRE2. We’re not using PCRE extensions at all in our applications, and I’d rather not pull in another extraneous library. We are a largely embedded product, and adding libraries is a technical and bureaucratic effort. Can I just build mariadb without PCRE support at all, using the traditional extended POSIX regexps instead? I don’t see a way to do this in the cmake files/parameters. - Chris -- Chris Ross cross2(a)cisco.com

3 5

[Maria-discuss] Planning on setting up Postfix + Dovecot + Webmail + Apache + MariaDB + PHP using Webmin Control Panel in the Cloud
by Turritopsis Dohrnii Teo En Ming 21 Apr '21

21 Apr '21

Subject: Planning on setting up Postfix + Dovecot + Webmail + Apache + MariaDB + PHP using Webmin Control Panel in the Cloud Good day from the Republic of Singapore, I am planning to setup Postfix Email Server + Dovecot IMAP/POP3 Incoming Mail Server + Webmail (Roundcube or Squirrelmail) + Apache Web Server + MariaDB Database Server + PHP using Webmin Control Panel specifically, in the cloud, preferably Amazon EC2. Are there any very good and well written guides on doing this type of setup? I am looking forward to your reply. Thank you very much. Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 21 April 2021 Wed, is a TARGETED INDIVIDUAL (TI) living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

2 1

[Maria-discuss] mysql_upgrade fails
by Majed Zouhairy 21 Apr '21

21 Apr '21

in last time's upgrade and today i got: sudo mysql_upgrade -u root -p Enter password: Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1813 (HY000) at line 77: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 81: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 85: Tablespace for table '`mysql`.`transaction_registry`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 153: Tablespace for table '`mysql`.`gtid_slave_pos`' exists. Please DISCARD the tablespace before IMPORT ERROR 1146 (42S02) at line 642: Table 'mysql.innodb_index_stats' doesn't exist ERROR 1243 (HY000) at line 643: Unknown prepared statement handler (stmt) given to EXECUTE ERROR 1146 (42S02) at line 645: Table 'mysql.innodb_table_stats' doesn't exist ERROR 1243 (HY000) at line 646: Unknown prepared statement handler (stmt) given to EXECUTE ERROR 1146 (42S02) at line 650: Table 'mysql.innodb_index_stats' doesn't exist ERROR 1146 (42S02) at line 654: Table 'mysql.innodb_table_stats' doesn't exist ERROR 1146 (42S02) at line 657: Table 'mysql.innodb_table_stats' doesn't exist FATAL ERROR: Upgrade failed what to do?

2 1

[Maria-discuss] SST's TLS in Galera prevents bootstrapping a new cluster
by lejeczek 12 Apr '21

12 Apr '21

Hi guys. It's pretty strange, in fact so strange that I presume I must be messing up something rather that MariaDB/Galera behaving this way by design. Thus before I paste any log snippets this question first: Why having this - [sst] tkey = /etc/my.cnf.d/certs/sst/sst.key tcert = /etc/my.cnf.d/certs/sst/sst.crt - would prevent a user to bootstrap new cluster? If do not use tkey/tcert I can bootstrap fine and after, cluster is up & running I put those back, then very next restart of mariadb on any/each node will work just fine. What is happening there some expert would mind to shed more light? many thanks, L.

1 0

[Maria-discuss] Exceeding configured memory
by Sig Pam 12 Apr '21

12 Apr '21

Hi all! I run into problems with the memory configuration of MariaDB 10.3.27 on Debian 10. It looks as if it exceeds the configured memory. The server hat 4 GB of memory. It runs MariaDB and a Kopano (Mail/Collaboration) server. The configuration I believe I have made is 2GB for MariaDB, plus 1 GB for Kopano. The rest is for reserved for the OS. If I look at the process list, I can see that mysql greatly exceeds the configured 2GB limit: root@exchange64:~# ps -e -o "vsz rss pid comm" | grep mysql 4933304 1604212 852 mysqld The virtual process size is 4.9 GB, with 1.6 GB resident in RAM. I think this is the reason why the server heavily uses swap: root@exchange64:~# free total used free shared buff/cache available Mem: 4027240 2812284 117932 49712 1097024 875632 Swap: 4191228 1963464 2227764 <- 1.9 GB of swap in use The server runs only one database. The database itself consists only INNODB tables, with some of them being quite large: MariaDB [zarafa]> show table status; +-------------------+--------+---------+------------+---------+------------- ---+-------------+-----------------+--------------+-------------+----------- -----+---------------------+---------------------+------------+------------- -------+----------+-----------------------------------------+---------+----- -------------+-----------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary | +-------------------+--------+---------+------------+---------+------------- ---+-------------+-----------------+--------------+-------------+----------- -----+---------------------+---------------------+------------+------------- -------+----------+-----------------------------------------+---------+----- -------------+-----------+ | abchanges | InnoDB | 10 | Compact | 223 | 293 | 65536 | 0 | 98304 | 13429112832 | 57841 | 2019-10-02 08:01:57 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | acl | InnoDB | 10 | Compact | 134 | 122 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:01:57 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | changes | InnoDB | 10 | Compact | 3531055 | 136 | 483393536 | 0 | 693075968 | 13429112832 | 4601576 | 2019-10-02 08:06:06 | 2021-04-12 08:06:29 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | deferredupdate | InnoDB | 10 | Compact | 20038 | 116 | 2342912 | 0 | 442368 | 13429112832 | NULL | 2019-10-02 08:06:06 | 2021-04-12 08:06:29 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | hierarchy | InnoDB | 10 | Compact | 348880 | 61 | 21495808 | 0 | 12976128 | 13429112832 | 2361351 | 2019-10-02 08:06:07 | 2021-04-12 08:06:29 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | indexedproperties | InnoDB | 10 | Compact | 259399 | 103 | 26787840 | 0 | 25640960 | 13429112832 | NULL | 2019-10-02 08:06:10 | 2021-04-12 08:06:29 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | lob | InnoDB | 10 | Compact | 130069 | 175090 | 22773825536 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | 2021-04-12 08:05:53 | NULL | utf8mb4_general_ci | NULL | max_rows=1000000000 avg_row_length=1750 | | 0 | N | | mvproperties | InnoDB | 10 | Compact | 9703 | 158 | 1540096 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | 2021-04-12 06:58:44 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | names | InnoDB | 10 | Compact | 3283 | 69 | 229376 | 0 | 671744 | 13429112832 | 3294 | 2019-10-02 08:08:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | object | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 32768 | 13429112832 | 7 | 2019-10-02 08:08:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | objectmvproperty | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | objectproperty | InnoDB | 10 | Compact | 40 | 409 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | objectrelation | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | outgoingqueue | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:08:59 | 2021-04-12 07:20:40 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | properties | InnoDB | 10 | Compact | 9895527 | 414 | 4104863744 | 0 | 0 | 13429112832 | NULL | 2019-10-02 08:12:52 | 2021-04-12 08:18:25 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | receivefolder | InnoDB | 10 | Compact | 60 | 273 | 16384 | 0 | 16384 | 13429112832 | 209 | 2019-02-06 17:01:07 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | searchresults | InnoDB | 10 | Compact | 7404 | 161 | 1196032 | 0 | 0 | 13429112832 | NULL | 2019-02-06 17:01:08 | 2021-04-12 06:58:46 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | settings | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-02-06 17:07:00 | 2021-04-12 08:05:53 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | singleinstances | InnoDB | 10 | Compact | 93075 | 70 | 6553600 | 0 | 4472832 | 13429112832 | 789962 | 2019-02-06 17:01:09 | 2021-04-12 08:05:53 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | stores | InnoDB | 10 | Compact | 16 | 1024 | 16384 | 0 | 16384 | 13429112832 | 55 | 2019-02-06 17:01:09 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | syncedmessages | InnoDB | 10 | Compact | 31098 | 135 | 4210688 | 0 | 2113536 | 13429112832 | NULL | 2019-02-06 17:01:09 | 2021-04-12 08:08:07 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | syncs | InnoDB | 10 | Compact | 30813 | 85 | 2621440 | 0 | 5750784 | 13429112832 | 255042 | 2019-02-06 17:01:10 | 2021-04-12 08:18:24 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | tproperties | InnoDB | 10 | Compact | 8528065 | 103 | 881278976 | 0 | 319373312 | 13429112832 | NULL | 2019-02-06 17:06:59 | 2021-04-12 08:06:29 | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | usergroup_acl | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 13429112832 | NULL | 2018-11-08 18:13:26 | NULL | NULL | latin1_swedish_ci | NULL | | | 0 | N | | users | InnoDB | 10 | Compact | 24 | 682 | 16384 | 0 | 16384 | 13429112832 | 86 | 2019-02-06 17:06:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | versions | InnoDB | 10 | Compact | 88 | 186 | 16384 | 0 | 0 | 13429112832 | NULL | 2019-02-06 17:06:59 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | +-------------------+--------+---------+------------+---------+------------- ---+-------------+-----------------+--------------+-------------+----------- -----+---------------------+---------------------+------------+------------- -------+----------+-----------------------------------------+---------+----- -------------+-----------+ 26 rows in set (0.490 sec) These are the innodb variables: MariaDB [zarafa]> show variables like 'innodb%'; +---------------------------------------------+------------------------+ | Variable_name | Value | +---------------------------------------------+------------------------+ | 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 | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 2147483648 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | crc32 | | 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 | OFF | | 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_disallow_writes | 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 | 1 | | innodb_flush_method | fsync | | 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 | 200 | | 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 | 33554432 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 536870912 | | 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 | 75.000000 | | innodb_max_dirty_pages_pct_lwm | 0.000000 | | innodb_max_purge_lag | 0 | | 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 | 1000 | | innodb_optimize_fulltext_only | OFF | | innodb_page_cleaners | 4 | | 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 | 4 | | 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 | ON | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | 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.27 | | innodb_write_io_threads | 4 | +---------------------------------------------+------------------------+ 159 rows in set (0.217 sec) And for completeness, all variables (sorry, it's long and looks ugly): root@exchange64:/etc/mysql/conf.d# <mailto:root@exchange64:/etc/mysql/conf.d#> echo "show variables;" | mysql zarafa 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 ON aria_pagecache_age_threshold 300 aria_pagecache_buffer_size 134217728 aria_pagecache_division_limit 100 aria_pagecache_file_hash_size 512 aria_recover_options BACKUP,QUICK aria_repair_threads 1 aria_sort_buffer_size 268434432 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 80 basedir /usr big_tables OFF bind_address 127.0.0.1 binlog_annotate_row_events ON binlog_cache_size 32768 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 MIXED binlog_optimize_thread_scheduling ON binlog_row_image FULL binlog_stmt_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database latin1 character_set_filesystem binary character_set_results utf8mb4 character_set_server utf8mb4 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ check_constraint_checks ON collation_connection utf8mb4_general_ci collation_database latin1_swedish_ci collation_server utf8mb4_general_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 AUTO connect_timeout 10 core_file OFF datadir /var/lib/mysql/ 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 InnoDB default_tmp_storage_engine default_week_format 0 delay_key_write ON 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 10 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 general_log_file exchange64.log group_concat_max_len 1048576 gtid_binlog_pos gtid_binlog_state gtid_current_pos 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_openssl NO have_profiling YES have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES histogram_size 0 histogram_type SINGLE_PREC_HB host_cache_size 279 hostname exchange64 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 134217728 innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 25 innodb_buffer_pool_filename ib_buffer_pool innodb_buffer_pool_instances 8 innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_load_now OFF innodb_buffer_pool_size 2147483648 innodb_change_buffer_max_size 25 innodb_change_buffering all innodb_checksum_algorithm crc32 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 OFF 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_disallow_writes 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 1 innodb_flush_method fsync 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 200 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 33554432 innodb_log_checksums ON innodb_log_compressed_pages ON innodb_log_file_size 536870912 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 75.000000 innodb_max_dirty_pages_pct_lwm 0.000000 innodb_max_purge_lag 0 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 1000 innodb_optimize_fulltext_only OFF innodb_page_cleaners 4 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 4 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 ON innodb_temp_data_file_path ibtmp1:12M:autoextend innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 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.27 innodb_write_io_threads 4 insert_id 0 interactive_timeout 28800 join_buffer_size 262144 join_buffer_space_limit 2097152 join_cache_level 2 keep_files_on_create OFF key_buffer_size 134217728 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 key_cache_file_hash_size 512 key_cache_segments 0 large_files_support ON large_page_size 0 large_pages OFF last_gtid last_insert_id 0 lc_messages en_US lc_messages_dir /usr/share/mysql lc_time_names en_US license GPL local_infile ON lock_wait_timeout 86400 locked_in_memory OFF log_bin OFF log_bin_basename log_bin_compress OFF log_bin_compress_min_len 256 log_bin_index log_bin_trust_function_creators OFF log_disabled_statements sp log_error /var/log/mysql/error.log 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_filter admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan, query_cache,query_cache_miss,tmp_table,tmp_table_on_disk log_slow_rate_limit 1 log_slow_slave_statements ON log_slow_verbosity log_tc_size 24576 log_warnings 2 long_query_time 10.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 master_verify_checksum OFF max_allowed_packet 16777216 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 100 max_connections 151 max_delayed_threads 20 max_digest_length 1024 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_long_data_size 16777216 max_prepared_stmt_count 16382 max_recursive_iterations 4294967295 max_relay_log_size 1073741824 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 32 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 BACKUP,QUICK myisam_repair_threads 1 myisam_sort_buffer_size 134216704 myisam_stats_method NULLS_UNEQUAL myisam_use_mmap OFF 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 16186 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_selectivity_sampling_limit 100 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_in tersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=of f,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmat ch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_ma tch_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr _cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cac he=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optim ize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in= on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materi alized=on optimizer_use_condition_selectivity 1 performance_schema OFF performance_schema_accounts_size -1 performance_schema_digests_size -1 performance_schema_events_stages_history_long_size -1 performance_schema_events_stages_history_size -1 performance_schema_events_statements_history_long_size -1 performance_schema_events_statements_history_size -1 performance_schema_events_waits_history_long_size -1 performance_schema_events_waits_history_size -1 performance_schema_hosts_size -1 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances -1 performance_schema_max_digest_length 1024 performance_schema_max_file_classes 50 performance_schema_max_file_handles 32768 performance_schema_max_file_instances -1 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances -1 performance_schema_max_rwlock_classes 40 performance_schema_max_rwlock_instances -1 performance_schema_max_socket_classes 10 performance_schema_max_socket_instances -1 performance_schema_max_stage_classes 160 performance_schema_max_statement_classes 200 performance_schema_max_table_handles -1 performance_schema_max_table_instances -1 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances -1 performance_schema_session_connect_attrs_size -1 performance_schema_setup_actors_size 100 performance_schema_setup_objects_size 100 performance_schema_users_size -1 pid_file /run/mysqld/mysqld.pid plugin_dir /usr/lib/x86_64-linux-gnu/mariadb19/plugin/ plugin_maturity gamma port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 progress_report_time 5 protocol_version 10 proxy_protocol_networks proxy_user pseudo_slave_mode OFF pseudo_thread_id 10804 query_alloc_block_size 16384 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 16777216 query_cache_strip_comments OFF query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 24576 rand_seed1 27429069 rand_seed2 533006049 range_alloc_block_size 4096 read_binlog_speed_limit 0 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 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 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_system_variables autocommit,character_set_client,character_set_connection,character_set_resul ts,time_zone session_track_transaction_info OFF skip_external_locking ON skip_name_resolve OFF 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 OFF slow_query_log_file exchange64-slow.log socket /run/mysqld/mysqld.sock sort_buffer_size 2097152 sql_auto_is_null OFF sql_big_selects ON sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE _SUBSTITUTION sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter 0 sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_crl ssl_crlpath ssl_key standard_compliant_cte ON storage_engine InnoDB stored_program_cache 256 strict_password_validation ON sync_binlog 0 sync_frm ON sync_master_info 10000 sync_relay_log 10000 sync_relay_log_info 10000 system_time_zone CEST system_versioning_alter_history ERROR system_versioning_asof DEFAULT table_definition_cache 400 table_open_cache 1000 table_open_cache_instances 8 tcp_keepalive_interval 0 tcp_keepalive_probes 0 tcp_keepalive_time 0 thread_cache_size 151 thread_concurrency 10 thread_handling one-thread-per-connection thread_pool_idle_timeout 60 thread_pool_max_threads 65536 thread_pool_oversubscribe 3 thread_pool_prio_kickup_timer 1000 thread_pool_priority auto thread_pool_size 4 thread_pool_stall_limit 500 thread_stack 299008 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1618208688.935216 tmp_disk_table_size 18446744073709551615 tmp_memory_table_size 16777216 tmp_table_size 16777216 tmpdir /tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ tx_read_only OFF unique_checks ON updatable_views_with_limit YES use_stat_tables NEVER userstat OFF version 10.3.27-MariaDB-0+deb10u1 version_comment Debian 10 version_compile_machine x86_64 version_compile_os debian-linux-gnu version_malloc_library system version_source_revision dba846ce2a4c57363c4f0256b0e6d2dd1a55ac40 version_ssl_library YaSSL 2.4.4 wait_timeout 28800 warning_count 0 wsrep_osu_method TOI wsrep_auto_increment_control ON wsrep_causal_reads OFF wsrep_certification_rules strict wsrep_certify_nonpk ON wsrep_cluster_address wsrep_cluster_name my_wsrep_cluster wsrep_convert_lock_to_trx OFF wsrep_data_home_dir /var/lib/mysql/ wsrep_dbug_option wsrep_debug OFF wsrep_desync OFF wsrep_dirty_reads OFF wsrep_drupal_282555_workaround OFF wsrep_forced_binlog_format NONE wsrep_gtid_domain_id 0 wsrep_gtid_mode OFF wsrep_load_data_splitting ON wsrep_log_conflicts OFF wsrep_max_ws_rows 0 wsrep_max_ws_size 2147483647 wsrep_mysql_replication_bundle 0 wsrep_node_address wsrep_node_incoming_address AUTO wsrep_node_name exchange64 wsrep_notify_cmd wsrep_on OFF wsrep_patch_version wsrep_25.24 wsrep_provider none wsrep_provider_options wsrep_recover OFF wsrep_reject_queries NONE wsrep_replicate_myisam OFF wsrep_restart_slave OFF wsrep_retry_autocommit 1 wsrep_slave_fk_checks ON wsrep_slave_uk_checks OFF wsrep_slave_threads 1 wsrep_sst_auth wsrep_sst_donor wsrep_sst_donor_rejects_queries OFF wsrep_sst_method rsync wsrep_sst_receive_address AUTO wsrep_start_position 00000000-0000-0000-0000-000000000000:-1 wsrep_sync_wait 0 Can somebody please give me an advice what I made wrong to limit the memory usage of MariaDB to 2 GB? Thank you very much! Sig

2 1

[Maria-discuss] MEMORY tables and binlog
by Dajka Tamás 07 Apr '21

07 Apr '21

Hi All, I'm facing a weird error. We're migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster's master. When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid - containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is - let's say - corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first). new-node01/02 running 10.3.27-MariaDB-0+deb10u1-log on latest Debian 10.9 (replication from old-node is OK), replication is GTID based. HW is a new HP Gen10 DL380. When checking the binlog on the new-node01 with mysqlbinlog, I get the following: # at 2361 #210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans /*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/; START TRANSACTION /*!*/; # at 2403 #210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id=341297541 exec_time=318 error_code=0 SET TIMESTAMP=1617786814/*!*/; DELETE FROM `mydb1`.`collect_output`e<FB>#<98> /*!*/; # at 2527 #210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`.`collect_output` mapped to number 1330 Please notice the illegal chars at the end of the "DELETE FROM" line. (DELETE FROM is normal, since new-node01 is starting up from shutdown and mydb1.collect_output is a MEMORY table -> will delete the contents, so that the replications slaves have the same data). Is this a bug or a known issue? Is there any other way to solve this, than exclude the MEMORY tables from the replication? Thanks, Tom

5 7

[Maria-discuss] ODBC picking nearby connections?
by Antony Stone 07 Apr '21

07 Apr '21

Hi. I have the following setup: Two data centres which I shall label North and South for the following explanation. Four virtual machines, Whiskey and Xray in data centre North, and Yankee and Zulu in data centre South, which have the Debian package unixodbc installed, together with libmaodbc. Four other virtual machines, Alfa and Bravo in North and Charlie and Delta in South, all running MariaDB with Galera replication. A single hostname DB.example.com which resolves to the four IP addresses of the four database servers Alfa, Bravo, Charlie and Delta. Every instance of /etc/odbc.ini on machines Whiskey, Xray, Yankee and Zulu has the setting "SERVER=DB.example.com" in it. When I start an application which uses ODBC (in my case this is Asterisk), I find that each application server manages to connect to a database server in its own data centre. Given a 50% chance of this happening for machine Whiskey, a 50% chance for machine Xray, etc., this comes out as a 1 in 16 combined probability of happening by chance. Therefore I think it is not by chance :) I've done some packet captures, and I see each machine Whiskey, Xray, Yankee and Zulu doing a single DNS query for DB.example.com and getting the four IP addresses of Alfa, Bravo, Chalie and Delta returned. As expected, the order the addresses is returned in is randomised for different queries. Each machine then makes a single connection to MariaDB on port 3306 on one of the IP addresses in the list. This address can be the first in the list returned from the DNS resolver, or it can the the last one, or it can be on in the middle. There is no attempt to ping the four addresses and compare round-trip times, or to connect to MariaDB on more than one server and measure the query response times. Each applicaton server connects to one database server, and they always manage to connect to a machine in their own data centre. My question is: how do they do this? How does machine Whiskey manage to select one of the two IP addresses for database servers Alfa and Bravo, and connect to it, ignoring the addresses for Charlie and Delta? If I can supply any further information to help with answering this question, please let me know. Thanks, Antony. -- "Have you been drinking brake fluid again? I think you're addicted to the stuff." "No, no, it's alright - I can stop any time I want to." Please reply to the list; please *don't* CC me.

1 0

[Maria-discuss] Galera with TLS not happy.
by lejeczek 30 Mar '21

30 Mar '21

Hi guys. I'm trying to add encryption to my already working galera cluster and I've look at number of tutorials, official ones included. I've added these to configs: [mariadb] ssl_cert = /etc/my.cnf.d/certs/c8kubernode2.private.pawel.crt ssl_key = /etc/my.cnf.d/certs/c8kubernode2.private.pawel.key ssl_ca = /etc/my.cnf.d/certs/ca.crt [mysqld] wsrep_provider_options="socket.ssl=yes;socket.ssl_cert=/etc/my.cnf.d/certs/c8kubernode2.private.pawel.crt;socket.ssl_key=/etc/my.cnf.d/certs/c8kubernode2.private.pawel.key;socket.ssl_ca=/etc/my.cnf.d/certs/ca.crt" First server, above configs, starts okey with 'galera_new_cluster' but the second, I'm on Centos 8, when started as normal with systemd shows: ... 2021-03-29 17:33:34 0 [ERROR] WSREP: gcomm/src/asio_tcp.cpp:handshake_handler():128: handshake with remote endpoint ssl://10.1.1.223:4567 failed: asio.ssl:337047686: 'certificate verify failed' ( 337047686: 'error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed') 2021-03-29 17:33:36 0 [ERROR] WSREP: gcomm/src/asio_tcp.cpp:handshake_handler():128: handshake with remote endpoint ssl://10.1.1.223:4567 failed: asio.ssl:337047686: 'certificate verify failed' ( 337047686: 'error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed') ... and eventually after a moment fails. The second server has the same bits in configs only, naturally, files names are different respectively. I also see this, in case might tell more or be relevant, this is on 'galera_new_cluster' up & running: -> $ mysql --ssl -h c8kubernode2.private.pawel -u wordpress -p --ssl-verify-server-cert=true Enter password: ERROR 2026 (HY000): SSL connection error: self signed certificate in certificate chain -> $ mysql --ssl -h c8kubernode2.private.pawel -u wordpress -p --ssl-verify-server-cert=false Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. But if this is 'CN' problem then looking at the mysql server cert: -> $ _my._sslPrintCert.sh c8kubernode2.private.pawel.crt issuer=CN = "nodemaster.private.pawel," # <= here, it matches server's hostname as expected. subject=CN = c8kubernode2.private.pawel notAfter=Jul 2 20:50:57 2023 GMT Certificate: ... Also, in case it might matter, I do not have as you can see [sst] bits done yet. Any ideas someone cares to share I'll appreciate. many thanks, L.

1 0

[Maria-discuss] Introduction and Task Suggestion
by Hemant Kumar Singh 22 Mar '21

22 Mar '21

Hi everyone, I am Hemant from Nepal, currently a Computer Science Junior at NIT Warangal, India willing to take part in GSoC 2021. I saw a major priority task "MDEV-13648- Add FULL OUTER JOIN to MariaDB (https://link.getmailspring.com/link/F02B618E-9401-406B-BD92-A573A39AD858@ge…)" unresolved since gsoc 2019; but it's not listed in 2021 List of Ideas. So, I was just wondering whether I can contribute to this task for gsoc 2021. Can anyone confirm this? Regards, Hemant Kumar Singh https://iamhks.com (https://link.getmailspring.com/link/F02B618E-9401-406B-BD92-A573A39AD858@ge…)

3 3

[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

[Maria-discuss] Issue authenticating as mysql in 10.5 on Ubuntu 20.04
by Alexander Nolting 11 Jan '21

11 Jan '21

Hi all, This was probably already discussed. I did a fresh 20.04 and MariaDB 10.5.8 install and now I'm having a question related to the new authentication concept starting from 10.4. As stated at Authentication from MariaDB 10.4 and Authentication in MariaDB 10.4 — Understanding the Changes there are two administrative accounts created: root and mysql. As I understand using both account sockets. Logging as normal user as root into mariadb using unix_sockets is no problem: testusr@host:~$ sudo mariadb -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-MariaDB-1:10.5.8+maria~focal mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Ctrl-C -- exit! Aborted Doing the same with as mysql testusr@host:~$ sudo mariadb -u mysql returns ERROR 1698 (28000): Access denied for user 'mysql'@'localhost'. If someone can point me to a source detailing me what I may not have understood - any help is appreciated. --- Best Alex

2 2

[Maria-discuss] MariaDB Server MiniFest Dec 9 14h00 CET (UTC+1)
by Ian Gilfillan 22 Dec '20

22 Dec '20

The MariaDB Server MiniFest is scheduled to start on December 9th 2020, at 14:00 UTC+1. The main topic for the MiniFest is MariaDB Server Releases, their quality, frequency and security. You will get a view both from a developer perspective and from a user perspective when it comes to new MariaDB Server releases and we will conclude with a Panel discussing the topic. Details at: https://mariadb.org/minifest2020/ Hope to see you there!

1 1

[Maria-discuss] Galera replication - optimistic locking problems
by Antony Stone 16 Dec '20

16 Dec '20

Hi. I hope this is a reasonable place to ask about Galera replication problems - if not, please point me elsewhere and I'll happily ask others :) I have four Debian 10 "Buster" servers installed with MariaDB 10.1.47 and Galera 25.3.19-2. I have other servers which write to these machines (there's no single "master", since I don't want any particular client to depend on any single master being available), and also read from them (in case it matters, those machines are running Asterisk, and writing Call Event Logs and Call Detail Records into MariaDB via ODBC). My problem is the optimistic locking used by Galera when replicating between nodes. It results in Asterisk log file entries such as: WARNING[20089]: res_odbc.c:460 in ast_odbc_print_errors: SQL Execute returned an error: 40001: [ma-3.0.6][10.1.47-MariaDB-0+deb9u1]Deadlock found when trying to get lock; try restarting transaction Now, that's all very well for an application which can respond to such a warning (personally I'd call it an error, when a write did not take place, but that's a question for Asterisk, not MariaDB) and perform the retry, but Asterisk does not do this. Therefore I'd like to find some way to: 1. tell Galera to use pessimistic locking for replication if possible (I can accept the performance penalty) 2. tell MariaDB to automatically retry the write when the error occurs (although I can't think of any way that could be done, since I can't create a transaction of any sort - the operation is entirely determined for me by Asterisk) 3. find a High-Availability (which basically means no single point of failure) front-end for the whole cluster of 4 MariaDB servers so that the problem does not occur. Questions: 1. How do other people deal with this problem? 2. Are any of my potential solutions above actually feasible? (If so, how?) 3. Does anyone have any alternative ideas about how to connect an application which doesn't understand retrying database writes (Asterisk) with a database which doesn't guarantee to write the data you give it (MariaDB + Galera)? Thanks, Antony. -- Schrödinger's rule of data integrity: the condition of any backup is unknown until a restore is attempted. Please reply to the list; please *don't* CC me.

3 4

Re: [Maria-discuss] Is GET_LOCK() & friends safe when using MIXED mode replication?
by pslawek83 08 Dec '20

08 Dec '20

I think what the documentation is trying to say is that you cannot get VALUE of GET_LOCK(...) inside insert, update, etc. because in statement based replication that VALUE will be obtained both on the master and then again, on the replica itself. So it would probably create data discrepancy between master and slave. INSERT INTO mytable VALUES(GET_LOCK(...)) with replication in STATEMENT mode, you can get eg. "1" inserted to your db on the master and "0" on the slave. True for functions which are non-deterministic, while GET_LOCK is deterministic, we could say is not deterministic on the slave because slave could hold different locks so you can be sure what it'll return on the master but you can't be sure what it'll return on the slave AFTER it gets read from replication log. a. It's of course safe to do in your case, it isn't even DB related as you're not storing the returned value in the DB so the replica can't get out of sync because of that. b. correct Best, S. Dnia 8 grudnia 2020 10:28 Jan Zankowski <jan.zankowski(a)gmail.com> napisał(a): Hello, I'd like to use GET_LOCK() and RELEASE_LOCK() on MariaDB 10.2 to implement basic distributed locking, to prevent simultaneous runs of a job unrelated to the DB. I see however in the mariadb.com MariaDB docs that: "Statements using the GET_LOCK function are not safe for statement-based replication." I also see in dev.mysql.com MySQL docs that this is likely only for statements such as "INSERT INTO mytable VALUES(GET_LOCK(...))." Full excerpt from MySQL docs: The following restriction applies to statement-based replication only, not to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the replica knowing the concurrency context on the source. Therefore, these functions should not be used to insert into a source table because the content on the replica would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)). These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. Could someone please confirm that: (a) We will be safe using GET_LOCK() & RELEASE_LOCK() in MIXED mode. (b) If we used STATEMENT mode, the only case of not being safe is when the return value of GET_LOCK() etc. is directly involved in some other DB operations (e.g. inserted into a table). In particular, the use case when the locks are used only to prevent simultaneous runs of an external job should be fine. I strongly suspect this is not what "unsafe" means in this context, but not being an expert on replication, what I'm really worried about is that our replication would simply blow up or get totally out of sync if I used GET_LOCK(). Please calm these fears if you can. :) Thanks! Jan ______________________________ Mailing list: launchpad.net launchpad.net Post to : maria-discuss(a)lists.launchpad. Unsubscribe : launchpad.net launchpad.net More help : help.launchpad.net help.launchpad.net

1 0

[Maria-discuss] Is GET_LOCK() & friends safe when using MIXED mode replication?
by Jan Zankowski 08 Dec '20

08 Dec '20

Hello, I'd like to use GET_LOCK() and RELEASE_LOCK() on MariaDB 10.2 to implement basic distributed locking, to prevent simultaneous runs of a job unrelated to the DB. I see however in the MariaDB docs <https://mariadb.com/kb/en/get_lock/> that: "Statements using the GET_LOCK function are not safe for statement-based replication." I also see in MySQL docs <https://dev.mysql.com/doc/refman/5.7/en/replication-features-functions.html> that this is likely only for statements such as "INSERT INTO mytable VALUES(GET_LOCK(...))." Full excerpt from MySQL docs: *The following restriction applies to statement-based replication only, not to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the replica knowing the concurrency context on the source. Therefore, these functions should not be used to insert into a source table because the content on the replica would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)). These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode.* Could someone please confirm that: (a) We will be safe using GET_LOCK() & RELEASE_LOCK() in MIXED mode. (b) If we used STATEMENT mode, the only case of not being safe is when the return value of GET_LOCK() etc. is directly involved in some other DB operations (e.g. inserted into a table). In particular, the use case when the locks are used only to prevent simultaneous runs of an external job should be fine. I strongly suspect this is not what "unsafe" means in this context, but not being an expert on replication, what I'm really worried about is that our replication would simply blow up or get totally out of sync if I used GET_LOCK(). Please calm these fears if you can. :) Thanks! Jan

1 0

[Maria-discuss] Remove everything except email addresses in from_addr table in mariaDB
by Indunil Jayasooriya 27 Nov '20

27 Nov '20

Hi list, I am running MariaDB Server version: 10.4.17-MariaDB in Cent OS7. I have a column called from_addr from table msgs. Plz see its OUTPUT. select from_addr from msgs; +----------------------------------------------------------------+ | from_addr | +----------------------------------------------------------------+ | admin(a)sender.com | | userl(a)mysys.net | | Firstname Lastname <user622(a)yahoo.com> | | userl(a)mysys.net | | Firstname Lastname <user75(a)gmail.com> | | root(a)domain.net (root) | | userl(a)mysys.net | | userl(a)mysys.net | | Firstname Lastname <user75(a)gmail.com> | | userl(a)mysys.net | +--------------------------------------------------------------+ When Users send mails this from_addr column fills. some times with just email addresses, Some times email address with Display names. What I expect is to remove all the display name part. I only need email addresses. I expect from_addr column in this way. +--------------------------------------------+ | from_addr | +--------------------------------------------+ | admin(a)sender.com | | userl(a)mysys.net | | user622(a)yahoo.com | | userl(a)mysys.net | | user75(a)gmail.com | | root(a)domain.net | | userl(a)mysys.net | | userl(a)mysys.net | | user75(a)gmail.com | | userl(a)mysys.net | +--------------------------------------------+ How can I solve it. I tried a lot. But, I could NOT get what I expect. finally I subscribed to mariaDB mailing list. I need your INPUTS. -- cat /etc/motd Thank you Indunil Jayasooriya http://www.theravadanet.net/

2 1

[Maria-discuss] MaxScale 2.5 - [mariadbclient] (send_authetication_error)
by Micha Ballmann 23 Nov '20

23 Nov '20

Hi @all, I have a problem and I stuck with MaxScale 2.5. I deployed a 5 node galeracluster and installed MaxScale as proxy. First i thought everthing is fine. I can monitor and connect each galera node but i have a strange problem by connecting a client through the proxy. I have a media wiki db and user. user: wiki dbname: wiki permissions: grant all host: wiki@% I can successful connect the dabatabse from my client server on cli like: # mysql -u wiki -p jlkadjfg78 -h maxscale.example.com -> this ist working. Now when configuring the database in media wiki my proxy throws the following error: Nov 20 16:17:45 maxscale maxscale[12714]: (95) [mariadbclient] (process_authentication): User accounts have been recently updated, cannot update again for 'wiki'@'<IP.-from-media-wiki>'. Nov 20 16:17:45 maxscale maxscale[12714]: (95) [mariadbclient] (send_authetication_error): Authentication failed for user 'wiki'@['<IP.-from-media-wiki>] to service 'Galera-Service'. Originating listener: 'Galera-Listener'. MariaDB error: 'Access denied for user 'wiki'@''<IP.-from-media-wiki>' to database 'wiki''. I dont know why access denied. I can connect with the local mysql client from the media wiki server. I checked the grants of my maxscale "admin" user (https://mariadb.com/kb/en/maxscale-troubleshooting/) This is my maxscale config: ################## # Globals [maxscale] threads = auto syslog = 1 maxlog = 1 log_augmentation = 1 ms_timestamp = 1 # Servers [maria1] type = server address = 10.20.105.224 port = 3306 protocol = MariaDBBackend ... # Galera Monitor [Galera-Monitor] type = monitor module = galeramon servers = maria1,maria2,maria3,maria4,maria5 user =maxscale password =passwd monitor_interval= 2000 # Galera Router [Galera-Service] type = service router = readwritesplit servers = maria1,maria2,maria3,maria4,maria5 user = maxscale password = passwd # Galera Listener [Galera-Listener] type = listener service = Galera-Service protocol = MariaDBClient port = 3306 address = 0.0.0.0 ################## I hope you can help. Feel free to ask for information. All servers are running on Ubuntu 20.04 - latest mariadb and maxscale. Br fettfoen

2 1

[Maria-discuss] Increase in IOPS between 5.5 and 10.4
by martin doc 14 Nov '20

14 Nov '20

Recently upgraded a cluster of MariaDB servers from 5.5 to 10.4 and with grafana, I can see that all of the 10.4 instances use about 20% IOPS for the same workload. i.e, if inserting 100,000 rows took 200,000 IOPs with 5.5 then on 10.4 it takes 240,000. Has anyone else noticed this? Or know how to tune MariaDB to get better IOPS out of 10.4?

2 2

Re: [Maria-discuss] Using SSH keys for authenticating MariaDB users? (pam_ssh module or ed25519)
by Otto Kekäläinen 20 Oct '20

20 Oct '20

> > PS. The die links from the wiki seem a bit outdated. I would recommend > > > linking directly to a source that inherits its contents from fresh > > > Linux distro packages, such as > > > https://manpages.debian.org/unstable/libpam-ssh/pam_ssh.8.en.html or > > > https://manpages.ubuntu.com/manpages/focal/en/man8/pam_ssh.8.html > > > > > > > > This seems like a good suggestion, anybody object? > Yes but there are lot's of other die.net links in the page so maybe we > should change them all then. Also regarding pam_unix manual, this seems > to be the source (but there is no pam_ssh man on that site): > http://linux-pam.org/Linux-PAM-html/sag-pam_unix.html Yes, I suggest so. Seems die.net has been stagnant since 2017. I prefer the distro sites or man7.org by Michael Kerrisk.

1 0

[Maria-discuss] Using SSH keys for authenticating MariaDB users? (pam_ssh module or ed25519)
by Otto Kekäläinen 20 Oct '20

20 Oct '20

Hello! Has anybody successfully set up MariaDB authentication using SSH keys? I've tried but can't figure out how to get the client part working.. On https://mariadb.com/kb/en/authentication-plugin-pam/ it is stated that using the PAM module one can benefit of the pam_ssh PAM module: > Authentication using SSH keys. See the pam_ssh PAM module. This got me thinking that it might be possible. I installed the module in MariaDB, installed the PAM module on the system, configured the MariaDB server part and added a user but then ran into a wall when trying to authenticate. There does not seem to be any way to tell the mariadb client that "send this SSH key to server", it always wants to use a password. It got me thinking, that since the mariadb client does not seem to support anything else than dialog and password, maybe "Authentication using SSH keys" isn't actually possible yet? Should the documentation in the wiki be updated a bit to outline what is actually possible now, and what is potentially in the future? Here are my configs global_priv user localhost@: {"access":0,"ssl_type":0,"ssl_cipher":"","x509_issuer":"","x509_subject":"","max_questions":0,"max_updates":0,"max_connections":0,"max_user_connections":0,"max_statement_time":0.000000,"plugin":"pam","authentication_string":"mariadb","password_last_changed":1602657078,"password_lifetime":-1,"account_locked":false,"default_role":"","is_role":false} # mysql -e "show plugins" | grep pam pam ACTIVE AUTHENTICATION auth_pam.so GPL # cat /etc/mysql/conf.d/pam-ssh.cnf [mariadb] plugin_load_add = auth_pam # cat /etc/pam.d/mariadb auth required pam_ssh.so audit account required pam_ssh.so audit On https://mariadb.com/kb/en/authentication-plugin-ed25519/ there is also documentation about the plugin that stores passwords in the ed25519 format. My SSH keys are using ed25519 - can this auth plugin be (mis)used so that I store the public key in the MariaDB user database as a password somehow and then send in as username/password some proof signed by my private key? Thus essentially using SSH keys and the same authentication, but without invoking any SSH? PS. The die links from the wiki seem a bit outdated. I would recommend linking directly to a source that inherits its contents from fresh Linux distro packages, such as https://manpages.debian.org/unstable/libpam-ssh/pam_ssh.8.en.html or https://manpages.ubuntu.com/manpages/focal/en/man8/pam_ssh.8.html

2 2

[Maria-discuss] How to write streaming data to mariaDB columnStorage efficiently？
by wanglei2＠geekplus.com 16 Oct '20

16 Oct '20

I just have a try. CREATE TABLE `robotmetrics2` ( `robotid` varchar(16) DEFAULT NULL, `ts` bigint(20) DEFAULT NULL, `xloc` int(11) DEFAULT NULL, `yloc` int(11) DEFAULT NULL, `velocity` int(11) DEFAULT NULL, `angular_velocity` int(11) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8 But when i write data to the table with python connector, the qps is only about 10/s. And the cpu is 60%+. And it takes a lot of disk space. Mainly this file: /var/lib/columnstore/data1/versionbuffer.cdf Does i do something wrong? Thanks, Lei wanglei2(a)geekplus.com

1 0

[Maria-discuss] InnoDB: background jobs & fsyncs
by Julien Muchembled 21 Sep '20

21 Sep '20

Hello, We are evaluating new hardware by reproducing real-life workload on real-life data which works fine on an existing server. We copied over the software used to ensure an apple-to-apple comparison, which is based on MariaDB 10.2.23 and uses primarily InnoDB tables, with a few Mroonga tables which do not seem involved in the problem. On the new hardware, we are seeing catastrophically bad performance, especially chain-deadlocks happening and being resolved but producing virtually no useful work at our usual parallelism level of 64 active connections. If we reduce parallelism to 2 active connections, queries start to succeed in any meaningful proportion. The database was freshly restored from a mysqldump (as we cannot interrupt the original database), could this have any effect on deadlocks ? The main tables involved in the deadlocking queries (job queues) are initially (in the test, and periodically in reality) empty, so it would seem surprising. What should I check to debug further ? Note that these deadlocks happen in production but they're negligible. See attachment for mariadb configuration file. Some more details: When we restore from mysqldump, we use the following extra options to speed things up: innodb_flush_log_at_trx_commit = 0 innodb_flush_method = nosync innodb_doublewrite = 0 sync_frm = 0 as during that period nothing of value can be lost (worst case we restart the restore from scratch). BTW, despite these settings, we are still noticing a lot of fsyncs. Is this expected ? Are we missing some other option ? Also, after the import and without any connection, MariaDB was still producing a non-trivial amount of activity on the machine: 5% CPU, read <1MB/s, write 10MB/s, 60 fsync/s. I could not identify what is causing these, where should I look ? How can I tell when it will stabilise back to idle ? When I interrupt the benchmark workload, I see a similar resource usage. I saw https://jira.mariadb.org/browse/MDEV-18698 And InnoDB seems to throttle its background activity: is there a way to tell InnoDB to perform its background tasks at maximum speed ? (the goal is that after the import, we can make a clean tarball that we extract when we want to launch the test case again) Regards, Julien

3 3

[Maria-discuss] How to replicate TO a HA pair of MariaDB servers?
by martin doc 16 Sep '20

16 Sep '20

Hi, I've got a bit of a different question regarding MariaDB replication. But first, let me say that I'm familiar with both master-master and master-slave replication. What I'm after advice on is how to replicate tables from a number of single host instances of MariaDB to a single HA pair of MariaDB servers (HA-1/HA-2) that have a VIP that moves between them. One way of looking at this might be to say that the single host instances of MariaDB are satellite servers that I need to pull table data back into a primary pair. Kind of like a backup. I've tried to think of how I could do this using what I know. Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)? The idea is that the replication would fail from HA-1 when the VIP is on HA-2. In this case the single-host masters would only allow replication connections from the VIP. Or the connection out from the slave to the master would fail to bind to the VIP (and thus fail to connect to the master) if the VIP was not on a local interface. Of course I want both servers (HA-1/HA-2) in the redundant pool to be able to serve queries, so I'm not too keen on setting the bind address in [mysqld] to be the VIP and using net.ipv4.ip_nonlocal_bind - but I could be open to that. Is it possible to specify a bind address for replication that is separate to that used in [msyqld]? Are there other ways to solve this problems? Cheers.

3 7

[Maria-discuss] MariaDB Server Fest 14-20 September
by Ian Gilfillan 13 Sep '20

13 Sep '20

1 0

Re: [Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5
by Chris Ross (cross2) 13 Sep '20

13 Sep '20

Following up, with some more specifics... After configuring with below options, we have the first `maria*config` find(1) results, and then running “make” yields the expected binary : % find . -name maria\*config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config <make> % find . -name maria\*config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config Then, a “make DESTDIR=destdir install” yields the following: ./destdir/usr/bin/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config And, after no other changes, rerunning the exact same make install command produces the following. Clearly, something is wrong with the make install process. So, should the first install create the sym-link, but isn’t? Or, is it configured in some way that the sym-link shouldn’t be created, and it is, but only when running make install twice? ./destdir/usr/bin/mariadb_config ./destdir/usr/bin/mariadb-config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config % ls -l destdir/usr/bin/maria*config -rwxr-xr-x 1 cross2 5011 25936 Sep 11 14:05 destdir/usr/bin/mariadb_config lrwxrwxrwx 1 cross2 5011 14 Sep 11 14:56 destdir/usr/bin/mariadb-config -> mariadb_config Please let me know. This looks like a bug in the build/install process, but I want to know how to work around it on my side while the fix is developed. Thank you! - Chris From: Maria-discuss <maria-discuss-bounces+cross2=cisco.com(a)lists.launchpad.net> on behalf of "Chris Ross (cross2)" <cross2(a)cisco.com> Date: Friday, September 11, 2020 at 08:42 To: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Subject: [Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5 Hello there friends. We have recently moved our product to MariaDB 10.5.5, and someone on my team noticed an oddity that occurs in some build processes. The product has a complicated make-based build system, and many phases including “make src” and “make build” which pre-req’s the “src” target, and includes packaging. The dev work to integrate mariadb used “make build”, which works. Someone found unexpectedly this morning, that if we perform a “make src ; make build” we get a different outcome. There is a sym-link created by the install target lrwxrwxrwx 1 cross2 5011 14 Sep 11 08:30 ./destdir/usr/bin/mariadb-config -> mariadb_config that is _not_ created in the first build. The first build, which given the dependencies of other parts of our product needing mariadb libraries to build, performs the destdir install (via “make install” in mariadb). And, that includes mariadb_config, but _not_ mariadb-config. Can someone help me figure out where that sym-link is created, and why it seems to only be created when maria’s “make install” is run twice? - Chris Cmake config: CONF_OPTIONS:=-DBUILD_CONFIG=mysql_release \ -DAWS_SDK_EXTERNAL_PROJECT=OFF \ -DCMAKE_INSTALL_PREFIX:PATH=${EXTRAPREFIX}/usr \ -DINSTALL_LAYOUT=RPM \ -DINSTALL_PLUGINDIR=lib64/plugin \ -DINSTALL_SYSCONFDIR="" \ -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO \ -DWITH_JEMALLOC=OFF \ -DWITH_PCRE=auto \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO \ -DPLUGIN_AUTH_TEST_PLUGIN=NO \ -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO \ -DPLUGIN_TEST_SQL_DISCOVERY=NO \ -DPLUGIN_TEST_VERSIONING=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO \ -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF \ -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_ROCKSDB=NO CONF_OPTIONS += -DSKIP_TESTS=ON -DWITH_UNIT_TESTS=NO -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

2 3

[Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5
by Chris Ross (cross2) 11 Sep '20

11 Sep '20

Hello there friends. We have recently moved our product to MariaDB 10.5.5, and someone on my team noticed an oddity that occurs in some build processes. The product has a complicated make-based build system, and many phases including “make src” and “make build” which pre-req’s the “src” target, and includes packaging. The dev work to integrate mariadb used “make build”, which works. Someone found unexpectedly this morning, that if we perform a “make src ; make build” we get a different outcome. There is a sym-link created by the install target lrwxrwxrwx 1 cross2 5011 14 Sep 11 08:30 ./destdir/usr/bin/mariadb-config -> mariadb_config that is _not_ created in the first build. The first build, which given the dependencies of other parts of our product needing mariadb libraries to build, performs the destdir install (via “make install” in mariadb). And, that includes mariadb_config, but _not_ mariadb-config. Can someone help me figure out where that sym-link is created, and why it seems to only be created when maria’s “make install” is run twice? - Chris Cmake config: CONF_OPTIONS:=-DBUILD_CONFIG=mysql_release \ -DAWS_SDK_EXTERNAL_PROJECT=OFF \ -DCMAKE_INSTALL_PREFIX:PATH=${EXTRAPREFIX}/usr \ -DINSTALL_LAYOUT=RPM \ -DINSTALL_PLUGINDIR=lib64/plugin \ -DINSTALL_SYSCONFDIR="" \ -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO \ -DWITH_JEMALLOC=OFF \ -DWITH_PCRE=auto \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO \ -DPLUGIN_AUTH_TEST_PLUGIN=NO \ -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO \ -DPLUGIN_TEST_SQL_DISCOVERY=NO \ -DPLUGIN_TEST_VERSIONING=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO \ -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF \ -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_ROCKSDB=NO CONF_OPTIONS += -DSKIP_TESTS=ON -DWITH_UNIT_TESTS=NO -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

1 0

[Maria-discuss] Set password for all users, regardless of host value
by Chris Ross (cross2) 08 Sep '20

08 Sep '20

Hello there. We have scripts to restore credentials to MySQL databases from external store. The mechanism that was in use, however, stores usernames and passwords, without consideration of the scope (host) of that auth record. In older systems, UPDATE mysql.user SET password = PASSWORD(‘rawpassword’) WHERE user = ‘username’ worked, updating it for all values of that user that might exist in the table. But, I’m not sure how to do this for MariaDB 10.5. Is there way to form an “ALTER USER” statement such that it will set the password for any and all userspecs that exist with the given username? We don’t have that many, and I could iterate the known configurations with “ALTER USER IF EXISTS”, but I worry that might miss things added in the future. Thank you. - Chris -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

4 8

[Maria-discuss] How to Migrate Wordpress Website from 32-bit CentOS Linux 6.3 to 64-bit CentOS Linux 8.2 (2004)
by Turritopsis Dohrnii Teo En Ming 31 Aug '20

31 Aug '20

Subject: How to Migrate Wordpress Website from 32-bit CentOS Linux 6.3 to 64-bit CentOS Linux 8.2 (2004) Author of this Guide: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 31 August 2020 Monday Singapore Time Type of Publication: Plain Text Document Version: 20200831.01 Redundant links to my guide: [1] https://lkml.org/lkml/2020/8/31/454 [2] http://lkml.iu.edu/hypermail/linux/kernel/2008.3/09047.html [3] https://marc.info/?l=linux-kernel&m=159888024529073&w=2 [4] https://lwn.net/ml/linux-kernel/70ce3070fa816dbbe2e6424b2ae7f322%40teo-en-m… [5] http://lists.linuxfromscratch.org/pipermail/lfs-chat/2020-August/029149.html [6] https://lists.centos.org/pipermail/centos/2020-August/351448.html [7] https://marc.info/?l=apache-httpd-users&m=159888114229385&w=2 [8] https://marc.info/?l=php-general&m=159888203429670&w=2 [9] https://mta.openssl.org/pipermail/openssl-users/2020-August/012915.html -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] Can't disable Mariadb general query log
by Artem Russakovskii 31 Aug '20

31 Aug '20

Hi folks, I'm at a loss here. I've had a mysql and now mariadb (MariaDB 10.4.13-MariaDB-log) slave with query logging enabled for years, but I'm now trying to turn it off using the my.cnf option and it does not seem to stick on server restarts. What's weird is the other slaves with the exact same my.cnf don't log, but this one slave refuses to stop doing it. To clarify, SET global general_log = 0 does stop logging but the setting comes back on after a restart. I'm turning it off in my.cnf like this: general_log = off #Enter a name for the query log file. Otherwise a default name will be used. general_log_file=/var/log/mysql/mysqld-queries.log Commenting out general_log_file simply changes it to go to a different location. I also tried general_log=0 without any luck. ps shows it as running with: mysql 27580 1 99 16:26 ? 00:01:40 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --user=mysql So --general-log isn't getting set on command line. What am I missing? Why does it insist on getting turned on and refuses to listen to the setting? Is it a bug? Thanks. Sincerely, Artem -- Founder, Android Police <http://www.androidpolice.com>, APK Mirror <http://www.apkmirror.com/>, Illogical Robot LLC beerpla.net | @ArtemR <http://twitter.com/ArtemR>

2 6

[Maria-discuss] Missing table rows when importing a MySQL 5.7.8 database into MariaDB 10.4.8 database
by Elliot Holden 27 Aug '20

27 Aug '20

Hello All, New to the list, so maybe an expert has the easy answer to this one... When I use mysqldump to backup a MySQL 5.7.8 database then import that database into a MariaDB 10.4.8 database, at least one (maybe more) of the imported tables is missing rows. This a WordPress database and the table in question is the *wp_options* table. I'm only getting 29 rows imported when there should be over 600 (in the original MySQL database). I'm using the *mysqldump* and *mysql* utilities located on the MariaDB server: mysqldump Ver 10.17 Distrib 10.4.8-MariaDB, for Linux (x86_64) mysql Ver 15.1 Distrib 10.4.8-MariaDB, for Linux (x86_64) using readline 5.1 My steps as performed on the MariaDB server. 1. mysqldump -u username -pPassword -h remote_mysql_server database_name > wp_backup.sql 2.mysql -u username -pPassword -h localhost database_name < wp_backup.sql _* *_ When I run *mysql* (on the local MariaDB server) and import the wp_backup.sql to the remote_mysql_server everything works fine and all rows are importing into a test database out there_*:*_ mysql -u username -pPassword -h remote_mysql_server database_name < wp_backup.sql So the above works I know I'm probably missing something simple but just don't know... they both seem to use the same InnoDB engine. I did notice the Db character set is different between the databases though (highlighted in blue below). Maybe do I need to upgrade to MariaDB *10.4.14* Any help would be greatly appreciated.....? Details about each server listed below: _* *_ _*MySQL*_ Server version: 5.7.28-log Source distribution Protocol version: 10 Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 *************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES _* *__*MariaDB*_ Server version: 5.5.5-10.4.8-MariaDB MariaDB Server Protocol version: 10 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 *************************** 7. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, foreign keys and encryption for tables Transactions: YES XA: YES Savepoints: YES -- Elliot Holden ‪(404)884-8593‬ elliot(a)elliotmywebguy.com https://www.ElliotMyWebGuy.com

7 15

[Maria-discuss] Out of sort memory, consider increasing server sort buffer size - after upgrade to 10.5.5
by Jeff Dyke 26 Aug '20

26 Aug '20

Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors. That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days. All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade. Thanks for reading and giving it a thought.

2 3

[Maria-discuss] MariaDB 10.4: subtle change of result ordering
by Reindl Harald 24 Aug '20

24 Aug '20

3 8

[Maria-discuss] Mariadb 10.4: mysql_test broken - No option named 'plugin-dir' in group 'mysqld.1' at lib/My/ConfigFactory.pm line 349
by Reindl Harald 24 Aug '20

24 Aug '20

one last thing before i restore the snapshot with the latest 10.3 build ----------------------------------------- /usr/bin/su -c 'cd /usr/share/mysql-test; ./mysql-test-run.pl --parallel=4 --max-test-fail=0 --mysqld=--binlog-format=row --force --suites="main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol-" --skip-test-list=/var/lib/mysql/skip-tests.txt' mysql ----------------------------------------- [root@testserver:~]$ mysql-test.sh Logging: ./mysql-test-run.pl --parallel=4 --max-test-fail=0 --mysqld=--binlog-format=row --force --suites=main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol- --skip-test-list=/var/lib/mysql/skip-tests.txt Using binlog format 'row' vardir: /usr/share/mysql-test/var Removing old var directory... Couldn't remove directory '/usr/share/mysql-test/var': Device or resource busy at /usr/share/perl5/File/Find.pm line 511. Creating var directory '/usr/share/mysql-test/var'... Checking supported features... MariaDB Version 10.4.14-MariaDB - SSL connections supported Using suites: main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol- Collecting tests... No option named 'plugin-dir' in group 'mysqld.1' at lib/My/ConfigFactory.pm line 349 ----------------------------------------- %build export CFLAGS="%{optflags} %{O3_flags} -fPIC -fwrapv -fno-stack-protector -fstack-protector --param=ssp-buffer-size=8 -fno-strict-aliasing -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -Wno-stack-protector -Wa,--noexecstack" export CXXFLAGS="$CFLAGS -fno-rtti" export CPPFLAGS="$CFLAGS" export SH_LDFLAGS="-Wl,--as-needed -Wl,-z,now -Wl,-z,relro -Wl,-z,noexecstack -Wl,-z,nodump" export LDFLAGS="$SH_LDFLAGS -pie -fPIE" cmake . \ -DFEATURE_SET="large" \ -DCMAKE_INSTALL_PREFIX="%{_prefix}" \ -DINSTALL_INCLUDEDIR=include/mysql \ -DINSTALL_LAYOUT=RPM \ -DDAEMON_NAME="mysqld" \ -DDAEMON_NO_PREFIX="mysqld" \ -DNICE_PROJECT_NAME="MariaDB" \ -DINSTALL_LIBDIR="%{_lib}/mysql" \ -DINSTALL_MANDIR=share/man \ -DINSTALL_MYSQLSHAREDIR=share/mysql \ -DINSTALL_MYSQLTESTDIR=share/mysql-test \ -DINSTALL_PLUGINDIR="%{_lib}/mysql/plugin" \ -DINSTALL_SBINDIR=libexec \ -DINSTALL_SCRIPTDIR=bin \ -DINSTALL_SQLBENCHDIR= \ -DINSTALL_SUPPORTFILESDIR=share/mysql \ -DMYSQL_DATADIR="%{_sharedstatedir}/mysql" \ -DMYSQL_UNIX_ADDR="%{_sharedstatedir}/mysql/mysql.sock" \ -DENABLED_PROFILING=OFF \ -DENABLE_DEBUG_SYNC=OFF \ -DENABLE_DTRACE=OFF \ -DPLUGIN_ARIA=YES \ -DPLUGIN_CSV=YES \ -DPLUGIN_MYISAM=YES \ -DPLUGIN_ARCHIVE=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CASSANDRA=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO \ -DPLUGIN_FEDERATED=NO \ -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_FEEDBACK=NO \ -DPLUGIN_GALERA=NO \ -DPLUGIN_MROONGA=NO \ -DPLUGIN_MYISAMMRG=NO \ -DPLUGIN_OQGRAPH=NO \ -DPLUGIN_PARTITION=NO \ -DPLUGIN_PERFSCHEMA=NO \ -DPLUGIN_ROCKSDB=NO \ -DPLUGIN_SEMISYNC=NO \ -DPLUGIN_SEQUENCE=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_XTRADB=NO \ -DPLUGIN_AUTH_SOCKET=NO \ -DWITHOUT_DYNAMIC_PLUGINS=ON \ -DWITH_ATOMIC_OPS=smp \ -DWITH_EMBEDDED_SERVER=OFF \ -DWITH_INNODB_DISALLOW_WRITES=OFF \ -DWITH_INNODB_BZIP2=OFF \ -DWITH_INNODB_LZ4=OFF \ -DWITH_INNODB_LZMA=OFF \ -DWITH_INNODB_LZO=OFF \ -DWITH_INNODB_SNAPPY=OFF \ -DWITH_MYSQLCOMPAT=ON \ -DSECURITY_HARDENED=OFF \ -DWITH_LIBARCHIVE=OFF \ -DWITH_LIBWRAP=OFF \ -DWITH_MARIABACKUP=OFF \ -DWITH_PIC=NO \ -DWITH_READLINE=OFF \ -DWITH_SAFEMALLOC=OFF \ -DWITH_VALGRIND=OFF \ -DWITH_WSREP=OFF \ -DWITH_JEMALLOC=OFF \ -DWITH_SSL=system \ -DWITH_ZLIB=system

2 1

[Maria-discuss] [Maria Discuss] Building from source failing on Mac OS X complaining of librarymariadb
by Rodrigo Souza 24 Aug '20

24 Aug '20

Hello all, I’m a newbie, trying to familiarize myself with the source code, however, once I followed the steps outlined here https://mariadb.org/get-involved/getting-started-for-developers/get-code-bu… <https://mariadb.org/get-involved/getting-started-for-developers/get-code-bu…> to build the server on Mac OS X, currently the MAKE fails on me complaining about a deprecated GSSAPI.h as follows: /mariadb/server/libmariadb/plugins/auth/gssapi_client.c:72:12: error: 'gss_import_name' is deprecated: use GSS.framework [-Werror,-Wdeprecated-declarations] major= gss_import_name(&minor, &principal_name_buf, GSS_C_NT_USER_NAME, &service_name); ^ /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/include/gssapi/gssapi.h:591:19: note: 'gss_import_name' has been explicitly marked deprecated here gss_name_t *) GSSKRB_APPLE_DEPRECATED("use GSS.framework") __API_DEPRECATED("Use GSS.framework", macos(10.0, 10.8)) __API_UNAVAILABLE(macCatalyst); /* output_name */ ^ /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/include/gssapi/gssapi.h:57:51: note: expanded from macro 'GSSKRB_APPLE_DEPRECATED' #define GSSKRB_APPLE_DEPRECATED(x) __attribute__((deprecated(x))) My Mac OS X is Catalina 10.15.6 and XCode Tools 11.6. Have you guys ever had this kind of issue trying to build on Mac OS? Thank you, Rodrigo Souza

2 1

[Maria-discuss] logging options of mariadb are a joke
by Reindl Harald 16 Aug '20

16 Aug '20

it's a shame that even MySQl 5.7 options are unknown in MariaDB 10.3 which was *long* after ------------------------------------------ https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… [ERROR] /usr/libexec/mysqld: unknown variable 'log-syslog=1' don't set "log-error" is sloppy crap and results in way to much long lines with repeated stuff - hell either support proper config or just don't spit date/time to stdout/stderr, journald knows it anyways Aug 16 20:23:09 backupserver mysqld[315856]: 2020-08-16 20:23:09 6 [Note] Slave I/O thread exiting, read up to log 'bin.000012', position 15906130 ------------------------------------------ https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… log_error_verbosity = 2 2020-08-16 22:17:46 0 [ERROR] /usr/libexec/mysqld: unknown variable 'log_error_verbosity=2' ------------------------------------------

1 0

[Maria-discuss] Single-threaded DDL limitation still present in Galera?
by Artem Russakovskii 13 Aug '20

13 Aug '20

Hi, On this page https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/, a user in 2017 pointed out the following: 3 years, 3 months ago Björn Schneider <https://mariadb.com/kb/user/id/4916> Schema changes of large tables in Galera <https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/#comment…> You should be warned that every DDL statement executed on a Galera cluster will per default BLOCK the complete cluster (not only the table, or even just the database the table resides in)! This is the default "Total Order Isolation" (TOI) mode. The DDL statements can't be killed - once issued, it will run until completed or an error occurs. Issuing e.g. a column-altering DDL statement on a large table will take the complete cluster out of commission until every node has completed the migration. Only some operations (e.g. changing DEFAULT values) are always short-timed and won't interefere with the cluster's opperations. Long-running, cluster-blocking DDL are of course a no-go on a productive system. To resolve this issue, there are several resulutions; Percona, for example, provides a script to "online" migrate a table (pt-online-schema-change), or you can use the "Rolling Schema Upgrade" (RSU) for data-compatible changes. More about that in the Galera documentation. In my opionion, this behaviour should definitely added as "observation" to this page - it's definitely not something you'd expect coming from a "normal" MariaDB/MySQL system. We're considering moving to a cluster environment from a 1 master - 3 slave configuration that has proven inflexible, but such a severe limitation seems unusable in production. Is this still the case in 2020? What is the reasoning behind such an architectural decision, if so? Thank you. Sincerely, Artem -- Founder, Android Police <http://www.androidpolice.com>, APK Mirror <http://www.apkmirror.com/>, Illogical Robot LLC beerpla.net | @ArtemR <http://twitter.com/ArtemR>

2 3

[Maria-discuss] Test failures in my build of MariaDB 10.5.4
by Chris Ross (cross2) 08 Aug '20

08 Aug '20

I am building MariaDB 10.5.4 from source with the cmake options shown below. I am running mtr with --skip-test-list=unstable-tests, but still seeing multiple failures: Failing test(s): csv.read_only main.flush_logs_not_windows main.mysqld--help perfschema.bad_option unit.conc_charset I can copy and paste many sections of the stdout.log and specific files, but wanted to ask if this is the right forum to pursue these. These seem pretty core (main.mysqld--help, which reported ‘mysqltest: Result length mismatch‘ after a small unified diff output showing only some numeric value differing), so I’m not sure why I would be getting these. Thanks. Guidance appreciated. - Chris Cmake I used to build: cmake -DBUILD_CONFIG=mysql_release -DAWS_SDK_EXTERNAL_PROJECT=OFF -DCMAKE_INSTALL_PREFIX:PATH=/usr \ -DINSTALL_LAYOUT=RPM -DINSTALL_PLUGINDIR=lib64/plugin -DINSTALL_SYSCONFDIR="" -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO -DWITH_JEMALLOC=OFF -DWITH_PCRE=auto -DWITH_SSL=system -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO -DPLUGIN_AUTH_TEST_PLUGIN=NO -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO -DPLUGIN_TEST_SQL_DISCOVERY=NO -DPLUGIN_TEST_VERSIONING=NO -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO -DPLUGIN_SPHINX=NO -DPLUGIN_TOKUDB=NO -DPLUGIN_ROCKSDB=NO -DCMAKE_BUILD_TYPE=Debug

2 3

[Maria-discuss] Testing Slave's data integrity
by Richard Reina 25 Jul '20

25 Jul '20

Good afternoon, I have a couple slaves that I have set up over the last year. Is there a way to perhaps compare them to the master on a database or table level to ensure their integrity? Thanks

3 2