Server — MariaDB — MyISAM

The previous article covered InnoDB: the default storage engine, the one all application tables use, the one with transactions, crash recovery, and row-level locking. MyISAM is the one that came before it. Understanding why InnoDB replaced it, where MyISAM still shows up on February, and what to do about it is a clean way to close out the storage engine topic.

What MyISAM is

MyISAM was MySQL’s default storage engine from the beginning until version 5.5, when InnoDB took over. It predates most of the requirements we now take for granted in a database engine: transactions, foreign keys, crash recovery, row-level locking. It was designed for a simpler world and optimised for read-heavy workloads on hardware where RAM was scarce and disk was slow.

It has three main differences from InnoDB that matter in practice.

No transactions. MyISAM does not support BEGIN, COMMIT, or ROLLBACK. Every statement is committed immediately and cannot be rolled back. If a sequence of writes is interrupted partway through by a crash or an error, there is no rollback mechanism. The data is left in whatever partial state it was in when the interruption occurred.

Table-level locking. When a MyISAM table is being written to, the entire table is locked. No other connection can read from or write to it until the write completes. On a server with concurrent connections, this becomes a bottleneck quickly. InnoDB locks only the specific rows being modified.

No crash recovery. InnoDB’s redo log allows it to replay uncommitted changes after a crash and return to a consistent state. MyISAM has no equivalent. A crash or unclean shutdown while a MyISAM table is being written to can corrupt the table entirely. Recovery requires running myisamchk or REPAIR TABLE, which may or may not succeed, and may lose data.

For any application that writes data and cares about its integrity, those three deficiencies make MyISAM the wrong choice. MariaDB’s own documentation describes it as suitable for legacy purposes only. There is essentially no reason to create new MyISAM tables in 2026.

Where MyISAM still lives on February

Despite InnoDB being the default for application tables, MyISAM has not disappeared entirely. MariaDB’s internal system tables in the mysql database use a mix of storage engines, and some of them are MyISAM. This is by design and is not something to change.

To see the full picture:

sudo mariadb -e "
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'MyISAM'
ORDER BY table_schema, table_name;"

On a fresh MariaDB 10.11 installation on Ubuntu, the MyISAM tables you will find are all in the mysql system database. Tables like mysql.general_log and mysql.slow_log use CSV or MyISAM for historical reasons. The mysql.help_* tables use MyISAM. These are internal tables managed by MariaDB itself and should not be converted.

What you should not find is any MyISAM table in the application databases: powerdns, pdnsadmin, or any other database added for services running on February. All application tables should be InnoDB. If this query returns rows in application databases, those tables need attention.

Why application tables might be MyISAM

On a fresh February installation where all databases were created after MariaDB was configured with InnoDB as the default, you should not have any MyISAM application tables. The only scenario where they appear is if a database was created or imported from a source that explicitly specified ENGINE=MyISAM in the CREATE TABLE statements, or if an old application schema predates the InnoDB default.

PowerDNS and PowerDNS Admin both create InnoDB tables. If February’s databases were set up following this series, everything should already be InnoDB.

Run a targeted check on the application databases specifically:

sudo mariadb -e "
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'MyISAM'
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY table_schema, table_name;"

If this returns nothing, the application databases are clean. If it returns rows, those tables need to be converted.

Converting MyISAM tables to InnoDB

Conversion is a single SQL statement per table:

ALTER TABLE database_name.table_name ENGINE = InnoDB;

ALTER TABLE with a storage engine change rewrites the entire table. For small tables this is fast. For large tables it takes time proportional to the table size and briefly locks the table during the conversion. On February’s application databases, all tables are small enough that this is not a concern.

Before converting anything, take a backup:

sudo mysqldump --all-databases > /tmp/pre-myisam-conversion.sql

Then generate and run the conversion statements for all MyISAM application tables in one pass:

sudo mariadb -e "
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM'
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');" | grep -v CONCAT | sudo mariadb

This generates the ALTER TABLE statements from the information schema and pipes them directly into MariaDB for execution. The grep -v CONCAT strips the column header from the output.

Confirm the conversion:

sudo mariadb -e "
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'MyISAM'
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');"

This should return nothing.

Preventing new MyISAM tables

To ensure no future application accidentally creates a MyISAM table, set the default storage engine explicitly and deny MyISAM creation by omission. MariaDB 10.11 already defaults to InnoDB, but making it explicit in 50-server.cnf documents the intent:

default_storage_engine = InnoDB

This does not prevent an application from explicitly specifying ENGINE=MyISAM in a CREATE TABLE statement, but it ensures that any table created without specifying an engine gets InnoDB. For an application that insists on creating MyISAM tables, the right fix is in the application schema, not in MariaDB’s configuration.

The one case where MyISAM is still reasonable

Full-text search is the traditional reason cited for keeping MyISAM. Prior to MariaDB 10.0, InnoDB did not support full-text indexes at all, and MyISAM was the only option for tables that needed them. Since 10.0, InnoDB has had full-text search support.

For February’s applications, full-text search is not in use. There is no scenario on this server where a new MyISAM table is the right choice. If a future application requires full-text search, use InnoDB’s native support rather than reaching for MyISAM.

What remains

After this check and any necessary conversions, February’s database posture is clean: all application tables are InnoDB, with transactions, crash recovery, and row-level locking. The MyISAM tables that remain are in the system databases, managed by MariaDB, and are not your problem to fix.

The default storage engine is explicitly set to InnoDB. Any new table created by a future application will be InnoDB unless the application explicitly requests otherwise, at which point you will know about it.