- discuss - lists.mariadb.org

[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 15 Sep '20

15 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 30 Aug '20

30 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