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

[bethimc]

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]

/dev/shm need to be writable check your file permissions

ls -l /dev/ | grep shm

 

[bethimc]

/dev/shm need to be writable check your file permissions

ls -l /dev/ | grep shm

It is fully writable.

drwxrwxrwt 2 root root 40 Mar 27 12:26 shm/

 

[cPanelMichael]

Hello @bethimc,

Can you open a support ticket so we can access the affected system to see what's happening? You can post the ticket number here and we'll link this thread to it.

Thank you.

 

[bethimc]

Hello @bethimc,

Can you open a support ticket so we can access the affected system to see what's happening? You can post the ticket number here and we'll link this thread to it.

Thank you.

Ticket is 11773495

 

[cPanelMichael]

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.