1

I am running a GeoServer 2.16.2 that connects to a Microsoft SQL Server 2008 database using the SQL Server extension and the Microsoft JDBC Drivers. Java Version: AdoptOpenJDK: 1.8.0_242.

I can publish an SQL view as a GeoServer layer and easily import and display it in QGIS as a WMS service. But whenever I try to access the same layer via WFS, I only get an empty layer in QGIS. GeoServer gives the following error message whenever I try to connect via WFS:

2020-04-16 20:38:50,352 INFO [geoserver.wfs] - 
Request: getServiceInfo
2020-04-16 20:38:51,207 INFO [geoserver.wfs] - 
Request: getFeature
    service = WFS
    version = 2.0.0
    baseUrl = http://x.x.x.x:8080/geoserver/
    count = 1
    outputFormat = application/gml+xml; version=3.2
    resolve = none
    resolveDepth = *
    resolveTimeout = 300
    resultType = results
    startIndex = 0
    abstractQueryExpressionGroup[0] = wfs:abstractQueryExpression=net.opengis.wfs20.impl.QueryTypeImpl@1a4d687b (handle: null) (abstractProjectionClause: null, abstractSelectionClause: null, abstractSortingClause: null, aliases: null, typeNames: [{Server}View]) (featureVersion: null, srsName: urn:ogc:def:crs:EPSG::4326, filter: null, propertyNames: null, sortBy: null)
    abstractQueryExpression[0]:
        typeNames[0] = {Server}View
        srsName = urn:ogc:def:crs:EPSG::4326
2020-04-16 20:38:51,220 INFO [geoserver.servlets] - OutputStream was successfully aborted.
2020-04-16 20:38:51,221 ERROR [geoserver.ows] - 
java.lang.RuntimeException: java.lang.RuntimeException: java.io.IOException
    at org.geotools.xsd.Encoder.encode(Encoder.java:732)
    at org.geotools.xsd.Encoder.encode(Encoder.java:552)
    at org.geoserver.wfs.xml.GML32OutputFormat.encode(GML32OutputFormat.java:151)
    at org.geoserver.wfs.xml.GML3OutputFormat.write(GML3OutputFormat.java:312)
    at org.geoserver.wfs.WFSGetFeatureOutputFormat.write(WFSGetFeatureOutputFormat.java:198)
    at org.geoserver.ows.Dispatcher.response(Dispatcher.java:1031)
    at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:269)
    at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:177)
    at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:52)
    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.doGet(FrameworkServlet.java:890)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:873)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1623)
    at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:26)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:69)
    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.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:37)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    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.GeoServerSecurityContextPersistenceFilter$1.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.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:101)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.XFrameOptionsFilter.doFilter(XFrameOptionsFilter.java:79)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:47)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:46)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:42)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1602)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:540)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:146)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:257)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1700)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1667)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:220)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:152)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.Server.handle(Server.java:505)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
    at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)
    at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:698)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:804)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: java.io.IOException
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:165)
    at org.geoserver.feature.RetypingFeatureCollection.features(RetypingFeatureCollection.java:45)
    at org.geoserver.feature.RetypingFeatureCollection.features(RetypingFeatureCollection.java:31)
    at org.geotools.data.crs.ReprojectFeatureResults.openIterator(ReprojectFeatureResults.java:105)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:65)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:50)
    at org.geoserver.security.decorators.SecuredFeatureCollection.features(SecuredFeatureCollection.java:43)
    at org.geoserver.security.decorators.SecuredSimpleFeatureCollection.features(SecuredSimpleFeatureCollection.java:74)
    at org.geotools.feature.collection.DecoratingSimpleFeatureCollection.features(DecoratingSimpleFeatureCollection.java:88)
    at org.geotools.gml2.simple.FeatureCollectionEncoderDelegate.encode(FeatureCollectionEncoderDelegate.java:110)
    at org.geotools.xsd.Encoder.encode(Encoder.java:730)
    ... 103 more
Caused by: java.io.IOException
    at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:625)
    at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:609)
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:163)
    ... 113 more
Caused by: java.io.IOException: Cannot do natural order without a primary key, please add it or specify a manual sort over existing attributes
    at org.geotools.jdbc.JDBCDataStore.sort(JDBCDataStore.java:3507)
    at org.geotools.jdbc.JDBCDataStore.selectSQL(JDBCDataStore.java:3336)
    at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:592)
    ... 115 more

How can I fix this? I tried different wfs-settings as well as different layers.

Improve this question
2
  • Does a WFS GetCapabilities request work in a browser? Does your view have a primary key?
    – nmtoken
    Commented Apr 17, 2020 at 3:14
  • Yes, GetCapabilites works in a browser and my view does have a primary key.
    – Peter K
    Commented Apr 17, 2020 at 8:14

1 Answer 1

Reset to default
0

The error message seems clear to me "Cannot do natural order without a primary key, please add it or specify a manual sort over existing attributes" - you do not have a primary key on your view so GeoServer is unable to serve it up as there is no guarantee of the order of the features.

To fix this add a primary key to your view, this can be achieved by providing a metadata table that GeoServer can look up the column to use a primary key for feature id generation. See the manual for more details of format etc.

Improve this answer
2
  • Thank you very much it was indeed a problem with the primary key! Apparently Geoserver does not recognize the primary key in your view if you try to Publish the view directly as new layer. Instead you have to create a new SQL view within Geoserver: "Select * From yourfirstview" and then select your primary key column as "Identifier"
    – Peter K
    Commented Apr 20, 2020 at 8:55
  • or you could follow the instructions in how to set up a metadata table to tell it how to find the primary key that I linked to
    – Ian Turton
    Commented Apr 20, 2020 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.