Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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!

share|improve this answer
    
dumping a db and then recreating is not as simple as that. a lot of intervention might be necessary, especially if user has many views, because recreation order is a factor. i would not recommend this as the simplest solution, though it is a solution. –  tony gil Sep 18 at 12:38

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.