tutorials

Converting to InnoDB from MyISAM tables using the Command Line

InnoDB is a more robust and better database table type for modern applications like MODX Revolution compared to the older MyISAM table type. Your sites should perform better and faster, and they should also be more tolerant of database hiccups that can occur.

This is a slightly more advanced companion to our previous tutorial to convert to InnoDB using phpMyAdmin. It is especially useful for power users and larger databases that may fail conversion when using a GUI web app.

Requirements

You’ll need the following in order to convert your database to use InnoDB tables instead of MyISAM.

  1. Comfort with using the command line
  2. SSH access to your database server and your database credentials
  3. A database version that supports full text indexes in InnoDB table types

Process

First, ALWAYS back up your database before doing anything potentially destructive. For the purposes of this tutorial, we’re assuming you are doing this in MODX Cloud, so you can just click the Backup Cloud menu option and get started right away. Different environments may require slightly different commands as follows:

  1. SSH into your instance
  2. Enter the following into your command line, and use your MySQL password. Make sure to substitute your your username (e.g., c0000) and your database (e.g., instance_c0000_modx) in both of the appropriate spots, below:

    mysql -B --user=c0000 --host=127.0.0.1 --password instance_c0000_modx --disable-column-names --execute "SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema='instance_c0000_modx';"  

  3. Copy the resulting alter statements this generates, and paste it into a text editor. On a Mac, you can simply press shift-cmd-A to select the result of the last command in the built-in Terminal.app then cmd-c to copy.

  4. At the shell prompt again, execute the following command to get to the MySQL command prompt. Make sure to substitute your your username (e.g., c0000) and database name (e.g., instance_c0000_modx) below:

    mysql --user=c0000 --host=127.0.0.1 --password instance_c0000_modx    

  5. Copy the alter statements from two steps earlier, and paste them into your terminal. Depending on the size of your database, your tables should convert to use InnoDB in anywhere from a few seconds to a minute or so.

We hope you find this useful as you prepare for the future of MODX Revolution.

Hi. We’re MODX.

We’re here to help you fix, build and grow fantastic sites. How can we help?




How can we help?

Tell us the general reason for reaching out so we can connect you with the right team.

MODX Diagnostics

MODX’s Open Source software is 100% free for anyone to download and use. As the team behind it for more than a decade, we know it inside, out, and then some.

Like any software, sometimes things break; we can usually fix them very fast. But, we do have to charge for our time to support our families and fund its ongoing development. There are almost an unlimited variety of things that can cause problems, including server upgrades, corrupt files, accidental changes, outdated software, database hiccups and more. We will save you a lot of time and frustration, and get you back in action.

With our MODX Diagnostic service, we determine the source of issues, and often fix them on the spot. For more extensive problems needing more time, like hacked sites or overdue upgrades, we provide additional estimates and guidance. MODX Diagnostics cost $99 for standard business hours support (US Central Time), or $500 for priority, rush or after-hours emergencies.

If you don’t have budget for professional support from the source, you look for answers in the MODX Forums or Documentation, or seek help from MODXers in the Community Slack, or from MODX Professionals near you.

  I’m not ready to pay, let’s talk…

After submitting this form and completing payment, we will collect your access credentials in a secure support ticket. We look forward to helping restore your site back to full health.

Hi! We’d love to work together.

If you have a simple problem that needs our assistance, please request quick fix help here.

What should we keep in mind?

The project involves:
(select all that apply)
What are you planning?
(select all that apply)

Some other considerations

Specific project information

Commercial Support Customers

Customers with a current Commercial Support agreeement can get help using this form. Learn more about MODX Preferred Support.

Let’s get started

What seems to be the issue?

Contact MODX

We welcome conversations, ideas, inquiries and even the occassional cold sales call, but support and requests about how to use MODX software sent via this form cannot be guaranteed a response. That said, we try to respond to everyone that reaches out to us within two business days.

To report a security issue or file a bug for MODX software, please email security [at] modx.com to reach our security team. If you are looking for help with MODX, many times you can find an answer in the MODX Forums or MODX Documentation, from MODXers in realtime at the MODX Community Slack Channel, or from a MODX Professional near you.

How can we help?