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

Question

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,0G            0  5,0G            0% /dev
tmpfs            1,0G         110M  915M           11% /run
/dev/sda2         35G          22G   12G           65% /
tmpfs            5,0G            0  5,0G            0% /dev/shm
tmpfs            5,0M            0  5,0M            0% /run/lock
tmpfs            5,0G            0  5,0G            0% /sys/fs/cgroup
/dev/sda1        511M         3,4M  508M            1% /boot/efi
tmpfs            1,0G            0  1,0G            0% /run/user/1000

Answer 1

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:<size> 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.