bug: wrong user_version in sqlite database that blocks the run of a Waku node

[Ivansete-status]

Problem

A Waku node with Store protocol mounted and using SQLite as archive driver, fails to start due to a wrong user_version value in the SQLite database.

The node finds the SQLite file with user_version PRAGMA set to 0, and that makes it think that this database schema needs to be upgraded to the current schema version, which is 7. And, the node crashes because there is no upgrade script.

node: To see the current value of user_version, just enter the database with sqlite3 app and type PRAGMA user_version;.

Impact

The node doesn't start and shows the next message:

waku-waku-1-1  | ERR 2023-09-11 13:46:56.993+00:00 failed to execute migration statement      topics="sqlite" tid=1 file=db_sqlite.nim:465 statement="INSERT INTO Message_backup SELECT id, timestamp, contentTopic, pubsubTopic, payload, version FROM Message;" error="constraint failed"
waku-waku-1-1  | ERR 2023-09-11 13:46:56.993+00:00 4/7 Mounting protocols failed              topics="wakunode main" tid=1 file=wakunode2.nim:85 error="failed to setup archive driver: failed to execute migration scripts: failed to execute migration statement"

To reproduce

It happened when upgrading from 0.18.0 to 0.19.0, and the node should mount Store with SQLite.

Expected behavior

The node starts as usual and the user_version present within the PRAGMA database is 7.

[Ivansete-status]

I tested what user_version is being written to the database when starting a node from scratch.

0.14.0 -> user_version is set to 0
0.16.0 -> user_version is set to 0
0.17.0 -> user_version is set to 0
0.18.0 -> user_version is set to 0
0.19.0 -> user_version is set to 7

The version should have been != 0 in all cases, and the version 0.19.0 unveiled an issue with that.
I'll try to submit a change that doesn't try to apply the migration script if the read user_version is 0.

[Ivansete-status]

Once @apentori is back from OOO, he will share the current versions in the databases within the Status fleets.

[apentori]

The versions are the following :

• Prod :
  • node-01.do-ams3.status.prod >> 7
  • node-02.do-ams3.status.prod >> 7
  • node-01.gc-us-central1-a.status.prod >> 7
  • node-02.gc-us-central1-a.status.prod >> 7
  • node-01.ac-cn-hongkong-c.status.prod >> 0
  • node-02.ac-cn-hongkong-c.status.prod >> 0
• Test :
  • node-01.do-ams3.status.test >> 7
  • node-01.gc-us-central1-a.status.test >> 7
  • node-01.ac-cn-hongkong-c.status.test >> 7

[Ivansete-status]

Thanks so much for the info @apentori! Very interesting!

After further analysis, I can explain the issue more accurately:

• The SchemaVersion (user_version) was set to 7 in 0.13.0.
• We stopped applying the migration scripts from version 0.14.0 until 0.18.0. Within these versions, if the database already had user_version == 7, then all is ok and nothing is touched.
• We resumed applying migration scripts again, in version 0.19.0.
• The problem starts to occur if the nwaku Docker container starts to work in versions between 0.14.0 and 0.18.0. In those cases, the user_version is not properly set to 7 (0 as the default value), although the database is created reflecting the SchemeVersion 7. Then, if the user/operator updates to version 0.19.0, the migration logic is applied but there is a discrepancy: The user_version is 0 but the database scheme is actually 7, so the migration process decides to start but it fails and the node doesn't start properly.

Summarizing: the problem happens for docker containers that create their SQLite database from versions [0.14.0, 0.18.0].

cc: @jm-clius, @vpavlin

[Ivansete-status]

Weekly Update

• achieved: bug fix that prevented a Store nwaku to start if the SQLite db was created with versions [0.14.0 - 0.18.0]