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.
Follow these steps to migrate data in a running system
- Start your Derby DB and Glassfish Server
- Make sure that your Workflow System is up and running
- Switch to the Imixs JEE Admin Client.
- Connect to the EntityPersistenceManager from your Workflow Instance
- Start a export using the default EQL Statement. This will export all existing Data of your workflow system into a file
>SELECT wii FROM Entity wiI
If the export did not succeed a reason can be the HeapSize of your Glassfish Server instance. You can increase the default HeapSize from 512m to 1024m using the Glassfish Admin Console.
Go to : Applicationserver->JVM Settings -> JVM Optionsand change -Xmx512m into -Xmx1024m - Undeploy your Workflow Instance
- Shutdown your Derby Database
- Setup a new MySQL Database and create a new JDBC Connection Pool using the Glassfish Console
- Now you can switch the JDBC/Ressource of your Workflow System from your Derby Database to your new MySQL Database
- Deploy your Workflow Instance again – this will generate the necessary Tables in your new MySQL Database
- Optionall : after deplyoment you can setup Entity Index Fields manually if necessary
- Import your Data form the Export File you generated in Step 5.
Thats it!
If the import process fails on MySQL check the following:
PACKETTOOBIGEXCEPTION
If you got the Excepiton:
com.mysql.jdbc.PacketTooBigException: Packet for query is too large
you should change the max_allowed_packet setting in your MySQL server for the import process
- Open your MySQL console
>mysql -u root -p - Change the max_allowed_packet setting
>SET GLOBAL max_allowed_packet=3000000000; - Restart your Glassfish Server (not the MySQL Database!)
- Reimport your data
- Restart you MySQL Server and Glassfish Server to reset the max_allowed_packet settings.
PERSISTENCEEXCEPTION
If you got the Exeption:
javax.persistence.PersistenceException: Exception [TOPLINK-4002] (Oracle TopLink Essentials – 2.0.1 (Build b09d-fcs (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘DATA’ at row 1
MySQL tells you that the BLOB field is to small to store the data. This is because MySQL generates per default a SmallBlob (64K) for your Data Fields in the Entity Table. You can change the Blob type after the deplyoment of your Workflow Instance :
- Open your MySQL console
>mysql -u root -p - Connect to to your Database
>USE mydatabase; - Change the BLOB Type to LONGBLOB (4GB) or MEDIUMBLOB (16MB)
>ALTER TABLE Entity CHANGE DATA DATA MEDIUMBLOB NOT NULL;