Description:
When facing "Disk full" state when using internal temporary table and the query returns the error "ERROR 1114 (HY000): The table '/mytmp/#sql54e5_a_1' is full".
But the tablename is always prefixed by tmpdir, even if the temporary table doesn't use tmpdir at all (ex. InnoDB temporary table uses innodb_temp_tablespaces_dir, not tmpdir).
How to repeat:
### temptable_max_ram = 2MB && temptable_use_mmap = OFF, for easy to fallback Internal temporary table to use InnoDB.
mysql80 10> SHOW VARIABLES LIKE '%tmp%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | /mytmp |
| internal_tmp_mem_storage_engine | TempTable |
| replica_load_tmpdir | /mytmp |
| slave_load_tmpdir | /mytmp |
| tmp_table_size | 16777216 |
| tmpdir | /mytmp |
+---------------------------------+-----------+
7 rows in set (0.01 sec)
mysql80 10> SHOW VARIABLES LIKE '%temp%';
+-----------------------------+-----------------------+
| Variable_name | Value |
+-----------------------------+-----------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | /mytmp/innodb_temp |
| show_old_temporals | OFF |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 2097152 |
| temptable_use_mmap | OFF |
+-----------------------------+-----------------------+
7 rows in set (0.00 sec)
### Fill /mytmp to easy to face "Disk full"
### $ df -h /mytmp
### Filesystem Size Used Avail Use% Mounted on
### /dev/loop0 97M 86M 11M 89% /mytmp
### Using internal temporary table
mysql80 10> SET SESSION cte_max_recursion_depth = 10000000;
Query OK, 0 rows affected (0.00 sec)
mysql80 10> WITH RECURSIVE v AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM v WHERE n <= 1999999), v2 AS (SELECT n, MD5(n) AS m FROM v) SELECT n, m, md5(m) AS o FROM v2 ORDER BY RAND() LIMIT 1;
ERROR 1114 (HY000): The table '/mytmp/#sql54e5_a_1' is full
^^^^^^
/mytmp is tmpdir, the query uses /mytmp/innodb_tmp (setting by innodb_temp_tablespaces_dir), that's wrong.
### Error-log describes "which file is facing to disk full" correctly.
### But last 1 line (ER_SERVER_RECORD_FILE_FULL) is including unnecessary tmpdir name.
2023-02-20T17:04:27.378420+09:00 10 [ERROR] [MY-012639] [InnoDB] Write to file /mytmp/innodb_temp/temp_10.ibt failed at offset 10485760, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2023-02-20T17:04:27.378440+09:00 10 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2023-02-20T17:04:27.378453+09:00 10 [Note] [MY-012641] [InnoDB] Refer to your operating system documentation for operating system error code information.
2023-02-20T17:04:27.378468+09:00 10 [Warning] [MY-012145] [InnoDB] Error while writing 1048576 zeroes to /mytmp/innodb_temp/temp_10.ibt starting at offset 10485760
2023-02-20T17:04:27.378490+09:00 10 [ERROR] [MY-013132] [Server] The table '/mytmp/#sql54e5_a_1' is full!
Suggested fix:
Remove tmpdir prefix in Error:1114 and Error:13132
Description: When facing "Disk full" state when using internal temporary table and the query returns the error "ERROR 1114 (HY000): The table '/mytmp/#sql54e5_a_1' is full". But the tablename is always prefixed by tmpdir, even if the temporary table doesn't use tmpdir at all (ex. InnoDB temporary table uses innodb_temp_tablespaces_dir, not tmpdir). How to repeat: ### temptable_max_ram = 2MB && temptable_use_mmap = OFF, for easy to fallback Internal temporary table to use InnoDB. mysql80 10> SHOW VARIABLES LIKE '%tmp%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | default_tmp_storage_engine | InnoDB | | innodb_tmpdir | /mytmp | | internal_tmp_mem_storage_engine | TempTable | | replica_load_tmpdir | /mytmp | | slave_load_tmpdir | /mytmp | | tmp_table_size | 16777216 | | tmpdir | /mytmp | +---------------------------------+-----------+ 7 rows in set (0.01 sec) mysql80 10> SHOW VARIABLES LIKE '%temp%'; +-----------------------------+-----------------------+ | Variable_name | Value | +-----------------------------+-----------------------+ | avoid_temporal_upgrade | OFF | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | innodb_temp_tablespaces_dir | /mytmp/innodb_temp | | show_old_temporals | OFF | | temptable_max_mmap | 1073741824 | | temptable_max_ram | 2097152 | | temptable_use_mmap | OFF | +-----------------------------+-----------------------+ 7 rows in set (0.00 sec) ### Fill /mytmp to easy to face "Disk full" ### $ df -h /mytmp ### Filesystem Size Used Avail Use% Mounted on ### /dev/loop0 97M 86M 11M 89% /mytmp ### Using internal temporary table mysql80 10> SET SESSION cte_max_recursion_depth = 10000000; Query OK, 0 rows affected (0.00 sec) mysql80 10> WITH RECURSIVE v AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM v WHERE n <= 1999999), v2 AS (SELECT n, MD5(n) AS m FROM v) SELECT n, m, md5(m) AS o FROM v2 ORDER BY RAND() LIMIT 1; ERROR 1114 (HY000): The table '/mytmp/#sql54e5_a_1' is full ^^^^^^ /mytmp is tmpdir, the query uses /mytmp/innodb_tmp (setting by innodb_temp_tablespaces_dir), that's wrong. ### Error-log describes "which file is facing to disk full" correctly. ### But last 1 line (ER_SERVER_RECORD_FILE_FULL) is including unnecessary tmpdir name. 2023-02-20T17:04:27.378420+09:00 10 [ERROR] [MY-012639] [InnoDB] Write to file /mytmp/innodb_temp/temp_10.ibt failed at offset 10485760, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded. 2023-02-20T17:04:27.378440+09:00 10 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device' 2023-02-20T17:04:27.378453+09:00 10 [Note] [MY-012641] [InnoDB] Refer to your operating system documentation for operating system error code information. 2023-02-20T17:04:27.378468+09:00 10 [Warning] [MY-012145] [InnoDB] Error while writing 1048576 zeroes to /mytmp/innodb_temp/temp_10.ibt starting at offset 10485760 2023-02-20T17:04:27.378490+09:00 10 [ERROR] [MY-013132] [Server] The table '/mytmp/#sql54e5_a_1' is full! Suggested fix: Remove tmpdir prefix in Error:1114 and Error:13132