28

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the default storage engine?

  • Maybe it's not so much about PHPMyAdmin but MySQL directly. Have you tried this: - stackoverflow.com/questions/2286813/… – Sebastian Roth Jan 4 '11 at 4:01
  • 2
    It is possible by doing these steps: navigate to > phpmyadmin -> more -> variables -> storage engine and change it MyISAM to InnoDB. – user21380 Mar 16 '13 at 20:33
  • @user21380: that's where you can ONLY see what engine MySQL is using. – machineaddict Nov 21 '14 at 8:55
20

You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your operation system) and restart the mysqld service.

I don't believe you can change this through PhpMyAdmin.

2
UPDATE `GLOBAL_VARIABLES` 
SET `VARIABLE_VALUE`="InnoDB" 
WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"
  • 1
    database: information_schema – Owen Parker Jun 26 '16 at 14:43
1

This answer is kind of late, but it might help others. If you are afraid to mess something up on the MySQL server, you can change the default engine when creating a table from phpMyAdmin. The default select creator for MySQL engines is this function under StorageEngine.class.php in libraries folders (in phpMyAdmin 3.5.8.2):

<?php
/**
 * returns HTML code for storage engine select box
 *
 * @param string  $name                    The name of the select form element
 * @param string  $id                      The ID of the form field
 * @param string  $selected                The selected engine
 * @param boolean $offerUnavailableEngines Should unavailable storage engines be offered?
 *
 * @static
 * @return  string  html selectbox
 */
static public function getHtmlSelect($name = 'engine', $id = null,
  $selected = null, $offerUnavailableEngines = false)
{
    $selected   = strtolower($selected);
    $output     = '<select name="' . $name . '"'
        . (empty($id) ? '' : ' id="' . $id . '"') . '>' . "\n";

    foreach (PMA_StorageEngine::getStorageEngines() as $key => $details) {
        // Don't show PERFORMANCE_SCHEMA engine (MySQL 5.5)
        // Don't show MyISAM for Drizzle (allowed only for temporary tables)
        if (! $offerUnavailableEngines
            && ($details['Support'] == 'NO'
            || $details['Support'] == 'DISABLED'
            || $details['Engine'] == 'PERFORMANCE_SCHEMA')
            || (PMA_DRIZZLE && $details['Engine'] == 'MyISAM')
        ) {
            continue;
        }

        $output .= '    <option value="' . htmlspecialchars($key). '"'
            . (empty($details['Comment'])
                ? '' : ' title="' . htmlspecialchars($details['Comment']) . '"')
            . (strtolower($key) == $selected || (empty($selected) && $details['Support'] == 'DEFAULT')
                ? ' selected="selected"' : '') . '>' . "\n"
            . '        ' . htmlspecialchars($details['Engine']) . "\n"
            . '    </option>' . "\n";
    }
    $output .= '</select>' . "\n";
    return $output;
}

This select is populated from the following query:

SHOW STORAGE ENGINES

The following code is selecting the default engine set by MySQL config file:

(empty($selected) && $details['Support'] == 'DEFAULT')

However, we can change it to make it select InnoDB as the default engine:

(empty($selected) && $details['Engine'] == 'InnoDB')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.