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 wkb
	at 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 wkb
	at 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 input
	at 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 databases
		if (columnName == null) {
			continue;
		}

		// Alter By ZXHM
		Boolean 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 have
		PrimaryKeyColumn col = null;

		// 1. Auto Incrementing?
		Statement st = cx.createStatement();
		try {
			// not actually going to get data
			st.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 on
				LOGGER.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的空间数据库。