ETL fails when there are multiple Repeatable Question Groups within a single form

[himeshr]

Related ticket https://avni.freshdesk.com/a/tickets/4173.
ETL fails when there are multiple Repeatable Question Groups within a single form.

In Cini-uat org, we encounter below error while running ETL.

Error logs

2024-06-12 05:21:48,175 ERROR Avni ETL_Worker-1 [etl.service.EtlService] Error in job run
org.quartz.JobExecutionException: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table cini_uat.school_school_student_assessment_hindi_assessment_details rename column "Hindi assessment details Marks obtained in question 7" to "Mathematics assessment details Marks obtained in question 7";]] [See nested exception: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table cini_uat.school_school_student_assessment_hindi_assessment_details rename column "Hindi assessment details Marks obtained in question 7" to "Mathematics assessment details Marks obtained in question 7";]]]
        at org.quartz.core.JobRunShell.run(JobRunShell.java:218)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table cini_uat.school_school_student_assessment_hindi_assessment_details rename column "Hindi assessment details Marks obtained in question 7" to "Mathematics assessment details Marks obtained in question 7";]]
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        ... 1 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table cini_uat. rename column "Hindi assessment details Marks obtained in question 7" to "Mathematics assessment details Marks obtained in question 7";]
        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.GeneratedMethodAccessor66.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.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)

Analysis

The root-cause for this seems to be that, we are not segregating the TableMetaData correctly within a form, as we only groupBy "form_mapping_uuid", when we should rather groupBy both "form_mapping_uuid" and "repeatable_question_group_concept_uuid".

[himeshr]

ETL run after deploying fix for the issue reported here failed due to clash in Encounter Cancel names for EncounterTypes:

• Action Item Follow Up for Cluster Coordinator (CC)
• Action Item Follow Up for Field Coordinator (FC)

Please follow naming recommendations specified here to resolve that issue.

In local, after making changes to the EncounterType names, was able to successfully complete ETL.

[mahalakshme]

@AchalaBelokar select scheduled_job_run.success, scheduled_job_run.ended_at from scheduled_job_run join organisation on job_name = organisation.uuid where organisation.name = 'Collectives for Integrated Livelihood Initiatives (CInI)' order by scheduled_job_run.id desc;

In the above query using gvamp will not work, u need to use org name and see the results. And also columns also need to be checked. If columns created for all observations in RQG. So will mark it tech card.

[mahalakshme]

achala has validated ETL passes. someone among us need to validate structure and below cases:

• basic case
• adding question group
• removing a question group
• adding a form element to the existing RQG
• etc.,

[AchalaBelokar]

dinesh@dineshbootcamp individual registration

• testing with multiple QG present in the form
• added new QG in the existing for
• deleted on QG from form now only QG is present it is giving me error

[1t5j0y]

Issue is due to child elements of question group form element not being voided when the question group form element is voided. Tracked as a separate issue - avniproject/avni-webapp#1262

[AchalaBelokar]

• only tech testing is remaining .