My disk is full, an .MAD file in tmpdir takes up all the space on the file system (179G out of 189G)
from log:
[Warning] mysqld: Disk is full writing '/var/tmp/#sql_b6f_0.MAD' (Errcode: 28). Waiting for someone to free space
Can I stop mariadb, or do I need to free some space first and then stop it?
I was able to do a service mysqld stop and that deleted the files in tmpdir, that solved the problem....
Aaargh I had been searching for a fix for exactly 5 hours! However I'm quite the beginner so yeah... We're running our MariaDB on a SUSE Linux server in production, so stopping the whole mysql service was not a good option for me.
I ran a select query with nested selects using order by and inner join and this was my exact error:
Error Code: 1021 Disk full (/tmp/#sql_xxx_y.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
By connecting to the server and running
cd /tmp
ls -al
or
ls-lh
I could find the culprits: #sql_xxx_y.MAD (as big as my total available space on tmp partition) and #sql_xxx_y.MAI size 8K (this must be the temp query). Also the *.MAD file would keep updating its date - yet another indicator of a still running query!
So this thread and this other one helped me figure it out:
mysql -u root -p
show processlist;
kill "thread_id (number in 1st col of the faulty query)";
I ran show processlist; twice after that: 1st time it showed my query as killed, the 2nd time my query had dissapeared from the list and the .MAD and .MAI files were gone and my disk space back in place.
Note: luckily my "faulty" query was an ad-hoc select, and did not alter any tables, nor was it set to run on a regular basis/ from some webservice or a client in production. So watch out, if your situation is different then all those threads where someone like me can easily waste 5h might still be useful :D
