Should I use MyISAM or InnoDB for my MySQL or MariaDB database?

Question

Prior to MySQL version 5.5.5, the default storage engine was MyISAM.  MySQL version 5.5.5 and later use InnoDB as the default storage engine.  Which storage engine should I use with QIE?

Answer 1

QIE relies on database transactions which are not support by the MyISAM storage engine.  As a result, you should use the InnoDB storage engine.

If your QIE database is using the MyISAM storage engine, you will need to convert the tables to InnoDB.  Instructions on how to do that can be found here: http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html

If you do use the MyISAM storage engine, you may encounter duplicate key errors with the MessageFilterIndexer when QIE is restarted.  You may also encounter other data integrity problems if there is ever an error encountered while committing the database transaction.  Consequently, it is recommended that you avoid using the MyISAM storage engine for the QIE database.

To prevent using MyISAM storage engine on new QIE tables, you will want to make sure that you are running a more up-to-date dialect.  If you are running QIE using the

-Dhibernate.dialect=com.qvera.qie.persistence.MySQLUnicodeDialect
or
-Dhibernate.dialect=org.hibernate.dialect.MySQLDialect

then you should update to the minimum version of

-Dhibernate.dialect=com.qvera.qie.persistence.MySQL5UnicodeDialect

This should be done after converting all of your existing MyISAM storage engine tables to InnoDB.