Database question MyISAM -> InnoDB

[crythias]

I have messed with upgrading a 3.0.x database to 3.1.x and encountered issues because of MyISAM source and InnoDB destination. (Explicitly: Foreign Keys/FK)

I was successful in doing the following: Export/dump database.
make a backup of that export.
edit the database dump sql file
global search/replace :%s/MyISAM/InnoDB/g
drop/rename source database from mysql interface
import changed sql file.
run otrs upgrade.

It so far seems to work. Should anyone else have a question on this or suggestion, I'm open to it.

(Don't use my experience and expect it to work for you. It might. It might not. I'm looking for feedback from others if this makes sense. To me, it does, but if it doesn't work, I don't want to be held responsible for your data loss.)
edit: This post: http://www.amitbiswas.com/database/conv ... -in-mysql/ seems to bear out what I've suggested.

[Val]

Hi crythias,

Thanks a million! This did work.

As you suggested I did the following.

1) mysqldump -uusername database -p | gzip > dump-$(date +%Y-%m-%d).sql.gz
2) gunzip dump-2012...sql.gz
3) sed -e 's/MyISAM/InnoDB/g' -i dump-2012-...sql
4) mysql -uusername database -p < dump-2012---..sql

5) ran the Perl upgrade script.


Thank you very much!
Kind regards,
Valentin
MuntInternet Networks

[crythias]

Another Article seems to indicate it may be simpler to do this:

Code: Select all

ALTER TABLE (tablename) ENGINE = InnoDB;