Fixed - OpenBalena API Migration Issues (v0.209.2 --> v14.3.6)

teslov · August 29, 2023, 7:46am

Description:

Hello OpenBalena Community,

We are currently seeking assistance with a migration issue that has arisen after updating.
OpenBalena to a new tag (v0.209.2 → v14.3.6) . Blame me, it was my fault, during investigating what is wrong with balena after updating to new CLI version . Unfortunately there was a major changes that block updates and device provisioning for new balena devices

The migration process (migration 082) consistently fails during the update, causing subsequent problems, such as difficulties with using the latest OpenBalena API tag and login issues with balenaCLI SDK.

Issue Summary:

After updating to a new tag, the migration process with migration number 082 fails repeatedly.
With log below:

Loading application config
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00076"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00077"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00078"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00079"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00080"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00081"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Running migration "00082"
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Error while executing migrations, rolled back
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Failed to executed synchronous migrations from api root model MigrationError: operator does not exist: boolean <> integer
Aug 28 17:45:49 f1f9f65d0501 api[1598]: Error loading application config MigrationError: Failed to execute 'balena' model from 'undefined' due to: operator does not exist: boolean <> integer
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at /usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:97:10
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at processTicksAndRejections (node:internal/process/task_queues:95:5)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async lockMigrations (/usr/src/app/node_modules/@balena/pinejs/src/migrator/utils.ts:261:11)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async $run (/usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:74:2)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:586:5
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Promise.all (index 0)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async executeModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:582:26)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async /usr/src/app/node_modules/@balena/pinejs/src/config-loader/config-loader.ts:199:7
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Promise.all (index 0)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async /usr/src/app/node_modules/@balena/pinejs/src/config-loader/config-loader.ts:248:4
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Object.transaction (/usr/src/app/node_modules/@balena/pinejs/src/database-layer/db.ts:446:20)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async loadConfig (/usr/src/app/node_modules/@balena/pinejs/src/config-loader/config-loader.ts:115:3)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Object.loadApplicationConfig (/usr/src/app/node_modules/@balena/pinejs/src/config-loader/config-loader.ts:425:4)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Promise.all (index 0)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async Object.init (/usr/src/app/node_modules/@balena/pinejs/src/server-glue/module.ts:80:3)
Aug 28 17:45:49 f1f9f65d0501 api[1598]:     at async setup (/usr/src/app/src/index.ts:372:2)
Aug 28 17:45:49 f1f9f65d0501 api[1589]: Program node index.js exited with code 1

If i try to downgrade open-balena-api version to something like v11.x.x it doesn’t help either.
Log after downgrading below:

Aug 29 08:13:01 c42f70312b7e api[1492]: Failed to execute standard models. MigrationError: Unexpected token . in JSON at position 4
Aug 29 08:13:01 c42f70312b7e api[1492]:     at /usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:95:10
Aug 29 08:13:01 c42f70312b7e api[1492]:     at processTicksAndRejections (node:internal/process/task_queues:95:5)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async lockMigrations (/usr/src/app/node_modules/@balena/pinejs/src/migrator/utils.ts:263:11)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async $run (/usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:72:2)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:587:5
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Promise.all (index 0)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async executeModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:583:26)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async executeStandardModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1862:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1890:4
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.transaction (/usr/src/app/node_modules/@balena/pinejs/src/database-layer/db.ts:446:20)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.setup (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1889:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.init (/usr/src/app/node_modules/@balena/pinejs/src/server-glue/module.ts:61:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async setup (/usr/src/app/src/index.ts:372:2)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async init (/usr/src/app/init.ts:240:27)
Aug 29 08:13:01 c42f70312b7e api[1492]: Could not execute standard models MigrationError: Unexpected token . in JSON at position 4
Aug 29 08:13:01 c42f70312b7e api[1492]:     at /usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:95:10
Aug 29 08:13:01 c42f70312b7e api[1492]:     at processTicksAndRejections (node:internal/process/task_queues:95:5)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async lockMigrations (/usr/src/app/node_modules/@balena/pinejs/src/migrator/utils.ts:263:11)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async $run (/usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:72:2)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:587:5
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Promise.all (index 0)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async executeModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:583:26)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async executeStandardModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1862:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1890:4
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.transaction (/usr/src/app/node_modules/@balena/pinejs/src/database-layer/db.ts:446:20)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.setup (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:1889:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async Object.init (/usr/src/app/node_modules/@balena/pinejs/src/server-glue/module.ts:61:3)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async setup (/usr/src/app/src/index.ts:372:2)
Aug 29 08:13:01 c42f70312b7e api[1492]:     at async init (/usr/src/app/init.ts:240:27)
Aug 29 08:13:01 c42f70312b7e api[1483]: Program node index.js exited with code 1

Does anybody faced with this issue before?

teslov · August 29, 2023, 12:36pm

Resolved some issues what was during constraint in migration.
Main pain was here https://github.com/balena-io/open-balena-api/blob/master/src/migrations/00082-pinejs-15.sql#L46.

Constraint in my case have different key, so was not dropped, it causes fail during migration.

Another pain is errupt - is loopback redirects during login and executing any requests.
But seems that ping is working

Here is example of response for ping:

And for the /login is TOO many redirects on browser, same as in cli maximum redirect reached at: https://<mydomain>/actor/v1/whoami

HTTP/1.1 301 Moved Permanently
Location: https://<mydomain>/login
Vary: Accept
Content-Type: text/html; charset=utf-8
Content-Length: 114
Date: Tue, 29 Aug 2023 12:38:54 GMT
Keep-Alive: timeout=5

teslov · August 29, 2023, 1:21pm

Redirects was fixed due problem in services.yml compose file.

Need to set TRUST_PROXT env variable in environment block of api, due to Add `TRUST_PROXY` environment variable by bartversluijs · Pull Request #1328 · balena-io/open-balena-api · GitHub.

After patch it looks like

  api:
    extends:
      file: ./common.yml
      service: component
    image: balena/open-balena-api:${OPENBALENA_API_VERSION_TAG}
    depends_on:
      - db
      - s3
      - redis
    environment:
      ...//other fields
      TRUST_PROXY: "true"

chris980 · September 13, 2023, 7:44am

Hi. How did you fix the Postgress migration error?
I keep getting

Error loading application config MigrationError: Failed to execute 'balena' model from 'undefined' due to: operator does not exist: boolean <> integer

even with v14.5.5

teslov · September 13, 2023, 12:09pm

Try to see table release and drop constraint with this similar ID `DROP CONSTRAINT IF EXISTS “release$this_will_be_another_one_in_your_case”,

MaxFerretti78 · May 23, 2024, 7:36am

I’m facing this issue upgrading from 3.4.1 to 3.8.5
It’s struck on Migration 82 but connecting to database on table release I couldn’t find any constraint like the one mentioned.

The error I’m having is:

 Running migration "00082"
May 23 06:54:53 762d33ea6fe5 api[518840]: Error while executing migrations, rolled back
May 23 06:54:53 762d33ea6fe5 api[518840]: Failed to executed synchronous migrations from api root model MigrationError: operator does not exist: boolean <> integer
May 23 06:54:53 762d33ea6fe5 api[518840]: Error loading application config MigrationError: Failed to execute 'v6' model from 'undefined' due to: Failed to execute 'balena' model from 'undefined' due to: operator does not exist: boolean <> integer
May 23 06:54:53 762d33ea6fe5 api[518840]:     at /usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:100:10
May 23 06:54:53 762d33ea6fe5 api[518840]:     at processTicksAndRejections (node:internal/process/task_queues:95:5)
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async lockMigrations (/usr/src/app/node_modules/@balena/pinejs/src/migrator/utils.ts:267:11)
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async $run (/usr/src/app/node_modules/@balena/pinejs/src/migrator/sync.ts:77:2)
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async /usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:601:38
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async Promise.all (index 0)
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async executeModels (/usr/src/app/node_modules/@balena/pinejs/src/sbvr-api/sbvr-utils.ts:597:26)
May 23 06:54:53 762d33ea6fe5 api[518840]:     at async /usr/src/app/node_modules/@balena/pinejs/src/config-loader/config-loader.ts:200:7
May 23 06:54:53 762d33ea6fe5 api[1521]: Worker 3802 died, replacing it

While connecting to databse with psql here is the description of the table.


resin=# \d+ release
 created at             | timestamp without time zone |           | not null | CURRENT_TIMESTAMP                   | plain    |             |              | 
 modified at            | timestamp without time zone |           | not null | CURRENT_TIMESTAMP                   | plain    |             |              | 
 id                     | integer                     |           | not null | nextval('release_id_seq'::regclass) | plain    |             |              | 
 belongs to-application | integer                     |           | not null |                                     | plain    |             |              | 
 commit                 | character varying(255)      |           | not null |                                     | extended |             |              | 
 composition            | text                        |           | not null |                                     | extended |             |              | 
 status                 | character varying(255)      |           | not null |                                     | extended |             |              | 
 source                 | character varying(255)      |           | not null |                                     | extended |             |              | 
 build log              | text                        |           |          |                                     | extended |             |              | 
 is invalidated         | integer                     |           | not null | 0                                   | plain    |             |              | 
 start timestamp        | timestamp without time zone |           | not null |                                     | plain    |             |              | 
 end timestamp          | timestamp without time zone |           |          |                                     | plain    |             |              | 
 update timestamp       | timestamp without time zone |           | not null |                                     | plain    |             |              | 
 release version        | character varying(255)      |           |          |                                     | extended |             |              | 
 contract               | text                        |           |          |                                     | extended |             |              | 
 is passing tests       | integer                     |           | not null | 1                                   | plain    |             |              | 
 release type           | character varying(255)      |           | not null | 'final'::character varying          | extended |             |              | 

resin=#

But I couldn’t find the problem

teslov · May 23, 2024, 7:08pm

Forgot to mention - add this ID of migration (in your case 82) in list already done migration by hand (e.g. - skip it, like it was done).
As i remember it can be executed by adding the number of to the array of done migrations. I may be wrong, but also (maybe, not 100% sure), i executed some parts of this migrations by hand.

Sorry, but didn’t fully remember what exactly table (it must be related to the migration itself) and field it was.