Introduction

As YAWL uses hibernate as persistence layer it supports every database that Hibernate supports. (see here) All you need to do is to configure the database in the hibernate.properties file of the three web applications "resourceService", "yawl" and "workletService". This is briefly explained in the YAWL user manual section 2.4.4. In the following sections we provide detailed information to get YAWL working with different database management systems.

Databases

Regardless the database you'd like to use, there is one (since YAWL 3) configuration file you have to adjust:

  • \engine\apache-tomcat-x.x.xx\yawllib\hibernate.properties

Apache Derby

The YAWL4Study edition is pre-configured to use the embedded database Apache Derby. Although this database provides full support for YAWL it is generally not recommended to be used in a production system. For development or evaluation use it is sufficient. The database is located in the directory \engine\databases of your YAWL4Study installation. Details on building the correct connection url for Apache Derby can be found in the Derby documentation.

hibernate.properties:

## Apache Derby
hibernate.dialect=org.hibernate.dialect.DerbyDialect
hibernate.connection.url=jdbc:derby://localhost/C:/Users/UserXY/YAWL4Study-2.2.01/engine/databases/yawldb;create=true
hibernate.connection.driver_class=org.apache.derby.jdbc.ClientDriver

PostgreSQL

PostgreSQL is the default database of the YAWL enterprise edition. Therefore the easiest way to use PostgreSQL is to install the enterprise edition of YAWL and you will be asked for details about your PostgreSQL server during installation. But if you already installed YAWL4Study, you have to adjust your hibernate.properties to the following configuration. Details on building the correct connection url for PostgreSQL can be found in the PostgreSQL documentation.

hibernate.properties:

## PostgreSQL
hibernate.dialect org.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class org.postgresql.Driver
hibernate.connection.url jdbc:postgresql:yawl
hibernate.connection.username yawl
hibernate.connection.password yawl

MySQL

To use MySQL you should install the YAWL enterprise edition, as MySQL is a configuration option during the installation. If you'd like to switch to MySQL later on, then adjust the hibernate.properties files as follows and add the correct MySQL JDBC Driver to the lib directory of YAWLs Apache Tomcat. Details on building the correct connection url for MySQL can be found in the MySQL documentation. You may want to choose the MySQL5InnoDBDialect instead of the usual MySQlDialect if you want YAWL to generate InnoDB tables. (Read more about InnoDB)

MySQL JDBC Driver:

  1. Download JDBC Driver from http://dev.mysql.com/downloads/connector/j/
  2. Copy mysql-connector-java-5.x.xx-bin.jar to \engine\apache-tomcat-6.0.18\lib

hibernate.properties:

## MySQL
#hibernate.dialect org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.dialect org.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql:///yawl
hibernate.connection.username root
hibernate.connection.password root

MSSQL (SQL Server 2005/2008)

To use MSSQL you need to adjust the hibernate.properties files as follow and add the correct MSSQL JDBC Driver to the lib directory of YAWLs Apache Tomcat. Please note, the Microsoft JDBC Driver is not Open Source and there are other implementations available. (For Example jDTS) Details on building the correct connection url for MSSQL can be found in the MSSQL documentation.

MSSQL JDBC Driver:

  1. Download JDBC Driver from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21599
  2. Copy sqljdbc4.jar to \engine\apache-tomcat-6.0.18\lib

hibernate.properties:

## MS SQL Server
hibernate.connection.driver_class com.microsoft.sqlserver.jdbc.SQLServerDriver
hibernate.connection.url jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=yawl;user=yawl;password=yawl;SelectMethod=cursor
hibernate.dialect org.hibernate.dialect.SQLServerDialect
hibernate.connection.username yawl
hibernate.connection.password yawl

I am using YAWL4Study 2.3 on an Ubuntu Linux.

I set up a postgres database with a database called 'yawl' and a user 'yawl' with password 'yawl' who owns the yawl database and has administration rights.

Then I modified the three hibernate properties files like this:

**********************************************************
## PostgreSQL
hibernate.dialect org.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class org.postgresql.Driver
hibernate.connection.url jdbc:postgresql:yawl
hibernate.connection.username yawl
hibernate.connection.password yawl

## H2

#hibernate.dialect=org.hibernate.dialect.H2Dialect
#hibernate.connection.driver_class=org.h2.Driver
#hibernate.connection.url=jdbc:h2:/home/ahense/YAWL4Study-2.3final/engine/databases/yawldb;MVCC=TRUE
**********************************************************

After restarting the engine, YAWL keeps using the file-based database and does not create any tables in postgres.

What am I missing here?

I tried to run with all 'hibernate.properties' files modified like in your configuration. YAWL creates the tables in PostgreSQL automatically and is running fine. Can you check the console output of YAWL (or log file if used as service) for lines like the following and post them.

- HHH000130: Instantiating explicit connection provider: org.hibernate.service.j
dbc.connections.internal.C3P0ConnectionProvider
- HHH010002: C3P0 using driver: org.postgresql.Driver at URL: jdbc:postgresql:ya
wl
- HHH000046: Connection properties: {user=yawl, password=****}
- HHH000006: Autocommit mode: false
- Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@518f739a [
connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@

Thank you. I have tried with several fresh installations of YAWL 2.3: it keeps using the file for persistence and ignores postgres. Even renaming the three hibernate.property files didn't work.

I have tried it with YAWL 2.2: it used postgres right away after changing the hibernate.property files accordingly.

I have looked at all the files in the logs directory of tomcat but there are no lines like the ones you posted. The catalina.out looks like this:

Dec 04, 2012 3:34:22 PM org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: /usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
Dec 04, 2012 3:34:22 PM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
Dec 04, 2012 3:34:22 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 449 ms
Dec 04, 2012 3:34:22 PM org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
Dec 04, 2012 3:34:22 PM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.18
Dec 04, 2012 3:34:22 PM org.apache.catalina.startup.DigesterFactory register
WARNING: Could not get url for /javax/servlet/resources/web-app_2_5.xsd
Dec 04, 2012 3:34:22 PM org.apache.catalina.startup.DigesterFactory register
WARNING: Could not get url for /javax/servlet/resources/web-app_2_5.xsd
log4j:WARN No appenders could be found for logger (com.sun.faces.config.ConfigureListener).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
ContextListener: attributeAdded('com.sun.faces.ApplicationAssociate', 'com.sun.faces.application.ApplicationAssociate@4faa65fe')
Dec 04, 2012 3:34:30 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Dec 04, 2012 3:34:30 PM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Dec 04, 2012 3:34:30 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/21  config=null
Dec 04, 2012 3:34:30 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 8392 ms

Maybe the log4j warnings give some hints?

The log4j warnings are not about the issue. Unfortunately I can't test with Linux at the moment. Recheck if there is a file like "postgresql-9.0-801.jdbc4.jar" in the "engine/apache-tomcat-6.0.18/lib" of YAWL.

Please attach all log files (I just enabled "attaching files in comments"), that includes log files in "engine/apache-tomcat-6.0.18/bin" and "engine/apache-tomcat-6.0.18/logs". Probably it is best to upload  the whole, zipped, YAWL directory and I will have a look.

Christoph Lehnert

Thu, 10/24/2013 - 17:54

I am also using YAWL4Study 2.3.5 on an Ubuntu Linux and faced the same problem.
After adjusting the configuration file \engine\apache-tomcat-6.0.18\webapps\documentStore\WEB-INF\classes\hibernate.properties as mentioned above everything worked fine for me.

matteo spiotta

Wed, 06/08/2016 - 10:19

I'm using YAWL 4 with MSSQL 2008. All work fine aside handling YDocument. Im using YAWL with the data store service. By default the yDoc column in the YDocument table is defined as varbinary(255).
It can be extended to 8000 (maximum size allowed by MSSQL) adapting the function fixH2BinarySize in DocumentSore.java to work also for MSSQL db.
To handle bigger file I had to modify the methods getDocument and PutDocument to store and retrive document directly in/from the filesystem.
Please contact me if you solved the same problem in other ways.