Change tmpdir MariaDB on Linux not working

Question

I've tried to change tmpdir of mariadb. I changed the value of variable tmpdir but when I restart mariadb the tmpdir is always /tmp. This is my configuration file.

[mysqld]

#
# * Basic Settings
#

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /home/utente/utente/media/E_linux/dati
tmpdir                  = /home/utente/utente/media/E_linux/tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking

When I restart mysql I have an error:

mariadb.service - MariaDB 10.6.4 database server
     Loaded: loaded (/etc/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─dontprotecthome.conf, migrated-from-my.cnf-settings.conf
     Active: failed (Result: exit-code) since Sat 2021-09-25 10:30:58 CEST; 3min 32s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 286590 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 286591 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 286593 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-en>
    Process: 286644 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
   Main PID: 286644 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"


set 25 10:30:58 WS-C621E-SAGE systemd[1]: Starting MariaDB 10.6.4 database server...
set 25 10:30:58 WS-C621E-SAGE mariadbd[286644]: 2021-09-25 10:30:58 0 [Note] /usr/sbin/mariadbd (server 10.6.4-MariaDB-1:10.6.4+maria~focal) starting as process 286644 ...
set 25 10:30:58 WS-C621E-SAGE mariadbd[286644]: 2021-09-25 10:30:58 0 [Warning] Can't create test file /home/utente/utente/media/E_linux/dati/WS-C621E-SAGE.lower-test
set 25 10:30:58 WS-C621E-SAGE mariadbd[286644]: [122B blob data]
set 25 10:30:58 WS-C621E-SAGE mariadbd[286644]: 2021-09-25 10:30:58 0 [ERROR] Aborting
set 25 10:30:58 WS-C621E-SAGE systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
set 25 10:30:58 WS-C621E-SAGE systemd[1]: mariadb.service: Failed with result 'exit-code'.
set 25 10:30:58 WS-C621E-SAGE systemd[1]: Failed to start MariaDB 10.6.4 database server.
set 25 10:31:25 WS-C621E-SAGE systemd[1]: /etc/systemd/system/mariadb.service.d/dontprotecthome.conf:1: Assignment outside of section. Ignoring.

However, mariadb working and datadir is changed but not tmpdir.

I'd look for other lines in the config file that set this value. The last line in the file that sets tmpdir takes priority. — Bill Karwin, Sep 25 '21 at 18:15
Also try inserting an obviously bogus line in the config file and restart mysqld. See if that causes an error. If it does not, then mysqld isn't reading that config file. — Bill Karwin, Sep 25 '21 at 18:15

score 0 · Answer 1 · answered Sep 26 '21 at 10:12

0

Run these commands on the server .

chown -R mysql:mysql  /home/utente/utente/media/E_linux/dati/
chown -R mysql:mysql  /home/utente/utente/media/E_linux/tmp/

then restart MySQL service on the server .

systemctl restart mysql

answered Sep 26 '21 at 10:12

Rakesh Gadhwal

183
3

I can't set up permissions because it is not a unix filesystem. But mariadb write in /home/utente/utente/media/E_linux/dati folder... – Maria Sep 26 '21 at 11:52
can I know OS ? – Rakesh Gadhwal Sep 26 '21 at 14:19
Ubuntu. The path /home/utente/utente/media/E_linux is mounted on another disk – Maria Sep 26 '21 at 14:21
@Maria for change tmp dir in msql . MySQL requires ownership (mysql:mysql) on tmp dir.So please check with mount service (NFS) NFS should be rw – Rakesh Gadhwal Sep 26 '21 at 14:42
Also for change datadir MySQL requires ownership but it working, I dont't know why – Maria Sep 26 '21 at 14:43
once the MySQL starts that time MySQL creating some tmp file so in your case MySQL is unable to write any temp file in tmp dir due to ownership. – Rakesh Gadhwal Sep 26 '21 at 14:46
Unfortunately I don't have enough space on the main partition and since I can't set permissions on the second disk, is there another way to write to that path? – Maria Sep 26 '21 at 14:49
1

@Maria you need to use symbolic link https://www.digitalocean.com/community/tutorials/how-to-change-a-mysql-data-directory-to-a-new-location-using-a-symlink – Rakesh Gadhwal Sep 26 '21 at 14:53

Change tmpdir MariaDB on Linux not working

1 Answers1