How can I change the default storage engine in phpmyadmin?
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.
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')
UPDATE `information_schema`.`GLOBAL_VARIABLES`
SET `VARIABLE_VALUE`="InnoDB"
WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"