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 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.

Creation of the database

PostgreSQL

To create a new 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";

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 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'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON squashtm.* TO 'squash-tm'@'%' WITH GRANT OPTION;
GRANT ALL ON squashtm.* TO 'squash-tm'@'%';
CREATE ROLE alter_squash_table_seq;
GRANT alter_squash_table_seq TO 'squash-tm'@'%';
SET DEFAULT ROLE alter_squash_table_seq FOR 'squash-tm'@'%';
FLUSH PRIVILEGES;

Database setup

The database is installed automatically on the first start of Squash TM. Similarly, the update of the base during version upgrades of Squash TM will be provided by Squash TM itself. These updates are controlled by the parameter squash.db.update-mode. The possible values 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.

Removal of orphaned large objects from PostgreSQL

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, it is necessary to use the vacuumlo program (see documentation from PostgreSQL) in order to make available the space occupied by the deleted attachments.