ETL fails when concept recreated with same name

[mahalakshme]

Need:

Frequently to change datatype of a concept, the old concept is renamed or deleted. And then new concept with the right datatype is created with the same name. And when ETL runs again after the creation of the new concept, it fails with error org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table {schema_name}.{table_name} add column {column_name} {new_data_type}

Issues:

• Was able to reproduce the issue in prerelease, uninhibiteduat, concept name: Who is responsible for cleaning the toilets?
• Try doing select * from scheduled_job_run where error_message like '%add_column%' order by id desc; - in prod everytime I do there are atleast consistent failures in 2 to 4 orgs.
• In IPH SCD UAT on March 13, concept name 'HydroxyUrea is contra indicated in pregnancy' of Text type was voided first and concept with same name with coded type created after that. ETL failed 'stating the column already exists' and below are its logs.
• Similarly in 'At what age formula food given' was changed from Coded to Numeric again leading to ETL failure on 19th Feb
• 'Post Office Account no' in sakhiapp changed from Numeric to Text - same case - has been failing until February

Acceptance criteria:

• ETL should not fail and should handle the scenario.
• Whether the old concept was deleted or just disassociated from the form, the change in data type should reflect in the ETL table without failing.

Questions:

Aren't we first doing the necessary deletes/renames before creating?

Inputs:

rename before creating
DBT-integration test code
complex trans

ETL failure logs:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table iphscd_uat.participant_sickle_cell_disease_affected_follow_up_scd add column "HydroxyUrea is contra indicated in pregnancy" text;] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431) at org.avniproject.etl.repository.SchemaMetadataRepository.lambda$applyChanges$24(SchemaMetadataRepository.java:298) at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:298) at jdk.internal.reflect.GeneratedMethodAccessor63.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.repository.SchemaMetadataRepository$$SpringCGLIB$$0.applyChanges(<generated>) at org.avniproject.etl.service.SchemaMigrationService.migrate(SchemaMigrationService.java:39) at jdk.internal.reflect.GeneratedMethodAccessor60.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.service.SchemaMigrationService$$SpringCGLIB$$0.migrate(<generated>) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:52) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35) at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: org.postgresql.util.PSQLException: ERROR: column "HydroxyUrea is contra indicated in pregnancy" of relation "participant_sickle_cell_disease_affected_follow_up_scd" already exists at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292) at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ... 36 more

[petmongrels]

also this commit - avniproject/avni-server@dba4225

[dinesh2096]

After deleting the concept the ETL gets failed

Db reference :

ETL table reference :

[petmongrels]

the issue is not reproducible. Currently ETL status is passed. I tried deleting one more field. The ETL passed again after that also.
Maybe the issue is with how the data is shown when the job is running the status is shown incorrectly.

[dinesh2096]

Env: Pre-release
Org : testdashboard
user : dinesh@testdashboard

Steps to reproduce :

1.Created the Concept as named as Test ETL
2.And delete the Test ETL concept in the webapp
3.ETL sync got completed without any issue
4.But ETL Table still shows the concept without table updated to voided.

Refer the DB image :

Web app image :

[petmongrels]

ETL not deployed on prerelease.

[dinesh2096]

• Tested the org without changes
• Tested by adding the concept named as Test ETL(text) and validate the ETL table.
• Tested by deleting the same concept and validate the ETL Table
• Tested by adding the same concept again and validate the ETL Table
• Tested by deleting the same concept and added the with the same name but with diff concept eg: Test ETL(coded) and validate the ETL table