Enabling GoCD to use MySQL

Note: While, support for MySQL is added in GoCD 20.5.0 and a basic round of migration tests has been completed, the functional test suite does not regularly run against MySQL as a part of the build pipeline. This is something to be aware of if moving to MySQL. H2 and PostgreSQL are tested thoroughly as a part of GoCD’s build pipelines.

Step 1: Install MySQL Server

In order to use MySQL database with GoCD, an external MySQL database server is needed to host the GoCD Server’s database. Refer MySQL Installation documentation to install the latest MySQL Database Server based on your environment. GoCD supports MySQL version 8.0.

Note: GoCD needs support for case-insensitive identifiers and on Unix systems at least this needs to be done before MySQL is installed! These pages from the MySQL documentation might be useful:

Step 2: Create an empty database

Once the MySQL Server is started, an empty database can be created from the command-line using the mysql or mysqladmin utilities, which MySQL ships with. Refer create database documentation to setup database.

  1. CREATE DATABASE gocd;
  2. CREATE USER 'gocd_user'@'localhost' IDENTIFIED BY 'password';
  3. GRANT ALL ON gocd.* TO 'gocd_user'@'localhost';
  4. GRANT SUPER ON *.* TO 'gocd_user'@'localhost';

Note: You need to add the SUPER privilege for the first time, since there is a trigger created. MySQL doesn’t allow that trigger to be created without the SUPER privilege and will fail with error 1419 if it is not provided. This privilege can be revoked after the first startup.

Step 3: Configure GoCD with MySQL connection details

A properties file with the name db.properties needs to be created in the GoCD’s configuration directory (config/). The location of GoCD’s configuration directory varies per operating system. Refer GoCD server installation docs to know the location of GoCD Server config directory.

This file should contain information about the database server, so that the GoCD Server can connect to it.

See GoCD Database Configuration Properties to know the full list of configuration properties that can be specified under db.properties.

An example properties file to connect to MySQL database:

  1. db.driver=com.mysql.cj.jdbc.Driver
  2. db.url=jdbc:mysql://localhost:3306/gocd
  3. db.user=gocd_user
  4. db.password=password

Step 4: Start the GoCD Server

See Managing the GoCD server process to start your GoCD Server.

Troubleshooting

MySQL: Identifier case senitivity

You might see a message such as this in the GoCD server logs, if you are using MySQL:

  1. Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.commons.dbcp2.BasicDataSource]: Factory method 'getDataSource' threw exception; nested exception is java.sql.SQLException: Unable to migrate the database
  2.         at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)
  3.         at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588)
  4.         ... 73 common frames omitted
  5. Caused by: java.sql.SQLException: Unable to migrate the database
  6.         at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:68)
  7.         at com.thoughtworks.go.server.database.Database.getDataSource(Database.java:63)
  8.         at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  9.         at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
  10.         at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  11.         at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  12.         at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162)
  13.         ... 74 common frames omitted
  14. Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db-migration-scripts/initial/create-trigger.xml::107::gocd(generated):
  15.      Reason: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
  16.                 AFTER INSERT ON buildStateTransitions
  17.                 FOR EACH ROW
  18.                 BEGIN
  19.                     UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
  20.                 END]
  21.         at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
  22.         at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
  23.         at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
  24.         at liquibase.Liquibase.update(Liquibase.java:202)
  25.         at liquibase.Liquibase.update(Liquibase.java:179)
  26.         at liquibase.Liquibase.update(Liquibase.java:175)
  27.         at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:54)
  28.         ... 80 common frames omitted
  29. Caused by: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
  30.                 AFTER INSERT ON buildStateTransitions
  31.                 FOR EACH ROW
  32.                 BEGIN
  33.                     UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
  34.                 END]
  35.         at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
  36.         at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
  37.         at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
  38.         at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276)
  39.         at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258)
  40.         at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
  41.         ... 86 common frames omitted
  42. Caused by: java.sql.SQLSyntaxErrorException: Table 'gocd.buildStateTransitions' doesn't exist
  43.         at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
  44.         at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  45.         at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  46.         at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
  47.         at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
  48.         at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
  49.         at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
  50.         at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
  51.         ... 91 common frames omitted

If you see this, the most probable cause is that your MySQL instance has case-sensitive identifiers turned on. GoCD needs case-insensitive identifiers and you will need to change your MySQL instance to enable that. Please note that, according to the documentation, it is not possible to change the lower_case_table_names variable once the MySQL instance is initialized. You might need to recreate the instance.