HOW TO CHANGE THE MYSQL DATABASE ENGINE

When you are running Imixs Workflow on a Linux Server using MySQL the database default engine is typically ‘MyISAM’. This is a fast engine type.

But for transactional systems it is recommended to use a database engine which is supporting transactions. This database engine in MySQL is called ‘InnoDB’. When you change the default engine type in your MySQL setup an new Imixs Workflow instance will create tables based on this engine type. But you can also simply change the engine type from ‘MyISAM’ to ‘InnoDB’ with a sql script:

ALTER TABLE `CALENDARITEM` ENGINE = InnoDB;
ALTER TABLE `DOUBLEITEM` ENGINE = InnoDB;
ALTER TABLE `ENTITY` ENGINE = InnoDB;
ALTER TABLE `ENTITYINDEX` ENGINE = InnoDB;
ALTER TABLE `ENTITY_CALENDARITEM` ENGINE = InnoDB;
ALTER TABLE `ENTITY_DOUBLEITEM` ENGINE = InnoDB;
ALTER TABLE `ENTITY_INTEGERITEM` ENGINE = InnoDB;
ALTER TABLE `ENTITY_READACCESS` ENGINE = InnoDB;
ALTER TABLE `ENTITY_READACCESSENTITY` ENGINE = InnoDB;
ALTER TABLE `ENTITY_TEXTITEM` ENGINE = InnoDB;
ALTER TABLE `ENTITY_WRITEACCESS` ENGINE = InnoDB;
ALTER TABLE `ENTITY_WRITEACCESSENTITY` ENGINE = InnoDB;
ALTER TABLE `INTEGERITEM` ENGINE = InnoDB;
ALTER TABLE `READACCESS` ENGINE = InnoDB;
ALTER TABLE `READACCESSENTITY` ENGINE = InnoDB;
ALTER TABLE `TEXTITEM` ENGINE = InnoDB;
ALTER TABLE `WRITEACCESS` ENGINE = InnoDB;
ALTER TABLE `WRITEACCESSENTITY` ENGINE = InnoDB;

This sql script did not connect to a specific database. For this reason it is necessary that you connect fist to the database the changes should be assigend. To run the script you can use the mysql command line tool in the following way:

First save the script into a local script file (e.g ‘mysql_innodb_imixs’). This file will be called later from the mysql console.

Now connect into the mysql Server:

mysql -u root -p

next select the database where the engine should be changed:

mysql> connect mydatabase;

finally you can run the script with the following command (assuming the you have stored the script before into a file named ‘mysql_innodb_imixs’ which is located in the current directory) :

mysql> source  mysql_innodb_imixs;

Thats it. Now you tables will use the new Database engine ‘InnoDB’.

When you configure the JDBC Datapool connection from your Glassfish Server it is strongly recommended to use a ‘javax.sql.XADataSource’ connection. This DataSource type supports the transaction scope provided by the Imixs Workflow System.