Geek Answers Handbook

MySQL user created temporary table is full

I have a temporary table that uses a memory engine:

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 error # 1114 because the table is full. The mysql docs explain that changing tmp_table_size and max_heap_table_size does nothing to increase the size of user-created temporary tables, which I think I have. How do I increase the size of this table?

I would like to do this dynamically with a SET call, but I tried setting tmp_table_size and max_heap_table_size , and they both far exceed the amount of data that I expect in this table. So does anyone know how to resolve this error in this table? Thanks to everyone who helps.

+7
mysql memory temp-tables
source share
2 answers

max_heap_table_size is a size limit on any table (temporary or otherwise) that uses the MEMORY storage engine. You will need to increase this configuration parameter to store more data in the table.

+9
source share

In MySQL, by default, temporary tables created using the memory engine can quickly grow beyond the maximum size of 16 MB and the size of the tmp-table, as each row allocates more memory than is usually required. In the above example, VARCHAR (400) will allocate memory based on the maximum row size, not the actual size of your data. Each character may require more than 1 byte. Suppose each row requires 2kb, then only 8k rows are required to reach the limit.

For many applications, this problem can be solved using ROW_FORMAT = DYNAMIC , as described here:

http://www.percona.com/doc/percona-server/5.5/flexibility/improved_memory_engine.html

+3
source share

More articles:


All Articles