October 2022 - discuss - lists.mariadb.org

[Maria-discuss] Maria-db refuses to start
by Jogchum Reitsma 08 Nov '22

08 Nov '22

Hi, I am on OpenSuse Tumbleweed, rolling distro. Since some snapshots of Tumbleweed my mariadb does not start at system start. I don't use the mariadb databases a lot, so I didn't notice it immediately. Hence I'm unsure when the problem occurred first time. When trying to start it manually, I got the message [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.5.8. Searching that message, I saw a post on https://bbs.archlinux.org/viewtopic.php?id=259364 Like me, he had a non-default location for the databases configured in /etc/my.cnf. The solution that worked for him was to change that to another location in my.cnf, starting the database (which now succeeded), and copy the existing databases to that new location. So I decided to mimic that: commented out the existing database location, entered an new one, and started the database server (as root) with systemctl start mariadb After a while, the new location is created (owned by mysql:root) and populated: l mysql_recover totaal 111048 drwx------ 6 mysql root 243 2 okt 21:33 ./ drwxr-xr-x 225 jogchum users 16384 2 okt 21:26 ../ -rw-rw---- 1 mysql mysql 417792 2 okt 21:27 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 2 okt 21:27 aria_log_control -rw-rw---- 1 mysql mysql 942 2 okt 21:27 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 2 okt 21:27 ibdata1 -rw-rw---- 1 mysql mysql 100663296 2 okt 21:27 ib_logfile0 drwx------ 2 mysql mysql 4096 2 okt 21:27 mysql/ -rw-rw---- 1 root root 14 2 okt 21:27 mysql_upgrade_info drwx------ 2 mysql mysql 28 2 okt 21:27 performance_schema/ drwx------ 2 mysql mysql 8192 2 okt 21:27 sys/ drwx------ 2 mysql mysql 28 2 okt 21:27 test/ But unlike the posting at bbs.archlinux.org just mentioned, the server crashed again, now with the following messages in journalctl: ################################################################################# journalctl -xeu mariadb.service A start job for unit mariadb.service has finished with a failure. The job identifier is 2782226 and the job result is failed. okt 02 21:20:47 linux-mkay systemd[1]: Starting MariaDB database server... Subject: A start job for unit mariadb.service has begun execution Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel A start job for unit mariadb.service has begun execution. The job identifier is 2790830. okt 02 21:20:47 linux-mkay mysql-systemd-helper[44977]: 2022-10-02 21:20:47 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as process 44977 ... okt 02 21:20:48 linux-mkay systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Subject: Unit process exited Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel An ExecStart= process belonging to unit mariadb.service has exited. The process' exit code is 'exited' and its exit status is 1. okt 02 21:20:48 linux-mkay systemd[1]: mariadb.service: Failed with result 'exit-code'. Subject: Unit failed Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel The unit mariadb.service has entered the 'failed' state with result 'exit-code'. okt 02 21:20:48 linux-mkay systemd[1]: Failed to start MariaDB database server. Subject: A start job for unit mariadb.service has failed Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel A start job for unit mariadb.service has finished with a failure. The job identifier is 2790830 and the job result is failed. okt 02 21:26:55 linux-mkay systemd[1]: Starting MariaDB database server... Subject: A start job for unit mariadb.service has begun execution Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel A start job for unit mariadb.service has begun execution. The job identifier is 2792404. okt 02 21:26:55 linux-mkay mysql-systemd-helper[45372]: Creating MySQL privilege database... okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: Two all-privilege accounts were created. okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: One is root@localhost, it has no password, but you need to okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: be system 'root' user to connect. Use, for example, sudo mysql okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: The second is mysql@localhost, it has no password either, but okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: you need to be the system 'mysql' user to connect. okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: After connecting you can set the password, if you would need to be okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: able to connect as any of these users with a password and without sudo okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: See the MariaDB Knowledgebase at https://mariadb.com/kb okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: Please report any problems at https://mariadb.org/jira okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: The latest information about MariaDB is available at https://mariadb.org/. okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: Consider joining MariaDB's strong and vibrant community: okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: https://mariadb.org/get-involved/ okt 02 21:27:17 linux-mkay mysql-systemd-helper[45468]: 2022-10-02 21:27:17 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as process 45468 ... okt 02 21:27:17 linux-mkay mysql-systemd-helper[45468]: 2022-10-02 21:27:17 0 [Warning] Can't create test file /home/jogchum/mysql_recover/linux-mkay.lower-test okt 02 21:27:17 linux-mkay mysql-systemd-helper[45468]: [103B blob data] okt 02 21:27:17 linux-mkay mysql-systemd-helper[45468]: 2022-10-02 21:27:17 0 [ERROR] Aborting okt 02 21:27:17 linux-mkay systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Subject: Unit process exited Defined-By: systemd Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel ############################################################################################ Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that? Thanks in advance! regards, Jogchum

6 22

[Maria-discuss] Galera with stunnel for rsync sst method
by John Fawcett 15 Oct '22

15 Oct '22

Hi Can someone point out or help with a step by step guide to setting up Galera to use stunnel for rsync sst? I was following the instructions here: https://mariadb.com/kb/en/introduction-to-state-snapshot-transfers-ssts/ and my main doubts are: 1. Does stunnel itself have to be configured and if so is it the same configuration on all galera nodes? 2. Does stunnel have to be running as a service on any of the galera nodes? 3. Does the configuration shown in the galera documentation [sst] tkey = /etc/my.cnf.d/certificates/client-key.pem tcert = /etc/my.cnf.d/certificates/client-cert.pem have to be done on all the galera nodes? 4. Do the above key and cert have to be the same (or have to be different) on all galera nodes? 5. more of a stunnel question, but following the link from the galera documentation to the stunnel documentation for certificate generation, https://www.stunnel.org/howto.html I got stuck on this command which no longer works on openssl 3.0.5.5: openssl gendh 2048 >> stunnel.pem Any idea on what has replaced it? Thanks for any help. John

1 0

[Maria-discuss] Migration path for unix_socket authentication plugin
by William Edwards 14 Oct '22

14 Oct '22

Hi, On MariaDB servers that were installed with >= MariaDB 10.4, I have one root user that was created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket FWIW: The statements on https://mariadb.com/kb/en/authentication-from-mariadb-104/#description are reversed (`IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'` instead of `IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket`). On MariaDB servers that were installed with < MariaDB 10.4, I have 3 root users that were created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` MariaDB [(none)]> show create user 'root'@'::1'; CREATE USER `root`@`::1` MariaDB [(none)]> show create user 'root'@'127.0.0.1'; CREATE USER `root`(a)`127.0.0.1` I've upgraded all MariaDB servers that were installed with MariaDB <= 10.4 to MariaDB 10.6. I would like to update these root users, that were created when installing on < 10.4, to use UNIX socket authentication too. If I run: ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket ... I have a user with the same create statement as on servers that were installed with >= MariaDB 10.4: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket I have 3 questions regarding this: 1. Is the ALTER USER statement above the correct migration path, to update root users created when installing on < 10.4 to use UNIX socket authentication, like root users created when installing on >= 10.4? Am I forgetting anything? 2. I presume the 'root'@'::1' and 'root'@'127.0.0.1' users were created to support TCP connections when skip_name_resolve=1. If so, they are no longer needed when using a UNIX socket. Can I safely delete these users, after updating the 'root'@'localhost' user to use UNIX socket authentication? 3. Is there a plan to automatically remove the debian-sys-maint user on future major upgrades? It still exists on servers that were installed on < 10.4. The /etc/mysql/debian.cnf file is deprecated already, so this user should be unused. FYI: All MariaDB servers were installed from the MariaDB Debian repository. Thank you! William Edwards

2 1

[Maria-discuss] Retrieve authentication plugins for user
by William Edwards 13 Oct '22

13 Oct '22

Hi, When creating an account with multiple authentication plugins, in this case 'unix_socket' OR 'mysql_native_password', the mysql.user.plugin column is set to either one. How should I retrieve *all* used authentication plugins for a given user? Example: ``` MariaDB [(none)]> CREATE USER 'testing'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> select user,host,password,authentication_string,plugin from mysql.user where User = 'testing'; +---------+-----------+----------+-----------------------+-----------------------+ | User | Host | Password | authentication_string | plugin | +---------+-----------+----------+-----------------------+-----------------------+ | testing | localhost | invalid | invalid | mysql_native_password | +---------+-----------+----------+-----------------------+-----------------------+ 1 row in set (0.003 sec) ``` William Edwards

2 1

[Maria-discuss] What is the right way to update based on a DB change?
by ngtech1ltd＠gmail.com 03 Oct '22

03 Oct '22

Hey Everybody, I have the next scenario which I am not sure about the right way for a solution. I have a radius server that authenticate clients from multiple WIFI Access Points. I have couple systems and they need to know the current client login identifier (MAC or IP Address). It is stored in the DB and the DB is being updated on every login. I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this. So what I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds. I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a Processed column that will be updated when the software finished processing the row. Then in turn every 24 hours I will run a DB cleanup cron. I will try later on to share the schema. Any advice is more than welcome. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd(a)gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/

3 3