GeoServer returns "schema does not exists" when creating SQLView Layer using REST API

Question

I'm using GeoServer 2.16.2 (Kartoza/geoserver + postgis docker image) and I'm trying to create a SQLView layer using the REST interface. First I manually created the SQLView layer and copied the layer json description from the GeoServer REST interface. I then deleted the layer and tried to recreate it using a POST call to the GeoServer REST API. But this failed with the error "Schema 'chuva_munic_semana' does not exist."

The only 'complex' thing that I'm trying yo do in my SQLView is that my SQL statement contains a JOIN between two tables in PostGIS and uses a time dimension.

The funny thing is that doing a similar procedure but with a very simple SQL works. This makes me think that the problem lies with the SQL query.

Bellow is the JSON I'm sending to the REST API. GEO_HOST and GEO_PORT are parameters that are substituted prior to the POST call.

{
 "featureType": {
 "name": "chuva_munic_semana",
 "nativeName": "chuva_munic_semana",
 "namespace": {
 "name": "zarc",
 "href": "http://GEO_HOST:GEO_PORT/geoserver/rest/namespaces/zarc.json"
 },
 "title": "Chuva semanal",
 "abstract": "Chuva nos municipios",
 "keywords": {
 "string": [
 "features",
 "chuva_munic_semana"
 ]
 },
 "nativeCRS": "GEOGCS[\"WGS 84\", \n DATUM[\"World Geodetic System 1984\", \n SPHEROID[\"WGS 84\", 6378137.0, 298.257223563, AUTHORITY[\"EPSG\",\"7030\"]], \n AUTHORITY[\"EPSG\",\"6326\"]], \n PRIMEM[\"Greenwich\", 0.0, AUTHORITY[\"EPSG\",\"8901\"]], \n UNIT[\"degree\", 0.017453292519943295], \n AXIS[\"Geodetic longitude\", EAST], \n AXIS[\"Geodetic latitude\", NORTH], \n AUTHORITY[\"EPSG\",\"4326\"]]",
 "srs": "EPSG:4326",
 "nativeBoundingBox": {
 "minx": -73.9831821599999,
 "maxx": -28.8477703530605,
 "miny": -33.7511779939999,
 "maxy": 5.26958083300002,
 "crs": "EPSG:4326"
 },
 "latLonBoundingBox": {
 "minx": -73.9831821599999,
 "maxx": -28.8477703530605,
 "miny": -33.7511779939999,
 "maxy": 5.26958083300002,
 "crs": "EPSG:4326"
 },
 "projectionPolicy": "FORCE_DECLARED",
 "enabled": true,
 "metadata": {
 "entry": [
 {
 "@key": "elevation",
 "dimensionInfo": {
 "enabled": false
 }
 },
 {
 "@key": "JDBC_VIRTUAL_TABLE",
 "virtualTable": {
 "name": "chuva_munic_semana",
 "sql": "select a.geocodigo as geocodigo, nome, ano, semana, data_ini, chuva, dias, wkb_geometry from dado_semana_munic as a left join lm_municipio_a as b on a.geocodigo = b.geocodigo\n",
 "escapeSql": false,
 "geometry": {
 "name": "wkb_geometry",
 "type": "Geometry",
 "srid": 4326
 }
 }
 },
 {
 "@key": "time",
 "dimensionInfo": {
 "enabled": true,
 "attribute": "data_ini",
 "presentation": "LIST",
 "units": "ISO8601",
 "defaultValue": {
 "strategy": "MAXIMUM"
 },
 "nearestMatchEnabled": true,
 "acceptableInterval": "P1W/P0D"
 }
 }
 ]
 },
 "store": {
 "@class": "dataStore",
 "name": "zarc:gis",
 "href": "http://GEO_HOST:GEO_PORT/geoserver/rest/workspaces/zarc/datastores/gis.json"
 },
 "serviceConfiguration": false,
 "maxFeatures": 0,
 "numDecimals": 0,
 "padWithZeros": false,
 "forcedDecimal": false,
 "overridingServiceSRS": false,
 "skipNumberMatched": false,
 "circularArcPresent": false,
 "attributes": {
 "attribute": [
 {
 "name": "geocodigo",
 "minOccurs": 1,
 "maxOccurs": 1,
 "nillable": false,
 "binding": "java.lang.String"
 },
 {
 "name": "nome",
 "minOccurs": 0,
 "maxOccurs": 1,
 "nillable": true,
 "binding": "java.lang.String"
 },
 {
 "name": "ano",
 "minOccurs": 1,
 "maxOccurs": 1,
 "nillable": false,
 "binding": "java.lang.Integer"
 },
 {
 "name": "semana",
 "minOccurs": 1,
 "maxOccurs": 1,
 "nillable": false,
 "binding": "java.lang.Integer"
 },
 {
 "name": "data_ini",
 "minOccurs": 0,
 "maxOccurs": 1,
 "nillable": true,
 "binding": "java.sql.Date"
 },
 {
 "name": "chuva",
 "minOccurs": 0,
 "maxOccurs": 1,
 "nillable": true,
 "binding": "java.lang.Integer"
 },
 {
 "name": "dias",
 "minOccurs": 0,
 "maxOccurs": 1,
 "nillable": true,
 "binding": "java.lang.Integer"
 },
 {
 "name": "wkb_geometry",
 "minOccurs": 0,
 "maxOccurs": 1,
 "nillable": true,
 "binding": "org.locationtech.jts.geom.Geometry"
 }
 ]
 }
 }
}

Bellow is the (cliped) GeoServer log messages (logging level up to GEOTOOLS-DEV). I tryed to create the SQLView at 2020年06月10日 13:50:33,211. The full log can be found at https://pastebin.com/y7NdLUur

2020年06月10日 13:47:44,518 INFO [geoserver.logging] - Suppressing StdOut logging. If you want to see GeoServer logs, be sure to look in '/opt/geoserver/data_dir/logs/geoserver.log'
2020年06月10日 13:47:44,596 WARN [geoserver.security] - Failed login, user admin from 172.21.0.1
2020年06月10日 13:47:44,597 INFO [geoserver.security] - Brute force attack prevention, delaying login for 3594ms
2020年06月10日 13:48:01,669 INFO [rest.catalog] - Added workspace zarc
2020年06月10日 13:48:01,670 INFO [rest.catalog] - POST workspace zarc
2020年06月10日 13:48:05,956 INFO [rest.catalog] - POST data store gis
2020年06月10日 13:48:08,900 WARN [geotools.xsd] - Sax parser property 'http://www.oracle.com/xml/jaxp/properties/entityExpansionLimit' not recognized. Xerces version is incompatible.
2020年06月10日 13:48:09,564 INFO [rest.catalog] - POST Style chuva_semanal
2020年06月10日 13:48:10,302 WARN [geotools.xsd] - Sax parser property 'http://www.oracle.com/xml/jaxp/properties/entityExpansionLimit' not recognized. Xerces version is incompatible.
2020年06月10日 13:48:10,329 INFO [rest.catalog] - POST Style dias_sem_chuva_semanal
2020年06月10日 13:48:53,633 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_munic]
2020年06月10日 13:48:54,762 INFO [rest.catalog] - POST feature typegis,bc250_munic
2020年06月10日 13:48:54,799 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_uf]
2020年06月10日 13:48:54,878 INFO [rest.catalog] - POST feature typegis,bc250_uf
2020年06月10日 13:49:12,356 WARN [geoserver.security] - Failed login, user admin from 172.21.0.1
2020年06月10日 13:49:12,356 INFO [geoserver.security] - Brute force attack prevention, delaying login for 2000ms
2020年06月10日 13:50:33,211 WARN [rest.catalog] - Unable to fill in metadata from underlying feature source
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
 at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:592)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:97)
 at org.geoserver.rest.catalog.FeatureTypeController.featureTypePost(FeatureTypeController.java:246)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.base/java.lang.reflect.Method.invoke(Method.java:566)
 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:207)
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998)
 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:901)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
 at 
<content clipped - too long>
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:69)
 at org.geoserver.flow.controller.IpBlacklistFilter.doFilter(IpBlacklistFilter.java:89)
 at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
 at org.geoserver.flow.ControlFlowCallback.doFilter(ControlFlowCallback.java:260)
 at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
 at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:73)
 at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
 at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:41)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
 at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:37)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
 at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
 at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
 at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
 at org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:51)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
 at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:215)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
 at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
 at org.geoserver.security.filter.GeoServerBasicAuthenticationFilter.doFilter(GeoServerBasicAuthenticationFilter.java:81)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
 at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
 at org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter1ドル.doFilter(GeoServerSecurityContextPersistenceFilter.java:52)
 at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
 at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
 at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
 at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
 at org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:142)
 at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
 at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
 at 
< content clipped - too long >
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.base/java.lang.Thread.run(Thread.java:834)
2020年06月10日 13:50:33,231 WARN [geoserver.catalog] - Error while getting feature type, flushing cache and retrying: Schema 'chuva_munic_semana' does not exist.
2020年06月10日 13:50:33,235 WARN [geoserver.catalog] - Unable to flush 'http://zarc:chuva_munic_semana
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
 at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:303)
 at org.geoserver.catalog.ResourcePool.flushState(ResourcePool.java:2679)
 at org.geoserver.catalog.ResourcePool.flushDataStore(ResourcePool.java:2652)
 at org.geoserver.catalog.ResourcePool.getFeatureType(ResourcePool.java:935)
 at org.geoserver.catalog.ResourcePool.getFeatureType(ResourcePool.java:921)
 at org.geoserver.catalog.impl.FeatureTypeInfoImpl.getFeatureType(FeatureTypeInfoImpl.java:123)
 at org.geoserver.catalog.CatalogBuilder.getDefaultStyle(CatalogBuilder.java:1567)
 at org.geoserver.catalog.CatalogBuilder.buildLayer(CatalogBuilder.java:1516)
 at org.geoserver.rest.catalog.FeatureTypeController.featureTypePost(FeatureTypeController.java:284)
 at 
< content clipped ...>
 at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.base/java.lang.Thread.run(Thread.java:834)
2020年06月10日 13:50:33,242 ERROR [geoserver.rest] - Schema 'chuva_munic_semana' does not exist.
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
 at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
 at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:303)
<content clipped -- was too long>
2020年06月10日 13:50:33,244 WARN [annotation.ExceptionHandlerExceptionResolver] - Resolved [java.io.IOException: Schema 'chuva_munic_semana' does not exist.]

I also found this error coming from PostGIS docker, not sure if it's related

db_1 | 2020年06月10日 13:48:53.664 UTC [348] docker@gis ERROR: relation "public.gt_pk_metadata" does not exist at character 15
db_1 | 2020年06月10日 13:48:53.664 UTC [348] docker@gis STATEMENT: SELECT * FROM "public".GT_PK_METADATA WHERE 1 = 0

• Ian Turton: Please look in the GeoServer log files and add any relevant error messages to your question using the edit button. You may need to turn the logging level up to GEOTOOLS-DEV to see exactly what is going on
• Daniel: Thanks Ian. I added the log messages. Just noticed that I'm seeing 2020年06月10日 13:48:53,633 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_munic] but those layers are created without any issues. And the SQLView layer that I'm trying to create uses one of those layers

Answer 1

So it turned out that the problem was how we choose to send the payload to GeoServer. We are using an R Script to open the JSON payload file (jsonlite package) and post it to the server (httr package). Our post call was:

res = POST(url = url, config = auth, body = fromJSON(payload), encode = "json")

Somehow, the fromJSON(payload) was OK for simple JSON files but, when we tried it whit a more complex payload (SQL View Layer), the content got messed up. A friend speculates that it could be related to boxing/unboxing ([]) of some parts of the file.

Anyway, we changed our POST line and now use

res = POST(url = url, config = auth, body = upload_file(payloadFile), encode = 'json')

And all works fine. So the trick was changing from fromJSON() to upload_file()