Intermitant 500 error reading timeseries when threading returns ORA-20047 / "Database Error" or "System Error"

[msweier]

Describe the bug
When using the GET /timeseries with cwms-python (which spawns 20 threads by default), I get intermittent 500 errors on dev server (running 2026.05.11-develop ) returning "Database Error" or "System Error". When looking at the CDA logs, I see ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application, raised from CWMS_UTIL.GET_DB_OFFICE_ID. The store will succeed on retry, so it has something to do with the frequent requests.

Expected behavior
I would think the office would always be assigned to the request.

To Reproduce
Steps to reproduce the behavior:

1. Use cwms-python to read period of record for Baldhill_Dam.Flow-Out.Inst.~15Minutes.0.best (e.g. cwms.get_timeseries(ts_id = 'Baldhill_Dam.Flow-Out.Inst.~15Minutes.0.best', office_id=MVP, begin= datetime.datetime(1950,1,1))) on dev server or issue concurrent requests to any office-scoped endpoint (e.g. 20 parallel GET /timeseries?...&office=<OFFICE> requests, chunked by time window).
2. Observe that a fraction of responses come back as HTTP 500 with body "Database Error" or "System Error", while the rest succeed.

Logs/Incident Identifier

The Error Chain

Server log shows the following sequence:

1. ORA-01422: exact fetch returns more than requested number of rows

   • Occurs inside CWMS_UTIL.GET_DB_OFFICE_ID when trying to resolve the office code
   • This is detection, not the root cause — without session office context, the office lookup query returns multiple rows instead of exactly one

2. ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application

   • The PL/SQL catches ORA-01422, recognizes it means session context is broken, and re-raises with a clear error message
   • Stack trace shows: CWMS_UTIL.GET_DB_OFFICE_ID → CWMS_TS.* or other package calls

3. Client sees: HTTP 500 with body "Database Error" (for main query failures) or "System Error" (for async count query failures)

   • The ORA code is wrapped by jOOQ DataAccessException and caught by ApiServlet's generic handler, which hard-codes the response body
   • Clients cannot distinguish this from other database errors

Example Log Sequence

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 599
ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 605

CDA Version (please complete the following information):
2026.05.11-develop

Additional context
I didn't see these errors in test or prod so I'm wondering if the new timeseries query may expose this a bit more?

AI suggests the reason is the request-time DSL builder never installs SessionOfficePreparer on pooled connections, but I'm wondering why we haven't seen this before. It's been that way for a long time.

[rma-rripken]

Can you include some of the logs that you gave to the AI for its analysis? I feel like I don't have enough info to decide if I agree with the AI or not.

The python code ends up making application/json;version=2 requests - right?

Looking at the pl/sql I don't see GET_DB_OFFICE_ID throwing SESSION_OFFICE_ID_NOT_SET.
But user_office_id will. The TimeSeries Dao code can call into user_office_id if the office parameter is null.

But you are setting the office parameter and it looks like the TimeSeriesController would have thrown an error if office wasn't set (at least for v2 requests).

I'm not sure exactly why the timeseries dao needs to call GET_DB_OFFICE_ID or user_office_id but its been that way forever.

For public GET requests CDA doesn't set the session to whatever office parameter was specified. I don't think it would work.

Do you know if the server has this property set? cwms.dataapi.default.office
If that is set then SessionOfficePreparer is used with the provided office for guest requests. We could twiddle the logging config on the server and verify that SessionOfficePreparer is calling SET_SESSION_OFFICE_ID

[MikeNeilson]

Do you know if the server has this property set? cwms.dataapi.default.office

it is not, but I looked at where's it's used and it'll default to the application context (<office>-data) where CWMS becomes HQ.

it is possible this is just a bug in some internal session handling.

@msweier we need the whole stacktrace from oracle.

[msweier]

Sure, I've attached the log.

I did a little more digging.

You're right, error is only occurs with authenticated requests. I confirmed this with a POR query on dev.

When CdaAccessManager.prepareContext() runs, it branches on whether a principal exists:

• Guest (no API key): calls AuthDao.prepareGuestContext() which creates a SessionOfficePreparer(defaultOffice) and adds it to the connection preparer chain. Session office gets set on every connection checkout.

• Authenticated (API key): calls AuthDao.prepareContextWithUser() which only adds a DirectUserPreparer(username). No SessionOfficePreparer is added. Session office is never set on checkout.

But what bothered me is the code hasn't changed forever, so why did I see it now? The change in the timeseries query from PR #1692.

Before #1692: timeseries data was fetched via the PL/SQL stored procedure cwms_ts.retrieve_ts_out_tab(...). This procedure receives the office as a parameter and manages session context internally. Session office on the JDBC connection was irrelevant.

After #1692: the new direct-read path (getRequestedTimeSeriesDirect) queries AV_TSV_DQU directly with CWMS_UTIL.CONVERT_UNITS(...) as an inline SQL function call. CONVERT_UNITS internally calls GET_DB_OFFICE_ID, which checks SYS_CONTEXT('CWMS_ENV','SESSION_OFFICE_ID'). When session office isn't set, you get ORA-01422 (multiple rows) → ORA-20047.

You can see this in the stack trace — the error comes from:

TimeSeriesController.lambda$getAll$0(TimeSeriesController.java:476)
CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)

That's the async path running the new direct-read query, not the old retrieve_ts_out_tab.

I've got a draft AI assisted PR here: #1757 for the suggested fix and get the ideas going.

issue-1753-logs.txt

[krowvin]

I wonder if these 500s are related to what I mentioned on the DB Call today.

National is still seeing intermittent 500s

But those are not authenticated requests. Possibly just different 500s altogether.

[Enovotny]

I think this issue discussed #1831