- discuss - lists.mariadb.org

[Maria-discuss] Problem with mariabackup
by Riccardo Brunetti 17 Jun '21

17 Jun '21

Hello. We are using the mariabackup tool to stream to a remote server the backup of our databases. We are having some issue because in the middle of the operation the db server stops responding for many minutes until we ultimately kill the mariabackup process. During the lock, we can see the following: > MariaDB [(none)]> show processlist; > > +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+ > > | Id | User | Host | db | Command | Time | State | Info | Progress | > > +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+ > > | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | > > | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | > > | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000 | > > | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query | 17 | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET reason = CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 | > > | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47 | Waiting for global read lock | DELETE FROM `ps4d_marketplace_product_action` > > WHERE `id_product` = 7356 AND `id_lan | 0.000 | > > | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Query | 46 | Waiting for global read lock | INSERT INTO wp_post_views (id, type, period, count) > > VALUES (62075, 3, '2021', 1) > > ON DUPLIC | 0.000 | > > | 302395 | uqlugtoj_enedina.it | localhost | uqlugtoj_enedina | Query | 44 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '1623829414' WHERE `option_name` = '_transient_timeout_wc_r | 0.000 | > > | 302404 | udlah5r1_remigio | localhost:50782 | udlah5r1_vtiger71 | Query | 44 | Waiting for global read lock | insert into vtiger_pbxmanager(pbxmanagerid,direction,callstatus,starttime,endtime,totalduration,bill | 0.000 | > > | 302431 | utlmb6ko_6M0oD0 | localhost | utlmb6ko_6M0oD0 | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '1623743080' WHERE `option_name` = 'action_scheduler_lock_a | 0.000 | > > | 302432 | uml1gkxr_58IJ3 | localhost:50836 | uml1gkxr_prod | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 | > > | 302433 | uml1gkxr_58IJ3 | localhost:50838 | uml1gkxr_prod | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 | > > | 302440 | uqtx0c94_giorgio | localhost:50848 | uqtx0c94_due | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '[]' WHERE `option_name` = '_transient_adsforwp_transient_a | 0.000 | > > | 302473 | uw6vnwff_helpdesk | localhost:50932 | uw6vnwff_helpassist | Query | 30 | Waiting for global read lock | INSERT INTO ost_syslog SET created=NOW(), updated=NOW() ,title='DB Error #1205',log_type='Error',log | 0.000 | At the same time, we see messages like these in the system log: > Executing FLUSH NO_WRITE_TO_BINLOG TABLES... > > Executing FLUSH TABLES WITH READ LOCK... This is the command we launch to backup data: > mariabackup --backup --stream=xbstream --user=root --extra-lsndir=${BCKDBDIR} -u${MYSQLUSER} -p${MYSQLPW} -H ${MYSQLHOST}" Is it somehow an expected behavior (some of the db are quite heavily used)? How can we avoid those locks? Thanks Riccardo

3 7

[Maria-discuss] Disable Some Operators in Fulltext Boolean mode
by Samuel Lorch 12 Jun '21

12 Jun '21

Hello, I am using MariaDB in a Project and to Allow users to Search the Data we use Fulltext Indexes, as you cannot make a Fulltext Index across multiple tables we have multiple match against statements and are Combining them with OR. The Problem is that the Natural Language Mode only Matches Full Words and not Partial ones. The Solution is Boolean Mode where you can append a * to a word to allow 0 or more characters. But Boolean Mode also Brings Other Operators. These other Operator can Behave Unexpectedly for The User when Combining Multiple Match Against Statements With OR. Using the "ft_boolean_syntax" variable one can change the characters used for these operators but this is limited to ASCII characters below 127. I suggest That we add a special character that disables a operator. What do you think about this? Or do you See a different Solution? Regards, Samuel

1 0

[Maria-discuss] DBSeeder Version 2.9.0
by Walter Weinmann 08 Jun '21

08 Jun '21

We are happy to announce that version 2.9.0 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder) is now available for general use. DBSeeder also supports the latest version of MariaDB Server available on DockerHub (10.6.1).

2 2

[Maria-discuss] Can't build mariadb 10.5 without gssapi?
by Chris Ross (cross2) 27 May '21

27 May '21

G’day there. So, I was trying to turn off the auth_gssapi plugin which we aren’t using in our build of mariadb 10.5.8. However, adding -DPLUGIN_AUTH_GSSAPI=NO, which is how I've turned off multiple other plugins, seems to have no effect. It still searches for gssapi, and then later builds auth_gssapi_client. Is there an issue/defect open yet that this plugin can't be disabled, or have I done something incorrectly to turn it off? - Chris

2 1

[Maria-discuss] Performance of MariaDB ODBC connector
by Dušan Pavlica 05 May '21

05 May '21

Hi, I'm new with MariaDB. We try to switch from MySQL 5.0.96 to MariaDB 10.5.9. Move data to MariaDB was easy but now we have problems with speed of ODBC connector. I tried MariaDB ODBC connector 3.1.12 with default settings without SSL and our applications (mostly written in Embarcadero RAD Studio) showed very poor performance. When I tried MySQL ODBC connector 8.0.24 everything ran smoothly. Queries through MariaDB's connector was 3-5 times slower. In general query log I found that MariaDB's connector generates a lot of queries to information_scheme.columns table and all over again it reads variable TX_ISOLATION. Short extract - MariaDB connector: 3 Query SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION' 3 Prepare select * from stanice 3 Execute select * from stanice 3 Prepare SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Execute SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Close stmt 3 Reset stmt 3 Query SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION' 3 Prepare SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID 3 Execute SELECT * FROM stanice WHERE Sit_ID = 1 ORDER BY Sit_ID, Podsit_ID, ID 3 Prepare SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Execute SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Close stmt 3 Reset stmt MySQL connector: 3 Query SELECT @@tx_isolation ... 3 Query select * from stanice 3 Prepare SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID 3 Execute SELECT * FROM stanice WHERE Sit_ID = '1' ORDER BY Sit_ID, Podsit_ID, ID 3 Close stmt I think those "unnecessary" queries are making difference in performance of those connectors. Does anybody have any ideas what could I do to speed up MariaDB's connector? We prefer not using MySQL connectors anymore. Thanks in advance Dušan Pavlica

2 1

[Maria-discuss] How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server
by Turritopsis Dohrnii Teo En Ming 04 May '21

04 May '21

Subject: How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 3rd May 2021 Monday Type of Publication: PDF Manual Document Version: 20210503.01 ***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 Host Multiple Mail Domains in iRedMail with Nginx Link: https://www.linuxbabe.com/mail-server/set-up-iredmail-multiple-domains-nginx 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 In addition, you need to add MX, A and TXT records to your ISC BIND DNS domain name server. Redundant download links for Turritopsis Dohrnii Teo En Ming's PDF manual: [1] https://www.mediafire.com/file/q6txmx0rc1cwfzw/Additional+Mail+Domains+1st+… [2] https://www.docdroid.net/Q9TRL5D/additional-mail-domains-1st-release-pdf [3] https://www.scribd.com/document/506193434/Additional-Mail-Domains-1st-Relea… [4] https://drive.google.com/file/d/10AYGt4-omBeC3vXJfzswQq0M7E09gXpG/view?usp=… [5] https://drive.google.com/file/d/113dy0AKmBYGugBueK1vS_dJwI35J6XmJ/view?usp=… [6] https://drive.google.com/file/d/1dhpYIZp31ug5hoiVNbjh6uh99s_PEx8q/view?usp=… Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 4th May 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-----

2 1

[Maria-discuss] Help for USE is missing
by Krzysztof Żelechowski 27 Apr '21

27 Apr '21

Mel: \h USE mysql: Nothing found Expected: <URL: https://mariadb.com/kb/en/use/ > Please fix, Chris

1 0

[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