CODEWITHSUNDEEP
mysqltemp-tables
Matthijs
Matthijs

Reputation: 155

how to set mysql max temporary in memory table size

I am using a temporary table by using following code:

CREATE TEMPORARY TABLE temp (ID INT(11), Actress varchar(200)) 
ENGINE=MEMORY
SELECT ID, Actress FROM actress_titles order by id;

But I am getting the next error: Error code 1114, the table 'temp' is full. I have also tried to increase temporary table size by executing following code:

SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 5;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 5;

But when I read the variable the size is 99999744 bytes

(select @@max_heap_table_size;)

So, I'm still getting the 1114 error...

How can I increase the max size of temporary tables beyond this 99mb?

Upvotes: 3

Views: 6477

Answers (1)

Matthijs
Matthijs

Reputation: 155

The following code must be executed to set the variables:

SET @@tmp_table_size = 1024 * 1024 * 1024 * 5;
SET @@max_heap_table_size = 1024 * 1024 * 1024 * 5;

Upvotes: 4

Related Questions