search

USACE / cwms-data-api

Corps Water Management System RESTful Data Service
MIT License
13 stars 14 forks source link

Getting Error when creating location #849

Closed Enovotny closed 1 month ago

Enovotny commented 2 months ago

Getting the following in cloud watch for CWBI test. Don't think this is a schema issue since I was able to create a location in our local database without an issue using the same schema. This was working a couple weeks ago in CWBI-test.

When running

curl -X 'POST' \ 'https://cwms-data-test.cwbi.us/cwms-data/locations' \ -H 'accept: /' \ -H 'Authorization: apikey XXX' \ -H 'Content-Type: application/json' \ -d '{ "office-id": "MVP", "name": "TestLoc_evn3432", "latitude": 0, "longitude": 0, "active": true, "public-name": "TEST", "long-name": "TEST", "description": "TEST", "timezone-name": "America/Los_Angeles", "location-kind": "PROJECT", "nation": "US", "published-longitude": 0, "horizontal-datum": "NAD83", "published-latitude": 0 }'

  | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 3700   | 2024-08-16T21:39:18.306Z | ORA-06512: at "MDSYS.MD", line 1723   | 2024-08-16T21:39:18.306Z | ORA-06512: at "MDSYS.MDERR", line 17   | 2024-08-16T21:39:18.306Z | ORA-06512: at "MDSYS.PRVT_IDX", line 12   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 9928   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 2081   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 3658   | 2024-08-16T21:39:18.306Z | ORA-20025: LOCATION_ID_NOT_FOUND: The Location: "TestLoc_evn3432" does not exist.   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 1453   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_ERR", line 80   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 245   | 2024-08-16T21:39:18.306Z | ORA-01403: no data found   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 213   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 182   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 274   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 1054   | 2024-08-16T21:39:18.306Z | ORA-06512: at "CWMS_20.CWMS_LOC", line 3628

MikeNeilson commented 2 months ago

Schema no, database, yes. there appears to be something wrong with the geospatial index, hence the MDSYS.* lines in that output. Well, or the schemas are slightly different, log/long 0/0 is not within the MVP office, the US for that matter, so maybe this db doesn't have the change for allowing the use to dictate the bounding office, or you may need to set it so it doesn't try looking it up.

Enovotny commented 2 months ago

you are right it was the Lat and Longs being 0. I copied an existing location information and it worked. It allowed me to do that before. and I was able to do it in our test databases. I tried some other values as well and it errored out so I don't what the boundary is that it is checking.

DanielTOsborne commented 1 month ago

I think there might be more to it. That error message above is incomplete. I get that same message, but that's after the real error occurred.

27-Sep-2024 21:13:22.253 INFO [https-openssl-nio-8243-exec-44] cwms.cda.data.dao.AuthDao.prepareContextWithUser Validated Api Key for user=L2EDHDTO [CONTEXT ratelimit_period="5 SECONDS" ]
27-Sep-2024 21:13:22.396 INFO [https-openssl-nio-8243-exec-44] cwms.cda.ApiServlet.lambda$init$12 5518740146750825719: Not Found.
        cwms.cda.api.errors.NotFoundException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                at cwms.cda.data.dao.JooqDao.buildNotFound(JooqDao.java:338)
                at cwms.cda.data.dao.JooqDao.wrapException(JooqDao.java:230)
                at cwms.cda.data.dao.JooqDao.connection(JooqDao.java:564)
                at cwms.cda.data.dao.LocationsDaoImpl.storeLocation(LocationsDaoImpl.java:221)
                at cwms.cda.api.LocationController.create(LocationController.java:312)
                at io.javalin.apibuilder.CrudFunction$3.invoke$lambda-0(CrudHandler.kt:32)
                at io.javalin.apibuilder.CrudFunctionHandler.handle(CrudHandler.kt)
                at cwms.cda.security.KeyAccessManager.manage(KeyAccessManager.java:30)
                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:865)
                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.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
                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.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:596)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
                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: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.lang.Thread.run(Thread.java:750)
        Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CWMS_20.CWMS_LOC", line 3700
ORA-06512: at "CWMS_20.CWMS_LOC", line 1876
ORA-06512: at "CWMS_20.CWMS_LOC", line 3658
ORA-20025: LOCATION_ID_NOT_FOUND: The Location: "Animas River @ Farmington" does not exist.
ORA-06512: at "CWMS_20.CWMS_LOC", line 1453
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_LOC", line 245
ORA-01403: no data found
ORA-06512: at "CWMS_20.CWMS_LOC", line 213
ORA-06512: at "CWMS_20.CWMS_LOC", line 182
ORA-06512: at "CWMS_20.CWMS_LOC", line 274
ORA-06512: at "CWMS_20.CWMS_LOC", line 1054
ORA-06512: at "CWMS_20.CWMS_LOC", line 3628
ORA-06512: at line 1

No matter what I do, I can't create a location, and I have no idea why.

I've attached a script that fails when I run it. I get a server error, and the server error is the above trace.

locations_create.py.txt

As for the GPS, the published-latitide and published-longitude are supposed to be optional, and I could see 0 being a useful value to indicate you don't want to publish the GPS.

DanielTOsborne commented 1 month ago

Ok, to follow-up, I finally figured out the problem: the location name was too long.

I spent 5 hours on this issue. CDA needs better error messages, and it needs them now. It's frustration, anger, and wasted time by people trying to use this (and I'm not the only one).

MikeNeilson commented 1 month ago

I don't disagree with you, but just magically coming up with good error messages has been difficult.

  1. We're taking a vague first approach so we don't have to panic about any vulnerability reports with annoying deadlines.
  2. Often we're fighting with vague messages from the database that are difficult to map. (That's assuming we even get an error from the db.)

Like here for example "character string buffer too small" is clearly the cause but where? We know it's the location id, but there's no context for CDA to pick it up.

Good new is this one should be fairly easy to address. However, not every error will be, please keep opening issues, and if at all possible consider helping with the actual fix.

MikeNeilson commented 1 month ago

I'm going to close this one, it was originally for a specific error. The new update has a bit more of a generic bent and an issue focused on the error reporting (same context (too long) different table/column) was opened a few weeks ago.

Additional identified need covered in #870