MySQL Can t create write to file tmp sql 3c6 0 MYI Errcode 2 - What does it even mean

Question

For some reason my production DB decided to spew out this message  All application calls fail to the DB with the error   PreparedStatementCallback  SQL     long sql statement here       Can t create write to file   tmp  sql 3c6 0 MYI   Errcode  2    nested exception is java sql SQLException  Can t create write to file   tmp  sql 3c6 0 MYI   Errcode  2    I have no idea  what this even means  There is no file  sql 3c6 0 MYI in  tmp and I can t create one with a   character for some reason  Has anyone heard about it or seen this error  What could be wrong and some possible things to look at   The MySQL DB seems to be up and running and can be queried via the console but the application can t seem to get through to it  There was no change to the application code files  It just happened out the blue  So I m not even sure where to start look or what resolution tactics to apply  Any ideas

User · Answer

A better way worked for me       chown root root  tmp   chmod 1777  tmp    etc init d mysqld restart   That is it    See here  http   nixcraft com databases-servers 14260-error-1-hy000-cant-create-write-file-tmp-sql 9f3 0-myi-errcode-13-a html  http   smashingweb info solved-mysql-tmp-error-cant-createwrite-to-file-tmpmykbo3bl-errcode-13

User · Answer

For me this issue came after a long period of not using mysql nor the webserver  So I was sure that my settings where correct  Simply restarting the service fixes this issue  The weird part about the issue is that one can still connect to the database  and even query add tables using the mysql tool  for example    mysql -u root -p   I restarted using     systemctl start mysqld service   or     service mysqld restart or      etc init d mysqld restart  Note   depending on the machine environment on of these commands should restart the service

User · Answer

Often this means your  tmp partition has run out of space and the file can t be created  or for whatever reason the mysqld process cannot write to that directory because of permission problems  Sometimes this is the case when selinux rains on your parade   Any operation that requites a  temp file  will go into the  tmp directory by default  The name you re seeing is just some internal random name

User · Answer

Check permission issues  mysql config    Also check if you haven t reached disk space  quota limits   Note  Some systems are limiting number of files  not just space   deleting some old session files helped fixed the issue in my case

User · Answer

I meet this error too when I run a wordpress on my Fedora system   I googled it  and find a way to fix this   Maybe this will help you too    check mysql config   my cnf   cat  etc my cnf   grep tmpdir   I can t see anything in my my cnf add tmpdir  tmp to my cnf under  mysqld  restart web app and mysql server   etc init d mysqld restart

User · Answer

On Fedora with systemd MySQL gets private  tmp directory  In  proc PID of MySQL mountinfo you will find the line like      156 129 8 1  tmp systemd-namespace-AN7vo9 private  tmp rw relatime -   ext4  dev sda1 rw seclabel data ordered   This means a temporary folder  tmp systemd-namespace-AN7vo9 private is mounted as  tmp in private namespace of MySQL process  Unfortunately this folder is deleted by tmpwatch if not used frequently   I modified  etc cron daily tmpwatch and inserted the exclude pattern -X   tmp systemd-namespace   like this    usr sbin tmpwatch   flags  -x  tmp  X11-unix -x  tmp  XIM-unix           -x  tmp  font-unix -x  tmp  ICE-unix -x  tmp  Test-unix           -X   tmp systemd-namespace             -X   tmp hsperfdata    10d  tmp   The side effect is that unused private namespace folders will not be deleted automatically

User · Answer

Its due to access control security policies specifically when SELinux is enabled it won t allow external executables to create temporary files in the system locations   Disable SELinux by issuing below command   echo 0   selinux enforce  You can now start mysql it wont give any permission related errror while reading writing to  tmp or system directories   In case you wish to enable the SELinux security back change 0 to 1 in above command

User · Answer

The filename looks like a temporary table created by a query in MySQL   These files are often very short-lived  they re created during one specific query and cleaned up immediately afterwards     Yet they can get very large  depending on the amount of data the query needs to process in a temp table   Or you may have multiple concurrent queries creating temp tables  and if enough of these queries run at the same time  they can exhaust disk space   I do MySQL consulting  and I helped a customer who had intermittent disk full errors on his root partition  even though every time he looked  he had about 6GB free   After we examined his query logs  we discovered that he sometimes had four or more queries running concurrently  each creating a 1 5GB temp table in  tmp  which was on his root partition   Boom   Solutions I gave him    Increase the MySQL config variables tmp table size and max heap table size so MySQL can create really large temp tables in memory   But it s not a good idea to allow MySQL to create 1 5GB temp tables in memory  because there s no way to limit how many of these are created concurrently   You can exhaust your memory pretty quickly this way  Set the MySQL config variable tmpdir to a directory on another disk partition with more space  Figure out which of your queries is creating such big temp tables  and optimize the query   For example  use indexes to help that query reduce its scan to a smaller slice of the table   Or else archive some of the data in the tale so the query doesn t have so many rows to scan

User · Answer

it s very easy  you just grant the  tmp folder as 777 permission   just type   chmod -R 777  tmp

User · Answer

Tremendous thanks to ArturZ for pointing me in the right direction on this  I don t have tmpwatch installed on my system so that isn t the cause of the problem in my case  But the end result is the same  The private  tmp that systemd creates is getting removed  Here s what happens    systemd creates a new process via clone   with the CLONE NEWNS flag to obtain a private namespace  Or maybe it calls unshare   with CLONE NEWNS  Same thing  systemd creates a subdirectory in  tmp  e g   tmp systemd-namespace-XRiWad private  and mounts it on  tmp  Because CLONE NEWNS was set in  1  this mountpoint is invisible to all other processes  systemd then invokes mysqld in this private namespace  Some specific database operations  e g   describe     create  amp  remove temporary files  which has the side effect of updating the timestamp on  tmp systemd-namespace-XRiWad private  Other database operations execute without using  tmp at all  Eventually 10 days go by where even though the database itself remains active  no operations occur that update the timestamp on  tmp systemd-namespace-XRiWad private   bin systemd-tmpfiles comes along and removes the  old   tmp systemd-namespace-XRiWad private directory  effectively rendering the private  tmp unusable for mysqld while the public  tmp remains available for everything else on the system    Restarting mysqld works because this starts everything over again at step  1  with a brand new private  tmp directory  However  the problem eventually comes back again  And again   The simple solution is to configure  bin systemd-tmpfiles so that it preserves anything in  tmp with the name  tmp systemd-namespace-   I did this by creating  etc tmpfiles d privatetmp conf with the following contents   x    tmp systemd-namespace-  x    tmp systemd-namespace-  private   Problem solved

User · Answer

For those using VPS   virtual hosting   I was using a VPS  getting errors with MySQL not being able to write to  tmp  and everything looked correct   I had enough free space  enough free inodes  correct permissions  Turned out the problem was outside my VPS  it was the machine hosting the VPS that was full  I only had  virtual space  in my file system  but the machine in the background which hosted the VPS had no  physical space  left  I had to contact the VPS company any they fixed it   If you think this might be your problem  you could test writing a larger file to  tmp  1GB    dd if  dev zero of  tmp file txt count 1024 bs 1048576    I got a No space left on device error message  which was a giveaway that it was a disk volume in the background that was full

User · Answer

I m using mariadb  When I try to put this line at  etc my cnf    mysqld  tmpdir  tmp    It solved the error generated from website frontend related to  tmp  But  it has backend problem with  tmp  Example  when I try to rebuild mariadb from the backend  it couldn t read the  tmp dir  and then generated the similar error   mysqldump  Couldn t execute  show fields from  wp autoupdate    Can t create write to file   tmp  sql 1680 0 MAI   Errcode  2  No such file or directory    1    So this one work for both front end and back end   1  mkdir  var lib mysql tmp  2  chown mysql mysql  var lib mysql tmp  3  Add the following line into the  mysqld  section       tmpdir    var lib mysql tmp   4  Restart mysqld  eg  Centos7  systemctl restart mysqld

User · Answer

On debian 7 5 I got the same error  I realized the  tmp folder owner and permissions were off  As another answer suggested I did as follows  must be root    chown root root  tmp  amp  amp  chmod 1777  tmp   I did not even have to restart mysql daemon

User · Answer

On an Ubuntu box  I started getting this error after moving  tmp to a different volume  symlink   Even after setting the required permission 1777  the issue was not resolved    MySQL is protected by AppArmor  which was disallowing writes to the new tmp location  mnt tmp   I had to add the following lines to  etc apparmor d abstractions user-tmp to fix this     owner  mnt tmp       rwkl           mnt tmp             rw

[mysql] MySQL: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2) - What does it even mean?

Examples related to mysql

Examples related to exception

Examples related to jdbc