How to fix `The table '/tmp/mysql/#sql_xxxxx' is full`?

Ubuntu server 16.04, php7, nginx 1.10, mysql 5.7 in Hyper-V on Windows 10 host

How to fix The table '/tmp/mysql/#sql_xxxxx' is full ?

In mysql-error.log


[ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
add another file or use 'autoextend' for the last file in setting
innodb_temp_data_file_path


[ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
[ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

[ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
add another file or use 'autoextend' for the last file in setting
innodb_temp_data_file_path


[ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
[ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

But in /tmp/mysql/ - nothing.

In mysqld.cnf

Секция InnoDB

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 5M
innodb_flush_log_at_trx_commit = 1 
innodb_file_per_table          = 1 
innodb_buffer_pool_size        = 1G
innodb_log_buffer_size         = 8M
innodb_max_undo_log_size       = 50M
innodb_temp_data_file_path     = ibtmp1:12M:autoextend:max:512M

If you remove the limit to 512 MB, the file fills all the available space in a few minutes.

```
innodb_temp_data_file_path = ibdata1:10M:autoextend:max:512M
innodb_thread_concurrency = 8
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_pct = 75
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_io_capacity = 600
innodb_io_capacity_max = 1000
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_fast_shutdown = 0
innodb_max_dirty_pages_pct = 0

# df -h

udev 5,

The relevant configuration is this:


ibtmp1:12M:autoextend:max:512M

You are restricting your temporary tables (and similarly your main innodb tablespace via innodb_data_file_path) to 512M total. This includes implicit (disk) temporary tables that might be created by various ORDER BY / GROUP BY / DISTINCT operations.

MySQL reports this failure relative to your configured tmpdir, although it's actually using the ibtmp1 file.

Increase or remove the :max: limit to resolve this.

Alternatively you might find a way to avoid these sort of temporary tables by inspecting the offending queries - possibly via adding an index, changing data types or changing the query itself among other strategies.

StackExchange Database Administrators Q#140113, answer score: 4