- discuss - lists.mariadb.org

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