mysqld fills /tmp with file /tmp/#sql_26be_0.MAD
We have MariaDB CE 10.1.11 running on RHEL. We were loading data and started getting this error in the mysqld.log: 2016-04-22 9:45:54 139696698296064 [Warning] mysqld: Disk is full writing '/tmp/#sql_26be_0.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) We moved the large file out of /tmp, but had to reboot in order to free up the /tmp. Restarted mysqld and it appears to be OK. Any information on what this might be and how to get to root cause will be appreciated.
Answer Answered by Jamie G in this comment.
Hello.
When you run certain ALTER or CREATE commands on an InnoDB table, a temporary table is created in mysql's tmpdir during the operation.The default value in my.cnf is /tmp.
When you are loading the data, /tmp is being filled. I would suggest you change the value of tmpdir in my.cnf to be on a disk with more space. Stop mysql first, change the value in my.cnf and start mysql.
Check the tmpdir value is correct by running:
show variables like "tmpdir";
then try loading your data.
If the value is not as you set it in my.cnf, make sure you have it in the correct section, [mysqld] or [mysqld-safe]
