alter table engine=memory ERROR 1114

In this Document

APPLIES TO:

SYMPTOMS

When creating, modifying or altering the table engine to MEMORY, you get this error even if max_heap_table_size can host the data:

CAUSE

The MEMORY engine can allocate more memory than its InnoDB or MyISAM equivalent.

If the problematic table contains long varchar, the MEMORY engine will allocate fixed length strings so it can take a lot of space in memory, even more when the utf8 or utf8mb4 character set is used.

SOLUTION

Increase max_heap_table to an even larger value.

Consider using InnoDB with a large buffer pool, so that in practice, the data is in memory and can be accessed concurrently unlike MEMORY table data. There is very little reason to use the MEMORY engine nowadays.

REFERENCES