"The table is full" on SELECT .. GROUP BY, even though I have plenty of disk space left-CodePudding

I receive the message: The table '/tmp/#sql1_8_4' is full when I try executing the following query

CREATE TABLE temp 
SELECT DATE_FORMAT(createdDate, "%Y-%m-01") `date`, userId, COUNT(id) AS `jobsCount`
FROM backup_jobs jobs 
GROUP BY DATE_FORMAT(createdDate, "%Y-%m-01"), userId;

The backup_jobs table is more than 19Gb, but I have more than 300Gb available on the partition where ibdata1 is located

set tmp_table_size = 1024 * 1024 * 32;
set max_heap_table_size = 1024 * 1024 * 32;

And in mysql config:

innodb_data_file_path = ibdata1:100M:autoextend

I run mysql on docker, but the volume is mounted (I saw that the max image size could be a reason in case the mysql files ae stored in the image)

I can CREATE OR SELECT the table without the GROUP BY But I can't CREATE or SELECT with GROUP BY

Thanks for your help

CodePudding user response：

try turn tempfile_use_mmap off and use the disk to create the temp table or try increasing temptable_max_ram variable max ram

Defines whether the TempTable storage engine allocates space for internal in-memory temporary tables as memory-mapped temporary files when the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable. When temptable_use_mmap is disabled, the TempTable storage engine uses InnoDB on-disk internal temporary tables instead.

check your temporary disk allocation rules. make sure the datafile can grow to the adequate size for the temp file. confirm you have adequate harddrive partition space where your datafile resides

SELECT table_schema "DB Name",
        ROUND(SUM(data_length   index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

see https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html for setup your tablespace to datafile configurations. Make sure autoextend feature is on

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G

this query will show how much data_free space if available

check your mysql configuration variable and use the max key word

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

see Implicit temporary table variables (https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/)

tmp_table_size 
max_heap_table_size

set tmp_table_size = 1024 * 1024 * 32;
set max_heap_table_size = 1024 * 1024 * 32;

confirm your recordsize with the allocated memory amounts. confirm your groupby and joins did not create a cross product cardesian result set.

restart the server

quote:

As any other InnoDB table in the database, the temporary tables have their own tablespace file. The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE. The ibtmp1 makes no difference, as you cannot use OPTIMIZE. The only way to shrink ibtmp1 size to zero is to restart the server.

In memory temp table exhaustion results in usage of the disk file

see (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html)

temptable_max_mmap

Defines the maximum amount of memory (in bytes) the TempTable storage engine is permitted to allocate from memory-mapped temporary files before it starts storing data to InnoDB internal temporary tables on disk. A setting of 0 disables allocation of memory from memory-mapped temporary files.