Home / server / Questions / 83111
Accepted
Brian Armstrong
Asked: 2009-11-10 17:19:06 +0800 CST

phpMyAdmin showing database is MyISAM but tables are InnoDB

  • 772

Is it possible for a database to be of one type (MyISAM) and the tables to be of another type (InnoDB) or is this a bug in phpMyAdmin?

It is showing exactly that. Picture Here http://www.startbreakingfree.com/wp-content/uploads/2009/11/Picture-10.png

I tried to verify the database type from the command but couldn't find the right command to show it. If the database is in fact MyISAM how can I change it to InnoDB?

Thank you!

mysql

5 Answers

  1. Best Answer

    Databases don't have storage engines, only tables do. I have no idea what PhpMyAdmin is trying to indicate there, perhaps the system-wide default engine or something. The documentation would presumably shed some light on WTF is going on.

    • 9

  2. This is not a bug. From the MySQL documentation:

    A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

    So you're fine.

    To see the table type:

    SHOW CREATE TABLE schema_migration

    To see the database storage engine:

    mysqldump --no-data $YOURDATABASE
    • 3

  3. The only bug is that PhpMyAdmin is showing anything at all for the database, because anything it shows in that context is irrelevant. As womble suggests, it may be indicating the default engine (and encoding) but I suggest you ignore it and only concern yourself with the engine used for the tables. After all, a database is little more than a logical collection of tables.

    • 0

  4. yes we can create 2 kind of tables in phpmyadmin, i did create InnoDB and MyISAM tables in my database. This is what you want. Thanks

    • 0

  5. @womble is right. However, if you want some peace of mind you can set a few configuration options. Your mysql configuration file, my.cnf, which, if you are using XAMPP is located at /Applications/XAMPP/etc/my.cnf, or if you are using a standard installation is usually located at /etc/my.cnf, has a section called [mysqld]

    The default storage engine is MyISAM, but you can change it to InnoDB by adding this under that section:

    [mysqld]
default-storage-engine = innodb

    That will change what shows up in that last row:

    If you are taking the time to make sure your database is really set up well, you might be interested in a few more items relating to setting the character set to utf-8 as well, because it is generally seen as a best practices for web development:

    In your config.inc.php, which is located in your phpmyadmin folder, you can set these parameters:

    // use utf_8 by default (the standard values on newer versions of pma)
// *******************************************************************
$cfg['DefaultCharset'] = 'utf_8';


//set a default Collation (utf8_general_ci is default on newer versions)
// *********************************************************************
$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';


//use mysqli for security and, reading utf normally displayed with the BLOB type
// *****************************************************************************
$cfg['Servers'][$i]['extension'] = 'mysqli';

    You can find all config options by visiting the PmaWiki or by looking at (but not changing!) the values in the this file: phpmyadmin/libraries/config.default.php.

    Using utf-8 is the new recommended standard with web applications, especially for applications where users may input words with different languages / keyboard sets. Check out some of the differences between the utf8_bin and utf8_general_ci character sets in this nice little post which should help you choose the right one.

    • 0