GoAdmio

myisam2innodb

Convert the MyISAM tables of one MariaDB database, or of every database, to the InnoDB 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/myisam2innodb changes the storage engine of MariaDB tables from MyISAM to InnoDB. InnoDB is the default engine in current MariaDB and supports transactions, row-level locking, foreign keys, and crash recovery. The command works in three modes:

[!WARNING] In one-database and all modes the command needs the MariaDB root password, but it never reads it. Unlike myisam2aria, the script does not load it from GoAdmio's config.php, so mariadb asks for the password, and if no one is there to type it, nothing is converted. Single-table mode is not affected. See Known issue.

[!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 (you will be asked for the MariaDB root password)
/go/myisam2innodb shop_db

Syntax

/go/myisam2innodb <database>            # every MyISAM table in one database
/go/myisam2innodb <database> <table>    # one table (any engine)
/go/myisam2innodb 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 InnoDB... all mode: the start of one database, between two lines of dashes.
Converting Table '<table>' to InnoDB... A table is being converted.
(nothing) The database has no MyISAM tables, or the table list could not be read.

There is no summary at the end. Every call is also appended to /var/log/go/myisam2innodb.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.


Examples

# Convert all MyISAM tables in one database
/go/myisam2innodb shop_db
# Convert one table
/go/myisam2innodb shop_db orders
# Convert every database on the server
/go/myisam2innodb 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
A password prompt appears, or Access denied, and nothing is converted The root password is never loaded. See Known issue. Type the root password when asked, or convert tables one at a time with <database> <table>.
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, or the password step failed. 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=InnoDB;
Row size too large The table has too many or too wide columns for InnoDB. Reduce the columns or convert wide VARCHAR columns to TEXT, then convert the table by hand. Until then, keep it on MyISAM or use myisam2aria.
The command is slow, or writes to a table wait Each table is rebuilt during conversion. Run it during a quiet period.
Disk full errors The rebuild needs free space, and InnoDB tables are usually larger than MyISAM ones. Free disk space with clean, then run again.

Details

Known issue: root password

In one-database and all modes, the command lists tables with mariadb -uroot -p$MySQLRootPassword. The script never sets MySQLRootPassword, so the option becomes a bare -p and mariadb prompts for the password (unless the variable is already set in your shell environment). myisam2aria has an extra line that reads the password from /usr/local/goadmio/include/config/config.php; this script does not.

Mode Effect
One database Prompts once to list the tables. Without a correct password, the list is empty and nothing is converted.
all Prompts to list the databases, then again for each database.
One table Not affected. The conversion uses sudo mariadb without a password.

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 Aria, not MyISAM, 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.

InnoDB or Aria

InnoDB is the usual choice for active sites: it adds transactions, foreign keys, row-level locking, and automatic crash recovery. The trade-offs are larger files on disk and slower SELECT COUNT(*) without a WHERE clause. Aria is a smaller step: it keeps MyISAM's behavior but is crash-safe. See myisam2aria. After converting many tables, consider re-running my, which tunes the MariaDB configuration to the server's RAM and database usage.

What it does

flowchart TD
    A["myisam2innodb ..."] --> 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 myisam2innodb 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 InnoDB"]
    F -- "Yes" --> I["ALTER that table to InnoDB"]
  1. Appends the command line to /var/log/go/myisam2innodb.log.
  2. Exits with status 1 if there are no arguments.
  3. With all: lists every database except the three system ones, prints a header for each, runs /go/myisam2innodb <database> for it, then exits with status 1.
  4. With a database only: lists its MyISAM tables, saves them to /usr/local/goadmio/<database>.txt, and runs ALTER TABLE <database>.<table> ENGINE=InnoDB for each.
  5. With a database and a table: runs ALTER TABLE <database>.<table> ENGINE=InnoDB without checking the current engine.

See also

Command Description
myisam2aria Convert MariaDB tables from MyISAM to Aria
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