Normalise MariaDB Table File Extensions Safely

You will correct the case of MyISAM or ISAM table filenames after copying a data directory between systems with different filename rules. The installed mysql_fix_extensions command renames matching files in place. Allow about fifteen minutes for inspection, a backup and the rename; allow longer if the directory is large or the server needs a maintenance window.

This guide uses MariaDB client package version 1:10.11.14-0ubuntu0.24.04.1, installed here. The manual page is labelled MariaDB 10.11. Your output and package version may differ. The command is also available as mariadb-fix-extensions; on this system mysql_fix_extensions is a symlink to it.

1. Stop before changing table files

Do not run this against a live data directory. Renaming table files while MariaDB is using them can cause failed queries or inconsistent state. Arrange a maintenance window, stop the MariaDB service using your normal service-management procedure, and confirm that no process is still writing the directory. Stopping a service is an operational change, so keep the service name specific to your host.

The command itself normally needs no special privilege beyond access to the directory. Use an elevated shell only when the database files are unreadable or unwritable by your account. Do not widen permissions just to make the example work.

Checkpoint: identify the exact data directory and command:

$ command -v mysql_fix_extensions
/usr/bin/mysql_fix_extensions
$ dpkg-query -W -f='\${Package} \${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1
$ test -d /path/to/mariadb-data && echo directory-found
directory-found

Replace /path/to/mariadb-data with the directory that contains the database subdirectories. Do not point it at one database directory unless that is the data directory's actual parent.

2. Inspect the names that will change

The utility targets files inside immediate subdirectories of the supplied directory. It looks for names ending in any lettercase variant of .frm, .myd, .myi, .isd or .ism. It changes their suffixes to .frm, .MYD, .MYI, .ISD and .ISM, respectively. Other files are left alone.

List the candidates first. This is read-only:

$ find /path/to/mariadb-data -mindepth 2 -maxdepth 2 -type f \
    \( -iname '*.frm' -o -iname '*.myd' -o -iname '*.myi' \
       -o -iname '*.isd' -o -iname '*.ism' \) -print

Review the list for the expected databases and table names. The command does not infer a database format, repair table contents or convert InnoDB files. Its purpose is filename normalisation after a transfer, particularly from a case-insensitive system such as Windows.

3. Make a recoverable backup

Take a filesystem-level backup or snapshot before renaming anything. Keep it until MariaDB has started and affected tables have passed your checks. A backup made while the server is writing may not be consistent, so use the stopped service and your normal database backup policy.

$ tar --acls --xattrs -czf /path/to/backup/mariadb-data-before-fix.tgz \
    -C /path/to mariadb-data
$ tar -tzf /path/to/backup/mariadb-data-before-fix.tgz | head
mariadb-data/
mariadb-data/example/
...

If the archive command fails, stop here. Do not treat a partial backup as a rollback plan.

4. Run the normalisation

Run one positional argument: the data directory. This changes matching filenames in place and prints nothing on success:

$ mysql_fix_extensions /path/to/mariadb-data
$ printf 'exit status: %s\n' "$?"
exit status: 0

The utility reports a usage message and exits with status 2 when the argument is missing or is not an existing directory. A rename that cannot be completed is reported as a warning by the installed script, so inspect the result rather than relying only on status 0.

Checkpoint: list the candidates again:

$ find /path/to/mariadb-data -mindepth 2 -maxdepth 2 -type f \
    \( -iname '*.frm' -o -iname '*.myd' -o -iname '*.myi' \
       -o -iname '*.isd' -o -iname '*.ism' \) -printf '%P\n' | sort
example/orders.frm
example/orders.MYD
example/orders.MYI

The -iname check shows both corrected and uncorrected candidates. To look specifically for common non-canonical suffixes:

$ find /path/to/mariadb-data -mindepth 2 -maxdepth 2 -type f \
    \( -name '*.FRM' -o -name '*.Myd' -o -name '*.MYd' -o -name '*.myi' \
       -o -name '*.IsD' -o -name '*.ism' \) -print

An empty result is useful evidence, but it is not a database integrity check.

5. Start MariaDB and verify the tables

Start the service using your normal service-management command. Unit names and deployment methods vary, so do not paste an assumed service command. Watch the service log and run a harmless connection check before application traffic returns.

$ mariadb-admin ping
mariadbd is alive

Then query a representative affected table:

$ mariadb -u DB_USER -p -D DATABASE_NAME -e 'CHECK TABLE \`TABLE_NAME\`;'
Database  Table       Op     Msg_type  Msg_text
DATABASE  TABLE_NAME  check  status    OK

Replace each uppercase placeholder. Your account, socket and output columns may differ. A failed connection or table check is a reason to stop application traffic and restore the backup or snapshot, then investigate the path, ownership, permissions and table format.

Common traps and recovery

• Do not pass a single database subdirectory when the files are beneath the parent data directory; the installed utility scans one level below its argument.
• Do not expect it to repair InnoDB, BDB or table contents. It is for the legacy filename patterns documented above.
• Do not use a wildcard as the argument. Supply one existing directory.
• If a rename warning appears, preserve the backup and investigate the exact source, destination and permission error before rerunning.

To undo a successful run, stop MariaDB again and restore the pre-run archive or filesystem snapshot. There is no option that reverses individual renames, and manual suffix changes can miss a file or overwrite an existing name. Keep the backup until the service and representative tables are healthy.