cannot utilize /cwms-data/timeseries/recent/{group-id} - give invalid error

[katfeingold]

Cannot utilize /cwms-data/timeseries/recent/{group-id} on either cwms-data-test.cwbi.us or https://cwms-data.usace.army.mil/ cwms-data-test.cwbi.us give the following error: { "message": "Invalid User", "incidentIdentifier": "user input error", "details": {} }

And the https://cwms-data.usace.army.mil/ is giving the following error: { "message": "Invalid arguments supplied, group has neither Timeseries Group info nor Timeseries IDs", "incidentIdentifier": "-7295522450672782195", "details": {} }

and lacking the fields that CWBI-Test has.

[MikeNeilson]

ApiSerlvet has roles added to the route, pretty sure that should be empty, not require a login.

However, even after fixing that the following:

curl -i 'http://localhost:7005/spk-data/timeseries/recent/?ts-ids=Black%20Butte.Stor-Top%20Con.Inst.~1Day.0.Calc-val&office-id=SPK

Does this wonderful thing where you immediately get a 501 error but at least once I got it to actually try the request anyways which fails with a database error of a column being abigously defined.

     at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
                at java.base/java.lang.Thread.run(Thread.java:829)
05-Apr-2024 22:19:55.075 WARNING [http-nio-7005-exec-3] cwms.cda.ApiServlet.lambda$init$16 error on request[-4168412388982983651]: /spk-data/timeseries/recent/DMZ%20Include%20List
        org.jooq.exception.DataAccessException: SQL [select CWMS_TS_ID, OFFICE_ID, TS_CODE, UNIT_ID, DATE_TIME, VERSION_DATE, DATA_ENTRY_DATE, VALUE, QUALITY_CODE, START_DATE, END_DATE from (select "CWMS_20"."AV_TSV_DQU".*, max("CWMS_20"."AV_TSV_DQU"."DATE_TIME") over (partition by "CWMS_20"."AV_TSV_DQU"."TS_CODE") "max_date_time", "CWMS_20"."AV_CWMS_TS_ID2"."CWMS_TS_ID" from "CWMS_20"."AV_TSV_DQU" join "CWMS_20"."AV_CWMS_TS_ID2" on "CWMS_20"."AV_TSV_DQU"."TS_CODE" = cast("CWMS_20"."AV_CWMS_TS_ID2"."TS_CODE" as number(19)) where ("CWMS_20"."AV_CWMS_TS_ID2"."CWMS_TS_ID" in (?) and "CWMS_20"."AV_TSV_DQU"."VALUE" is not null and "CWMS_20"."AV_TSV_DQU"."DATE_TIME" < cast(? as date) and "CWMS_20"."AV_TSV_DQU"."DATE_TIME" > cast(? as date) and "CWMS_20"."AV_TSV_DQU"."START_DATE" <= cast(? as date) and "CWMS_20"."AV_TSV_DQU"."END_DATE" > cast(? as date))) "alias_49393241" where DATE_TIME = "alias_49393241"."max_date_time"]; ORA-00918: column ambiguously defined

                at org.jooq_3.18.7.ORACLE18C.debug(Unknown Source)
                at org.jooq_3.18.7.ORACLE18C.debug(Unknown Source)
                at org.jooq.impl.Tools.translate(Tools.java:3470)
                at org.jooq.impl.Tools.translate(Tools.java:3458)
                at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
                at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:360)
                at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301)
                at org.jooq.impl.SelectImpl.fetchLazy(SelectImpl.java:2857)
                at org.jooq.impl.ResultQueryTrait.forEach(ResultQueryTrait.java:1458)
                at cwms.cda.data.dao.TimeSeriesDaoImpl.findMostRecentsInRange(TimeSeriesDaoImpl.java:761)
                at cwms.cda.api.TimeSeriesRecentController.handle(TimeSeriesRecentController.java:170)
                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.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)
                at java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)
                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.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:680)
                at java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:658)
                at java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2100)
                at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
                at java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)
                at java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)
                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:622)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:210)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:154)
                at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:179)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:154)
                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:179)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:154)
                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:481)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:241)
                at fixtures.tomcat.SingleSignOnWrapper.invoke(SingleSignOnWrapper.java:21)
                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:390)
                at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
                at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928)
                at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1786)
                at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
                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.base/java.lang.Thread.run(Thread.java:829)
        Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

[rma-rripken]

oh. I think I previously fixed that

[rma-rripken]

What should be done to resolved this issue? Should we remove the codes from the serialized response? Do we make user specify either SI or EN and then only return in the default unit for the specified system?

[MikeNeilson]

• CODE: yes
• units: allow EN or SI, but perhaps just use the default display unit of the given time series? (procedure/table for it, shouldn't be too hard.)

We might want to expand that later to allow unit per ts; somehow. But for now at least allowing the unit system makes it consistent.

Oh, if the user is logged in, technically they can set default display units per user, not a requirement now, but if it is easy, food for though anyways.

[rma-rripken]

Would you prefer something that joins with AV_DISPLAY_UNITS? Or that calls "CWMS_20"."CWMS_UTIL"."GET_DEFAULT_UNITS" ?

[rma-rripken]

Oh, also, can you tell me a little more about timeseries that use % ? Do those need to be handled specially? Is the thinking that this end-point would have an additional unit_system parameter where the user to specify EN or SI and the results would come out in the default units for the timeseries in that unit system? Is the parameter just the two options? EN or SI? Does it default to one if the parameter isn't present? Can the user specify something other than EN/SI?

[MikeNeilson]

Would you prefer something that joins with AV_DISPLAY_UNITS? Or that calls "CWMS_20"."CWMS_UTIL"."GET_DEFAULT_UNITS" ?

I would start with which ever is easier and go from there. Performance should be roughly the same, but Dave may have a better suggestion. They did just rewrite av_location_level to handle that better so may be look and follow that example.

[MikeNeilson]

Oh, also, can you tell me a little more about timeseries that use % ? Do those need to be handled specially? Is the thinking that this end-point would have an additional unit_system parameter where the user to specify EN or SI and the results would come out in the default units for the timeseries in that unit system? Is the parameter just the two options? EN or SI? Does it default to one if the parameter isn't present? Can the user specify something other than EN/SI?

It's not even just %, not every "level" means "height/depth/etc", the name local levels is insufficiently broad, it seems (though I also don't have a better one, so). Anyways, % is valid in both EN or SI, volts would be valid in EN or SI. So main issue is that you can't simply assume "EN -> ft", "SI -> m" as the local level could be Elevation, or Percent full, or a max flow, or storage value, etc.

[rma-rripken]

I added default units to https://github.com/USACE/cwms-data-api/pull/605 Along the way I also worked out why DATA_ENTRY_DATE wasn't mapping correctly.