DB migration fails on fresh install with sqlite

[cybe]

Pre issue-raising checklist

I have already (please mark the applicable with an x):

• Upgraded to the latest Pact Broker
• [?] Created an executable example that demonstrates the issue:
  • Dockerfile: This is difficult to provide, as this is run based on a custom Dockerfile. I included the relevant sections.

Software versions

• pact-broker gem version: 2.89.1
• pact-broker docker version: Custom build on top of SLES15 SP2, based on pact-broker docker 2.89.1.0
• OS: Linux SLES15 SP2
• pact broker client details: not relevant
• DB: SQLite 3.36.0
• Ruby: 2.7.4
• Gems

Expected behaviour

Start a fresh Pact-Broker installation without a database, using SQLite. The application should start successfully.

Actual behaviour

Migration 20210702_drop_unused_columns_from_deployed_versions.rb fails. See Stacktrace.

Note that our previous installed version 2.79.1 did run successfully. 20210702_drop_unused_columns_from_deployed_versions.rb was added in version 2.80.0.

Steps to reproduce

Starting pact broker with bundle exec puma.

Let me know if you need a complete reproducible example with docker-compose and whatnot. This requires some work.

Relevant log files

See gist.

[bethesque]

My colleagues and I have been facing the same issue for some time. Strangely, it works on about half of our machines, and not on the other half, and we still haven't been able to identify what the difference is. All the versions appear to be the same. I'm afraid the fix for now is to just comment out those migrations that cause problems until we work out what the issue is.

Just some notes for further investigation:

https://www.sqlite.org/lang_altertable.html

5. ALTER TABLE DROP COLUMN
   The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and rewrites its content to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint. Possible reasons why the DROP COLUMN command can fail include:

The column is a PRIMARY KEY or part of one.
The column has a UNIQUE constraint.
The column is indexed.
The column is named in the WHERE clause of a partial index.
The column is named in a table or column CHECK constraint not associated with the column being dropped.
The column is used in a foreign key constraint.
The column is used in the expression of a generated column.
The column appears in a trigger or view.

[bethesque]

Sorry, just seen you're trying to roll your own docker image. That's always very tricky for us to support, and I really don't recommend it, but I know there are often organisational constraints, so you've gotta do what you've gotta do. Secondly, just use postgres if you've got docker-compose. Sqlite is only ever for playing around with anyway - not a prod database.

[cybe]

Appreciate your input! Indeed, we roll our own docker image because of organizational constraints. It is also the case, that we are using Postgres in production. SQLite is only being used for smoke testing our built docker image. We could however switch to Postgres of course.

[bethesque]

I'd love to be able to recreate the issue in a docker image for debugging. Because I can never recreate it on my own machine I've not been able to investigate it. I've just tried with the latest pact-foundation image, and it's working fine (unfortunately!).

[bethesque]

I'm closing this issue for now until the issue can be recreated reliably, but please re-open if anyone can provide the code for that.

[cybe]

I was able to built a reproducer: https://gist.github.com/cybe/d882575509049fc218dd83b4cbbc39e7

$ docker build -t pact-broker .
$ docker run -ti --rm pact-broker

[bethesque]

Thanks! I'll have a look at it. Given that it's the same ruby code on both the work and non-working docker images, it does seem like it's something to do with an underlying part of the platform.

[bethesque]

I think something has changed between the minor versions of Sqlite.

# opensuse/leap:15.2
Information for package libsqlite3-0:
-------------------------------------
Repository     : Main Update Repository
Name           : libsqlite3-0
Version        : 3.36.0-lp152.4.3.1

# my mac
sqlite3 -version
    3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl

# pactfoundation/pact-broker
apk info sqlite
sqlite-3.34.1-r0 description:

I was able to recreate the issue, then manually run the statement on the generated database that was causing the error. It gave me a better error message than is shown in the migrations, luckily, and so I've been able to fix it. The fix was strange though - I had to fully qualify a column in an unrelated view that had become ambiguous because a column with the same name was added to one of the tables that made up the view. Dropping the column in the deployed_versions table seems to cause some re-calculation to happen, an issue I'd already run into in some migrations I wrote the other day. I don't know why SQLite waited for me to drop some columns before working it out was confused about the ambiguous column, instead of erroring when I added it in the first place 🤷🏽

I need to do some testing on some unrelated features early next week, but can put out the release after that.

[bethesque]

If you want a dirty hack to get you by until the next release, this does the trick - just don't run it on a production database.

RUN echo "Sequel.migration { up { drop_view(:all_verifications) }; down {}; }" >> /opt/pact-broker/vendor/bundle/ruby/2.7.0/gems/pact_broker-2.89.1/db/migrations/20210701_drop_all_verifications.rb

[cybe]

Thank you for your thorough investigation! I'll try the hack next week, but otherwise just wait for the next release.

[cybe]

I can confirm that your hack does indeed work.

[bethesque]

There is a new release out. Please update to 2.90.0.

[cybe]

Thanks, 2.90.0 is working as expected!