Upgrading from 2.30 to 2.31/2.31.7

Support - Assistance technique
1

Dear All,
I am trying to upgrade from Upgrading from 2.30 to 2.31/2.31.7 but i am receiving errors:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘flyway’ defined in URL [jar:file:/var/lib/dhis2/hmis/webapps/hmis/WEB-INF/lib/dhis-support-db-migration-2.31.7.jar!/META-INF/dhis/beans.xml]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V2_31_17__Clean_attribute_values.sql failed

SQL State : 23503
Error Code : 0
Message : ERROR: update or delete on table “attributevalue” violates foreign key constraint “fk601dda02b8e4fd1” on table “organisationunitattributevalues”
Detail: Key (attributevalueid)=(26261313) is still referenced from table “organisationunitattributevalues”.
Location : org/hisp/dhis/db/migration/2.31/V2_31_17__Clean_attribute_values.sql (/home/gerald/file:/var/lib/dhis2/hmis/webapps/hmis/WEB-INF/lib/dhis-support-db-migration-2.31.7.jar!/org/hisp/dhis/db/migration/2.31/V2_31_17__Clean_attribute_values.sql)
Line : 2
Statement : WITH nonRepeatedIds AS (select min(av2.attributevalueid) as id from attributevalue av2 LEFT JOIN dataelementattributevalues deav2 ON av2.attributevalueid = deav2.attributevalueid
group by deav2.dataelementid, av2.value, av2.attributeid),
uniqueAttributeIds AS (select av.attributevalueid as id from attributevalue av JOIN dataelementattributevalues deav ON av.attributevalueid = deav.attributevalueid WHERE (dataelementid, attributeid, value) IN
(select deav2.dataelementid, av2.attributeid, max(av2.value) as value from attributevalue av2 LEFT JOIN dataelementattributevalues deav2 ON av2.attributevalueid = deav2.attributevalueid
group by deav2.dataelementid, av2.attributeid)),
orphanAttributeValues AS (select av.attributevalueid as id from attributevalue av LEFT JOIN dataelementattributevalues deav ON av.attributevalueid = deav.attributevalueid WHERE deav.dataelementid IS NULL),
relationsDeleted AS (delete from dataelementattributevalues deav where deav.attributevalueid NOT IN (SELECT id FROM nonRepeatedIds UNION SELECT id FROM uniqueAttributeIds) returning attributevalueid),
categoryOptionComboDeleted AS (delete from categoryoptioncomboattributevalues where attributevalueid in (select id from orphanAttributeValues)),
indicatorDeleted AS (delete from indicatorattributevalues where attributevalueid in (select id from orphanAttributeValues)),
attributeValuesDeleted AS (delete from attributevalue where attributevalueid in (select id from orphanAttributeValues))
delete from attributevalue av
where av.attributevalueid NOT IN (SELECT id FROM nonRepeatedIds UNION SELECT id FROM uniqueAttributeIds)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1631)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1136)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1064)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:583)
... 56 more

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V2_31_17__Clean_attribute_values.sql failed

SQL State : 23503
Error Code : 0
Message : ERROR: update or delete on table “attributevalue” violates foreign key constraint “fk601dda02b8e4fd1” on table “organisationunitattributevalues”
Detail: Key (attributevalueid)=(26261313) is still referenced from table “organisationunitattributevalues”.
Location : org/hisp/dhis/db/migration/2.31/V2_31_17__Clean_attribute_values.sql (/home/gerald/file:/var/lib/dhis2/hmis/webapps/hmis/WEB-INF/lib/dhis-support-db-migration-2.31.7.jar!/org/hisp/dhis/db/migration/2.31/V2_31_17__Clean_attribute_values.sql)
Line : 2
Statement : WITH nonRepeatedIds AS (select min(av2.attributevalueid) as id from attributevalue av2 LEFT JOIN dataelementattributevalues deav2 ON av2.attributevalueid = deav2.attributevalueid
group by deav2.dataelementid, av2.value, av2.attributeid),
uniqueAttributeIds AS (select av.attributevalueid as id from attributevalue av JOIN dataelementattributevalues deav ON av.attributevalueid = deav.attributevalueid WHERE (dataelementid, attributeid, value) IN
(select deav2.dataelementid, av2.attributeid, max(av2.value) as value from attributevalue av2 LEFT JOIN dataelementattributevalues deav2 ON av2.attributevalueid = deav2.attributevalueid
group by deav2.dataelementid, av2.attributeid)),
orphanAttributeValues AS (select av.attributevalueid as id from attributevalue av LEFT JOIN dataelementattributevalues deav ON av.attributevalueid = deav.attributevalueid WHERE deav.dataelementid IS NULL),
relationsDeleted AS (delete from dataelementattributevalues deav where deav.attributevalueid NOT IN (SELECT id FROM nonRepeatedIds UNION SELECT id FROM uniqueAttributeIds) returning attributevalueid),
categoryOptionComboDeleted AS (delete from categoryoptioncomboattributevalues where attributevalueid in (select id from orphanAttributeValues)),
indicatorDeleted AS (delete from indicatorattributevalues where attributevalueid in (select id from orphanAttributeValues)),
attributeValuesDeleted AS (delete from attributevalue where attributevalueid in (select id from orphanAttributeValues))
delete from attributevalue av
where av.attributevalueid NOT IN (SELECT id FROM nonRepeatedIds UNION SELECT id FROM uniqueAttributeIds)

at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:369)
at org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:54)
at org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:282)
at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:279)
at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244)
at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:157)
at org.flywaydb.core.internal.command.DbMigrate.access$000(DbMigrate.java:54)
at org.flywaydb.core.internal.command.DbMigrate$1.call(DbMigrate.java:133)
at org.flywaydb.core.internal.command.DbMigrate$1.call(DbMigrate.java:130)
at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:71)
at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:94)
at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:139)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:130)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:189)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:149)
at org.flywaydb.core.Flyway.execute(Flyway.java:511)
at org.flywaydb.core.Flyway.migrate(Flyway.java:149)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1760)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1697)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1627)
... 68 moremi
1 Like
2

So i finally got the upgrade but i have to do the following:

delete from organisationunitattributevalues;
delete from datasetattributevalues;
drop sql views

Now i want to import them again but i am having issue when using psql import:

COPY datasetattributevalues(datasetid,attributevalueid) from ‘datasetattributevalues.csv’ DELIMITER ‘,’ CSV HEADER;

This is not working for me…
Please can someone help me?

1 Like
3

@Gerald_Thomas, I got the same Flyway error with 2.31.7! I was able to successfully upgrade to 2.31.6 instead (https://releases.dhis2.org/2.31/2.31.6/dhis.war).

1 Like
4

Hi @rs3524, @Gerald_Thomas, and others,

Unfortunately, the flyway error you are mentioning is a bug. You can follow the progress here. The good news is that we are planning to release an emergency patch (2.31.8) next week, disregarding our normal patch schedule. That patch should fix the issue and should allow you to upgrade :slight_smile:

/Gintare

2 Likes