Mysql – Tables with engines of “MEMORY” does not get dropped when server is restarted

database-engineMySQL

I read a description from When to Use MEMORY or NDB Cluster
from the MySQL website.

Operations involving transient, non-critical data such as session
management or caching. When the MySQL server halts or restarts, the
data in MEMORY tables is lost.

but my created tables with ENGINES = MEMORY were not dropped upon mysql server restart.

CREATE TABLE MY_TEMPORARY_TABLE (
COLUMN_1 varchar(255) DEFAULT NULL,
COLUMN_2 varchar(255) DEFAULT NULL,
COLUMN_3 varchar(255) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

Best Answer

Every table created with CREATE TABLE will create a .frm file followed by other files (such as .MYD and .MYI for MyISAM or .ibd for InnoDB).

When it comes to a table created with ENGINE=MEMORY will create only the .frm file and actual data and index storage will occur in RAM.

If mysqld crashes, the data will be lost, but the .frm will remain.

In your case, you ran CREATE TABLE MY_TEMPORARY_TABLE. This will create a file called MY_TEMPORARY_TABLE.frm.

Had you created the table using CREATE TEMPORARY TABLE MY_TEMPORARY_TABLE, then the table itself would disappear upon mysqld crash or shutdown.

Related Question