[Bug]: GET /timeseries fails with ORA-01422/SESSION_OFFICE_ID_NOT_SET for multi-office API-key users — regression from #1700

[Enovotny]

Describe the bug

Ran across this when running tests in cwms-python with the latest CDA build. Ran it through Claude and this is what it told me. I applied a to fix to get the cwms-python tests to run. This is a pretty specific bug that likely doesn't occur very often, but thought it might need to be fixed. This ticket included proposed code changes and a test. You can assign to me if you want me to implement.

Summary

GET /cwms-data/timeseries intermittently returns HTTP 500 for requests authenticated with an API key bound to a multi-office user (a user with 2+ rows in at_sec_user_office). The response body is a generic Database Error with
source:"Unknown":

{"message":"Database Error","incidentIdentifier":"<uuid>","source":"Unknown","details":{}}

• The request is well-formed and includes an explicit office= query parameter.
• POST /cwms-data/timeseries with the same key + payload does not fail.
• Other endpoint GETs (/locations, /ratings, /levels, /catalog, …) do not fail with the same key.
• Single-office API-key users are unaffected.

Root cause

Two-part:

1. TimeSeriesDaoImpl skips setOffice where every other DAO calls it

TimeSeriesDaoImpl.getRequestedTimeSeriesLegacy and getRequestedTimeSeriesDirect never call setOffice(conn, officeId) before running their queries. Every other DAO that invokes CWMS PL/SQL functions wraps its work in a connection(dsl, conn -> { setOffice(conn, officeId); ... }) block:

• StreamLocationDao.retrieveStreamLocation (lines 138–143)
• MeasurementDao, LookupTypeDao, EntityDao, ForecastSpecDao, BasinDao
• The timeseries store() path itself (line 2298 → getDslContext(connection, input.getOfficeId()) at JooqDao:175 → setOffice)

Only the timeseries GET path is missing this call.

CWMS PL/SQL internals invoked by cwms_ts.retrieve_ts_out_tab call get_db_office_code(NULL) and rely on SYS_CONTEXT('CWMS_ENV', 'SESSION_OFFICE_ID') being set. When it isn't, CWMS_UTIL.user_office_id falls back to:

SELECT a.office_id INTO l_office_id
  FROM cwms_office a, at_sec_user_office b
 WHERE b.username = l_username AND a.office_code = b.db_office_code;

For any user with 2+ rows in at_sec_user_office this raises ORA-01422 (TOO_MANY_ROWS). The WHEN OTHERS handler at CWMS_UTIL line 604 rewrites it as SESSION_OFFICE_ID_NOT_SET, which CDA renders as a generic HTTP 500 "Database Error."
Single-office users silently succeed (the query returns exactly one row) — which is why CDA's own test suite doesn't catch this: every user in fixtures.TestAccounts.KeyUser is single-office.

2. #1700 (91db87d2) exposed the latent bug

#1700 wrapped the DAO call in CompletableFuture.supplyAsync(() -> dao.getTimeseries(...)) in TimeSeriesController.getAll (line ~515) to enable a 45s request timeout.

• Before: DAO ran on the Jetty request thread. That thread had already been through auth/pre-handler code that set SESSION_OFFICE_ID on its connection, and JOOQ often reused that connection for subsequent queries. Bug was latent.
• After: DAO runs on ForkJoinPool.commonPool(), which acquires a different pool connection — one that may have no SESSION_OFFICE_ID set. Latent bug becomes visible.

Full ORA stack from incident 77c2e6b0-f81f-4264-ae04-7ced85dc0bf4

org.jooq.exception.DataAccessException: SQL [select ... from table(cwms_20.cwms_ts.retrieve_ts_out_tab(...)) ...]
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
ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 599
ORA-06512: at "CWMS_20.CWMS_UTIL", line 633
ORA-06512: at "CWMS_20.CWMS_UTIL", line 661
ORA-06512: at "CWMS_20.CWMS_UTIL", line 688
ORA-06512: at "CWMS_20.CWMS_UTIL", line 2539
ORA-06512: at "CWMS_20.CWMS_UTIL", line 3256
ORA-06512: at "CWMS_20.CWMS_TS", line 3448
ORA-06512: at "CWMS_20.CWMS_TS", line 3934
ORA-06512: at "CWMS_20.CWMS_TS", line 3983
ORA-06512: at "CWMS_20.CWMS_TS", line 4027                                                                                                                                                                                                             
    at cwms.cda.data.dao.TimeSeriesDaoImpl.getRequestedTimeSeries(TimeSeriesDaoImpl.java:531)
    at cwms.cda.data.dao.TimeSeriesDaoImpl.getTimeseries(TimeSeriesDaoImpl.java:251)                                                                                                                                                                   
    at cwms.cda.api.TimeSeriesController.lambda$getAll$0(TimeSeriesController.java:471)                                                                                                                                                              
    at java.util.concurrent.CompletableFuture$AsyncSupply.run(...)
    at java.util.concurrent.ForkJoinTask.doExec(...)
    at java.util.concurrent.ForkJoinPool.runWorker(...)
    at java.util.concurrent.ForkJoinWorkerThread.run(...)

Expected behavior

don't get the error and office is set.

To Reproduce

Reproduction

1. Bind an API key to a user with 2+ rows in at_sec_user_office. Example:

   cwms_sec.add_cwms_user('q0hectest', NULL, 'LRL');
   cwms_sec.add_cwms_user('q0hectest', NULL, 'SPK');
   cwms_sec.add_cwms_user('q0hectest', NULL, 'MVP');
   insert into cwms_20.at_api_keys (userid, key_name, apikey, created, expires)
   values ('Q0HECTEST', 'testkey', 'ak1_...$argon2id$...', sysdate, sysdate + 365);

2. Confirm the user is multi-office: SELECT COUNT(*) FROM cwms_20.at_sec_user_office WHERE username = 'Q0HECTEST'; returns ≥ 2.
3. Issue GET /cwms-data/timeseries?office=MVP&name=<tsid>&begin=...&end=... with that key against a fresh CDA process.
4. Observe HTTP 500 with {"message":"Database Error"} on the first GET.
5. docker logs <cda-container> | grep <incidentIdentifier> shows the ORA-01422 → ORA-20047 chain above.

Notes on intermittency:

Once SET_SESSION_OFFICE_ID(office) runs on a JDBC connection, SYS_CONTEXT('CWMS_ENV','SESSION_OFFICE_ID') stays set on that Oracle session for the connection's lifetime — the pool doesn't reset it. So the failure rate depends on:

• Whether the ForkJoin worker's acquired connection was previously used by any setOffice-calling code path (POSTs to /timeseries, other endpoint DAOs).
• Pool size and rotation (HikariCP default maximumPoolSize=10).

The bug is deterministic on cold-start or after connection recycling; it looks intermittent under sustained traffic.

Priority

Medium

Logs/Incident Identifier

No response

CURL Commands

CDA Version

Any CDA build containing #1700 (commit 91db87d2, "Feature/1288 ts timer", Apr 27 2026) or later. Earlier builds are unaffected.

Additional context

Proposed fix

Wrap getRequestedTimeSeriesLegacy and getRequestedTimeSeriesDirect in connectionResult(dsl, conn -> { ... }) and route the query through a DSLContext scoped to that connection, matching the pattern used by TimeSeriesDaoImpl.store() (line
2298) and StreamLocationDao.retrieveStreamLocation (line 138):

// cwms-data-api/src/main/java/cwms/cda/data/dao/TimeSeriesDaoImpl.java

protected TimeSeries getRequestedTimeSeriesLegacy(
        String page, int pageSize,
        @NotNull TimeSeriesRequestParameters requestParameters,
        @Nullable FilteredTimeSeriesParameters fp) {
    return connectionResult(dsl, conn -> {
        // Guarantee SESSION_OFFICE_ID is set on this connection before any
        // PL/SQL runs. Internal calls in cwms_ts.retrieve_ts_out_tab invoke                                                                                                                                                                           
        // get_db_office_code(NULL) and rely on SYS_CONTEXT being set;
        // omitting this causes ORA-01422 for multi-office users.                                                                                                                                                                                      
        setOffice(conn, requestParameters.getOffice());                                                                                                                                                                                              
        DSLContext scopedDsl = getDslContext(conn, requestParameters.getOffice());
        return doGetRequestedTimeSeriesLegacy(scopedDsl, page, pageSize,
                                              requestParameters, fp);
    });
}

Apply the same wrapping to getRequestedTimeSeriesDirect. Move each method's current body into a private helper that takes the scoped DSLContext as its first parameter, so all downstream dsl.select(...).fetch() calls run on the office-primed
connection.

Keep the CompletableFuture.supplyAsync timeout in TimeSeriesController.getAll — it's a valuable feature and independent of this defect.

Alternative (broader) fix

Add SessionOfficePreparer to AuthDao.prepareContextWithUser (line ~383), mirroring what prepareGuestContext (line ~447) already does. This would guarantee every authenticated-user connection has the office set at acquisition. The office is
per-request (URL param), not per-user, so this needs care — bind at auth time using the principal's default office, and let per-request setOffice calls override. The narrow DAO-level fix above is safer and matches existing patterns in the
codebase.

Regression test

Both the test and the fixture updates it depends on need to land in the same PR.

1. Add a multi-office test user

In cwms-data-api/src/test/java/fixtures/TestAccounts.java, add:

MULTI_OFFICE_NORMAL("multiofficeuser", "multiofficeuser", "1234567891",
        "multiofficekey", "MultiOfficeSession", "MVP",
        "CWMS Users", ApiServlet.CAC_USER),

Update the test DB fixture SQL (the CDA analog of your users.sql) to place this user in multiple offices:

cwms_sec.add_cwms_user('multiofficeuser', NULL, 'MVP');
cwms_sec.add_user_to_group('multiofficeuser', 'All Users',  'MVP');
cwms_sec.add_user_to_group('multiofficeuser', 'CWMS Users', 'MVP');
cwms_sec.add_cwms_user('multiofficeuser', NULL, 'SPK');
cwms_sec.add_user_to_group('multiofficeuser', 'All Users',  'SPK');
cwms_sec.add_user_to_group('multiofficeuser', 'CWMS Users', 'SPK');
cwms_sec.add_cwms_user('multiofficeuser', NULL, 'LRL');
cwms_sec.add_user_to_group('multiofficeuser', 'All Users',  'LRL');
cwms_sec.add_user_to_group('multiofficeuser', 'CWMS Users', 'LRL');
insert into cwms_20.at_api_keys (userid, key_name, apikey, created, expires)
values ('MULTIOFFICEUSER', 'multiofficekey', 'ak1_...$argon2id$...',
        sysdate, sysdate + 365);

2. Add the regression test

// cwms-data-api/src/test/java/cwms/cda/data/dao/TimeSeriesDaoSessionOfficeIT.java
package cwms.cda.data.dao;

import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;

import com.codahale.metrics.MetricRegistry;
import fixtures.CwmsDataApiSetupCallback;
import fixtures.DataApiTestIT;
import java.sql.PreparedStatement;
import java.time.ZonedDateTime;
import mil.army.usace.hec.test.database.CwmsDatabaseContainer;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Tag;
import org.junit.jupiter.api.Test;

@Tag("integration")
final class TimeSeriesDaoSessionOfficeIT extends DataApiTestIT {

    private static final String OFFICE_ID = "MVP";
    private static final String TS_ID =
            "TestLoc.Stage.Inst.15Minutes.0.SessionOfficeIT";

    @BeforeAll
    static void setup() throws Exception {
        // Create the location and store one value via the working POST path,
        // so a TSID exists for the GET to fetch. Assumes MULTI_OFFICE_NORMAL
        // user exists (see fixture changes above).
        createLocation("TestLoc", true, OFFICE_ID, "SITE");
        // ... store one value at 2023-01-01T00:00Z with value 99, units=ft
        //     using existing DAO test helpers or REST-assured POST.
    }

    @Test
    void getTimeseries_multiOfficeUser_worksWithoutSessionOfficePreset()
            throws Exception {
        // Regression guard for ORA-01422 / SESSION_OFFICE_ID_NOT_SET.
        // TimeSeriesDaoImpl.getRequestedTimeSeries must set SESSION_OFFICE_ID
        // itself; it must not depend on the acquired pool connection having
        // been through a prior office-setting code path.
        CwmsDatabaseContainer<?> db = CwmsDataApiSetupCallback.getDatabaseLink();
        db.connection(c -> {
            // Force the failure mode: clear any inherited SESSION_OFFICE_ID
            // context on this connection so the DAO cannot accidentally
            // succeed via sticky pool state.
            try (PreparedStatement stmt = c.prepareStatement(
                    "begin cwms_20.cwms_env.clear_session_privileges; end;")) {
                stmt.execute();
            }                                                                                                                                                                                                                                          
 
            // Build DSLContext WITHOUT the office-setting helper — mirrors                                                                                                                                                                            
            // what TimeSeriesController.getAll's ForkJoin worker sees on                                                                                                                                                                            
            // a cold pool connection today.
            DSLContext dsl = DSL.using(c, SQLDialect.ORACLE18C);
            TimeSeriesDao dao = new TimeSeriesDaoImpl(dsl, new MetricRegistry());

            TimeSeriesRequestParameters params =
                    new TimeSeriesRequestParameters.Builder()
                            .withNames(TS_ID)
                            .withOffice(OFFICE_ID)
                            .withUnits("EN")
                            .withBeginTime(ZonedDateTime.parse("2022-12-31T23:55:00Z"))
                            .withEndTime(ZonedDateTime.parse("2023-01-01T00:05:00Z"))
                            .withShouldTrim(true)
                            .build();

            // Before the fix: throws DataAccessException wrapping
            //   ORA-20047: SESSION_OFFICE_ID_NOT_SET
            //   ORA-01422: exact fetch returned more than the requested number of rows
            // After the fix (setOffice called inside getRequestedTimeSeries):
            //   returns normally.
            assertDoesNotThrow(
                    () -> dao.getTimeseries("", 500, params),
                    "GET /timeseries must succeed for multi-office API-key users "
                    + "even when SESSION_OFFICE_ID is unset on the acquired "
                    + "connection. Regression guard for ORA-01422 in "
                    + "CWMS_UTIL.user_office_id fallback.");
        });
    }
}

Deterministic (not pool-timing-dependent), fails hard on the current code, passes after the fix.

Acceptance criteria

• TimeSeriesDaoImpl.getRequestedTimeSeriesLegacy and getRequestedTimeSeriesDirect acquire a connection via connectionResult(dsl, conn -> ...) and call setOffice(conn, requestParameters.getOffice()) before any query executes.
• MULTI_OFFICE_NORMAL user added to TestAccounts.KeyUser and to the test DB fixture SQL.
• TimeSeriesDaoSessionOfficeIT.getTimeseries_multiOfficeUser_worksWithoutSessionOfficePreset added and passing.
• CompletableFuture.supplyAsync timeout in TimeSeriesController.getAll retained (do not revert Feature/1288 ts timer #1700).
• No regression in existing TimeSeriesController / TimeSeriesDao tests.

Client-side workaround (for downstream users on affected versions)

Until the fix ships, clients using multi-office API keys can add a warmup GET /cwms-data/timeseries (wrapped so failures are absorbed) in test-module or client-init setup:

# Python (cwms-python example)
try:
    ts.get_timeseries(some_existing_tsid, office_id, begin=..., end=...)
except Exception as e:
    print(f"Warmup GET failed (expected on cold CDA connection): {e}")

Not a real fix — sensitive to pool size and connection recycling — but empirically stops the failure in typical test-suite scenarios.