[Maria-discuss] Maria-db refuses to start
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
Hi, Jogchum, On Oct 12, Jogchum Reitsma wrote:
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.
normally the correct fix for that would be to install 10.5.8, start the server, shut it down, then upgrade.
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.
That's not how I read it at all. It seems to be about innodb_log_files_in_group option. Are you sure the url is correct?
2022-10-02 21:27:17 0 [Warning] Can't create test file /home/jogchum/mysql_recover/linux-mkay.lower-test ... 2022-10-02 21:27:17 0 [ERROR] Aborting
Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that?
permission problem, perhaps? it's not particularly important, as it's only a warning, and not a reason for the server failing to start. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Op 12-10-2022 om 16:48 schreef Sergei Golubchik:
Hi, Jogchum,
normally the correct fix for that would be to install 10.5.8, start the server, shut it down, then upgrade.
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. That's not how I read it at all. It seems to be about innodb_log_files_in_group option. Are you sure the url is correct?
No, sorry, it's not. The advice given there, to rename the log files, did not work for me. Funny thing, I can't find the posting any more which *did* the trick with editing my.cnf etc, I described above.....
2022-10-02 21:27:17 0 [Warning] Can't create test file /home/jogchum/mysql_recover/linux-mkay.lower-test ... 2022-10-02 21:27:17 0 [ERROR] Aborting
Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test can't be created and how to solve that? permission problem, perhaps?
All files and dir's under /home/jogchum/mysql_recover have mysql:mysql as owner:group
it's not particularly important, as it's only a warning, and not a reason for the server failing to start.
I get that, but then what is it that mariadb won't start in the new environment.... Thanks for your reply! regards, Jogchum
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
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
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?
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 - 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.
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? 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
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.
Op 13-10-2022 om 14:08 schreef Reindl Harald:
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
Well, not only on (re-)install, but also when you create a new user via Yast, the default is 755.
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?
I don't think I have to justify that here, after all, it sits some 25 years on the place it is now (which is *not* /home/jogchum/mysql_recover, I explain that below). Never was a problem.
- 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...
Yes, and in the Netherlands even more than in some other EU-countries.But to read it for others than me, my wife and son, one should hack one's way into my system. Which is not impossible, of course, but tin that case I think the damage will be more than a few addresses being read.
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.
Yes, it was a fresh installation, because I gave a new location, /home/jogchum/mysql_recover, before starting the db. As said in my first post, this was a solution that worked for someone with the same problem: roughly: - change the location of the db in /etc/my.cnf - start the database server, which will create the default environment in the new location - copy the existing database files to that new locatioN - restart the database server. In my case, this recipe fails in the second step: the new location is populated, but the server crashes immediately. Journalctl show only a warning regarding not being able to create a test file: Warning] Can't create test file /home/jogchum/mysql_recover/linux-mkay.lower-test which, according to Sergei, should not be a cause for the server to crash.And ideed, it's only a warning, no error. So, it still is a riddle why the server won't start. 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
On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma <jogchum.reitsma@gmail.com> 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...
Op 13-10-2022 om 01:46 schreef Daniel Black:
On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma <jogchum.reitsma@gmail.com> 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
If I issue that command, it translates to nano/etc/systemd/system/mariadb.service.d/.#override.conf5a67e18e4e904390 and the first lines of that file read ## Editing /etc/systemd/system/mariadb.service.d/override.conf ### Anything between here and the comment below will become the new contents of the file ### Lines below this comment will be discarded 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? regards, Jogchum
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 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 <jogchum.reitsma@gmail.com> 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 <jogchum.reitsma@gmail.com> 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
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
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 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
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
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
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
On Thursday 13 October 2022 at 16:36:41, Jogchum Reitsma wrote:
mount --bind <the-location-the-database-resides-now> /var/lib/mysql
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:
Have you tried setting the "alternative location" of your MariaDB files to some directory under /var/lib instead of trying to put them somewhere under /home? Antony. -- BASIC is to computer languages what Roman numerals are to arithmetic. Please reply to the list; please *don't* CC me.
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 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
On Thursday 13 October 2022 at 15:22:01, Jogchum Reitsma wrote:
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.
What are you actually trying to achieve here? What problem do you have with running MariaDB with the database files in the standard location? Antony. -- Schrödinger's rule of data integrity: the condition of any backup is unknown until a restore is attempted. Please reply to the list; please *don't* CC me.
Hi Jogchum, On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma <jogchum.reitsma@gmail.com> 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 advice given there is plain wrong, because it will cause the contents of the write-ahead log to be ignored. The 10.8 startup failure already proves that the log is nonempty, that is, some changes must be applied. It will result in a database whose state does not correspond to a single given point of time. Some pages are older, some newer. That approach could cause various crashes, potentially a long time afterwards. Many of the crashes on corrupted InnoDB data were recently fixed in MDEV-13542. I changed the InnoDB redo log format in MariaDB Server 10.8 in MDEV-14425. At the same time, in https://jira.mariadb.org/browse/MDEV-27199 I implemented a check that prevents corruption if such incorrect and dangerous "advice" is being followed. 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 -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
Op 14-10-2022 om 12:45 schreef Marko Mäkelä:
Hi Jogchum,
On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma <jogchum.reitsma@gmail.com> 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
Hi Jogchum, Sorry, I did not notice that you had a question for me. On Sun, Oct 16, 2022 at 11:02 AM Jogchum Reitsma <jogchum.reitsma@gmail.com> wrote:
Op 14-10-2022 om 12:45 schreef Marko Mäkelä:
Hi Jogchum,
On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma <jogchum.reitsma@gmail.com> 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. [...] 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.
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ä -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
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
participants (6)
-
Antony Stone -
Daniel Black -
Jogchum Reitsma -
Marko Mäkelä -
Reindl Harald -
Sergei Golubchik