GeoServer Microsoft SQLServer view not visible as WFS

Question

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.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.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.ChannelEndPoint2ドル.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.

• nmtoken: Does a WFS GetCapabilities request work in a browser? Does your view have a primary key?
• Peter K: Yes, GetCapabilites works in a browser and my view does have a primary key.

Answer 1

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.

• Peter K: 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"
• Ian Turton: 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