Environment
- GeoServer 2.28.x, gt-jdbc-mysql 34.4, JTS 1.20.0, JDK 21
- MySQL 8.0 (tested with stock Connector/J 9.6.0)
- Layer: native
geometry SRID 4326column, declared SRS EPSG:4326, projection policy FORCE_DECLARED
Problem
A WMS GetMap request against a MySQL 8 table whose geometry column is declared SRID 4326 fails with:
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation:
Data truncation: Latitude 119.475 is out of range in ST_GeomFromText.
The WKT pushed down is longitude-latitude (JTS internal order), but MySQL 8’s geographic SRS 4326 is defined as AXIS["Lat",NORTH], AXIS["Lon",EAST] and
validates the first ordinate against [-90,90], so the longitude value is rejected as a latitude.
Reproducing DDL / data
CREATE TABLE g (
id int primary key,
geom geometry SRID 4326
);
-- stored as lat-lon, as MySQL 8 SRID 4326 requires:
INSERT INTO g VALUES (1, ST_GeomFromText(
'POLYGON((30.2 120,30.2 120.2,30.4 120.2,30.4 120,30.2 120))', 4326));
Generated SQL (from the general log / stack trace)
... WHERE MBRIntersects(geom,
ST_GeomFromText('POLYGON((119.475 29.485, ...))', 4326))
-- ^^^^ lon-lat, from JTS
What I have already ruled out
- Not the JDBC driver: it receives a fully-built SQL string via
executeQuery(String)and sends it as-is; it does not parse or reorder WKT. Re-tested
with stock mysql-connector-j 9.6.0. - Not the request axis order: WMS 1.1.1 (east/north) and WMS 1.3.0 (north/east, flipped by GeoServer) produce the same pushed-down lon-lat WKT. The
request-to-internal flip happens correctly; the emitted WKT is JTS x=lon, y=lat. - PostGIS does not have this problem, because its geometry(4326) convention is lon-lat (it does not honor EPSG axis order), matching JTS. MySQL 8 honors
the SRS axis order. - gt-jdbc-mysql 34.4 (decompiled):
MySQLFilterToSQL.visitLiteralGeometry(...)callsgeometry.toText()directly in both the precise and non-precise
branches, then writes it with the current SRID — no axis swap anywhere.MySQLDialect.isMySqlVersion80OrAboveonly controlsST_SRID(col,0)wrapping for the
envelope and does not affect the WKT. There is also no data-store parameter for axis order. -Dorg.geotools.referencing.forceXY=falsemakes MySQL accept the WKT but breaks the persisted native bounding box (resulting inWHERE 0=1), reverses
the WFS coordinate output order, and still fails WMS 1.1.1 — so it is not a viable fix.
Questions
- Is this a known issue? I could not find an open GEOT ticket.
- Is the intended fix to flip ordinates for MySQL 8 geographic SRSes inside
visitLiteralGeometry(similar to how the SQL Server / Oracle dialects handle
their axis-aware geometry types)? I would be happy to contribute a patch if someone can confirm the right approach — flip only whenisMySqlVersion80OrAbove
and the SRID is geographic, or use an axis-order-aware WKT writer? - Is there a supported workaround on the GeoServer side without changing the column SRID to 0?
Happy to provide the full stack trace and DB general log on request.
