MySQL Table Name Change Error When Rolling Back Spinnaker (Undo Renamed Values)


Issue

Rolling back Spinnaker versions causes pods to continually have errors in the logs, including the following first example for Front50 (example is rolling back from 2.19.x to 2.18.x

2020-06-22 21:52:27.001  INFO 1 --- [           main] .s.f.m.p.DefaultPluginArtifactRepository : Warming Cache
2020-06-22 21:52:27.723 ERROR 1 --- [           main] .s.f.m.p.DefaultPluginArtifactRepository : Unable to warm cache: {}

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select max(last_modified_at) as `last_modified_at` from plugin_artifacts]; nested exception is java.sql.SQLSyntaxErrorException: Table 'front50_kinnon.plugin_artifacts' doesn't exist
	at org.jooq_3.12.3.MYSQL.debug(Unknown Source) ~[na:na]
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.1.14.RELEASE.jar:5.1.14.RELEASE]

Cause

When upgrading, the values change, and when reverting, the values do not change back to the old values, thus errors will occur accessing the database.

Example from 2.18.x to 2.19.x, plugin_artifacts table does get renamed to plugin_info and does not revert back when downgrading. 

Solution

Resolving the issue will require to roll back changes to the MySQL Database.

This guide is meant to help users through an example, and can be applied to any service using MySQL (e.g. CloudDriver, Orca).  

For this guide, the example used will be where upon upgrading from 2.18.x to 2.19.x, plugin_artifacts table does get renamed to plugin_info and the table does not revert back when rolling back to 2.18.x. 
 

Installation of Liquibase

Spinnaker uses Liquibase to make changes and migrations to the MySQL database.  Liquibase itself can follow changelogs in order to perform a rollback. 

Please find instructions on installing Liquibase here: https://docs.liquibase.com/concepts/installation/home.html

Brew can also be used to install Liquibase:

brew install liquibase

Download a Copy of the MySQL Connector Driver

The MySQL Connector is needed by Liquibase.  Save the location of the filepath to be used later when configuring the Liquibase Properties.

Download the .jar file version that matches the MySQL Database. Below are some examples

For Amazon RDS, the MySQL Connector link is located in the following page.  You will need to extract the ZIP File and refer to the JAR file later when setting up your liquibase properties
https://docs.aws.amazon.com/elasticbeanstalk/latest/dg/java-rds.html

The Maven MySQL Connector can be downloaded from the below link
https://repo1.maven.org/maven2/mysql/mysql-connector-java/

Pulling a Copy of the Spinnaker Source

To get a record of the changelog for Liquibase to use, the source file for the service will need to be attained.  In this example the Front50 changelog is located here
https://github.com/spinnaker/front50/tree/master/front50-sql/src/main/resources/db

Before downloading, users will need to set up their SSH Key for GitHub by following this guide.
https://help.github.com/en/github/authenticating-to-github/adding-a-new-ssh-key-to-your-github-account

Pull a copy of the source. 

git clone git@github.com:spinnaker/front50.git


Run commands from the resources directory.  This is VERY IMPORTANT! Don't forget this step!

cd front50/front50-sql/src/main/resources


Later on, when Liquibase is used to roll back a migration, the properties will point to the changelog in this directory. The changelog file will be in the changelog subdirectory.  Look for the particular changelog related to the issue, in this example:

20200113-rename-plugin-artifacts-to-plugin-info.yml

Record the location of this file for later when configuring the Liquibase properties

Configure Liquibase

From within the directory where the sourcecode was pulled, (e.g.  front50-sql/src/main/resources) create a liquibase.properties file

The file will contain the following information (here is an example):

changeLogFile: /front50/front50-sql/src/main/resources/db/changelog/20200113-rename-plugin-artifacts-to-plugin-info.yml 
url: jdbc:mysql://localhost:3306/front50 
classpath: /path/to/mysql-connector-java-8.0.20.jar
username: front50_migrate 
password: password
  • changeLogFile: provide the location to the .yml file downloaded at the Pulling a Copy of the Spinnaker Source section
  • url: attain from your Halyard Config or Operator SpinnakerService YAML file. (front50.sql.migration.jdbcUrl)
  • classpath: provide the location to the .jar file downloaded at the Download a Copy of the MySQL Connector Driver section
  • username: attain the migration username from your Halyard Config or Operator SpinnakerService YAML file. (front50.sql.migration.user)
  • password: attain the migration password from your Halyard Config or Operator SpinnakerService YAML file (front50.sql.migration.password)


Reverting Changes

To see a history of changes, run the following from command line.

liquibase history

To roll back, run the following

liquibase rollbackCount 1


You may need to roll back several times, depending on the amount of changes applied.

After completing the roll back, the pod (in this case Front50) should no longer have any issues executing changes.  


As Tested On Version

2.18.x to 2.19.x Migration