GoAdmio

myisam2aria

Convert the MyISAM tables of one MariaDB database, or of every database, to the Aria storage engine, or convert one named table of any engine

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · See also


Overview

/go/myisam2aria changes the storage engine of MariaDB tables from MyISAM to Aria, MariaDB's crash-safe replacement for MyISAM. It works in three modes:

It needs MariaDB. Aria does not exist in Oracle MySQL.

[!WARNING] In single-table mode the command does not check the table's current engine. An InnoDB table named on the command line is converted to Aria too, and its foreign keys are dropped. Database and all modes only touch MyISAM tables.

[!NOTE] Take a backup first. There is no confirmation prompt and no undo. See backup or mysqlbackup.


Quick start

# Convert all MyISAM tables in one database
/go/myisam2aria shop_db

Syntax

/go/myisam2aria <database>            # every MyISAM table in one database
/go/myisam2aria <database> <table>    # one table (any engine)
/go/myisam2aria all                   # every MyISAM table in every database

The values are positional. Running the command with no arguments exits silently with status 1.


Arguments

Position Name Required Notes
1 <database> or all Yes A database name, or the word all for every database.
2 <table> No A table in that database. If given, only this table is converted, whatever its engine.

The command has no --option flags.


Output

The command prints progress text. Errors come directly from MariaDB.

Message Meaning
Converting Database <database> to Aria... all mode: the start of one database, between two lines of dashes.
Converting Table '<table>' to Aria... A table is being converted.
(nothing) The database has no MyISAM tables, so there was nothing to do.

There is no summary at the end. Every call is also appended to /var/log/go/myisam2aria.log.

Exit codes

Situation Exit code
No arguments 1, no message
all Always 1, even when every conversion succeeded
One database or one table The status of the last step

Do not rely on the exit code in all mode.

Security

The command reads the MariaDB root password from GoAdmio's config.php and passes it to mariadb with -p to list the tables, so it is briefly visible in the process list. The password is not written to the log.


Examples

# Convert all MyISAM tables in one database
/go/myisam2aria shop_db
# Convert one table
/go/myisam2aria shop_db orders
# Convert every database on the server
/go/myisam2aria all
# Check the engines afterwards
mariadb -e "SELECT table_name, engine FROM information_schema.tables WHERE table_schema='shop_db';"

Troubleshooting

Symptom Likely cause Fix
Nothing happens, exit code 1 No arguments were given. Pass a database name or all.
Exit code 1 after all finished The script always exits 1 in this mode. Check the engines with the query in Examples.
No Converting lines The database has no MyISAM tables. Check the engines with the same query.
A syntax error near a - or other character The database or table name has special characters, and the command does not quote names. Convert it by hand: ALTER TABLE `db`.`table` ENGINE=Aria;
An InnoDB table became Aria It was named on the command line, and single-table mode does not check the engine. Convert it back with ALTER TABLE `db`.`table` ENGINE=InnoDB;. Foreign keys that were dropped must be added again.
The command is slow, or writes to a table wait Each table is rebuilt during conversion. Run it during a quiet period.
Access denied The root password in config.php is wrong or missing. Check it with config and mysqlset.
Disk full errors The rebuild needs free space about the size of the table being converted. Free disk space with clean, then run again.

Details

Which tables are converted

Mode Tables converted
One database Only tables whose engine is MyISAM
all Only tables whose engine is MyISAM, in every database except information_schema, performance_schema, and sys
One table The named table, whatever its engine

all mode includes the mysql system database and GoAdmio's own database. On MariaDB 10.4 and later the system tables are already Aria, so nothing in mysql is converted.

Table lists left behind

In one-database mode the list of MyISAM tables is saved to /usr/local/goadmio/<database>.txt and is not deleted afterwards. It is overwritten on the next run, and it is a useful record of which tables were converted. In all mode one such file is created per database.

Aria or InnoDB

Aria is crash-safe and a drop-in replacement for MyISAM, but, like MyISAM, it has no transactions or foreign keys. For those, convert to InnoDB with myisam2innodb instead.

What it does

flowchart TD
    A["myisam2aria ..."] --> B{"Any arguments?"}
    B -- "No" --> X["Exit 1"]
    B -- "Yes" --> C{"First argument is all?"}
    C -- "Yes" --> D["List all databases except system ones"]
    D --> E["Run myisam2aria for each database"]
    E --> Y["Exit 1"]
    C -- "No" --> F{"Table given?"}
    F -- "No" --> G["List MyISAM tables, save list to a file"]
    G --> H["ALTER each table to Aria"]
    F -- "Yes" --> I["ALTER that table to Aria"]
  1. Appends the command line to /var/log/go/myisam2aria.log.
  2. Reads the MariaDB root password from /usr/local/goadmio/include/config/config.php.
  3. Exits with status 1 if there are no arguments.
  4. With all: lists every database except the three system ones, prints a header for each, runs /go/myisam2aria <database> for it, then exits with status 1.
  5. With a database only: lists its MyISAM tables, saves them to /usr/local/goadmio/<database>.txt, and runs ALTER TABLE <database>.<table> ENGINE=Aria for each.
  6. With a database and a table: runs ALTER TABLE <database>.<table> ENGINE=Aria without checking the current engine.

See also

Command Description
myisam2innodb Convert MariaDB/MySQL tables from MyISAM to InnoDB
innodb2myisam Convert the InnoDB tables of a MariaDB/MySQL database, or a single table, to the MyISAM storage engine
repair Repair the tables of one MySQL/MariaDB database, or of all databases
mysqlbackup Back up MariaDB/MySQL to a remote server via rsync
backup Back up one or all GoAdmio accounts to local disk or remote storage
my Regenerate the MariaDB configuration file with settings tuned to the server