Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

Discussion in 'Database Discussion' started by bethimc, Mar 26, 2019.

  1. bethimc

    bethimc Member

    Joined:
    Nov 29, 2016
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Saratoga Springs, NY
    cPanel Access Level:
    Root Administrator
    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
    Code:
    [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"
    
     
  2. dalem

    dalem Well-Known Member PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,909
    Likes Received:
    127
    Trophy Points:
    368
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    /dev/shm need to be writable check your file permissions

    ls -l /dev/ | grep shm
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. bethimc

    bethimc Member

    Joined:
    Nov 29, 2016
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Saratoga Springs, NY
    cPanel Access Level:
    Root Administrator
    It is fully writable.

    drwxrwxrwt 2 root root 40 Mar 27 12:26 shm/
     
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,555
    Likes Received:
    2,182
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
  5. bethimc

    bethimc Member

    Joined:
    Nov 29, 2016
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Saratoga Springs, NY
    cPanel Access Level:
    Root Administrator
    Ticket is 11773495
     
    cPanelMichael likes this.
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,555
    Likes Received:
    2,182
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice