Changing your max_heap_table_size variable

Sometimes, when you use the engine memory in a MySQL database, you could face some problems related to the maximum size in memory available. (This is shown with a 1114 error).

By default, this value is around 16MB, what in some cases it is not enough for your purposes.

In my specific case I was interested in working just with some varchars and integers. So far I didn’t have any problem with the size of the
memory available until now…

So, just to check the number of bytes available in your variable:

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
| 16777216 |
+-----------------------+
1 row in set (0.00 sec)


And if you want to set a new value (for instance 512 MB):

mysql> set @@max_heap_table_size=536870912;
Query OK, 0 rows affected (0.00 sec)

Cheers.

Extra information at the mysql reference website.

———updated———

In any case you can always use the /etc/mysql/my.cnf file adding in the [mysqld] section the same value. For instance:
max_heap_table_size=512M

The first version shown in this post just updates the variable while the server is running. If there’s a reboot of the system, then, that value is missed. Thus, it is highly recommended to use the conf file to keep the value.