- discuss - lists.mariadb.org

[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