[CPANEL-26532] The user mysql cannot write to the configured temporary file directory

We have 4 dedicated servers. All WHM v78.0.18 and CentOS 6.10 (I know...working on it...one thing at a time). 3 of them are at PHP 7.1, my problem child is PHP 5.6 (working on final script updates for 2 clients). I have been upgrading MySQL to 5.6 on all the servers this week. We have complete database backups. All others upgraded fine. Problem child kicks out this fatal error, which I do not believe is related to the PHP version. Any suggestions? -------------------------------- The user "mysql" cannot write to the configured temporary file directory "/dev/shm:/tmp": (XID 37pu99) The system failed to create the temporary file "/dev/shm:/tmp/5809.WHOSTMGR_BIN_WHOSTMGR_PL__.tmp.c4761bec" because of an error: No such file or directory "mysql" needs permission to create files in this directory, and enough free space must exist to write temporary files. ----------------------------------- ipcs -lm gives the following ------ Shared Memory Limits -------- max number of segments = 4096 max seg size (kbytes) = 67108864 max total shared memory (kbytes) = 17179869184 min seg size (bytes) = 1 Additional Info Disk usage is at 44% of a 1TB drive /etc/my.conf below

[mysqld]
datadir="/var/lib/mysql"
socket="/var/lib/mysql/mysql.sock"
symbolic-links=0
collation_server=utf8_unicode_ci
character_set_server=utf8
default-storage-engine=MyISAM
#innodb_force_recovery = 1
tmpdir=/dev/shm:/tmp

innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
query_cache_size=64M
thread_cache_size=16 # can be increased on servers with large numbers of active users
key_buffer_size=64M
max_allowed_packet=536870912
table_open_cache=2048 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';
wait_timeout=300 # can be increased if using persistent connections
max_user_connections=25
open_files_limit=16384

#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)

#query_cache_limit=2M # leave at default unless there is a good reason
#join_buffer=2M # leave at default unless there is a good reason
#sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason

collation_server=utf8_unicode_ci
character_set_server=utf8

#innodb_flush_method=O_DSYNC # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=128M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_thread_concurrency=6 # Number of physical + virtual CPU's, be careful of adding more

table_definition_cache=2048 # increase by the same factor as table_open_cache
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=64M

general_log=0
slow_query_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=20 # select * from mysql.slow_log order by start_time desc limit 10;

innodb_io_capacity=100

tmp_table_size = 768M
max_heap_table_size = 768M
max_connections=150 # Should be between 100-150, increase *slowly* because it causes MySQL to consume more memory!

[mysqld_safe]
log-error="/var/log/mysqld.log"
pid-file="/var/run/mysqld/mysqld.pid"

dalem

March 27, 2019 17:38

/dev/shm need to be writable check your file permissions ls -l /dev/ | grep shm

bethimc

March 27, 2019 17:41

It is fully writable. drwxrwxrwt 2 root root 40 Mar 27 12:26 shm/

cPanelMichael

March 27, 2019 19:35

Hello @bethimc, Can you open a

March 27, 2019 19:57

March 28, 2019 21:49

Hello, To update, internal case CPANEL-26532 was opened to report an issue where "The user "mysql" cannot write to the configured temporary file directory" error appears in WHM >> MySQL/MariaDB if you attempt to upgrade and the system's /etc/my.cnf file uses multiple tmpdir values. I'll monitor this case and update this thread with more information as it becomes available. In the meantime, the workaround is to temporarily modify /etc/my.cnf so that only one of the tmpdir values is enabled, restart MySQL, and then proceed with the upgrade. Thank you.

[CPANEL-26532] The user mysql cannot write to the configured temporary file directory

Comments

Didn't find what you were looking for?