MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
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 name Recommended name
Area ST_Area
AsBinary ST_AsBinary
AsText ST_AsText
AsWKB ST_AsBinary
AsWKT ST_AsText
Buffer ST_Buffer
Centroid ST_Centroid
Contains MBRContains
ConvexHull ST_ConvexHull
Crosses ST_Crosses
Dimension ST_Dimension
Disjoint MBRDisjoint
Distance ST_Distance
EndPoint
ST_EndPoint
Envelope ST_Envelope
Equals
MBREquals
ExteriorRing ST_ExteriorRing
GeomCollFromText ST_GeomCollFromTxt
GeomCollFromWKB ST_GeomCollFromWKB
GeometryCollectionFromText ST_GeomCollFromTxt
GeometryCollectionFromWKB ST_GeomCollFromWKB
GeometryFromText ST_GeomFromText
GeometryFromWKB ST_GeomFromWKB
GeometryN ST_GeometryN
GeometryType ST_GeometryType
GeomFromText ST_GeomFromText
GeomFromWKB ST_GeomFromWKB
GLength ST_Length
InteriorRingN ST_InteriorRingN
Intersects
MBRIntersects
IsClosed
ST_IsClosed
IsEmpty ST_IsEmpty
IsSimple ST_IsSimple
LineFromText ST_LineFromText
LineFromWKB ST_LineFromWKB
LineStringFromText ST_LineFromText
LineStringFromWKB ST_LineFromWKB
MLineFromText ST_MLineFromText
MLineFromWKB ST_MLineFromWKB
MPointFromText ST_MPointFromText
MPointFromWKB ST_MPointFromWKB
MPolyFromText ST_MPolyFromText
MPolyFromWKB ST_MPolyFromWKB
MultiLineStringFromText ST_MLineFromText
MultiLineStringFromWKB ST_MLineFromWKB
MultiPointFromText ST_MPointFromText
MultiPointFromWKB ST_MPointFromWKB
MultiPolygonFromText ST_MPolyFromText
MultiPolygonFromWKB ST_MPolyFromWKB
NumGeometries ST_NumGeometries
NumInteriorRings ST_NumInteriorRing
NumPoints ST_NumPoints
Overlaps MBROverlaps
PointFromText
ST_PointFromText
PointFromWKB ST_PointFromWKB
PointN ST_PointN
PolyFromText ST_PolyFromText
PolyFromWKB ST_PolyFromWKB
PolygonFromText ST_PolyFromText
PolygonFromWKB ST_PolyFromWKB
SRID ST_SRID
StartPoint ST_StartPoint
Touches ST_Touches
Within MBRWithin
X
ST_X
Y ST_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 !