1

My system is using Ubuntu with mysql database:
I have a complex mysql select query to run. 

mysql -u root -p  myDB < query.sql

But when I try to run it it always gives me:

ERROR 3 (HY000) at line 1: Error writing file '/mnt/disk/tmp/MY0Wy7vA' (Errcode: 28 - No space left on device)

I have 11 GB free on disk and while the query is running, I keep track of it using 

df -h

and 

df -hi

to keep track of inodes

and I don't see any decrease in disk space while the query is running. All the time there is always 11 GB free on disk where the tmp folder is located.

This is the output of df -h: 

ubuntu@ip-10-0-0-177:~$ df -h  
Filesystem      Size  Used Avail Use% Mounted on  
udev            3.9G     0  3.9G   0% /dev  
tmpfs           799M   57M  742M   8% /run  
/dev/xvda1       30G   24G  5.4G  82% /  
tmpfs           3.9G     0  3.9G   0% /dev/shm  
tmpfs           5.0M     0  5.0M   0% /run/lock  
tmpfs           3.9G     0  3.9G   0% /sys/fs/cgroup  
/dev/xvdf1       50G   39G   11G  80% /mnt/disk  
tmpfs           799M     0  799M   0% /run/user/1000

This is the output of df -aTh:

Filesystem Type Size Used Avail Use% Mounted on
/dev/xvdf1 ext4 50G 39G 11G 80% /mnt/disk

Ahmedn1
  • 626
  • 1
  • 9
  • 20
  • Are you sure that partition has space? Is this your `/tmp` partition being full? What does `df -h` say? – tadman May 02 '18 at 18:05
  • Temporary storage permanently mounted at /mnt/disk? Really? – symcbean May 02 '18 at 18:58
  • @tadman it is not full I updated the answer to view the result of df -h – Ahmedn1 May 02 '18 at 19:26
  • You need to confirm which filesystem `/mnt/disk/tmp/` is on. Try `df -Ph /mnt/disk/tmp/`. Note that a query can create a temp table that is *very* large, and then as soon as the query finishes or is killed, the temp table is removed, so you run `df` and it looks like you have plenty of space. – Bill Karwin May 02 '18 at 19:42
  • It's worth noting that it can run out of space during an operation, then delete the temporary file, so unless you check `df` as it's running you won't be able to observe the problem. If you need to generate a >742MB file as part of an operation this will fail. Try `watch df -h` if you have (or can get) `watch` installed to monitor it as it's running. – tadman May 02 '18 at 20:13

1 Answers1

1

Take a look at the part of the error that indicate it’s actually writing to /tmp (see below)

: Error writing file '/mnt/disk/tmp/MY0Wy7vA

In some Linux distributions, /tmp is mounted as a tmpfs (ramdisk), so even if your disk has plenty of space you can get a "no space" error if you try to write too much there.

To investigate about the mount, try

$ cat /proc/mounts

OR $ cat /proc/self/mounts Or better yet

df - aTh

df -h /tmp

To see hidden ones, try

du -sc * .[^.]* | sort -n

Among other things you can try making MySQL use a different temp directory, or to not use tmpfs for /tmp

If TMPDIR is not set in my.cnf, MySQL uses the system default which is usually /tmp

You can change the MySQL tmp dir as the following suggests:

Changing the tmp folder of mysql

Credits :: https://github.com/LogBlock/LogBlock/issues/540

salah-1
  • 1,299
  • 11
  • 15