- discuss - lists.mariadb.org

Re: [Maria-discuss] Maria-db refuses to start
by Jogchum Reitsma 12 Dec '22

12 Dec '22

Op 08-11-2022 om 13:04 schreef Marko Mäkelä: > Hi Jogchum, > > On Tue, Nov 8, 2022 at 1:21 PM Jogchum Reitsma > <jogchum.reitsma(a)gmail.com> wrote: > [...] >> 2022-11-08 12:06:17 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files. >> (repeated several times) >> 2022-11-08 12:06:17 0 [ERROR] /usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: unknown option '--skip-locking' >> 2022-11-08 12:06:17 0 [ERROR] Aborting > Was that the end of the output? No shutdown message from InnoDB, like this? > > 2022-11-08 14:03:03 0 [Note] InnoDB: Shutdown completed; log sequence > number 2150107; transaction id 3085 > > If there was no output like that, you should remove any line that says > "skip_locking" or "skip-locking" from the configuration file and > retry. > > Marko Yes, that was indeed the end of the output. The skip-locking statement was not in /etc/my.cnf, but inmy.cnf in my home dir. Commented it out and now this complaint disappeared. But now I get #################################################################### 2022-11-08 13:37:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.12 2022-11-08 13:37:32 0 [Note] InnoDB: Number of pools: 1 2022-11-08 13:37:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2022-11-08 13:37:32 0 [Note] InnoDB: Using Linux native AIO 2022-11-08 13:37:32 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2022-11-08 13:37:32 0 [Note] InnoDB: Completed initialization of buffer pool 2022-11-08 13:37:32 0 [Note] InnoDB: 128 rollback segments are active. 2022-11-08 13:37:32 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2022-11-08 13:37:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2022-11-08 13:37:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2022-11-08 13:37:32 0 [Note] InnoDB: 10.6.10 started; log sequence number 578191791; transaction id 2935782 2022-11-08 13:37:32 0 [Note] InnoDB: Loading buffer pool(s) from /home/jogchum/mysql/ib_buffer_pool 2022-11-08 13:37:32 0 [Note] Plugin 'FEEDBACK' is disabled. 2022-11-08 13:37:32 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files. 2022-11-08 13:37:33 0 [Note] Server socket created on IP: '0.0.0.0'. 2022-11-08 13:37:33 0 [ERROR] Can't start server : Bind on unix socket: No such file or directory 2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on socket: /var/run/mysql/mysql.sock ? 2022-11-08 13:37:33 0 [ERROR] Aborting Warning: Memory not freed: 280 ########################################################################## (again no further output). /var/run/mysql/mysql.sock odoes not exeist, and "pgrep mariadbd", "pgrep mysql" and "pgrep mysqld" show nothing, so no serer is running anymore. ss -a | grep 3306 or ss -a | grep /var/run/mysql also gives nothing, so it seems there are no processes using that port. We're getting closer it seems, but not quite there yet... regards, Jogchum

7 48

[Maria-discuss] Statistics are not being updated?
by Colin Charles 08 Dec '22

08 Dec '22

Hi, Any reason statistics stopped being updated in October? https://mariadb.org/statistics/ Would be great if this got fixed, thanks cheers, -c -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

2 1

[Maria-discuss] Followup on MDEV-25625, still present in 10.5.16
by Chris Ross (cross2) 17 Nov '22

17 Nov '22

Hello all. I came across MDEV-25625 while looking for that same problem. It was closed, I think because the reporter didn’t get back to it, but I’m now seeing the same. I’ve added a comment, but I don’t know if commenting on a closed issue will notify anyone. Can anyone comment/re-open that issue? I don’t want to file a duplicate. Thank you. https://jira.mariadb.org/browse/MDEV-25625 - Chris

2 3

[Maria-discuss] subscribe
by Surya 15 Nov '22

15 Nov '22

Hello , -- Best regards, Surya mailto:surya@fastmail.in

1 0

[Maria-discuss] (no subject)
by Surya 15 Nov '22

15 Nov '22

Hello , -- Best regards, Surya mailto:surya@fastmail.in

1 0

[Maria-discuss] mariadb-10.3.37 strips "collate" from "show create table"
by Reindl Harald 10 Nov '22

10 Nov '22

mariadb-10.3.36 versus mariadb-10.3.37 in the past every field contained "collate latin1_german1_ci" and after this point-update collation is only part of the output if it's different then the tables default collation surely, i can update everything to mariadb-10.3.37 and rewrite all this tests in the hope the next point-update don't restore the old behavior but it's really a bad style --------------------------------------------------------------------------------------------------------------------- CALLER: '/autotests/custom/phpincludes_mysql.php:306': --------------------------------------------------------------------------------------------------------------------- --- old_9bb3995c3244abfff522da086526e88b5a8a81f1e7cf672d8343f20d.txt 2022-11-10 01:03:12.366665303 +0100 +++ new_53033703b16454931e7c7f62ef923229ca8183aff6f6fa017cdb44bd.txt 2022-11-10 01:03:12.366665303 +0100 @@ -1,14 +1,14 @@ create table `test_table` (`test_id` mediumint(7) unsigned not null auto_increment, -`char_char` char(200) collate latin1_german1_ci default null, +`char_char` char(200) default null, `char_date` date default null, `char_datetime` datetime default null, -`char_longtext` longtext collate latin1_german1_ci default null, -`char_mediumtext` mediumtext collate latin1_german1_ci default null, -`char_text` text collate latin1_german1_ci default null, +`char_longtext` longtext default null, +`char_mediumtext` mediumtext default null, +`char_text` text default null, `char_time` time default null, `char_timestamp` datetime default null, -`char_tinytext` tinytext collate latin1_german1_ci default null, -`char_varchar` varchar(200) collate latin1_german1_ci default null, +`char_tinytext` tinytext default null, +`char_varchar` varchar(200) default null, `number_bigint` bigint(200) unsigned default null, `number_double` double unsigned default null, `number_float` double unsigned default null,

1 0

[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] Bug in incremental backups since MariaDB version 10.5.7 with mariabackup?
by Michael Rößler 02 Nov '22

02 Nov '22

Hi @ll, I am using mariabackup since 2 years. I wanted to upgrade from version 10.4.24 to 10.6.10 but I found out that incremental Backups does not work as expected because of size. Further investigation show that it is since version 10.5.7. Then I have found out that in the file xtrabackup_checkpoints the value for to_lsn is not changing in version 10.5.7 and 10.6.10. Using the command 'mysql -e "SHOW ENGINE INNODB STATUS\G" | egrep "Last check"' show that values are the same with that command and the value to_lsn in the file xtrabackup_checkpoints. Backup-commands used in test environment: mariabackup --extra-lsndir=/var/backup/full --backup --compress --stream=xbstream --user=root --password=root --target-dir=/var/backup/full > /var/backup/full/full.xbstream mariabackup --extra-lsndir=/var/backup/inc1 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/full --target-dir=/var/backup/inc1 > /var/backup/inc1/inc1.xbstream mariabackup --extra-lsndir=/var/backup/inc2 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/inc1 --target-dir=/var/backup/inc2 > /var/backup/inc2/inc2.xbstream mariabackup --extra-lsndir=/var/backup/inc3 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/inc2 --target-dir=/var/backup/inc3 > /var/backup/inc3/inc3.xbstream Size of files compared between version 10.5.6 and 10.5.7: [root@mysqlsingle2 backup]# ls *10.5.6/*xbstream -lh -rw-r--r-- 1 root root 765K 2. Nov 13:03 full-10.5.6/full.xbstream -rw-r--r-- 1 root root 779K 2. Nov 13:06 inc1-10.5.6/inc1.xbstream -rw-r--r-- 1 root root 1,2M 2. Nov 13:16 inc2-10.5.6/inc2.xbstream -rw-r--r-- 1 root root 752K 2. Nov 13:17 inc3-10.5.6/inc3.xbstream [root@mysqlsingle2 backup]# ls -lh *10.5.7/*xbstream -rw-r--r-- 1 root root 763K 2. Nov 13:22 full-10.5.7/full.xbstream -rw-r--r-- 1 root root 723K 2. Nov 13:23 inc1-10.5.7/inc1.xbstream # added 2000 rows -rw-r--r-- 1 root root 2,1M 2. Nov 13:30 inc2-10.5.7/inc2.xbstream # added 20000 rows -rw-r--r-- 1 root root 2,2M 2. Nov 13:31 inc3-10.5.7/inc3.xbstream # added 2000 rows to_lsn in file xtrabackup_checkpoints: [root@mysqlsingle2 backup]# cat full*/*checkpoints | grep "to_lsn" to_lsn = 47455 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc1*/*checkpoints | grep "to_lsn" to_lsn = 487597 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc2*/*checkpoints | grep "to_lsn" to_lsn = 4572514 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc3*/*checkpoints | grep "to_lsn" to_lsn = 4995024 # 10.5.6 to_lsn = 45118 # 10.5.7 'Last checkpoint at' from command 'mysql -e "SHOW ENGINE INNODB STATUS\G" | egrep "Last check"': [root@mysqlsingle2 backup]# cat full*/*Last* Last checkpoint at 47455 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc1*/*Last* Last checkpoint at 487597 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc2*/*Last* Last checkpoint at 4572514 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc3*/*Last* Last checkpoint at 4995024 Last checkpoint at 45118 Kind regards Michael

1 0

[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