USACE / USACE/cwms-data-api

CDA doesn't run against a readonly database anymore

Open
#991 4 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

needs-approval
Dominant language
Java
Stars
28
Forks
25
Avg merge
4d 22h
Merged PRs (30d)
20

Description

Older versions of CDA worked even against an Oracle database set to readonly (COOP standby).
Now it doesn't work, and just fills up catalina log with tons of stacktraces (8 GB/day with current schedule).

If I'm not writing to the database, I would expect it to work even against a readonly database, since the request doesn't need to login.
I have this configured to always perform report generation, even on a standby COOP server, so the reports are continuous even in the event of a failover situation (also ensures COOP process is working and data matches with primary).

20-Dec-2024 00:00:00.007 WARNING [https-openssl-nio-8243-exec-7] cwms.cda.ApiServlet.lambda$init$17 error on request[6108007947412956016]: /spk-data/timeseries
        org.jooq.exception.DataAccessException: Unable to set user session.  user empty = false
                at cwms.cda.datasource.DirectUserPreparer.prepare(DirectUserPreparer.java:24)
                at cwms.cda.datasource.DelegatingConnectionPreparer.prepare(DelegatingConnectionPreparer.java:42)
                at cwms.cda.datasource.ConnectionPreparingDataSource.getConnection(ConnectionPreparingDataSource.java:25)
                at cwms.cda.datasource.ConnectionPreparingDataSource.getConnection(ConnectionPreparingDataSource.java:22)
                at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:87)
                at org.jooq.impl.DefaultDSLContext.connectionResult(DefaultDSLContext.java:652)
                at cwms.cda.data.dao.JooqDao.connectionResult(JooqDao.java:577)
                at cwms.cda.data.dao.TimeSeriesDaoImpl.isVersioned(TimeSeriesDaoImpl.java:449)
                at cwms.cda.data.dao.TimeSeriesDaoImpl.getVersionType(TimeSeriesDaoImpl.java:429)
                at cwms.cda.data.dao.TimeSeriesDaoImpl.getTimeseries(TimeSeriesDaoImpl.java:353)
                at cwms.cda.api.TimeSeriesController.getAll(TimeSeriesController.java:465)
                at io.javalin.apibuilder.CrudFunction$1.invoke$lambda-0(CrudHandler.kt:30)
                at io.javalin.apibuilder.CrudFunctionHandler.handle(CrudHandler.kt)
                at cwms.cda.security.GuestAccessManager.manage(GuestAccessManager.java:28)
                at cwms.cda.security.MultipleAccessManager.manage(MultipleAccessManager.java:41)
                at io.javalin.http.JavalinServlet.addHandler$lambda-0(JavalinServlet.kt:96)
                at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
                at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
                at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:99)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
                at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
                at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
                at io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)
                at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
                at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:628)
                at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1996)
                at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
                at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
                at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
                at io.javalin.http.JavalinServlet.service(JavalinServlet.kt:89)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
                at cwms.cda.ApiServlet.service(ApiServlet.java:819)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:199)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:331)
                at org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:158)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:597)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:241)
                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
                at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:388)
                at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
                at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:936)
                at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
                at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
                at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
                at java.lang.Thread.run(Thread.java:750)
        Caused by: java.sql.SQLException: ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "CWMS_20.CWMS_ENV", line 166
ORA-06512: at "CWMS_20.CWMS_MSG", line 528
ORA-06512: at "CWMS_20.CWMS_MSG", line 315
ORA-06512: at "CWMS_20.CWMS_MSG", line 732
ORA-06512: at "CWMS_20.CWMS_ENV", line 57
ORA-06512: at "CWMS_20.CWMS_ENV", line 150
ORA-06512: at line 1

                at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
                at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
                at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
                at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
                at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
                at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
                at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
                at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
                at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
                at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
                at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
                at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
                at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
                at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
                at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:498)
                at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
                at com.sun.proxy.$Proxy55.execute(Unknown Source)
                at cwms.cda.datasource.DirectUserPreparer.prepare(DirectUserPreparer.java:21)
                ... 67 more
        Caused by: Error : 16000, Position : 0, Sql = begin cwms_env.set_session_user_direct(upper(:1 )); end;, OriginalSql = begin cwms_env.set_session_user_direct(upper(?)); end;, Error Msg = ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "CWMS_20.CWMS_ENV", line 166
ORA-06512: at "CWMS_20.CWMS_MSG", line 528
ORA-06512: at "CWMS_20.CWMS_MSG", line 315
ORA-06512: at "CWMS_20.CWMS_MSG", line 732
ORA-06512: at "CWMS_20.CWMS_ENV", line 57
ORA-06512: at "CWMS_20.CWMS_ENV", line 150
ORA-06512: at line 1

                at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
                ... 86 more

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with cwms.cda.datasource.DirectUserPreparer and ConnectionPreparingDataSource, then follow the request path through TimeSeriesDaoImpl.isVersioned. Reproduce the /spk-data/timeseries request against a readonly Oracle database and inspect the session-user setup. Done means report generation works without ORA-16000 errors, with coverage for the readonly case if the existing tests support it.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
api, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.