How can I change the default storage engine in phpmyadmin?

Question

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?

score 25 · Accepted Answer · answered Jan 04 '11 at 17:50

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.

score 2 · Answer 2 · edited Nov 08 '20 at 12:42

2

UPDATE `information_schema`.`GLOBAL_VARIABLES` 
SET `VARIABLE_VALUE`="InnoDB" 
WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"

edited Nov 08 '20 at 12:42

Community

1

answered Jun 26 '16 at 14:42

Owen Parker

21
1

score 2 · Answer 3 · answered Nov 21 '14 at 09:16

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')

KD_Raj · Answer 4 · 2020-06-19T19:07:19.397

1

You can change it in PHPMYADMIN for each table:

Click on the table in phpmyadmin
Click "Operations" on the top right.
In the Table Options section choose your storage engine in the drop down menu eg InnoDB etc
Click GO

If you have many tables it may take some time to click in each one and do this but it is possible.

edited Jun 19 '20 at 19:07

answered Jun 19 '20 at 19:02

KD_Raj

119
2

score 1 · Answer 5 · answered Apr 05 '22 at 12:38

Its an old question but is needed every time for beginners or other peoples this is a good way that I found and worked:

The easiest way to change the default engine is to log on phpMyAdmin and then go to Variables >> storage engine (Variables tab works with WAMP.. for XAMPP find it in Operations tab)

click edit and type InnoDB.

the default storage engine is now InnoDB

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_storage_engine

=========================================================

main answer link: https://stackoverflow.com/a/21158741/8934351

score 0 · Answer 6 · answered Jul 14 '24 at 04:17

MyISAM is the default storage engine for WAMP/XAMP. So if you want to change default engine, just follow the instructions.

Locate the MySQL configuration file my.ini.
Find the default-storage-engine setting line. not the comment one starts with ";".
Insert or edit it to: default-storage-engine=INNODB.
Save the changes.
Restart server.

How can I change the default storage engine in phpmyadmin?

6 Answers6