gocd: Upgrade from 18.12.0 to 19.1.0 fails with Column "STAGES.CANCELLEDBY" not found

Issue Type

Bug Report

Summary

When upgrading my go.cd server with large H2 database it fails with querying the database when or after Retriving Active Pipelines from Database...

### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Column "STAGES.CANCELLEDBY" not found; SQL statement:
SELECT
        pipelines.id as pipelineId, pipelines.name as pipelineName, buildCauseType, label, buildCauseMessage,
        pipelines.counter as pipelineCounter, pipelines.label as pipelineLabel, pipelines.naturalOrder as naturalOrder,
        pipelines.comment as pipelineComment, stages.name as stageName,stages.counter as stageCounter, stages.id as stageId, stages.approvedBy as approvedBy, stages.cancelledBy as cancelledBy,
        stages.approvalType as approvalType, stages.result as stageResult, stages.latestRun, stages.rerunOfCounter,
        builds.id as buildId, builds.name as buildName, builds.state as buildState, builds.result as buildResult,
        builds.scheduledDate as scheduledDate, stages.orderId as orderId
        FROM pipelines
        INNER JOIN stages ON stages.pipelineId = pipelines.id AND
         
        stages.latestRun = true
     
        INNER JOIN builds ON builds.stageId = stages.id AND builds.ignored != true
     
        INNER JOIN (
            SELECT stages.pipelineId AS id
            FROM stages
            WHERE stages.state = 'Building' AND latestRun = true
        UNION
            SELECT max(pipelines.id) AS id
                FROM pipelines
                GROUP BY pipelines.name
        ) pipelines_to_show ON pipelines.id = pipelines_to_show.id
        ORDER BY pipelines.id DESC, stages.orderId ASC [42122-168]
### The error may exist in com/thoughtworks/go/server/dao/maps/Pipeline.xml
### The error may involve Pipeline.allActivePipelines
### The error occurred while executing a query

Basic environment details

Go Version: 19.1.0-8469
JAVA Version: open jdk 8
OS: alpine:3.6

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 22 (9 by maintainers)

Most upvoted comments

I’d suggest doing this right now, to avoid any further issues with your db:

shutdown your gocd server
Sync the database file to your local computer
Download the following jar http://central.maven.org/maven2/com/h2database/h2/1.3.168/h2-1.3.168.jar
Run via java -cp h2-1.3.168.jar org.h2.tools.Shell. Provide the following when asked
- url: jdbc:h2:/path/to/cruise do not append the .db extension
- username: sa
- password: (blank)
At the sql prompt, execute select * from changelog order by CHANGE_NUMBER desc limit 10;.

I currently suspect that the changelog table might have a START_DT value but no COMPLETE_DT value corresponding to the entry for 1901001_add_cancelled_by_to_stages_table.sql. If that is the case, it indicates that the transaction to perform the migration started off, but did not finish.

If this is the case, you should run the sql snippet above ALTER TABLE stages ADD COLUMN cancelledBy varchar(255); and update the COMPLETE_DT to the current timestamp by executing:

update changelog set COMPLETE_DT=current_timestamp() where CHANGE_NUMBER=1901001`;

ketan on Jan 31, 2019