Skip to content

Install database

Squash TM is delivered with an H2 database by default. This database can be used for evaluation purposes. It must not be used in production.

Squash TM is compatible with two DBMS: PostgreSQL and MariaDB.
The supported and recommended versions are indicated on this page.

Required disk space

During certain operations involving massive data deletion, database engines do not immediately release disk space. These operations can temporarily increase the space used by the database.
For example, when you delete a large number of records, the database engine initially retains the allocated space, simply marking it as available for future use. This behavior optimizes performance by avoiding constant reorganization of data files.
For this reason, it is recommended to have disk space at least 1.5 times larger than the current database size. This ensures sufficient margin for maintenance operations and processing large quantities of data.
If you want to effectively recover unused disk space, you must use specific commands:

  • For PostgreSQL: use the SQL VACUUM command
  • For MariaDB: use the SQL OPTIMIZE TABLE command

These commands physically reorganize data files, actually freeing up disk space that is no longer necessary.

PostgreSQL

To create a new UTF8 database for Squash TM, enter the following queries :

CREATE DATABASE squashtm WITH ENCODING='UTF8';
CREATE USER "squash-tm" WITH PASSWORD 'password';
ALTER DATABASE squashtm OWNER TO "squash-tm";
GRANT CONNECT ON DATABASE squashtm TO "squash-tm";

To install the database:

  • Access the squash.tm.cfg.properties configuration file;
  • Modify the parameter squash.db.update-mode to set its value to only;
  • Start Squash TM, which will install the database and immediately stop afterward.

Once the database is installed:

  • Set the appropriate value for the squash.db.update-mode parameter in the squash.tm.cfg.properties file. The possible values and their effects are described on this page.

Focus

We recommend performing all actions on the database using the user squash-tm.
Ensure that the user squash-tm has the ownership rights on the database. Reassign these rights if necessary.

Info

Deleting Squash TM objects containing attachments does not free the space occupied by these attachments in the database (only the link to the space occupied by the files).
To free up disk space, you need to use the vacuumlo program (see PostgreSQL documentation) to clean up the database of unneeded files.

MariaDB

MariaDB configuration

The @@sql_mode variable must contain the following tags:

  • NO_ENGINE_SUBSTITUTION
  • STRICT_TRANS_TABLES

It must not contain the following elements:

  • ONLY_FULL_GROUP_BY
  • EMPTY_STRING_IS_NULL
  • NO_BACKSLASH_ESCAPES

You must define the variables in the configuration file my.ini, in the part [mysqld].

Warning

You must absolutely use InnoDB. The database engine must absolutely be a transactional database engine.
For example, the engine MyISAM is not transactional and can corrupt your data. Henix does not recommend using it, and if you do, Henix is not responsible for any issues created by such use.

To create a new UTF8 database for Squash TM, enter the following queries using a user with full rights: 

CREATE DATABASE IF NOT EXISTS squashtm CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE USER 'squash-tm'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON squashtm.* TO 'squash-tm'@'localhost';
FLUSH PRIVILEGES;

To install the database:

  • Access the squash.tm.cfg.properties configuration file;
  • Modify the parameter squash.db.update-mode to set its value to only;
  • Start Squash TM, which will install the database and immediately stop afterward.

Once the database is installed, set the appropriate value for the squash.db.update-mode parameter in the squash.tm.cfg.properties file. The possible values and their effects are described on this page.

Analyzing database creation issues

Should any errors occur during the initialization of the database, you can check the logs in the squash-tm.log file. Initialization details, including execution status, are also logged in the DATABASECHANGELOG table.