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,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
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:&lt;size&gt; 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.