You are not logged in.

#1 2016-07-27 16:38:49

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Unable to restart mariadb after update with

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

#2 2016-07-27 16:40:55

Scimmia
Fellow
Registered: 2012-09-01
Posts: 11,463

Re: Unable to restart mariadb after update with

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

#3 2016-07-27 17:59:08

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

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

#4 2016-07-27 18:58:30

ukhippo
Member
From: Non-paged pool
Registered: 2014-02-21
Posts: 366

Re: Unable to restart mariadb after update with

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

Offline

#5 2016-07-28 03:39:58

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

ukhippo wrote:
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

#6 2016-07-28 05:05:41

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

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

#7 2016-07-28 05:15:54

Scimmia
Fellow
Registered: 2012-09-01
Posts: 11,463

Re: Unable to restart mariadb after update with

Which service file are you actually using? ie, which one is symlinked in the "wants" dir?

Offline

#8 2016-07-28 05:22:25

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

Scimmia wrote:

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

#9 2016-07-28 05:38:32

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

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

#10 2016-07-28 10:00:56

ukhippo
Member
From: Non-paged pool
Registered: 2014-02-21
Posts: 366

Re: Unable to restart mariadb after update with

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

#11 2016-07-31 06:27:20

severach
Member
Registered: 2015-05-23
Posts: 192

Re: Unable to restart mariadb after update with

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

#12 2016-07-31 11:07:06

Spider.007
Member
Registered: 2004-06-20
Posts: 1,175

Re: Unable to restart mariadb after update with

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

#13 2016-07-31 17:41:44

severach
Member
Registered: 2015-05-23
Posts: 192

Re: Unable to restart mariadb after update with

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

#14 2016-09-03 15:07:09

stoencold111
Member
Registered: 2016-07-06
Posts: 16

Re: Unable to restart mariadb after update with

severach wrote:

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

#15 2016-09-05 05:11:50

severach
Member
Registered: 2015-05-23
Posts: 192

Re: Unable to restart mariadb after update with

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

#16 2016-09-05 05:21:53

Scimmia
Fellow
Registered: 2012-09-01
Posts: 11,463

Re: Unable to restart mariadb after update with

severach wrote:

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

#17 2018-08-10 09:12:24

ghostintheweb
Member
Registered: 2018-08-10
Posts: 1

Re: Unable to restart mariadb after update with

Run the following command
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Offline

#18 2018-08-10 09:26:35

V1del
Forum Moderator
Registered: 2012-10-16
Posts: 21,427

Re: Unable to restart mariadb after update with

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.

Online

Board footer

Powered by FluxBB