GeoServer使用MySQL数据库出现“Error decoding wkb”错误的一种情况

错误

请看一下GeoServer官方文档的警告

Warning:Currently the MySQL extension is unmaintained and carries unsupported status. While still usable, do not expect the same reliability as with other extensions.

没事不要特立独行使用MySQL当GeoServer的空间数据库，会变得不幸，而且不知道为什么会不幸。就比如我标题遇到的错误，一样的系统、数据库版本、一样的数据库名、一样的编码和排序、一样的表名，一样的字段，一样的数据，一份数据工作正常，一份数据会出现这个错误。

渲染界面报错：

后台错误详情：

ERROR [renderer.lite] - java.io.IOException: Error decoding wkb
java.lang.RuntimeException: java.io.IOException: Error decoding wkbat org.geotools.jdbc.JDBCFeatureReader.readNextFeature(JDBCFeatureReader.java:389)at org.geotools.jdbc.JDBCFeatureReader.hasNext(JDBCFeatureReader.java:332)at org.geotools.data.store.ContentFeatureCollection$WrappingFeatureIterator.hasNext(ContentFeatureCollection.java:141)at org.geoserver.feature.RetypingFeatureCollection$RetypingIterator.hasNext(RetypingFeatureCollection.java:112)at org.geotools.data.crs.ForceCoordinateSystemIterator.hasNext(ForceCoordinateSystemIterator.java:121)at org.geotools.renderer.lite.StreamingRenderer.drawPlain(StreamingRenderer.java:2664)at org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRenderer.java:2286)at org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:917)at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:540)at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:202)at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:82)at org.geoserver.wms.GetMap.executeInternal(GetMap.java:344)at org.geoserver.wms.GetMap.run(GetMap.java:203)at org.geoserver.wms.GetMap.run(GetMap.java:113)at org.geoserver.wms.DefaultWebMapService.getMap(DefaultWebMapService.java:250)...
Caused by: java.io.IOException: Error decoding wkbat org.geotools.data.mysql.MySQLDialectBasic.decodeGeometryValue(MySQLDialectBasic.java:222)at org.geotools.jdbc.SQLDialect.decodeGeometryValue(SQLDialect.java:764)at org.geotools.jdbc.JDBCFeatureReader.readNextFeature(JDBCFeatureReader.java:381)... 153 more
Caused by: org.locationtech.jts.io.ParseException: Attempt to read past end of inputat org.locationtech.jts.io.ByteOrderDataInStream.read(ByteOrderDataInStream.java:142)at org.locationtech.jts.io.ByteOrderDataInStream.readInt(ByteOrderDataInStream.java:107)at org.locationtech.jts.io.WKBReader.readGeometry(WKBReader.java:229)at org.locationtech.jts.io.WKBReader.read(WKBReader.java:191)at org.locationtech.jts.io.WKBReader.read(WKBReader.java:159)at org.geotools.data.mysql.MySQLDialectBasic.decodeGeometryValue(MySQLDialectBasic.java:219)... 155 more

GeoServer使用MySQL

GeoServer默认是不支持MySQL的，需要安装相应的扩展组件才支持。比起根据官方文档中 Installing the MySQL extension的指引下载MySQL扩展组件，我更推荐到GeoTools官网下载对应版本的包，然后把其中的gt-jdbc-mysql-x.x.jar和mysql-connector-java-x.x.x.jar复制到geoserver/webapps/geoserver/WEB-INF/lib文件夹下，如果重启GeoServer之后可以添加MySQL数据源，发布图层、正常预览，那么恭喜，不需要往下看了，如果遇到我一样的错误，就很不幸，需要了解一点点Java开发才能解决问题。

解决方法

GeoTools是一个开源的Java 空间数据操作库，GeoServer里大量使用了GeoTools的库，虽然也没法调试吧，但是通过改代码替换包的形式，一点点追溯下来，最后发现不知道什么原因，他在读MySQL的时候会重复读取主键，比如主键是ID，那他读出来的字段列表会是ID,ID,filed1,the_geom。理论上the_geom的index是3（从1开始算），但有两列ID之后，用index=3读出来的会是field1字段，就导致读出来的并不是空间数据，强行解析成空间数据的时候就会出错，所以就会报错Error decoding wkb。

知道了原因，解决起来就简单了，在gt-jdbc这个包里HeuristicPrimaryKeyFinder.java文件的createPrimaryKey方法，加入如下代码，在构造主键时遇到重复的就跳过就可以了：

Boolean exist = false;
for (int i = 0; i < cols.size(); i++) {if (cols.get(i).name.equals(columnName)) {exist = true;break;}
}if (exist) {continue;
}

加了之后的createPrimaryKey方法完整代码：

/** Creates a key from a primary key or unique index.*/
PrimaryKey createPrimaryKey(JDBCDataStore store,ResultSet index,DatabaseMetaData metaData,String databaseSchema,String tableName,Connection cx)throws SQLException {ArrayList<PrimaryKeyColumn> cols = new ArrayList<>();while (index.next()) {String columnName = index.getString("COLUMN_NAME");// work around. For some reason the first record returned is always 'empty'// this was tested on Oracle and Postgres databasesif (columnName == null) {continue;}// Alter By ZXHMBoolean exist = false;for (int i = 0; i < cols.size(); i++) {if (cols.get(i).name.equals(columnName)) {exist = true;break;}}if (exist) {continue;}// look up the type ( should only be one row )ResultSet columns =metaData.getColumns(null,store.escapeNamePattern(metaData, databaseSchema),store.escapeNamePattern(metaData, tableName),store.escapeNamePattern(metaData, columnName));Class columnType;try {columns.next();columnType = store.getSQLDialect().getMapping(columns, cx);if (columnType == null) {int binding = columns.getInt("DATA_TYPE");columnType = store.getMapping(binding);if (columnType == null) {LOGGER.warning("No class for sql type " + binding);columnType = Object.class;}}} finally {store.closeSafe(columns);}// determine which type of primary key we havePrimaryKeyColumn col = null;// 1. Auto Incrementing?Statement st = cx.createStatement();try {// not actually going to get datast.setFetchSize(1);StringBuffer sql = new StringBuffer();sql.append("SELECT ");store.getSQLDialect().encodeColumnName(null, columnName, sql);sql.append(" FROM ");store.encodeTableName(tableName, sql, null);sql.append(" WHERE 0=1");LOGGER.log(Level.FINE, "Grabbing table pk metadata: {0}", sql);ResultSet rs = st.executeQuery(sql.toString());try {if (rs.getMetaData().isAutoIncrement(1)) {col = new AutoGeneratedPrimaryKeyColumn(columnName, columnType);}} finally {store.closeSafe(rs);}} finally {store.closeSafe(st);}// 2. Has a sequence?if (col == null) {try {String sequenceName =store.getSQLDialect().getSequenceForColumn(databaseSchema, tableName, columnName, cx);if (sequenceName != null) {col = new SequencedPrimaryKeyColumn(columnName, columnType, sequenceName);}} catch (Exception e) {// log the exception , and continue onLOGGER.log(Level.WARNING,"Error occured determining sequence for "+ columnName+ ", "+ tableName,e);}}if (col == null) {col = new NonIncrementingPrimaryKeyColumn(columnName, columnType);}cols.add(col);}if (!cols.isEmpty()) {return new PrimaryKey(tableName, cols);}return null;
}

我只是稍微了解一点Java，不知道我这个避免重复主键的写法有没有啥问题，现在就是能用就行。最后自己打包替换掉原有的gt-jdbc-x.x.jar然后重启GeoServer就可以了。当然这仅仅只是我遇到的情况，不保证使用MySQL数据库发生Error decoding wkb的报错都是这个情况。总而言之，不到万不得已，不要用MySQL作为GeoServer的空间数据库。