SQL Error [1114] [HY000]: (conn=6) The table ‘temp’ is full

Temporary tablespace is full in MariaDB use coding standard to avoid full

Check the file used for MariaDB

show variables like '%innodb_temp_data_file_path%'

Variable_name             |Value                       |
--------------------------+----------------------------+
innodb_temp_data_file_path|ibtmp1:12M:autoextend       |

In My case, the Temp file is unlimited and got an error due to the hard disk drive full where the temp file is present. So I decided to set a max size for the temp data file.

  1. Go to the my.cnf file on /etc/mysql/my.cnf file and add an entry into it.
[mysqld] 
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G

2. But when we set the temp file the same error occurred when the temp data file reached the max limit.

Example to reproduced the error for temp tablespace go beyond 10G size. for reproduced the error:


-- Created temporary table and put 5G table data into the TEMP table:
CREATE TEMPORARY TABLE temp (ID INT(11), col1 char(200),col2 char(200)) SELECT * FROM test.mytest;

--Again run the query to put 5GB again and get following error:
insert into temp select * from test.mytest;

SQL Error [1114] [HY000]: (conn=6) The table 'temp' is full

Note: When we Drop the temp table but it does not release the space of Temp table. When we try to create after drop it will give following error:

-- Drop the temporary table 
Drop table temp;

--Create the temporary table again for checking space is released but got error:
CREATE TEMPORARY TABLE temp (ID INT(11), col1 char(200),col2 char(200)) SELECT * FROM test.mytest;

SQL Error [1005] [HY000]: (conn=7) Can't' create table `test`.`temp` (errno: 135 "No more room in record file")

Solution:

  1. Reboot the MariaDB Services for release the temporary tablespace.(but its need to done again and again)
  2. Release space from the temporary table used in temporary tablespace, we need to truncate the table before executing drop. Otherwise the space is not released.

Example:


-- Create temporary table
CREATE TEMPORARY TABLE temp (ID INT(11), col1 char(200),col2 char(200)) SELECT * FROM test.mytest;

-- insert anthoer 5 gb data will throw error:
insert into temp select * from test.mytest;
SQL Error [1114] [HY000]: (conn=6) The table 'temp' is full

-- truncate the table first
TRUNCATE TABLE TEMP

-- drop table temp
DROP TABLE TEMP

-- Now again try to Create temporary table will released the space as you see no error:
CREATE TEMPORARY TABLE temp (ID INT(11), col1 char(200),col2 char(200)) SELECT * FROM test.mytest;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.