MySQL user created temporary table is full

mysql memory temp-tables
usumoio picture usumoio · Oct 11, 2013 · Viewed 14.2k times · Source

I have a created a temporary table using the memory engine as followed:

CREATE TEMPORARY TABLE IF NOT EXISTS some_text (
    id INT DEFAULT 0,
    string varchar(400) DEFAULT ''
) engine = memory;

When I insert rows into it I run into a #1114 error because the table is full. It explains in the mysql docs that changing the tmp_table_size and the max_heap_table_size don't do anything for increasing the size of user created temp tables, which is what I think I have here. How do I go about making this table larger?

I would love to be able to do this dynamically with a call to SET, but I've tried setting tmp_table_size and the max_heap_table_size and they are both well beyond the amount of data I am expecting in this table. So does anyone know how to resolve this error on this table? Thank you to anyone who helps.

Answer

Bill Karwin picture Bill Karwin · Oct 11, 2013

max_heap_table_size is a limit on the size of any table (temporary or otherwise) that uses the MEMORY storage engine. You'll have to increase this config option to store more data in the table.

Related questions

MySQL maximum memory usage

I would like to know how it is possible to set an upper limit on the amount of memory MySQL uses on a Linux server. Right now, MySQL will keep taking up memory with every new query requested so that …

Read More
Are tables created with "CREATE TEMPORARY TABLE" in memory or on disk?

In MySQL, when you create a temporary table, for example, CREATE TEMPORARY TABLE ..., is that table created and held in memory or on the disk? I have read through the docs and Google'd it and have not come up with …

Read More
Load a MySQL innodb database into memory

I have a MySQL innodb database at 1.9GB, showed by following command. SELECT table_schema "Data Base Name" , sum( data_length + index_length ) / 1 048 576 as "Data Base Size in MB" , sum( data_free )/ 1 048 576 as "Free Space in MB" FROM information_schema.…

Read More