Edit Database Settings
This page allows you to setup the SQL database connection.
It is mandatory to switch FileCatalyst Workflow to
an external MariaDB or MySQL instance, rather than continuing with the internal HSQL
database server. The HSQL database server is only provided to facilitate installation, and lacks
the security and performance required for a production environment.
Choosing a Database Type
Internal HSQL DB is the built-in database server. It is useful for initial setup and evaluation because it
runs with FileCatalyst Workflow on the same machine, but it is not recommended for production use.
MariaDB is the recommended external database option when using the JDBC driver included with the application.
FileCatalyst Workflow ships with MariaDB Connector/J, and the MariaDB driver can also be
used for MySQL-compatible deployments when a separate MySQL driver is not installed.
MySQL should be selected when connecting to a MySQL server with Oracle's MySQL Connector/J driver. The application
does not ship with the MySQL driver. To use the MySQL driver directly, add the external MySQL Connector/J JAR file to the
deployed application's WEB-INF/lib folder and restart Tomcat.
Other DB should be selected only when using a database that is compatible with MySQL syntax and behavior.
The external JDBC driver JAR file for that database must be added to the deployed application's
WEB-INF/lib folder, and Tomcat must be restarted before the driver can be used.
The database path typically looks like one of these options:
| Internal HSQL DB | jdbc:hsqldb:hsql://localhost:4406/ | HSQL runs on the same machine as
FileCatalyst Workflow, so localhost is acceptable. |
| MariaDB | jdbc:mariadb://{SERVER_HOST}:{PORT}/ | Use this URL with the included
MariaDB Connector/J driver. |
| MySQL | jdbc:mysql://{SERVER_HOST}:{PORT}/ | Use this URL when the external
MySQL Connector/J driver has been added to WEB-INF/lib. |
| Other DB | jdbc:{DRIVER_SCHEME}://{SERVER_HOST}:{PORT}/ | Use the JDBC URL
required by the external driver. The database must be MySQL-compatible. |
MySQL
Note that if connecting to a MySQL server located on another computer, create privileges will have to be granted
using the MySQL command line to the computer running FileCatalyst Workflow.
Drivers
Database drivers (JDBC) are freely available for most databases. The application ships with drivers for
Internal HSQL DB and MariaDB. The application no longer ships with the MySQL driver due to licensing restrictions.
If an external MySQL Connector/J JAR is not present, FileCatalyst Workflow will use
MariaDB Connector/J and a
jdbc:mariadb: URL for MySQL-compatible connections.
If an external MySQL Connector/J JAR is present, FileCatalyst Workflow can use the MySQL driver
with a
jdbc:mysql: URL. The "Other" option can also be used with an external JDBC driver for a
MySQL-compatible database. To add an external driver, follow these steps:
- Shut down the Tomcat server.
- Browse to lib directory of the application deployment. For instance on a Tomcat deployment the lib directory can be found at -
/{path to tomcat}/webapps/workflow/WEB-INF/lib
- Copy the MySQL Connector/J JAR file, or the JDBC driver JAR file for the MySQL-compatible database, into the
WEB-INF/lib folder.
- Restart Tomcat.
- Login to the application with your super-admin credentials and navigate to 'Edit Database Settings' page again.
- Select the "MySQL" option, or select the "Other" option if you need to use a custom JDBC driver.
- When using the MySQL driver directly, the JDBC driver class is
com.mysql.cj.jdbc.Driver.
Older MySQL Connector/J versions used com.mysql.jdbc.Driver.
- When using the "Other" option, provide the JDBC driver class name supplied by the database vendor.
- Fill out the database path for the database you are connecting.
- Provide the database name, the username and the password to authenticate the database connection.
- Click on
Convert Existing Data or Save Database Settings based on your use case.
Database Parameters
When using MySQL and not connecting securely using SSL, Database Parameters must include "useSSL=false", this will suppress
security warnings in the FileCatalyst Workflow logs.
Settings Related to the Database Connection Pool
A connection pool is a cache of database connections maintained by the database
so that the connections can be reused when the database receives future requests
for data. Connection pools are used to enhance the performance of executing commands on a
database. Opening and maintaining a database connection for each user, especially requests
made to a dynamic database-driven web site application, is costly and wastes resources.
In connection pooling, after a connection is created, it is placed in the pool and it is
used over again so that a new connection does not have to be established. If all the connections
are being used, a new connection is made and is added to the pool. Connection pooling also cuts
down on the amount of time a user must wait to establish a connection to the database.
FileCatalyst Workflow provides the following configurable pool settings:
minimum-connection-count:
The minimum number of connections we will keep open,
regardless of whether anyone needs them or not. Recommended default
is 5.
maximum-connection-count:
The maximum number of connections to the database. Recommended Default
is 20.
maximum-active-time:
If the housekeeper comes across a thread that has been active
for longer than this then it will kill it. So make sure you set
this to a number bigger than your slowest expected response!
Recommended Default is 5 minutes.
maximum-connection-lifetime:
The maximum amount of time that a connection exists for before
it is killed (milliseconds). Recommended Default is 4
hours.
prototype-count:
If there are fewer than this number of connections available
then we will build some more (assuming the
maximum-connection-count is not exceeded). For example. Of we
have 3 active connections and 2 available, but our
prototype-count is 4 then it will attempt to build another 2.
This differs from minimum-connection-count because it takes into account
the number of active connections. minimum-connection-count is absolute and
doesn't care how many are in use. prototype-count is the number of spare
connections it strives to keep over and above the ones that are currently active.
Recommended Default is 2.
View Pool Status:
Will display the current status of the database pool, use this option to optimize the pool settings for your deployment.
Save Buttons
"Save Pool Settings" button will only update the pool settings and will not change the database settings
"Convert Existing Data" button will save the database settings (but not the Pool settings), it will then create the database and import the
existing data to the new database. This button is recommended when migrating from HSQL to MySQL. Converting from MySQL back to HSQL is NOT supported.
"Save Database Settings" button will save the database settings (but not the Pool settings) - no data migration will be attempted.
Use this button when updating the MySQL connection to SSL for example
and when you already have all the data synchronized between the two database settings.
