4

So I think I am having problems with a component of Joomla because I do not have my db setup to use the InnoDB engine. I think this because I have not changed anything in the running of MySQL and believe MyISAM to be the defualt. Is there a way to check this first of all?

Secondly, if what I believe to be true is, does anyone know any good resources/guides for converting a DB from one engine to another? I have found a few but hasten to point out that I am a massive SQL beginner and this is proving to be somewhat of a baptism of fire. The guides I have looked at so far are all somewhat varied in their approach and a lot of the commands are all pumped together making it somewhat tricky to work out what is going on. The big boon is that this server is virtual and I have already taken a bunch of snapshots so any errors are not major.

I am using a Lamp stack with Ubuntu 9.10 x64, Apache 2.2.12, MySQL 5.1.37 and PHP version 5.2.10-2

2 Answers2

6

In answer to part one of your question. The quickest way to determine whoch storage engine is default is to issue the following;

$mysql -h localhost -u user -p -e "SHOW ENGINES"

This will result in a list of supported engines that the db server you are connected to is enabled with. The default one will have "Support DEFAULT" indicating which engine is configured as the services default one.

In relation to the second part of your question. There are a number of ways to do this. The first way is to use SQL, so you could issue;

ALTER TABLE mytable ENGINE = InnoDB;

This can have a number of drawbacks. It will take sometime if your table is large too. See http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for more info on it. But if its a quick and dirty method, that will be fine.

The other option is to use mysqldump to dump all your MyISAM database. You could then alter the resulting SQL dump using a find replace;

sed -i 's/MyISAM/INNODB/g' file.sql

However, like Patkos suggested, I would dump just the create syntax and then create a new db but set its default engine for that schema to be Innodb and re-import the data

AJM
  • 83
1

believe MyISAM to be the defualt. Is there a way to check this first of all?

Yes, it is, just look in my.ini (or my.cnf) and search for myisam or innodb or engine. If nothing is specified, check on the mysql website the default for your version of mysql. You can find out how tables were created with the SHOW TABLE STATUS command, and look for engine in the output.

does anyone know any good resources/guides for converting a DB from one engine to another?

The simplest way I know of, is just mysqldump current database, than open the dumped file and replace all myisam with innodb. Then drop old database, make a new one, and import from the modified dump.

PS: engines are at table level not at database level!

Patkos Csaba
  • 1,735