MySQL

To provide a database for ConSol CM or for the CMRF the same steps are required. For the CMRF database just change the database/database user name.

The recommended database character set is utf-8. The collation depends on the MySQL version, see UTF-8 settings.

MySQL 5 only supports up to 3-byte Unicode characters. Use MySQL 8 if you need 4-byte characters.

Installation prerequisites

MySQL must be installed with InnoDB as default storage engine.

This is configured in the MySQL configuration file. The default file is:

UTF-8 settings

For MySQL 5, the following settings are necessary in the mysqld section of the configuration file:

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

For MySQL 8, the following settings are necessary in the mysqld section of the configuration file:

character-set-server=utf8mb4

collation-server=utf8mb4_0900_as_ci

Timezone settings on Windows

When executing MySQL on Windows, you need to manually set the time zone (see https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-installation for further reference).

  1. Go to https://dev.mysql.com/downloads/timezones.html and download the latest timezone posix file, e.g. timezone_2021f_posix_sql.zip, for your MySQL version.

  2. Unpack the file and open a terminal at the location where the timezone_posix.sql file is located.

  3. Run the following command:

    mysql -u root -p mysql < timezone_posix.sql

    mysql has to be added to the path as an environmental variable.

  4. Enter the administrator password and press the enter key.

  5. Restart the MySQL server (e.g. in the task manager under Services -> MySQL -> restart).

  6. Open the directory of the MySQL server and add the timezone setting to the my.ini file, [mysqld] section.

    default_time_zone='Europe/Berlin'

    The timezone must match the timezone of the local machine.

  7. Restart the MySQL server again.

General preparations

Create a database for ConSol CM. To do so, log in to MySQL Workbench or SQL Monitor (command line) with:

mysql -u root -p

(Without -p if no password was set during database setup.)

Execute the following commands to create a new database (placeholder <CM_DB>) and a new user (placeholder <CM_USER>. Grant the user <CM_USER> all necessary rights on the database <CM_DB>.

The default password policy requires at least 8 characters and a mix of letters and numbers. Nevertheless, this is not checked when executing the commands in the command line.

MySQL 5:

create database <CM_DB> CHARACTER SET utf8;

grant all on <CM_DB>.* to <CM_USER>@'%' identified by '<PASSWORD>';

grant all on <CM_DB>.* to <CM_USER>@localhost identified by '<PASSWORD>';

flush privileges;

MySQL 8:

CREATE USER '<CM_USER>'@'localhost' IDENTIFIED BY '<PASSWORD>';

CREATE USER '<CM_USER>'@'%' IDENTIFIED BY '<PASSWORD>';

create database <CM_DB> CHARACTER SET utf8mb4;

grant all on <CM_DB>.* to '<CM_USER>'@'%';

grant all on <CM_DB>.* to '<CM_USER>'@localhost;

flush privileges;

Recommended configuration settings

To avoid problems with scene import, it is recommended to set xa_detach_on_prepare=off globally for the database if you use MySQL 8 in version 8.0.29 or higher.

Add the following setting to the [mysqld] section of the my.cnf / my.ini file of your MySQL installation.

[mysqld]

xa_detach_on_prepare=OFF

Optional configuration settings

Sometimes threads may be stuck after setup, it may not be possible to log in to CM and restart it.

In this case, disable the query cache in your MySQL configuration by editing the following file:

Make the following settings:

query_cache_type = OFF

query_cache_size = 0

Alternatively, you can execute the following commands:

SET GLOBAL query_cache_type = OFF;

SET GLOBAL query_cache_size = 0;

If you are planning to have attachments bigger than 1 MB, the MySQL configuration file must also be adapted:

In the Server section [mysqld], the allowed maximum size of attachments must be entered.

The following value allows 16 MB attachments:

max_allowed_packet=16777216

To allow larger attachments, e.g. 25M MB, you might also need to increase the size of the MySQL log files:

innodb_log_file_size=256M

Post-installation tasks

After the CM-specific database objects have been created during the first successful start of the CM application (see Setting up the ConSol CM application), you can set the starting ticket number. By default, the ticket numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement to start the ticket numbers with 10000:

ALTER TABLE cmas_ticket AUTO_INCREMENT = 10000;