[21.4.0] Migration fails

[Virsacer]

Expected behavior

Migrate to current version.
Create missing tables if neccessary...

Should this table exist?
I cant find it in the 20.8.1 sourcecode...

Actual behavior

md   main:MESSAGE:2021-04-16 16h12.57 utc:103:    Greenbone Vulnerability Manager version 21.4.0 (DB revision 242)
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating database.
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 234
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 235
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 236
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 237
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 238
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 239
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 240
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 241
md   main:   INFO:2021-04-16 16h12.57 utc:103:    Migrating to 242
md manage:WARNING:2021-04-16 16h12.57 utc:103: sql_exec_internal: PQexec failed: ERROR:  relation "vt_severities" does not exist (7)
md manage:WARNING:2021-04-16 16h12.57 utc:103: sql_exec_internal: SQL: ALTER TABLE vt_severities ALTER COLUMN score SET DATA TYPE double precision;
md manage:WARNING:2021-04-16 16h12.57 utc:103: sqlv: sql_exec_internal failed

Steps to reproduce

gvmd --migrate

GVM versions

21.4.0

[Virsacer]

Even after manually creating the missing table and a migration to 242, there are still differences in comparison to a new created database...

Only in new database:

public | result_new_severities_dynamic            | view     | root
public | result_new_severities_static             | view     | root
public | vulns                                    | view     | root

Only in migrated database:

public | current_credentials                      | table    | root
public | current_credentials_id_seq               | sequence | root
public | results_autofp                           | view     | root

Can/Should they be deleted?

[tuxmaster5000]

I have the same problem, which SQL statement do you have use?

[tuxmaster5000]

I have used this statements:

CREATE TABLE IF NOT EXISTS vt_severities (id SERIAL PRIMARY KEY,vt_oid text NOT NULL,type text NOT NULL, origin text,date integer,score double precision,value text);
SELECT create_index ('vt_severities_by_vt_oid','vt_severities', 'vt_oid');
ALTER TABLE vt_severities OWNER TO gvmd;

[Virsacer]

Yes, I just used the same CREATE TABLE statement, then the migration was able to ALTER the table...

[fourstepper]

CREATE TABLE IF NOT EXISTS vt_severities (id SERIAL PRIMARY KEY,vt_oid text NOT NULL,type text NOT NULL, origin text,date integer,score double precision,value text);
SELECT create_index ('vt_severities_by_vt_oid','vt_severities', 'vt_oid');
ALTER TABLE vt_severities OWNER TO gvmd;

Thanks a lot for this - worked for me @tuxmaster5000

[bjoernricks]

Just to let you know, we are working on this and there will be a patch as soon as possible.

[timopollmeier]

#1498 should fix the migration from gvmd 20.08.

After running gvmd --migrate I would also recommend running gvmd --rebuild with an up-to-date NVTs feed to ensure the new severity data is added to the database.