CDA doesn't run against a readonly database anymore

[DanielTOsborne]

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

[msweier]

Yeah the COOP swagger docs aren't available anymore. It used to work.

[MikeNeilson]

Ugh, I really dislike our permission model. That error is down in the CWMS_AAA code handling the session intercept.

It might be as simple as intercepting that error and letting it go; that connection should already be in the right state and since it read-only the state can't change anyways.