Enabling GoCD to use MySQL Database

    Your query search resulted in no results.

    Enabling GoCD to use MySQL

    Note: Support for MySQL was added in GoCD 20.5.0 and while a basic round of migration tests was 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.