Tag: MySQL

The Imixs-Workflow project offers a professional and modern way to manage a business process in enterprises and organizations. This Workflow platform focus mainly on human-based workflows with means long living business processes and also business processes with a lot of data. Typical you can also use Imixs Workflow for DMS functionality as it cares about not only the data access and an easy way to find a business workflow but also Imixs-Workflow can managed large data objects like documents (PDF, MS-Word, MS-Excel, Photos or documents provided from a scan process).

DMS with MySQL

As Imixs-Workflow solutions are mostly running on open source platforms the Database Management System (DBMS) MySQL is a typical platform to store the process information. Imixs-Workflow uses an Binary-Large-Object format (BLOB) to store documents together with other process data.

But storing large data into MySQL can become a little bit tricky if your data exceeds the 1MB border which can happen immediately if your store documents. To prepare you MySQL Server to handle such amount of big data there are some MySQL settings witch need to be checked before. The settings can be set global by editing the [mysqld] section int the /etc/mysql/my.cnf file.

max_allowed_packet

The setting “max_allowed_packet”  defines the maximum size of the data included int sql statement. It shuld be set to a size of the expected maximum file size.  Example:

max_allowed_packet = 16MB

innodb log buffer size

The second important setting is the parameter “innodb_log_file_size” which should be large enough to do two things:

• Accommodate any big BLOB or TEXT fields
• Holding bigger transactions

If the innodb_lof_file_size is to small the EJB container can throw the following kind of exception:

11:19:34.237+0200|WARNING|glassfish3.1.2|org.eclipse.persistence.session.file:/mnt/opt/glassfish3/glassfish/domains/domain1/applications/reemtsma_pl/office-aeo-mms-ejb-1.1.1_jar/_org.imixs.workflow.jee.jpa|_ThreadID=96;_ThreadName=Thread-2;|Local Exception Stack: 
 Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
 Error Code: 1205
 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
     bind => [2 parameters bound]

We recommend the following setting:

innodb_log_buffer_size = 100M
innodb_buffer_pool_size = 1G
innodb_log_file_size = 1G

After changing the configuration restart mysql with the following command:

mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

Using the innodb_fast_shutdown = 0 forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1.

Read more details about this topic here.

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;