tutorials

Convert your MyISAM Database to InnoDB with phpMyAdmin

InnoDB is a more robust and better database table type for modern applications like MODX Revolution compared to the older MyISAM table type. With InnoDB, you should see improvements in platform I/O, your sites should perform better and faster, and they should better survive database hiccups that can occur, too. MODX Revolution 2.6 will default to InnoDB for new installations, if supported.

The steps below will allow you to update existing installations to support InnoDB tables today. Credit is due to MODXer, Garry Nutting, for creating the method used in this tutorial.

Update: If you have a larger database, it’s more reliable to convert MyISAM to InnoDB via the command line.

Requirements

You’ll need the following in order to convert your database:

  1. The current version of phpMyAdmin (“PMA”) and some rudimentary knowledge/experience in using it
  2. A database version that supports full text indexes for InnoDB table types

Process

First, ALWAYS back up your database before doing anything potentially destructive. Once you have that done, you’re ready to go:

  1. Launch PMA. You must explicitly click on the name of your database in the top of the left column. There are sometimes multiple databases, so make sure to click the right one. Do not click on the one labelled information_schema.
  2. Click the Query tab
  3. In the box at the lower right, paste the following code into the “SQL query on database …” box, replacing “your-db-name” with your actual DB name:

    
    SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE ENGINE='MyISAM'
    AND table_schema = 'your-db-name'; 
    


  4. Press the submit query button to see a list of tables to alter to InnoDB
  5. Click the “+Options” link above the results, choose the “Full texts” option, and press the “Go” button in the shaeded area at the lower right
  6. Above the results, tick the “Show all” checkbox
  7. Copy all the queries using the “Copy to clipboard” link in the “Query results operations” box below the results
  8. Paste the result into a text editor and copy all the lines starting with “ALTER TABLE” to your clipboard
  9. Click the SQL tab above the results
  10. Paste the ALTER TABLE statements into the textarea and press the “Go” button in the lower right shaded area

To confirm your database was successfully converted, click your database name in the left column, and all table types should now say InnoDB. 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?