The problem is caused by the lack of disk space in /tmp folder.
The /tmp volume is used in queries that require to create temporary tables. These temporary tables are in MyISAM format even if the query is using only tables with InnoDB.
Here are some solutions:
- optimize the query so it will not create temporary tables (rewrite the query, split it in multiple queries, or add proper indexes, analyze the execution plan with pt-query-digest and
EXPLAIN <query>) See this Percona article about temporary tables.
- optimize MySQL so it will not create temporary tables (sort_buffer_size, join_buffer_size). See: https://dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it
- make tables smaller. If possible, delete unneeded rows
SELECT table1.col1, table2,col1 ...instead of
select *to use only the columns that you need in the query, to generate smaller temp tables
- use data types that take less space
- add more disk space on the volume where /tmp folder resides
- change the temp folder user by mysql by setting the
TMPDIRenvironment variable prior to mysqld start-up. Point
TMPDIRto a folder on a disk volume that has more free space. You can also use
--tmpdirin the command line of the mysqld service. See: B.5.3.5 Where MySQL Stores Temporary Files