1

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;

  • 1
    MySQL version? What is SHOW CREATE TABLE {tablename} of the tables? – danblack Jun 20 at 1:24
  • Was there any data in the table after the restart? – Rick James Jun 20 at 4:07
  • Both MEMORY and NDB are rarely used. The only Engine that the typical user needs to care about is InnoDB. – Rick James Jun 20 at 4:10
0

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.

  • I see, thanks for the explanation. – Robin Joaquin Jun 20 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.