2

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
  • Do you have tmpdir variable declared in your config? by default it uses /tmp in Unix*..do change it in the config and then try – Nawaz Sohail Jun 1 '16 at 13:02
  • I tried to change to /tmp, but the error persists. – Magi Jun 1 '16 at 13:24
  • what does show variables like 'tmpdir' and see to which directory it points or what error did you get changing this variable and starting mysqld – Nawaz Sohail Jun 1 '16 at 13:34
  • tmpdir | /tmp but 2016-06-02T13:54:48.439008Z 53260 [ERROR] /usr/sbin/mysqld: The table '/tmp/#sql_992_0' is full :( – Magi Jun 2 '16 at 14:02
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.