Migration / Issue on 4.2.8/4.4.2 related to null settings

[ianwallen]

Similar to issue #7459

Getting the following after upgrading to 4.2.8

Exception in extension function
  org.springframework.dao.InvalidDataAccessApiUsageException: The given id must not be
  null!; nested exception is java.lang.IllegalArgumentException: The given id must not be null!

The migration script is buggy. The following has the wrong upgrade script.

4.4 upgrade script

core-geonetwork/web/src/main/webapp/WEB-INF/classes/setup/sql/migrate/v442/migrate-default.sql

Line 4 in a65a995

INSERT INTO Settings (name, value, datatype, position, internal) VALUES ('region/getmap/useGeodesicExtents', 'false', 2, 9591, 'n');

4.2 upgrade script

core-geonetwork/web/src/main/webapp/WEB-INF/classes/setup/sql/migrate/v428/migrate-default.sql

Line 4 in bc6e885

INSERT INTO Settings (name, value, datatype, position, internal) VALUES ('region/getmap/useGeodesicExtents', 'false', 2, 9591, 'n');

It should specify the editable and encrypted value.

INSERT INTO Settings (name, value, datatype, position, internal, editable, encrypted) VALUES ('region/getmap/useGeodesicExtents', 'false', 2, 9591, 'n', 'y', 'n');

To fix the issue you must manually do the following.

Also the editable and encrypted fields should be updated to be not - null to prevent this in the future.

SELECT * FROM settings WHERE editable IS NULL;

Returns region/getmap/useGeodesicExtents

UPDATE Settings SET editable = 'y' WHERE editable IS NULL;

[ianwallen]

@fxprunayre, @josegar74,
How did you fix this issue in the 4.2.6 release?
Currently anyone trying to upgrade to 4.2.8 and 4.4.2(have not tested) will fail due to this issue.
Is it expected that they will find this issue an apply the workaround?

[fxprunayre]

So it means that we can have some databases without the proper default set for the editable column (and maybe same for internal and encrypted?)

core-geonetwork/domain/src/main/java/org/fao/geonet/domain/Setting.java

Line 214 in a65a995

@Column(name = "editable", nullable = false, length = 1, columnDefinition = "char default 'y'")

Something like the following would work (at least for postgres) if hibernate does not automatically set the default:

ALTER TABLE settings
    ALTER COLUMN editable SET DEFAULT 'y';