External database configuration

4 minute read

During the CloudBees CD server installation, if you elected not to install the built-in (default) database, you need to configure an alternate database. a CloudBees CD enterprise license is required to configure an alternate database. For more information about supported databases, see Database requirements. For installation instructions, see Configuring CloudBees CD to Use an Alternate Database.

Database interactions

Your database administrator (DBA) must create a database for use specifically by CloudBees CD. The CloudBees CD server interacts with the database using a JDBC driver for each of the databases that CloudBees CD supports.

The first step in any interaction is to present user credentials to the database. This information is stored in the CloudBees CD database.properties file as a user name plus a password. The password is stored as an encrypted string, using the passkey generated by the server.

Database user

For MS SQL Server and MySQL, the DBA creates a database user for use specifically by CloudBees CD. For Oracle, the DBA must create a database user for use specifically by CloudBees CD.

The CloudBees CD database user must have permissions to add or delete rows from the database at all times. The database user must also have rights to create or delete tables, and add or remove a columns, indices, and constraints to a table at certain defined times.

When the CloudBees CD server first starts up, CloudBees CD creates a schema in the specified database, so the database user should be the owner of the CloudBees CD database. This allows CloudBees CD to make the required schema changes.

If the CloudBees CD server cannot connect to the database, it continues to wait for a valid database configuration. Check the log files for a successful database connection.

Default database ports

The supported external databases use the following ports:

Port Used by

1521

Oracle

1433

Microsoft SQL Server

3306

MySQL

MySQL prerequisites

For non-production environments, only, if you intend to run MySQL on the same server as your CloudBees CD server, then install with the advanced mode option that avoids installing the MariaDB built-in database. In production environments, you must install and run MySQL on a separate server.

Database user permissions

CloudBees supports installations only where the database user has rights to create and delete tables at all times. To upgrade the MySQL databases, the database user must also have permissions to create and delete schema (databases) for the duration of the upgrade. For security reasons, permissions granted to database users to create and delete schema in multi-tenant MySQL databases may be revoked after the upgrade process is complete.

Installing the JDBC driver

For MySQL, the JDBC driver is not installed by the CloudBees CD installer (for licensing reasons). It must be downloaded and installed separately from the MySQL website. For more information, go to Installing the MySQL JDBC Driver .

Setting the default MySQL server timezone

By default, a MySQL instance uses the timezone from the operating system, which might cause the following bootstrap failure if the instance is using the MySQL JDBC Connector/J driver version 8.0 or later:

{noformat} 2018-07-27T03:47:35.070 | ERROR | bootstrap | HikariPool | {CD} - Exception during pool initialization. java.sql.SQLException: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) …

If this failure occurs, ensure that the database timezone is set to a supported timezone and that it matches the CloudBees CD server timezone. Apply the fix to the MySQL instance (not to the CloudBees CD server) as described in https://bugs.mysql.com/bug.php?id=86425. For more information about this issue and for a list of supported timezones, see https://bugs.mysql.com/bug.php?id=85816 and https://stackoverflow.com/a/47108511.

If it is not possible to set the MySQL database timezone then set it on the CloudBees CD server: configure the JDBC URL in <DATA_DIR>/conf/database.properties to include the serverTimezone parameter set to the database timezone. For example, serverTimezone=America/Los_Angeles.

In CloudBees CD v10.0 and later, the CloudBees CD server sets this in the generated JDBC URL in <DATA_DIR>/conf/database.properties at install time.

Configuring database settings

You must configure UTF-8 character encoding and case-insensitive collation with utf8 and utf8_general_ci respectively. Of note:

  • The utf8mb4 character set is not supported because it requires four bytes per character, which puts this width beyond the key limits.

  • The database must use the InnoDB MySQL DB storage engine with the innodb_large_prefix option enabled because of maximum 3072 bytes key limits.

    • MySQL versions previous to 5.7 does not enable innodb_large_prefix by default. It must be explicitly enabled.

    • MySQL 5.7 and later versions enables innodb_large_prefix by default.

    • MySQL 8.0 and later versions eliminates the innodb_large_prefix setting and defaults the behavior to true.

  • MySQL 8.0 and later versions default the character set and collation to utf8mb4 and utf8mb4_0900_ai_ci respectively. You must explicitly set these to utf8 and utf8_general_ci.

Tuning mmemory allocation

To tune memory allocation, see the KBEC-00038 - Improving CloudBees CD server performance by tuning memory allocation knowledge base article.

Oracle RAC

You can configure CloudBees CD to work with Oracle Real Application Clusters (RAC), which provides software for clustering and high availability in Oracle database environments. For instructions, see the KBEC-00064 - Using Oracle RAC Server with CloudBees CD knowledge base article.