Not able enable or disable analytical db of organisation on staging

[AchalaBelokar]

Describe the bug
Not able enable or disable analytical db of organisation on staging.
I login with admin user
go to some org show and tre
y to edit the anaytics data sync.
It is not showing me edit option

To Reproduce
Steps to reproduce the behavior:

1. Login with admin
2. click on orgnisation
3. go to specific org
4. then try to edit or create new one
5. then toggle the button analytic data

Expected behavior
user should able the button
Test Data
Give details of example users, environment detail, username against which this issue is happening.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Developer Checklist(Developer fixing the bug should fill this checklist)

• Does the fix require extensive regression testing?
  • Yes
  • No
• Are you mentioning the required scenarios that could be affected?
  • Yes
  • No

[1t5j0y]

Stack trace for reference

Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: 2024-11-18T11:58:49.291Z ERROR 1258321 --- [nio-8022-exec-1] o.a.etl.security.AuthenticationFilter    : Exception Message:
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: jakarta.servlet.ServletException: Request processing failed: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select sjr.started_at, sjr.ended_at, sjr.error_message, sjr.success from qrtz_job_details qjd
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:     left outer join scheduled_job_run sjr on sjr.job_name = qjd.job_name
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:      where qjd.job_name = ? and qjd.job_group = ?order by 1 desc
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: limit 1]
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:         at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1019) ~[spring-webmvc-6.0.9.jar!/:6.0.9]
.
.
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select sjr.started_at, sjr.ended_at, sjr.error_message, sjr.success from qrtz_job_details qjd
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:     left outer join scheduled_job_run sjr on sjr.job_name = qjd.job_name
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:      where qjd.job_name = ? and qjd.job_group = ?order by 1 desc
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: limit 1]
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:         at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) ~[spring-jdbc-6.0.9.jar!/:6.0.9]
.
.
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: Caused by: org.postgresql.util.PSQLException: ERROR: trailing junk after parameter at or near "$2o"
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:   Position: 217
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.0.jar!/:42.6.0]
.
.
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: 2024-11-18T11:58:49.297Z ERROR 1258321 --- [nio-8022-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select sjr.started_at, sjr.ended_at, sjr.error_message, sjr.success from qrtz_job_details qjd
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:     left outer join scheduled_job_run sjr on sjr.job_name = qjd.job_name
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:      where qjd.job_name = ? and qjd.job_group = ?order by 1 desc
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: limit 1]] with root cause
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]: org.postgresql.util.PSQLException: ERROR: trailing junk after parameter at or near "$2o"
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:   Position: 217
Nov 18 11:58:49 ip-10-20-1-8 start.sh[1258321]:         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.0.jar!/:42.6.0]

Root cause: No space between param and 'order by' in the history query

Fixed via: avniproject/avni-etl@0aea86c

Need to understand why this is not breaking in prerelease/prod since the change that introduced this bug was added some time ago.

[1t5j0y]

Don't see why this discrepancy occurs between staging and prerelease/prod.

In fact, postgresql is forgiving of lack of space between param and 'order by' but this behaviour is consistent across staging and other environments.
i.e. this query works fine if manually run:
select sjr.started_at, sjr.ended_at, sjr.error_message, sjr.success from qrtz_job_details qjd left outer join scheduled_job_run sjr on sjr.job_name = qjd.job_name where qjd.job_name = 'ss' and qjd.job_group = '24747'order by 1 desc limit 1; so it is unclear why it started breaking in staging all of a sudden.

[himeshr]

Don't see why this discrepancy occurs between staging and prerelease/prod.

In fact, postgresql is forgiving of lack of space between param and 'order by' but this behaviour is consistent across staging and other environments. i.e. this query works fine if manually run: select sjr.started_at, sjr.ended_at, sjr.error_message, sjr.success from qrtz_job_details qjd left outer join scheduled_job_run sjr on sjr.job_name = qjd.job_name where qjd.job_name = 'ss' and qjd.job_group = '24747'order by 1 desc limit 1; so it is unclear why it started breaking in staging all of a sudden.

This started to break in prerelease as well..
Moving this to qa-ready, as its now fixed in 10.1.0 branch, 10.1.1 release.

[dinesh2096]

@1t5j0y ETL is not running for many org even after the triggering ETL
env : pre-release
Org : rwb24uat,cini,cini_uat

[1t5j0y]

@dinesh2096 Can see from the logs that rwb24uat ETL is completing. cini and cini_uat are failing for other issues unrelated to this card. ETL will not always immediately run as soon as it is disabled/enabled for an org.

For this card, testing that you can enable and disable ETL is sufficient.

[mahalakshme]

commit for this is not there in 10.1 ETL branch and it is not breaking in prod - so making it as 10.2.0 card itself for simplicity

[1t5j0y]

@mahalakshme we're not clear on why this happened in staging and subsequently in prerelease - refer comment history on this card. Better to release sooner than later.