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