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.
Posted at 01:32PM Sep 15, 2011
Posted by: Ralph
Category: Business
MySQL and Blob field size
Using the Imixs Workflow together with mysql can lead into a problem with large data stored in a single workitem. The Database schema use by the Imixs Workflow will be generated automatically by the OR-Mapper (e.g. Toplink, Eclipslink). This is all done well. But the Data table 'EntityData' used by the Imixs Workflow engine contains the column 'itemcollection'. This column will store all undstuctured data. And in some cases this data can become very large. For example when storing a attachment into a workitem the field can be serveral MB in size. The default field type in mysql is 'BLOB'. And this fieldtype is restricted to a maximum size of 64KB. So the Imixs workflow engine can not store large workitems. This will result into a SQL Execption.
To avoid this problem the Datatype can be changed from 'BLOB' to 'MEDIUMBLOB' (=16MB) or 'LONGBLOB' (=4GB). This can be done with the MySQL Admin Client or from the MySQL command line.
ALTER TABLE <Tabellenname> CHANGE <columnname> <columnname> LONGBLOB;
To change the datatype for a example database 'imixsdb' use the following command:
ALTER TABLE ENTITYDATA CHANGE ITEMCOLLECTION ITEMCOLLECTION LONGBLOB;
Posted at 11:34AM Dec 12, 2010
Posted by: Ralph
Category: Business
Database Migration Derby -> MySQL
If you have started you workflow project using the build-in Derby Database from Glassfish you maybe can run into a performance problem if you manage a lot and complex of Data. This is because Derby is not so scalable in managing complex SQL statements like other Database Management Systems. In such a case you can easily migrate your workflow system form Derby to MySQL which is much more scalable....[Read More]
Posted at 09:36AM May 30, 2009
Posted by: Ralph
Category: Business