open-balena-api v0.76.3 sql migrations

openBalena
1

Hi there. I’m trying to use latest API and I found DB issues when calling balena apps.
Parsing GET /resin/my_application?$orderby=app_name%20asc&$select=id,app_name,slug,device_type&$expand=owns__device($select=is_online)
Failed to translate url: {
“resource”: “my_application”,
“options”: {
“$select”: {
“properties”: [
{
“name”: “id”,
“property”: null,
“count”: null
},
{
“name”: “app_name”,
“property”: null,
“count”: null
},
{
“name”: “slug”,
“property”: null,
“count”: null
},
{
“name”: “device_type”,
“property”: null,
“count”: null
}
]
},
“$expand”: {
“properties”: [
{
“name”: “owns__device”,
“property”: null,
“count”: null,
“options”: {
“$select”: {
“properties”: [
{
“name”: “is_online”,
“property”: null,
“count”: null
}
]
}
}
}
]
},
“$orderby”: {
“properties”: [
{
“name”: “app_name”,
“property”: null,
“count”: null,
“order”: “asc”
}
]
}
}
} GET SyntaxError: Could not resolve relationship mapping from ‘application’ to ‘device type’
at OData2AbstractSQL.ResolveRelationship (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:509:19)
at OData2AbstractSQL.ReferencedField (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:564:46)
at OData2AbstractSQL.AliasSelectField (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:548:38)
at /usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:537:119
at Array.map (:null:null)
at OData2AbstractSQL.AddSelectFields (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:537:102)
at OData2AbstractSQL.PathSegment (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:345:18)
at OData2AbstractSQL.match (/usr/src/app/node_modules/@resin/odata-to-abstract-sql/out/odata-to-abstract-sql.js:168:32)
at /usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/uri-parser.js:87:51
at /usr/src/app/node_modules/es5-ext/function/#/partial.js:12:16
at /usr/src/app/node_modules/memoizee/lib/configure-map.js:61:24
at /usr/src/app/node_modules/memoizee/lib/weak.js:87:21
at /usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/uri-parser.js:118:56
at Object.exports.translateUri (/usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/uri-parser.js:223:34)
at /usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/sbvr-utils.js:691:59

Could you help with it or could you please point me latest stable tag I have to use?

2

Hi there, I think the error happens because you are referencing device_type, but the actual field’s name is is_for__device_type. Try that and let us know if it works.

4

Actually - I just bump api container version and run ./scripts/compose up -d

5

Hey there, I didn’t understand, did bumping the container resolve your issue?

7

No, I used default container from open-balena v2.0.2
OPENBALENA_API_VERSION_TAG=v0.19.5

Later I updated version to the v0.76.3 and found migrations problem

8

Can anyone tell me exact versions of API, DB, S3, Registry, supervisor and CLI
Which are tested and compatible?

9

The versions described here are tested and compatible: https://github.com/balena-io/open-balena/blob/master/compose/versions and the supervisor version v10.8.0 should work with these. The CLI version shouldn’t really make a difference, just anything recent. The more recent supervisor v11 makes some changes which require a newer API, which we’ve not got around to integrating into open-balena.

11

yep, but they don’t work with latest supervisor since v11.3.8 - please take a look into another topic I opened - Device can't register with API

Initially, everything I’ve done according to official instructions and issues I faced/found was posted here(in forum threads).

12

Could you specify the migrations problems that you’re referencing please? We have two options (with the other thread in mind). Downgrade the supervisor to v10.8.0 and use the specified versions from open-balena, or we upgrade your API. Upgrading the API is more future proof so let’s try that.

Basically, what was the exact reason you couldn’t upgrade?

14

After I changed API version in compose file and applied compose up -d, new API started and failed:
ay 29 01:13:13 b58d2dc157d6 api[999]: Running node-supervisor with
May 29 01:13:13 b58d2dc157d6 api[999]: program ‘index.js’
May 29 01:13:13 b58d2dc157d6 api[999]: --watch ‘src’
May 29 01:13:13 b58d2dc157d6 api[999]: --extensions ‘js,node,coffee,sbvr,json,sql,pegjs,ts’
May 29 01:13:13 b58d2dc157d6 api[999]: --exec ‘node’
May 29 01:13:13 b58d2dc157d6 api[999]: Starting child process with ‘node index.js’
May 29 01:13:13 b58d2dc157d6 api[999]: Watching directory ‘/usr/src/app/src’ for changes.
May 29 01:13:13 b58d2dc157d6 api[999]: Press rs for restarting the process.
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “11.0.0-modified-at”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “11.0.0-modified-at”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “11.0.1-modified-at”
May 29 01:13:16 b58d2dc157d6 api[999]: Successfully executed standard models.
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “11.0.0-modified-at”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “11.0.1-modified-at”
May 29 01:13:16 b58d2dc157d6 api[999]: Successfully executed migrations model.
May 29 01:13:16 b58d2dc157d6 api[999]: Loading application config
May 29 01:13:16 b58d2dc157d6 api[999]: Parsing GET /Auth/permission?$select=id&$filter=name eq ‘resin.device_type.get’
May 29 01:13:16 b58d2dc157d6 api[999]: Parsing GET /Auth/user?$select=id&$filter=username eq ‘guest’
May 29 01:13:16 b58d2dc157d6 api[999]: Running GET /Auth/permission?$select=id&$filter=name eq ‘resin.device_type.get’
May 29 01:13:16 b58d2dc157d6 api[999]: Running GET /Auth/user?$select=id&$filter=username eq ‘guest’
May 29 01:13:16 b58d2dc157d6 api[999]: SELECT “permission”.“id”
May 29 01:13:16 b58d2dc157d6 api[999]: FROM “permission”
May 29 01:13:16 b58d2dc157d6 api[999]: WHERE (“permission”.“name”) IS NOT NULL AND (“permission”.“name”) = ($1) [ ‘resin.device_type.get’ ]
May 29 01:13:16 b58d2dc157d6 api[999]: SELECT “user”.“id”
May 29 01:13:16 b58d2dc157d6 api[999]: FROM “user”
May 29 01:13:16 b58d2dc157d6 api[999]: WHERE (“user”.“username”) IS NOT NULL AND (“user”.“username”) = ($1) [ ‘guest’ ]
May 29 01:13:16 b58d2dc157d6 api[999]: Parsing POST /Auth/permission?returnResource=false
May 29 01:13:16 b58d2dc157d6 api[999]: Running POST /Auth/permission?returnResource=false
May 29 01:13:16 b58d2dc157d6 api[999]: INSERT INTO “permission” (“name”)
May 29 01:13:16 b58d2dc157d6 api[999]: VALUES ($1) [ ‘resin.device_type.get’ ]
May 29 01:13:16 b58d2dc157d6 api[999]: Insert ID: permission 73
May 29 01:13:16 b58d2dc157d6 api[999]: Parsing GET /Auth/user__has__permission?$select=id&$filter=(user eq 1) and (permission eq 73)
May 29 01:13:16 b58d2dc157d6 api[999]: Running GET /Auth/user__has__permission?$select=id&$filter=(user eq 1) and (permission eq 73)
May 29 01:13:16 b58d2dc157d6 api[999]: SELECT “user-has-permission”.“id”
May 29 01:13:16 b58d2dc157d6 api[999]: FROM “user-has-permission”
May 29 01:13:16 b58d2dc157d6 api[999]: WHERE (“user-has-permission”.“user”) IS NOT NULL AND (“user-has-permission”.“user”) = ($1)
May 29 01:13:16 b58d2dc157d6 api[999]: AND (“user-has-permission”.“permission”) IS NOT NULL AND (“user-has-permission”.“permission”) = ($2) [ 1, 73 ]
May 29 01:13:16 b58d2dc157d6 api[999]: Parsing POST /Auth/user__has__permission?returnResource=false
May 29 01:13:16 b58d2dc157d6 api[999]: Running POST /Auth/user__has__permission?returnResource=false
May 29 01:13:16 b58d2dc157d6 api[999]: INSERT INTO “user-has-permission” (“user”, “permission”)
May 29 01:13:16 b58d2dc157d6 api[999]: VALUES ($1, $2) [ 1, 73 ]
May 29 01:13:16 b58d2dc157d6 api[999]: Insert ID: user__has__permission 1
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00006”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00007”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00008”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00009”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00010”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00011”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00012”
May 29 01:13:16 b58d2dc157d6 api[999]: Running migration “00013”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00014”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00015”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00016”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00017”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00018”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00019”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00020”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00021”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00022”
May 29 01:13:17 b58d2dc157d6 api[999]: Running migration “00023”
May 29 01:13:17 b58d2dc157d6 api[999]: Error while executing migrations, rolled back
May 29 01:13:17 b58d2dc157d6 api[999]: /usr/src/app/node_modules/@resin/pinejs/out/database-layer/db.js:44
May 29 01:13:17 b58d2dc157d6 api[999]: throw new DatabaseError(err);
May 29 01:13:17 b58d2dc157d6 api[999]: ^
May 29 01:13:17 b58d2dc157d6 api[999]: DatabaseError: current transaction is aborted, commands ignored until end of transaction block
May 29 01:13:17 b58d2dc157d6 api[999]: at wrapDatabaseError (/usr/src/app/node_modules/@resin/pinejs/out/database-layer/db.js:44:15)
May 29 01:13:17 b58d2dc157d6 api[999]: From previous event:
May 29 01:13:17 b58d2dc157d6 api[999]: at PostgresTx.executeSql (/usr/src/app/node_modules/@resin/pinejs/out/database-layer/db.js:156:19)
May 29 01:13:17 b58d2dc157d6 api[999]: at /usr/src/app/node_modules/@resin/pinejs/out/migrator/migrator.js:122:10
May 29 01:13:17 b58d2dc157d6 api[999]: From previous event:
May 29 01:13:17 b58d2dc157d6 api[999]: at lockMigrations (/usr/src/app/node_modules/@resin/pinejs/out/migrator/migrator.js:120:6)
May 29 01:13:17 b58d2dc157d6 api[999]: at /usr/src/app/node_modules/@resin/pinejs/out/migrator/migrator.js:53:31
May 29 01:13:17 b58d2dc157d6 api[999]: From previous event:
May 29 01:13:17 b58d2dc157d6 api[999]: at Object.exports.run (/usr/src/app/node_modules/@resin/pinejs/out/migrator/migrator.js:47:51)
May 29 01:13:17 b58d2dc157d6 api[999]: at /usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/sbvr-utils.js:216:20
May 29 01:13:17 b58d2dc157d6 api[999]: From previous event:
May 29 01:13:17 b58d2dc157d6 api[999]: at Object.exports.executeModels (/usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/sbvr-utils.js:213:54)
May 29 01:13:17 b58d2dc157d6 api[999]: at Object.exports.executeModel (/usr/src/app/node_modules/@resin/pinejs/out/sbvr-api/sbvr-utils.js:212:47)
May 29 01:13:17 b58d2dc157d6 api[999]: at /usr/src/app/node_modules/@resin/pinejs/out/config-loader/config-loader.js:86:37
May 29 01:13:17 b58d2dc157d6 api[999]: at processImmediate (internal/timers.js:456:21)
May 29 01:13:17 b58d2dc157d6 api[999]: at process.topLevelDomainCallback (domain.js:137:15)
May 29 01:13:17 b58d2dc157d6 api[999]: From previous event:
May 29 01:13:17 b58d2dc157d6 api[999]: at /usr/src/app/node_modules/@resin/pinejs/out/config-loader/config-loader.js:82:24
May 29 01:13:17 b58d2dc157d6 api[999]: Program node index.js exited with code 1

15

DB container log:
b_1 | 2020-05-29 01:13:16.729 UTC [31] ERROR: relation “uniq_model_model_type_vocab” already exists
db_1 | 2020-05-29 01:13:16.729 UTC [31] STATEMENT: CREATE UNIQUE INDEX “uniq_model_model_type_vocab” ON “model” (“is of-vocabulary”, “model type”);
db_1 | 2020-05-29 01:13:17.024 UTC [31] ERROR: relation “user-has-public key” does not exist
db_1 | 2020-05-29 01:13:17.024 UTC [31] STATEMENT: ALTER TABLE “application type”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “config”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “service instance”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “application”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “application environment variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “application config variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “service”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “service label”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “service environment variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “application tag”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “release”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “release tag”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “image”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device environment variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device config variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “image install”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “service install”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device service environment variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device tag”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “gateway download”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “image-is part of-release”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “image label”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “image environment variable”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “device type”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 | ALTER TABLE “user-has-public key”
db_1 | ADD COLUMN IF NOT EXISTS “modified at” TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
db_1 |
db_1 | 2020-05-29 01:13:17.028 UTC [31] ERROR: current transaction is aborted, commands ignored until end of transaction block
db_1 | 2020-05-29 01:13:17.028 UTC [31] STATEMENT:
db_1 | DELETE FROM “migration lock”
db_1 | WHERE “model name” = $1

21

Hi, I’m trying to reproduce the issue and will get back to you asap.

22

Great!
I’m in GMT+8. So might be I’ll respond 2 days later, due to weekend.

Also you can try to deploy new/clean v0.76.3 without upgrade from previous version and call balena apps.
I guess you’ll see error from my 1st post here.

23

Hi @mad666, one more thing while karaxuna is reproducing this.

This file represents the latest version of openBalena stack that we put out: https://github.com/balena-io/open-balena/blob/master/compose/versions

OPENBALENA_API_VERSION_TAG=v0.19.5
Later I updated version to the v0.76.3 and found migrations problem.

Sounds like you are trying to run a version of the API we haven’t put out in openBalena. Is this right? What’s the need to go beyond the listed API version?

We always recommend to stick to the versions pinned in the open-balena release. Once one diverges from these versions listed here, we have no proof that it will work.

Do you have issues when you migrate to the version listed in that file I linked to?

25

Hi @gelbal, please take a look into this thread - Device can't register with API. the reason is - difference within API calls. supervisor in balena OS v.2.50+ incompatible with OPENBALENA_API_VERSION_TAG=v0.19.5, as in balena-intel as in intel-nuc image. And, might be I missed it, I didn’t find information about compatible image/yocto code versions for open balena.

26

Hi, can you try balenaOS version that is lower than 2.49.0 ? See: https://github.com/balena-io/open-balena#getting-started
We are actively working to fix this and make later versions of balenaOS to be compatible with open-balena.

28

I saw you updated release to 2.0.3 and mentioned compatible balena OS version. I’ll try it and let you know.

30

Hey,

Did you manage to connect your devices flashed with balenaOS below version 2.49.0 ? If not, we are eager to find out more about you case :slight_smile:

Cheers !

32

Sorry for delay with answer. I use to downgrade supervisor version only and it works.

34

Hi, @odyslam . Recently i get some trouble with balenaOS. The device can not register into OpenBalena. The balenaOS lastest version of raspberry3 is 2.51.1, but it’s not compatible with OpenBalena. Can you provide balenaOS that version is lower than 2.49.0 for raspberry3?