You are not logged in.
Pages: 1
Topic closed
I have just updated `mariadb` to the latest version. I have the database folder in `/home/mysql`. Out of nowhere, I can't get it restart with `systemctl restart mysqld`. Here's the error log:
-- Unit mariadb.service has begun starting up.
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28 0:25:20 140554735999040 [Note] /usr/sbin/mysqld (mysqld 10.1.16-MariaDB)
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28 0:25:20 140554735999040 [Warning] Can't create test file /home/mysql/tao
Jul 28 00:25:20 tao mysqld[8523]: [87B blob data]
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28 0:25:20 140554735999040 [ERROR] Aborting
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 28 00:25:20 tao systemd[1]: Failed to start MariaDB database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mariadb.service has failed.
--
-- The result is failed.
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Unit entered failed state.
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 28 00:25:20 tao sudo[8405]: pam_unix(sudo:session): session closed for user root
This is probably the fatal error :
`Can't create test file /home/mysql/tao`
Does it have anything to do with my db folder being in the home directory? I wonder what has been changed that prevents mariadb from accessing `/home/mysql`.
/etc/systemd/system/mariadb.service file:
#
# /etc/systemd/system/mariadb.service
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others
[Unit]
Description=MariaDB database server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
##############################################################################
## Core requirements
##
Type=notify
# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false
##############################################################################
## Package maintainers
##
User=mysql
Group=mysql
# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK
# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full
NoNewPrivileges=true
PrivateDevices=true
# Prevent accessing /home, /root and /run/user
ProtectHome=true
# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true
# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && \
systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"
# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql
# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
KillMode=process
KillSignal=SIGTERM
# Don't want to see an automated SIGKILL ever
SendSIGKILL=no
# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s
UMask=007
##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following will override this file's settings.
# Useful options not previously available in [mysqld_safe]
# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600
# Explicitly start with high IO priority
# BlockIOWeight=1000
# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=true
##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##
# Number of files limit. previously [mysqld_safe] open-file-limit
LimitNOFILE=16364
# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=
# Nice priority. previously [mysqld_safe] nice
# Nice=-5
# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"
# Library substitutions. previously [mysqld_safe] malloc-lib with explict paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=
# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3
# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......
# crash-script equalivent
# FailureAction=
/home.mysql folder permission
drwxr-xr-x 6 mysql mysql 12288 Jul 27 19:34 mysql
my.cnf
[client]
port = 3306
socket = /run/mysqld/mysqld.sock
[mysqld]
init_connect = 'SET collation_connection = utf8_general_ci,NAMES utf8'
collation_server = utf8_general_ci
#character_set_client = utf8
skip-character-set-client-handshake
character_set_server = utf8
port = 3306
socket = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
bind-address = 127.0.0.1
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
datadir = /home/mysql
innodb_data_home_dir = /home/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Last edited by stoencold111 (2016-07-27 16:43:38)
Offline
If the service file is in /etc, it's one that you put there, not one installed by the package. How does it compare to the one currently in the package?
Offline
My service file is in /etc/systemd/system/multi-user.target.wants/. I can run `sudo mysqld -u root` to start mariadb, but not with the service file.
Last edited by stoencold111 (2016-07-27 18:00:21)
Offline
Does it have anything to do with my db folder being in the home directory? I wonder what has been changed that prevents mariadb from accessing `/home/mysql`.
/etc/systemd/system/mariadb.service file:
...snip... # Prevent accessing /home, /root and /run/user ProtectHome=true ...snip...
Is your answer
Offline
stoencold111 wrote:Does it have anything to do with my db folder being in the home directory? I wonder what has been changed that prevents mariadb from accessing `/home/mysql`.
/etc/systemd/system/mariadb.service file:
...snip... # Prevent accessing /home, /root and /run/user ProtectHome=true ...snip...
Is your answer
No, it doesnt help turning it to false
Offline
I suspect there is something wrong with the service file, because I'm able to manually run it as user mysql
[mysql@xxx xxx]$ cd '/usr' ; /usr/bin/mysqld --datadir='/home/mysql'
But `running systemctl start mysqld` fails to start the server. I have appended `data-dir=/home/mysql` at
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --data-dir=/home/mysql
Still not working
/usr/lib/systemd/system/mariadb.service
#
# /etc/systemd/system/mariadb.service
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others
[Unit]
Description=MariaDB database server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
##############################################################################
## Core requirements
##
Type=notify
# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false
##############################################################################
## Package maintainers
##
User=mysql
Group=mysql
# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK
# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full
NoNewPrivileges=true
PrivateDevices=true
# Prevent accessing /home, /root and /run/user
ProtectHome=true
# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true
# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && \
systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"
# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql
# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --data-dir=/home/mysql
# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
KillMode=process
KillSignal=SIGTERM
# Don't want to see an automated SIGKILL ever
SendSIGKILL=no
# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s
UMask=007
##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following will override this file's settings.
# Useful options not previously available in [mysqld_safe]
# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600
# Explicitly start with high IO priority
# BlockIOWeight=1000
# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=true
##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##
# Number of files limit. previously [mysqld_safe] open-file-limit
LimitNOFILE=16364
# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=
# Nice priority. previously [mysqld_safe] nice
# Nice=-5
# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"
# Library substitutions. previously [mysqld_safe] malloc-lib with explict paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=
# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3
# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......
# crash-script equalivent
# FailureAction=
Last edited by stoencold111 (2016-07-28 05:06:06)
Offline
Which service file are you actually using? ie, which one is symlinked in the "wants" dir?
Offline
Which service file are you actually using? ie, which one is symlinked in the "wants" dir?
/etc/systemd/system/multi-user.target.wants/mariadb.service links to /usr/lib/systemd/system/mariadb.service
I have tried some modifications in that file like:
# Prevent accessing /home, /root and /run/user
ProtectHome=false
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --datadir=/home/mysql
MariaDB Status:
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2016-07-28 13:19:04 HKT; 5min ago
Process: 1565 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --data-dir=/home/mysql (code=exited, status=1/FAILURE)
Process: 1508 ExecStartPre=/bin/sh -c VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 1506 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 1565 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
Last edited by stoencold111 (2016-07-28 05:33:09)
Offline
For unknown reasons, I get it working by deleting `/var/lib/mysql` and creating a soft link from `var/lib/mysql` to `/home/mysql`.
There is no need of any modifications in /usr/lib/systemd/system/mariadb.service. I don't understand why it just works. Maybe someone can explain it.
Last edited by stoencold111 (2016-07-28 05:41:57)
Offline
You've obviously not specified your different datadir in /etc/mysql/my.cnf -- /var/lib/mysql is the default datadir for mariadb in Arch.
You don't ever modify /usr/lib/systemd/... files. Never ever. Either replace totally in /etc/systemd/... or use drop-ins.
Offline
I just worked through all this. I also store my data in /home. ProtectHome is the problem. It was added by upstream from 10.1.15 to 10.1.16 . Did you forget to 'systemctl daemon-reload' after commenting the offending line out?
Unfortunately the soft link as decribed by stoencold111 breaks things.
# # I have already removed /var/lib/mysql
# ln -s /home/mysql /var/lib/mysql ## don't do it this way, see below.
# ls -l /var/lib | grep mysql
lrwxrwxrwx 1 root root 11 Jul 31 02:23 mysql -> /home/mysql/
# pacman -S mariadb
:: Processing package changes...
error: cannot remove /var/lib/mysql/ (Not a directory)
(1/1) reinstalling mariadb
# ls -l /var/lib | grep mysql
drwx------ 2 mysql mysql 4096 Jul 18 02:55 mysql/
pacman or one of its henchmen trampled my soft link with a new folder. The next upgrade will crash your app in possibly amazing ways.
I fixed it this way
# ln -s /home/mysql /var/lib/mysqldata
# vi /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysqldata
This will work until systemd gets all protectionist and enforces ProtectHome by following soft links like samba does with follow symlinks and wide links.
I'd like ProtectHome to be removed by the PKGBUILD in both services. I consider it to be an arbitrary and capricious addition. It could at least be replaced with some other command that does not protect /home.
The reference to /sbin should also be fixed.
Last edited by severach (2016-07-31 06:41:09)
Offline
I think ProtectHome is a very good default. If you decide your daemon is a user (which it isn't) you have to bear the consequences.
This is easy to fix anyway; remove the symlink and use https://wiki.archlinux.org/index.php/Sy … ided_units to overload or customize the mysqld.service
Offline
The data isn't in /home because mysql is a user. It's there because /home is where the disk space is. Leaving the data in /var/lib quickly fills up the root partition.
...
Turns out my method doesn't work either. On the next boot, no mysql. I just copied the service and removed the offending line.
Last edited by severach (2016-07-31 23:18:28)
Offline
The data isn't in /home because mysql is a user. It's there because /home is where the disk space is. Leaving the data in /var/lib quickly fills up the root partition.
...
Turns out my method doesn't work either. On the next boot, no mysql. I just copied the service and removed the offending line.
Do you have any other solutions?
I just updated mariadb and needed to do the followings:
Update /etc/systemd/system/multi-user.target.wants/mariadb.service:
#ProtectHome=true
ProtectHome=false
Then:
systemctl daemon-reload
systemctl start mariadb
Last edited by stoencold111 (2016-09-03 15:11:57)
Offline
Don't bother modifying the original. It's not listed in backup=() so it will be callously overwritten on every upgrade. Copy it to a new name and modify that one. Disable the original.
# cd '/usr/lib/systemd/system'
# cp -p 'mariadb.service' 'mariadb-foobar.service'
# sed -i -e 's:^ProtectHome:#&:g' 'mariadb-foobar.service'
# systemctl daemon-reload
# systemctl disable 'mariadb.service'
# # systemctl stop 'mariadb.service'
# systemctl enable 'mariadb-foobar.service'
# # systemctl start 'mariadb-foobar.service'
# # systemctl status 'mariadb-foobar.service'
There's probably a way to fix this with Pacman hooks.
Last edited by severach (2016-09-05 05:14:22)
Offline
Don't bother modifying the original. It's not listed in backup=() so it will be callously overwritten on every upgrade. Copy it to a new name and modify that one. Disable the original.
# cd '/usr/lib/systemd/system' # cp -p 'mariadb.service' 'mariadb-foobar.service' # sed -i -e 's:^ProtectHome:#&:g' 'mariadb-foobar.service' # systemctl daemon-reload # systemctl disable 'mariadb.service' # # systemctl stop 'mariadb.service' # systemctl enable 'mariadb-foobar.service' # # systemctl start 'mariadb-foobar.service' # # systemctl status 'mariadb-foobar.service'
There's probably a way to fix this with Pacman hooks.
Don't do that. There's ways of overriding parts of a service or the entire thing. Copying it in /usr is not one of them. Check the wiki.
Last edited by Scimmia (2016-09-05 05:23:21)
Offline
Run the following command
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
Offline
That's really only tangentially related to the issue at hand, and it was 2 years ago, so unlikely to still be relevant.
Don't necrobump: https://wiki.archlinux.org/index.php/Co … bumping.22
Closing.
Offline
Pages: 1
Topic closed