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.

CREATE DATABASE gocd;
CREATE USER 'gocd_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON gocd.* TO 'gocd_user'@'localhost';
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:

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/gocd
db.user=gocd_user
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:

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
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588)
        ... 73 common frames omitted
Caused by: java.sql.SQLException: Unable to migrate the database
        at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:68)
        at com.thoughtworks.go.server.database.Database.getDataSource(Database.java:63)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.base/java.lang.reflect.Method.invoke(Unknown Source)
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162)
        ... 74 common frames omitted
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db-migration-scripts/initial/create-trigger.xml::107::gocd(generated):
     Reason: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                END]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
        at liquibase.Liquibase.update(Liquibase.java:202)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.Liquibase.update(Liquibase.java:175)
        at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:54)
        ... 80 common frames omitted
Caused by: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                END]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
        ... 86 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'gocd.buildStateTransitions' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
        ... 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.