On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma wrote:

Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that?

In systemd ProtectHome=read-only is the default. systemctl edit mariadb.service and add: [Service] ProtectHome=false https://mariadb.com/kb/en/systemd/#useful-systemd-options You may also need to selinux relabel the new directory: sudo semanage fcontext -a -t mysqld_db_t "/home/jogchum/mysql_recover(/.*)?" sudo restorecon -Rv /home/jogchum/mysql_recover https://mariadb.com/kb/en/selinux/#setting-the-file-context-for-the-data-dir...

Am 13.10.22 um 12:14 schrieb Jogchum Reitsma:

Op 12-10-2022 om 16:48 schreef Sergei Golubchik:

...

...

Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that?

Hi, Jogchum, permission problem, perhaps? All files and dir's under /home/jogchum/mysql_recover have mysql:mysql as owner:group

and your userhome is 755 as well as systemd namespaces and SELinux allowing nosense like storing the databasedir in a userhome? just don't do that - and be it only because it's dumb set your userhome to chmod 755 which means any 644 file can be read by anyone

Am 13.10.22 um 13:55 schrieb Jogchum Reitsma:

Op 13-10-2022 om 12:59 schreef Reindl Harald:

...

All files and dir's under /home/jogchum/mysql_recover have mysql:mysql as owner:group

and your userhome is 755

which is the default, when opensuse creates a new user. So, if I understand you right,  that's wrong in your opinion?

plain wrong but i don#t even know the default of my Fedora machines given that the last time i saw an os-installer was in 2011 thanks to RAID and backups

...

as well as systemd namespaces and SELinux allowing nosense like storing the databasedir in a userhome?

Why is that nonsense? After all, it's not a company database we're talking about, just

and how does that justify doing something different than anybody else out there?

- some addresses of friends and family, mainly used for sending Christmas cards, - some metadata about video's I recorded, - some data about the yield of our solar panels.

Nothing secret in that.

i wonder if the friends see that this relaxed too, in europe there is more undestanding of privacy and data security as in the USA...

...

just don't do that - and be it only because it's dumb set your userhome to chmod 755 which means any 644 file can be read by anyone

As said, I did not chmod it to 755, it's the opensuse default (and maybe other distro's too, dunno). And anyone in my family is allowed to read anything on our systems.

More important, you don't say anything about how I could start the database?

dunno but this part of the logs is looking bad - on a updated machine that mustnt't have started at all and indicates for me the damage was that large that your installation looked like a fresh one 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.

Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

Op 13-10-2022 om 01:46 schreef Daniel Black:

...

On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma wrote:

...

Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that? In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add: [Service] ProtectHome=false

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here? in the open "/etc/systemd/system/mariadb.service.d/override.conf" add what was told above

[Service] ProtectHome=false and "Prevent accessing /home, /root and /run/user" as comment in system services should tell you something frankly why don#t you move the datadir out of your suerhome and change the path to the datadir in the config?

Op 13-10-2022 om 14:34 schreef Reindl Harald:

Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

...

Op 13-10-2022 om 01:46 schreef Daniel Black:

...

On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma   wrote:

...

Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that? In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add: [Service] ProtectHome=false

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here? in the open "/etc/systemd/system/mariadb.service.d/override.conf" add what was told above

[Service] ProtectHome=false

OK,thanks, I 'll give it a try

and "Prevent accessing /home, /root and /run/user" as comment in system services should tell you something

frankly why don#t you move the datadir out of your suerhome and change the path to the datadir in the config?

25 years ago I decided that the data is only useful to me, so why place it anywhere else than in my home directory? After all, that's where all my personal files reside, so why treat this data otherwise? There I have plenty of room in a raid6 configuration, where the default location is much smaller, and populated by the infamous (well, with me at least) btrfs filesystem. If I were convinced relocation would solve the problem, I could do that, but I have not seen that in the discussion so forth. Anyway, thanks for thinking with me - I may not agree with you in everything, but be sure I appreciate your replies! regards, Jogchum

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp

Op 13-10-2022 om 14:34 schreef Reindl Harald:

Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

...

Op 13-10-2022 om 01:46 schreef Daniel Black:

...

In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add: [Service] ProtectHome=false

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here? in the open "/etc/systemd/system/mariadb.service.d/override.conf" add what was told above

[Service] ProtectHome=false

I did that, and now the database starts with de db location set to the new one. But when I change it in /etc/my.cnf to the original (after making a copy of that location), tha server crashes again, with the same error messages. Maybe I should copy the databases in the original location to the new one, but probably not all of them? Just of my own databases? regards, Jogchum

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp

Op 13-10-2022 om 15:10 schreef Reindl Harald:

Am 13.10.22 um 14:54 schrieb Jogchum Reitsma:

...

...

frankly why don#t you move the datadir out of your suerhome and change the path to the datadir in the config? 25 years ago I decided that the data is only useful to me, so why place it anywhere else than in my home directory? After all, that's where all my personal files reside, so why treat this data otherwise?

but database files are not normal user files and can't be backuped while the service is running in a safe way like your personal document files

so it makes little sense to handle different beasts identical

OK, I get that. A philosophical issue in my feeling: what is more important, who's the owner of the (content of the) files, vs. what nature are they?

...

There I have plenty of room in a raid6 configuration, where the default location is much smaller, and populated by the infamous (well, with me at least) btrfs filesystem.

you can put the data in /home/mysql, make a bind-mount from /var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql

OK, so, to be quite sure, the command then is mount --bind <the-location-the-database-resides-now> /var/lib/mysql

voila, ProtectHome no longer hits because from the viewpoint of systemd and mariadb the datadir isn't below /home

https://unix.stackexchange.com/questions/198590/what-is-a-bind-mount

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp

Op 13-10-2022 om 15:36 schreef Reindl Harald:

Am 13.10.22 um 15:29 schrieb Jogchum Reitsma:

...

Op 13-10-2022 om 15:10 schreef Reindl Harald:

...

...

There I have plenty of room in a raid6 configuration, where the default location is much smaller, and populated by the infamous (well, with me at least) btrfs filesystem.

you can put the data in /home/mysql, make a bind-mount from /var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql

OK, so, to be quite sure, the command then is

mount --bind <the-location-the-database-resides-now> /var/lib/mysql yes, the only difference to any other mount is type "bind"

OK, I've done so, and now l /var/lib/mysql reads, no surprise, the contents of the original db location. In /etc/my.cnf I changed the location to /var/lib/mysql. But, starting the database server still fails with the same error and warning messages: okt 13 16:35:15 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 6638394. okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as process 92738 ... okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [Warning] Can't create test file /var/lib/mysql/linux-mkay.lower-test okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: [90B blob data] okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [ERROR] Aborting okt 13 16:35:15 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 13 16:35:15 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 13 16:35:15 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 6638394 and the job result is failed. So nothing is gained so far... regards, Jogchum

my /etc/fstab for /home while "/mnt/data/home" is the phyiscal location on a 4 TB RAID

/mnt/data/home /home  none  bind

i don't create bind mounts manually because you can mount, unmount and comment out them in fstab like every other mountpoint and i perfer them to survive reboots

I'm not sure my previous reply to this message came through, (maybe withheld because of html-format) so I give it a try again. Anyway, my problem is not solved yet. Funny thing is I don't see my own messages in this conversation (I'm on Thunderbird).. Op 13-10-2022 om 15:36 schreef Reindl Harald:

Am 13.10.22 um 15:29 schrieb Jogchum Reitsma:

...

Op 13-10-2022 om 15:10 schreef Reindl Harald:

...

...

There I have plenty of room in a raid6 configuration, where the default location is much smaller, and populated by the infamous (well, with me at least) btrfs filesystem.

you can put the data in /home/mysql, make a bind-mount from /var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql

OK, so, to be quite sure, the command then is

mount --bind <the-location-the-database-resides-now> /var/lib/mysql yes, the only difference to any other mount is type "bind"

my /etc/fstab for /home while "/mnt/data/home" is the phyiscal location on a 4 TB RAID

/mnt/data/home /home  none  bind

i don't create bind mounts manually because you can mount, unmount and comment out them in fstab like every other mountpoint and i perfer them to survive reboots

OK, I've done so, and now l /var/lib/mysql reads, no surprise, the contents of the original db location. In /etc/my.cnf I changed the location to /var/lib/mysql. But, starting the database server still fails with the same error and warning messages: okt 13 16:35:15 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 6638394. okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as process 92738 ... okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [Warning] Can't create test file /var/lib/mysql/linux-mkay.lower-test okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: [90B blob data] okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 16:35:15 0 [ERROR] Aborting okt 13 16:35:15 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 13 16:35:15 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 13 16:35:15 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 6638394 and the job result is failed. So nothing is gained so far... regards, Jogchum

Op 14-10-2022 om 12:45 schreef Marko Mäkelä:

Hi Jogchum,

On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma wrote: [...]

...

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 <...>

The correct advice is to install MariaDB Server (any version between 10.5 and 10.7 should do), shut down the database, and then upgrade to 10.8.

Before 10.5 (MDEV-12353), the last time the redo log format was changed was in MariaDB Server 10.2. If you got that error message when attempting to upgrade from something older than 10.5, then you should use the exact same major version of MariaDB to perform the shutdown, because there were some changes to the redo log format in each major release between 10.1 and 10.5.

I hope that someone can post a correction to that forum.

With best regards,

Marko

Hi Marko, I uninstalled the mariadb version from opensuse Tumbleweed, and downloaded and installed mariadb-10.6.10-linux-systemd-x86_64 (init system systemd)in /usr/local, according to the steps explained in the INSTALL_BINARY document. My assumption of the next steps to take is - create /usr/local/mysql/data - chown:chgrp it to mysql:mysql - in /etc/my.cnf, set /usr/local/mysql/data being the data directory (with opensuse, that is normally /var/lib/mysql, by the way) - do a mount --bind from my real data directory to /usr/local/mysql/data - then start the database with /usr/local/bin/mysqld_safe --user=mysql & Are the steps correct? If so, I would think the next thing to do is to stop the server with kill -15 <process-id>, remove the /usr/local/mariadb-10.6.10-linux-systemd-x86_64 directory, and install the current TW-version. After which things should be normal again... best regards, Jogchum

Sent it by accident only to Marko.... Op 08-11-2022 om 11:43 schreef Marko Mäkelä:

Hi Jogchum,

Sorry, I did not notice that you had a question for me. Why do I understand that so well .. :-) <...> Yes, the steps look roughly correct, possibly except for the part of shutting down the 10.6.10 server.

As far as the server is concerned, SIGTERM (signal 15) or SIGQUIT should initiate a shutdown.

I would think that on systemd, the preferred way to start and stop services would be the following:

systemctl start mariadb systemctl stop mariadb

Systemd could actually take the role of mysqld_safe.

It could be simplest to start the server directly as "mariadbd" so that nothing will keep restarting it after you initiated the shutdown. If you run the process in the foreground, then ctrl-\ should be SIGQUIT and it should initiate a shutdown.

You'd better check with "pgrep mariadbd" or "pgrep mysqld" that nothing is running, both before starting and after shutting down the server.

I will clarify the error message. For 10.3 and 10.4, it will look like this: https://github.com/MariaDB/server/commit/9ac8be4e2980aa995117147e39ae5b7ad79... Starting with 10.5, it may additionally suggest to use a version not later than MariaDB 10.4. Starting with 10.8, it may additionally suggest to use a version not later than MariaDB 10.7.

With best regards,

Marko Mäkelä

Hi Marko, Thanks a lot! What happened so far: On first start of the server by executing "/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd"as non-root from the command line, it complained "Can't read dir of '/etc/my.cnf.d"; indeed that dir did not exist. So I created it as root, and copied /etc/my.cnf to it. Restarting mariadbd the same way, I got ################################################################################ /usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd Warning: skipping '!includedir /etc/my.cnf.d' directive as maximum includerecursion level was reached in file /etc/my.cnf.d/my.cnf at line 124 2022-11-08 12:06:16 0 [Note] /usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd (server 10.6.10-MariaDB-log) starting as process 93785 ... /usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: Can't create file '/var/log/mysql/mysqld.log' (errno: 13 "Permission denied") 2022-11-08 12:06:16 0 [Note] InnoDB: Compressed tables use zlib 1.2.12 2022-11-08 12:06:16 0 [Note] InnoDB: Number of pools: 1 2022-11-08 12:06:16 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2022-11-08 12:06:16 0 [Note] InnoDB: Using Linux native AIO 2022-11-08 12:06:16 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2022-11-08 12:06:16 0 [Note] InnoDB: Completed initialization of buffer pool 2022-11-08 12:06:16 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=578191755,578191755 2022-11-08 12:06:17 0 [Note] InnoDB: Last binlog file './mysql-bin.000067', position 8811365 2022-11-08 12:06:17 0 [Note] InnoDB: 128 rollback segments are active. 2022-11-08 12:06:17 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" 2022-11-08 12:06:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2022-11-08 12:06:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2022-11-08 12:06:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2022-11-08 12:06:17 0 [Note] InnoDB: 10.6.10 started; log sequence number 578191767; transaction id 2935782 2022-11-08 12:06:17 0 [Note] Plugin 'FEEDBACK' is disabled. 2022-11-08 12:06:17 0 [Note] InnoDB: Loading buffer pool(s) from /home/jogchum/mysql/ib_buffer_pool 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 ################################################################################ I'm not quite sure if this would be enough to do the final steps (i.e. remove the 10.6.10 version, and install the Tumbleweed version). From the fact that innodb reports a crash recovery, I would say yes But the last errors make me somewhat unsure? regards, Jogchum