How to convert mysql database to INNODB

Help! (Home for newbies)
1

I have a Joomla database that was installed automatically, with MyISAM, I believe. I want to install a package (CiviCRM) which requires INNODB for transactions and foreign key support.

I have two questions.

  1. Is there an easy way to do this under Virtualmin/ Webmin?

  2. (For future reference), is there a way to set the default for new databases to INNODB? Script installs don’t seem to ask this point.

Thanks

2

Howdy,

I don’t know of a way to do this from the GUI, but there is a way to do it from within MySQL.

There are instructions on doing that here:

http://rackerhacker.com/2007/10/03/convert-myisam-tables-to-innodb/

You can use the MySQL config file to set what the default storage engine is. There’s details on that within the MySQL documentation, as well as here:

http://recurser.com/articles/2009/08/09/innodb-as-default-mysql-table-type/

3

So, at https://:10000/mysql/list_vars.cgi, it lists many variables that can be changed, but “storage engine” and “table type” are not editable.

Also, this page https://:10000/mysql/edit_cnf.cgi appears to be set up to edit the my.cfg file, but I don’t see any mention of default engine or table type there either.

This might be a very stupid question (I’m ignorant here), but since these pages only list a single “engine”, would changing the my.cfg file break existing databases? Can MySQL run both engines at the same time?

4

Howdy,

Well, first off, I’m not a storage engine guru :slight_smile:

A few thoughts though –

  1. You may want to read up on MySQL storage engines, and what all is available. You can do that on the MySQL site here:

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

2… You’re welcome to use as many storage engines on one server as you like, you’re not required to use the same one. The docs at the above link mention the following:

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

  1. If I were in your shoes, I wouldn’t change the default storage type for your entire server. To create a table using INNODB, so long as it’s supported by the server, you need only pass in a parameter when creating the table. I’d probably make the assumption that the app knows how to correctly create the table unless you find otherwise.

-Eric