Detecting Incompatible Use of Spatial Functions before Upgrading to MySQL 8.0

There are many changes to spatial functions in MySQL 8.0:

The first two are failing cases. If we upgrade without fixing those, queries will start failing. The last one will not always cause error messages, but may instead cause a silent change in behavior since computations may suddenly be geographic instead of Cartesian.

Whether we’ll actually run in to problems after an upgrade may in some cases depend on the data in our database. If some geometries are in a geographic spatial reference system (SRS), functions that don’t support geographic SRSs will return error messages, while functions that do support geographic SRSs may return a different value than before.

In this post we’ll look at how we can prepare for these changes already in 5.7. In particular, we’ll try to detect those cases where we may run into problems and make sure the behavior is the same in 5.7 and 8.0.

Please note: This is just a best effort at detecting potential problems. There may be other issues that aren’t discovered by the techniques described below. And there may be false positives.

Deprecated Function Aliases

MySQL has been warning us about these functions for a long time. It’s about time to stop using them and start using standard compliant function names. The full list of deprecated aliases and recommended replacements is:

Deprecated nameRecommended name
AreaST_Area
AsBinaryST_AsBinary
AsTextST_AsText
AsWKBST_AsBinary
AsWKTST_AsText
BufferST_Buffer
CentroidST_Centroid
ContainsMBRContains
ConvexHullST_ConvexHull
CrossesST_Crosses
DimensionST_Dimension
DisjointMBRDisjoint
DistanceST_Distance
EndPoint
ST_EndPoint
EnvelopeST_Envelope
Equals
MBREquals
ExteriorRingST_ExteriorRing
GeomCollFromTextST_GeomCollFromTxt
GeomCollFromWKBST_GeomCollFromWKB
GeometryCollectionFromTextST_GeomCollFromTxt
GeometryCollectionFromWKBST_GeomCollFromWKB
GeometryFromTextST_GeomFromText
GeometryFromWKBST_GeomFromWKB
GeometryNST_GeometryN
GeometryTypeST_GeometryType
GeomFromTextST_GeomFromText
GeomFromWKBST_GeomFromWKB
GLengthST_Length
InteriorRingNST_InteriorRingN
Intersects
MBRIntersects
IsClosed
ST_IsClosed
IsEmptyST_IsEmpty
IsSimpleST_IsSimple
LineFromTextST_LineFromText
LineFromWKBST_LineFromWKB
LineStringFromTextST_LineFromText
LineStringFromWKBST_LineFromWKB
MLineFromTextST_MLineFromText
MLineFromWKBST_MLineFromWKB
MPointFromTextST_MPointFromText
MPointFromWKBST_MPointFromWKB
MPolyFromTextST_MPolyFromText
MPolyFromWKBST_MPolyFromWKB
MultiLineStringFromTextST_MLineFromText
MultiLineStringFromWKBST_MLineFromWKB
MultiPointFromTextST_MPointFromText
MultiPointFromWKBST_MPointFromWKB
MultiPolygonFromTextST_MPolyFromText
MultiPolygonFromWKBST_MPolyFromWKB
NumGeometriesST_NumGeometries
NumInteriorRingsST_NumInteriorRing
NumPointsST_NumPoints
OverlapsMBROverlaps
PointFromText
ST_PointFromText
PointFromWKBST_PointFromWKB
PointNST_PointN
PolyFromTextST_PolyFromText
PolyFromWKBST_PolyFromWKB
PolygonFromTextST_PolyFromText
PolygonFromWKBST_PolyFromWKB
SRIDST_SRID
StartPointST_StartPoint
TouchesST_Touches
WithinMBRWithin
X
ST_X
YST_Y

If a query uses any of the deprecated aliases, a deprecation warning will be shown:​

But what if these functions are used in stored routines, view definitions or generated column definitions? There is a deprecation warning at create time, but not every time the routine, view or column is used. Luckily, MySQL 5.7 automatically replaces the deprecated function name when it stores view and generated column definitions. That is fine for generated columns, which are new in 5.7, but views could have been defined in an older version of MySQL that didn’t rename the function. So we have to check views in case some were defined in 5.6 or earlier. Stored routines must always be checked.

Based on the list above, we can create a crude function to check if a string contains one of the deprecated function calls:

And then we can use that function to find stored routines that need a closer inspection:

And similarly for views:

This is only a regex matching of the expressions, so there could of course be false positives.

Once the true positives have been fixed, it’s time to look at the changes in function behavior between 5.7 and 8.0.

Functions That Don’t Support Geography

As of 8.0.11 there are a few spatial functions that don’t yet support geography:

  • ST_Area
  • ST_Buffer
  • ST_Centroid
  • ST_ConvexHull
  • ST_Difference
  • ST_Distance (geography support limited to points and multipoints)
  • ST_Envelope
  • ST_Intersection
  • ST_IsClosed
  • ST_MakeEnvelope
  • ST_Simplify
  • ST_SymDifference
  • ST_Union

If these functions are called with geographic data as arguments, they will raise errors. We can define a function to detect these, too:

Note that this function expects that deprecated names have already been changed. E.g., it searches for “ST_Area”, not “Area”.

In this case, we have to search for stored routines, views and generated columns:

In this case, we have not used any of these functions. But what if we have?

These functions are the same in 8.0 and 5.7 as long as the input is Cartesian. But they will fail in 8.0 with an error if the input is geographic. This means that we have to make sure all the data we’re working on is in a Cartesian SRS. We can always have a peek at 8.0 and see which SRIDs refer to geographic SRSs and which refer to projected (i.e., Cartesian) SRSs. Or we can simply say that all our data is in SRID 0.

I recommend using only SRID 0 in 5.7. MySQL 5.7 really doesn’t understand any other SRS than that, so there’s not much point in pretending it does. If all geometries are in SRID 0, the upgrade to 8.0 is much smoother.

In the case of ST_Distance, geography support is currently limited to points and multipoints. This means that it in some cases behaves like a function that doesn’t support geography and returns an error message instead of a result, while it for points and mulitpoints returns a geographic distance.

Functions That Support Geography

While the two above categories of functions will cause error after an upgrade, functions that actually support geography may cause a silent change in behavior. The input may be the same as in 5.7, but 8.0 will compute a different result. Whether it is detected or not depends on the surrounding code.

There are two types of functions that support geography: Those that care about the coordinate system, an those that don’t. Examples of the latter are ST_Dimension and ST_SRID, which return the same result for both Cartesian and geographic data. Those are not much of a problem. The problem occurs in the functions that care about the coordinate system and return different results for geographic and Cartesian data.

In 8.0.11, the following functions return different results for geographic and Cartesian input:

  • MBRContains
  • MBRCoveredBy
  • MBRCovers
  • MBRDisjoint
  • MBREquals
  • MBRIntersects
  • MBROverlaps
  • MBRTouches
  • MBRWithin
  • ST_Contains
  • ST_Crosses
  • ST_Disjoint
  • ST_Distance (geography support limited to points and mulitpoints)
  • ST_Distance_Sphere
  • ST_Equals
  • ST_Intersects
  • ST_IsSimple
  • ST_IsValid
  • ST_Length
  • ST_Overlaps
  • ST_Touches
  • ST_Within
  • ST_X
  • ST_Y

As before, we create a function to detect these function names in a string:

Again, we have to search for stored routines, views and generated columns:

As we can see, this example instance hasn’t used any functions that return different results for geographic and Cartesian input.

If we had used such functions, we would have to check with SRIDs are used, just like we did for functions that don’t support geography. However, if we have used geographic SRIDs, we don’t necessarily have to change to SRID 0. It depends on which result we want. SRID 0 will make sure we get the same result in 5.7 and 8.0, but a geographic SRID will make sure we get a geographic result in 8.0.

Other issues

As mentioned in the beginning, this is a best effort approach at detecting potential problems. The above tests are not necessarily perfect. There may be both false positives and false negatives. And if there is a problem, there’s no solution that will fit all cases. We have to evaluate each case separately.

There are a few cases that I know we have skipped:

  • All spatial functions (whether they care about coordinate systems or not), may raise errors for latitude and longitude values that are out of range if called with a geometry in a geographic SRS.
  • All spatial functions may raise errors for non-existing SRIDs.
  • We have only looked for function calls in stored objects. There could be applications that issue queries with these functions. Detecting those requires monitoring the stream of queries or analyizing the applications.

Incomplete as it is, I hope you enjoyed this walk-through of potential upgrade issues and how to detect them!

Thank you for using MySQL !

About Norvald H. Ryeng

Norvald has been working as a software engineer on the MySQL Optimizer Team since 2011, where he mostly works on GIS. He is also the point of contact for package maintainers in Linux distributions. He holds a PhD in Computer and Information Science from the Norwegian University of Science and Technology.

Leave a Reply