MySQL loses data after server restart

This week I had one strange problem with a MySQL server after reloading a dump into a fresh MySQL (MariaDB) install. After that, everytime I restarted a MySQL server, data from a specific table were lost. Only from that specific table! No data corruption, error, etc. Just rows from that table disappear after each restart!

After careful looking at the database structure, I saw something unexpected:

show create table data;
| data |CREATE TABLE `data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `s1` float DEFAULT NULL,
  `sensor` int DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=Memory

Wow. ENGINE=Memory?

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

https://en.wikipedia.org/wiki/Memory_(storage_engine)

MEMORY writes table data in-memory. While the table structures are persisted on-disk, the rows in MEMORY tables are lost when MySQL stops.

Just changed the engine to InnoDB and problem solved! Strange that I never explicitly defined this table as using “memory engine”, but anyway, problem solved 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s