OpenVAS DB migration error - rev146->184: no such table: main.nvts_172

[olafweiler]

Error migrating a tasks.db from revision 146 to revision 184

First reported at: https://community.greenbone.net/t/openvas-db-migration-error-rev146-184-no-such-table-main-nvts-172/970

Expected behavior

openvasmd --migrate is running a successful migration

Current behavior

openvasmd --migrate fails to execute a migration step

Steps to reproduce

Happened at our site, might not be reproducible in general:

1. Have an OpenVAS Manager 6.0.9 installation, DB revision 146 (currently Debian Stretch)
2. Have an OpenVAS Manager 7.0.3 installation, DB revision 184 (currently Debian Unstable)
3. Copy tasks.db from 6.0.9 host to 7.0.3 host
4. Run openvasmd --migrate

GVM versions

gsa: (gsad --version)
Greenbone Security Assistant 7.0.3

openvasmd: (openvasmd --version)
OpenVAS Manager 7.0.3
Manager DB revision 184

openvas-scanner: (openvassd --version)
OpenVAS Scanner 5.1.3

gvm-libs:
libopenvas9 (9.0.3-1)

Environment

Operating system:
$ uname -a
Linux xxx 4.15.18-8-pve #1 SMP PVE 4.15.18-28 (Tue, 30 Oct 2018 14:27:50 +0100) x86_64 GNU/Linux

Logfiles

md main: INFO: Migrating to 181
md manage:WARNING: sql_exec_internal: sqlite3_step failed: error in view results_autofp: no such table: main.nvts_172
md manage:WARNING: sqlv: sql_exec_internal failed

[olafweiler]

Built latest stable version from source (e.g. latest OpenVAS Manager 7.0.3, etc.).
--> Migration works fine there.

Not sure, what's wrong with the Debian binary distribution.
--> Issue can be closed.

[volkov-am]

The problem raised with sqlite >= 3.25.0

Starting with sqlite-3.25.0 ALTER TABLE ... RENAME TO ... also rename dependent views

From sqlite Changelog:

2018-09-15 (3.25.0)
2.b. Fix table rename feature so that it also updates references to the renamed table in triggers and views.
https://sqlite.org/changes.html#version_3_25_0

Openvas migrate code:

migrate_172_to_173()
"ALTER TABLE nvts RENAME TO nvts_172;"
...
"DROP TABLE nvts_172;"

It works well with older sqlite versions but it breaks results_autofp view with recent sqlite

Workaround:
recreate results_autofp view with correct table name reference:

(echo "DROP VIEW results_autofp;"; sqlite3 /var/lib/openvas/mgr/tasks.db '.schema results_autofp' | sed 's/nvts_172/nvts/g') | sqlite3 /var/lib/openvas/mgr/tasks.db

[metersales]

The problem raised with sqlite >= 3.25.0

Starting with sqlite-3.25.0 ALTER TABLE ... RENAME TO ... also rename dependent views

From sqlite Changelog:

2018-09-15 (3.25.0)
2.b. Fix table rename feature so that it also updates references to the renamed table in triggers and views.
https://sqlite.org/changes.html#version_3_25_0

Openvas migrate code:

migrate_172_to_173()
"ALTER TABLE nvts RENAME TO nvts_172;"
...
"DROP TABLE nvts_172;"

It works well with older sqlite versions but it breaks results_autofp view with recent sqlite

Workaround:
recreate results_autofp view with correct table name reference:

(echo "DROP VIEW results_autofp;"; sqlite3 /var/lib/openvas/mgr/tasks.db '.schema results_autofp' | sed 's/nvts_172/nvts/g') | sqlite3 /var/lib/openvas/mgr/tasks.db

Perhaps I'm just a total newb, but how do I use the workaround? Is this to be used in bash or after logging in to sqlite3 or somewhere else? I'm having this problem in Ubuntu 20.04. I just upgraded from 18.04. I tried removing the package and reinstalling, but that didn't help. This seems to be the only answer available to me. I would appreciate any help you might offer.