Fails to run patch-add-invert-keyword.sql during deployment

[damyanmp]

📑 I have found these related issues/pull requests

n/a

🛡️ Security Policy

• I agree to have read this project Security Policy

Description

Currently running 1.22.1, trying to deploy the latest image but running into an issue with patching the DB

[DB] INFO: SQLite Version: 3.41.1 2024-10-01T22:28:04.677082857-04:00 2024-10-02T02:28:04Z [SERVER] INFO: Connected 2024-10-01T22:28:04.681933785-04:00 2024-10-02T02:28:04Z [DB] INFO: Your database version: 10 2024-10-01T22:28:04.682987928-04:00 2024-10-02T02:28:04Z [DB] INFO: Latest database version: 10 2024-10-01T22:28:04.683779502-04:00 2024-10-02T02:28:04Z [DB] INFO: Database patch not needed 2024-10-01T22:28:04.685781489-04:00 2024-10-02T02:28:04Z [DB] INFO: Database Patch 2.0 Process 2024-10-01T22:28:04.709987610-04:00 2024-10-02T02:28:04Z [DB] INFO: patch-add-invert-keyword.sql is not patched 2024-10-01T22:28:04.711347446-04:00 2024-10-02T02:28:04Z [DB] INFO: patch-add-invert-keyword.sql is patching 2024-10-01T22:28:04.719353490-04:00 2024-10-02T02:28:04Z [DB] INFO: Closing the database 2024-10-01T22:28:06.745397245-04:00 2024-10-02T02:28:06Z [DB] INFO: SQLite closed 2024-10-01T22:28:06.747357467-04:00 [Error: ALTER TABLE monitor 2024-10-01T22:28:06.747422165-04:00 ADD invert_keyword BOOLEAN default 0 not null - SQLITE_ERROR: duplicate column name: invert_keyword] { 2024-10-01T22:28:06.747434370-04:00 errno: 1, 2024-10-01T22:28:06.747441832-04:00 code: 'SQLITE_ERROR' 2024-10-01T22:28:06.747458226-04:00 } 2024-10-01T22:28:06.748052736-04:00 2024-10-02T02:28:06Z [DB] ERROR: Start Uptime-Kuma failed due to issue patching the database

👟 Reproduction steps

deploying any version newer than 1.22.1

👀 Expected behavior

Upgrading without recreating all monitors

😓 Actual Behavior

Failed state, cannot proceed with upgrades

🐻 Uptime-Kuma Version

1.22.1

💻 Operating System and Arch

Ubuntu 22.04

🌐 Browser

Crhome Version 129.0.6668.60 (Official Build) (x86_64)

🖥️ Deployment Environment

• Runtime: v1.24.12 +k3s1
• Database: 3.41.1
• Filesystem used to store the database on:
• number of monitors: 40

📝 Relevant log output

g startup jobs and maintenance tasks
2024-10-01T22:27:58.419340017-04:00 ==> Starting application with user 0 group 0
2024-10-01T22:27:58.563620148-04:00 Welcome to Uptime Kuma
2024-10-01T22:27:58.590541214-04:00 Your Node.js version: 18.20.3
2024-10-01T22:27:58.672256761-04:00 2024-10-02T02:27:58Z [SERVER] INFO: Welcome to Uptime Kuma
2024-10-01T22:27:58.674081935-04:00 2024-10-02T02:27:58Z [SERVER] INFO: Node Env: production
2024-10-01T22:27:58.674774542-04:00 2024-10-02T02:27:58Z [SERVER] INFO: Inside Container: true
2024-10-02T02:27:58Z [SERVER] INFO: Importing Node libraries
2024-10-01T22:27:58.676161462-04:00 2024-10-02T02:27:58Z [SERVER] INFO: Importing 3rd-party libraries
2024-10-01T22:28:03.426953614-04:00 2024-10-02T02:28:03Z [SERVER] INFO: Creating express and socket.io instance
2024-10-01T22:28:03.429731890-04:00 2024-10-02T02:28:03Z [SERVER] INFO: Server Type: HTTP
2024-10-01T22:28:03.433889315-04:00 2024-10-02T02:28:03Z [SERVER] INFO: Importing this project modules
2024-10-01T22:28:04.198797361-04:00 2024-10-02T02:28:04Z [NOTIFICATION] INFO: Prepare Notification Providers
2024-10-01T22:28:04.221343916-04:00 2024-10-02T02:28:04Z [SERVER] INFO: Version: 1.23.15
2024-10-01T22:28:04.431719639-04:00 2024-10-02T02:28:04Z [DB] INFO: Data Dir: ./data/
2024-10-01T22:28:04.434990678-04:00 2024-10-02T02:28:04Z [SERVER] INFO: Connecting to the Database
2024-10-01T22:28:04.666295004-04:00 2024-10-02T02:28:04Z [DB] INFO: SQLite config:
[ { journal_mode: 'wal' } ]
2024-10-01T22:28:04.671993830-04:00 [ { cache_size: -12000 } ]
2024-10-02T02:28:04Z [DB] INFO: SQLite Version: 3.41.1
2024-10-01T22:28:04.677082857-04:00 2024-10-02T02:28:04Z [SERVER] INFO: Connected
2024-10-01T22:28:04.681933785-04:00 2024-10-02T02:28:04Z [DB] INFO: Your database version: 10
2024-10-01T22:28:04.682987928-04:00 2024-10-02T02:28:04Z [DB] INFO: Latest database version: 10
2024-10-01T22:28:04.683779502-04:00 2024-10-02T02:28:04Z [DB] INFO: Database patch not needed
2024-10-01T22:28:04.685781489-04:00 2024-10-02T02:28:04Z [DB] INFO: Database Patch 2.0 Process
2024-10-01T22:28:04.709987610-04:00 2024-10-02T02:28:04Z [DB] INFO: patch-add-invert-keyword.sql is not patched
2024-10-01T22:28:04.711347446-04:00 2024-10-02T02:28:04Z [DB] INFO: patch-add-invert-keyword.sql is patching
2024-10-01T22:28:04.719353490-04:00 2024-10-02T02:28:04Z [DB] INFO: Closing the database
2024-10-01T22:28:06.745397245-04:00 2024-10-02T02:28:06Z [DB] INFO: SQLite closed
2024-10-01T22:28:06.747357467-04:00 [Error: ALTER TABLE monitor
2024-10-01T22:28:06.747422165-04:00     ADD invert_keyword BOOLEAN default 0 not null - SQLITE_ERROR: duplicate column name: invert_keyword] {
2024-10-01T22:28:06.747434370-04:00   errno: 1,
2024-10-01T22:28:06.747441832-04:00   code: 'SQLITE_ERROR'
2024-10-01T22:28:06.747458226-04:00 }
2024-10-01T22:28:06.748052736-04:00 2024-10-02T02:28:06Z [DB] ERROR: Start Uptime-Kuma failed due to issue patching the database
2024-10-01T22:28:06.748598093-04:00 2024-10-02T02:28:06Z [DB] ERROR: Please submit the bug report if you still encounter the problem after restart: https://github.com/louislam/uptime-kuma/issues

[damyanmp]

Recommendation;
Changing the db migration to ALTER TABLE monitor ADD COLUMN IF NOT EXISTS invert_keyword BOOLEAN DEFAULT 0 NOT NULL;

[louislam]

Not able to reproduce. And we never received such report from 1.22.1.

[damyanmp]

it's clearly a botched deployment in my instance, where I had to rollback to 1.22.1 (possibly higher, it's almost a year ago since this was deployed). Now I can't deploy any newer version to the migration failing.

if you want to recreate you will need to deploy higher version, then rollback to 1.22.1 then upgrade.

The easiest fix is to check for the column during the migration this way the migration can run successfully. the alternatives are modifying the database per container or redeploying a new instance and hoping the export/import migrates most of my settings.

[CommanderStorm]

rollback to 1.22.1

This might not be supported.
I think (working hypothesis) you did a partial migration beforehand where the migration was applied but not yet recorded in the DB.
Or do you mean revert to a backup?

Solving this might need to involve looking at the database schema in an editor and determine if the migration was applied. If it was, you can mark it as such.

[damyanmp]

Working hypothesis is that kubernetes deployment failed, the migration successfully ran and made a change to the database table. Due to the failed deployment I rolled back to 1.22.1. Given this is a new column an the older version has no issue with that and it's been working since.

The issue right now this migration that has already taken place is blocking new releases.