Tags: Database Installation MySQL

Preparing the MySQL Database for Installation

This page describes how to setup a MySQL database for codebeamer.

codebeamer is delivered with a Derby

database that should be used only for evaluation purposes. It is strongly recommend using codebeamer with MySQL or Oracle because they are faster, more robust and reliable databases. After Q3 2020,codebeamer does not support the Derby

database.

Supported MySQL versions:

Before codebeamer release 21.09 (EMMA), MySQL 5.7.x is supported, and InnoDB storage engine is required.
After Q3 2020, MySQL 8.0.x is also supported.
Since codebeamer release 21.09 (EMMA), only MySQL 8.0 is supported. If needed Migrate MySQL 5.7 CB database to 8.0.

To use this document, familiarity with MySQL database setup and administration is needed.

Backup your data! Before doing anything, backup your existing database. Also ensure that during migration nobody uses the database, because it may cause loss of data.

To migrate from utf8 schema to utf8mb4 schema, see: Migrate MySQL database to UTF8MB4 character encoding.

Windows, Unix, MAC-OS

Downloading and installing the MySQL database is not part of this document. Refer to the MySQL homepage for instructions.

Linux

Install MySQL via the distribution's package manager.

It is highly recommend using the same version that codebeamer was tested on. You can find this version in the release notes of the current version.

MySQL Configuration

Important note: when upgrading codebeamer from an earlier (pre 10) release, keep the existing encoding and collation (utf8) and perform the utf8mb4 database upgrade once the 10.1 codebeamer upgrade is completed. Then, continue the upgrade to Carmen (or later release) so the MySQL database procedures and functions will be updated as well.

When installing MySQL, be sure to set and remember a sensible root password.

Ensure that the following settings are configured in your database. You can find these settings in my.ini, that typically resides in the MySQL installation directory (e.g. in Windows: C:\ProgramData\MySQL\MySQL Server 5.7, or Linux/Unix: /etc/mysql/my.cnf, or MAC-OS: /usr/local/mysql/my.cnf).

When editing the my.ini file on Windows, make sure to save the file with ANSI encoding. Otherwise the MySQL service will not start.

MySQL 5.x

[mysqld]
character_set_server=utf8mb4
character_set_filesystem=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
max_allowed_packet=128M
sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

MySQL 8.0.x

[mysqld]
character_set_server=utf8mb4
character_set_filesystem=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
max_allowed_packet=128M
sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
disable_log_bin

MySQL 8.0.x comes with binary logging enabled by default (unlike previous releases). This must be disabled for successful post-installation.
The following option must be added/uncommented in the MySQL configuration file, in addition to the other codebeamer related options (described above): disable_log_bin. Comment out the line that starts with log-bin, and put a line below that with skip-log-bin.

Creating the database

The following steps are not required for new installations, because the database is created automatically by the installer. These steps are only necessary when upgrading or migrating codebeamer, or, when the database is created manually for any other reason.

In the following examples, it is assumed that the database is running on the same machine as codebeamer (localhost) and listens to default port 3306, but this is not required.

Prepare the codebeamer MySQL database

In order for codebeamer to access a MySQL database, an appropriate database schema and user have to be prepared. You can use any username and password instead of the cbroot and cbpassword used below:

Open a command prompt (on Windows execute "cmd"), and go into the $MYSQL_HOME$/bin directory (for example in Windows it could be C:\Program Files\MySQL\MySQL Server 5.7\bin).
Execute the command: mysql -u root -p. When required, provide it with the password which was configured during installation.
Execute commands:

MySQL 5.x

CREATE DATABASE codebeamer default character set 'utf8mb4' default collate 'utf8mb4_general_ci';
CREATE USER 'cbroot'@'localhost' IDENTIFIED BY 'CbPassword01!';
GRANT ALL PRIVILEGES ON codebeamer.* TO 'cbroot'@'localhost';
FLUSH PRIVILEGES;

MySQL 8.0.x

CREATE DATABASE codebeamer default character set 'utf8mb4' default collate 'utf8mb4_general_ci';
CREATE USER 'cbroot'@'localhost' IDENTIFIED BY 'CbPassword01!';
GRANT ALL PRIVILEGES ON codebeamer.* TO 'cbroot'@'localhost';
SET GLOBAL log_bin_trust_function_creators = 1;
FLUSH PRIVILEGES;

Configure codebeamer to use the MySQL database

Edit/create the configuration file: <cb-installation-dir>/config/configuration.properties

database.driver=com.mysql.jdbc.Driver
database.connectionURL=jdbc:mysql://localhost:3306/codebeamer?autoReconnect=true&zeroDateTimeBehavior=convertToNull&emulateLocators=true&characterEncoding=UTF-8&useSSL=false
database.username=user
database.password=pass
database.passwordStorageType=ENCRYPTED

Start codebeamer

After switching to a new database, the initial codebeamer server start takes longer than usual, as codebeamer first initializes the new, empty database schema.

Fast Links