There are many changes to spatial functions in MySQL 8.0:
- Old aliases for functions have been removed (after being deprecated in 5.7)
- Functions that don’t support geographic computations raise errors if called with geographic data in their arguments
- Many functions support geographic computations
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:
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SELECT SRID(GeomFromText('POINT(0 0)')) AS srid; +------+ | srid | +------+ | 0 | +------+ 1 row in set, 2 warnings (0,00 sec) Warning (Code 1287): 'SRID' is deprecated and will be removed in a future release. Please use ST_SRID instead Warning (Code 1287): 'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
DELIMITER | CREATE FUNCTION contains_deprecated_spatial_function_alias(s TEXT) RETURNS INT BEGIN RETURN s RLIKE '[[:<:]]Area[[:>:]]' OR s RLIKE '[[:<:]]AsBinary[[:>:]]' OR s RLIKE '[[:<:]]AsText[[:>:]]' OR s RLIKE '[[:<:]]AsWKB[[:>:]]' OR s RLIKE '[[:<:]]AsWKT[[:>:]]' OR s RLIKE '[[:<:]]Buffer[[:>:]]' OR s RLIKE '[[:<:]]Centroid[[:>:]]' OR s RLIKE '[[:<:]]Contains[[:>:]]' OR s RLIKE '[[:<:]]ConvexHull[[:>:]]' OR s RLIKE '[[:<:]]Crosses[[:>:]]' OR s RLIKE '[[:<:]]Dimension[[:>:]]' OR s RLIKE '[[:<:]]Disjoint[[:>:]]' OR s RLIKE '[[:<:]]Distance[[:>:]]' OR s RLIKE '[[:<:]]EndPoint[[:>:]]' OR s RLIKE '[[:<:]]Envelope[[:>:]]' OR s RLIKE '[[:<:]]Equals[[:>:]]' OR s RLIKE '[[:<:]]ExteriorRing[[:>:]]' OR s RLIKE '[[:<:]]GeomCollFromText[[:>:]]' OR s RLIKE '[[:<:]]GeomCollFromWKB[[:>:]]' OR s RLIKE '[[:<:]]GeometryCollectionFromText[[:>:]]' OR s RLIKE '[[:<:]]GeometryCollectionFromWKB[[:>:]]' OR s RLIKE '[[:<:]]GeometryFromText[[:>:]]' OR s RLIKE '[[:<:]]GeometryFromWKB[[:>:]]' OR s RLIKE '[[:<:]]GeometryN[[:>:]]' OR s RLIKE '[[:<:]]GeometryType[[:>:]]' OR s RLIKE '[[:<:]]GeomFromText[[:>:]]' OR s RLIKE '[[:<:]]GeomFromWKB[[:>:]]' OR s RLIKE '[[:<:]]GLength[[:>:]]' OR s RLIKE '[[:<:]]InteriorRingN[[:>:]]' OR s RLIKE '[[:<:]]Intersects[[:>:]]' OR s RLIKE '[[:<:]]IsClosed[[:>:]]' OR s RLIKE '[[:<:]]IsEmpty[[:>:]]' OR s RLIKE '[[:<:]]IsSimple[[:>:]]' OR s RLIKE '[[:<:]]LineFromText[[:>:]]' OR s RLIKE '[[:<:]]LineFromWKB[[:>:]]' OR s RLIKE '[[:<:]]LineStringFromText[[:>:]]' OR s RLIKE '[[:<:]]LineStringFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MLineFromText[[:>:]]' OR s RLIKE '[[:<:]]MLineFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MPointFromText[[:>:]]' OR s RLIKE '[[:<:]]MPointFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MPolyFromText[[:>:]]' OR s RLIKE '[[:<:]]MPolyFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MultiLineStringFromText[[:>:]]' OR s RLIKE '[[:<:]]MultiLineStringFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MultiPointFromText[[:>:]]' OR s RLIKE '[[:<:]]MultiPointFromWKB[[:>:]]' OR s RLIKE '[[:<:]]MultiPolygonFromText[[:>:]]' OR s RLIKE '[[:<:]]MultiPolygonFromWKB[[:>:]]' OR s RLIKE '[[:<:]]NumGeometries[[:>:]]' OR s RLIKE '[[:<:]]NumInteriorRings[[:>:]]' OR s RLIKE '[[:<:]]NumPoints[[:>:]]' OR s RLIKE '[[:<:]]Overlaps[[:>:]]' OR s RLIKE '[[:<:]]PointFromText[[:>:]]' OR s RLIKE '[[:<:]]PointFromWKB[[:>:]]' OR s RLIKE '[[:<:]]PointN[[:>:]]' OR s RLIKE '[[:<:]]PolyFromText[[:>:]]' OR s RLIKE '[[:<:]]PolyFromWKB[[:>:]]' OR s RLIKE '[[:<:]]PolygonFromText[[:>:]]' OR s RLIKE '[[:<:]]PolygonFromWKB[[:>:]]' OR s RLIKE '[[:<:]]SRID[[:>:]]' OR s RLIKE '[[:<:]]StartPoint[[:>:]]' OR s RLIKE '[[:<:]]Touches[[:>:]]' OR s RLIKE '[[:<:]]Within[[:>:]]' OR s RLIKE '[[:<:]]X[[:>:]]' OR s RLIKE '[[:<:]]Y[[:>:]]'; END| DELIMITER ; |
And then we can use that function to find stored routines that need a closer inspection:
|
1
2
3
4
5
6
|
mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION -> FROM INFORMATION_SCHEMA.ROUTINES -> WHERE contains_deprecated_spatial_function_alias(ROUTINE_DEFINITION) -> AND ROUTINE_NAME != 'contains_deprecated_spatial_function_alias' -> AND ROUTINE_SCHEMA != 'sys'\G Empty set (0,00 sec) |
And similarly for views:
|
1
2
3
4
5
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION -> FROM INFORMATION_SCHEMA.VIEWS -> WHERE contains_deprecated_spatial_function_alias(VIEW_DEFINITION) -> AND TABLE_SCHEMA != 'sys'\G Empty set (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
DELIMITER | CREATE FUNCTION contains_nongeographic_spatial_function(s TEXT) RETURNS INT BEGIN RETURN s RLIKE '[[:<:]]ST_Area[[:>:]]' OR s RLIKE '[[:<:]]ST_Buffer[[:>:]]' OR s RLIKE '[[:<:]]ST_Centroid[[:>:]]' OR s RLIKE '[[:<:]]ST_ConvexHull[[:>:]]' OR s RLIKE '[[:<:]]ST_Difference[[:>:]]' OR s RLIKE '[[:<:]]ST_Distance[[:>:]]' OR s RLIKE '[[:<:]]ST_Envelope[[:>:]]' OR s RLIKE '[[:<:]]ST_Intersection[[:>:]]' OR s RLIKE '[[:<:]]ST_IsClosed[[:>:]]' OR s RLIKE '[[:<:]]ST_MakeEnvelope[[:>:]]' OR s RLIKE '[[:<:]]ST_Simplify[[:>:]]' OR s RLIKE '[[:<:]]ST_SymDifference[[:>:]]' OR s RLIKE '[[:<:]]ST_Union[[:>:]]'; END| DELIMITER ; |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION -> FROM INFORMATION_SCHEMA.ROUTINES -> WHERE contains_nongeographic_spatial_function(ROUTINE_DEFINITION) -> AND ROUTINE_NAME != 'contains_nongeographic_spatial_function' -> AND ROUTINE_SCHEMA != 'sys'\G Empty set (0,00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION -> FROM INFORMATION_SCHEMA.VIEWS -> WHERE contains_nongeographic_spatial_function(VIEW_DEFINITION) -> AND TABLE_SCHEMA != 'sys'\G Empty set (0,00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, GENERATION_EXPRESSION -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE contains_nongeographic_spatial_function(GENERATION_EXPRESSION) -> AND TABLE_SCHEMA != 'sys'\G Empty set (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
DELIMITER | CREATE FUNCTION contains_geographic_spatial_function(s TEXT) RETURNS INT BEGIN RETURN s RLIKE '[[:<:]]MBRContains[[:>:]]' OR s RLIKE '[[:<:]]MBRCoveredBy[[:>:]]' OR s RLIKE '[[:<:]]MBRCovers[[:>:]]' OR s RLIKE '[[:<:]]MBRDisjoint[[:>:]]' OR s RLIKE '[[:<:]]MBREquals[[:>:]]' OR s RLIKE '[[:<:]]MBRIntersects[[:>:]]' OR s RLIKE '[[:<:]]MBROverlaps[[:>:]]' OR s RLIKE '[[:<:]]MBRTouches[[:>:]]' OR s RLIKE '[[:<:]]MBRWithin[[:>:]]' OR s RLIKE '[[:<:]]ST_Contains[[:>:]]' OR s RLIKE '[[:<:]]ST_Crosses[[:>:]]' OR s RLIKE '[[:<:]]ST_Disjoint[[:>:]]' OR s RLIKE '[[:<:]]ST_Distance[[:>:]]' OR s RLIKE '[[:<:]]ST_Distance_Sphere[[:>:]]' OR s RLIKE '[[:<:]]ST_Equals[[:>:]]' OR s RLIKE '[[:<:]]ST_Intersects[[:>:]]' OR s RLIKE '[[:<:]]ST_IsSimple[[:>:]]' OR s RLIKE '[[:<:]]ST_IsValid[[:>:]]' OR s RLIKE '[[:<:]]ST_Length[[:>:]]' OR s RLIKE '[[:<:]]ST_Overlaps[[:>:]]' OR s RLIKE '[[:<:]]ST_Touches[[:>:]]' OR s RLIKE '[[:<:]]ST_Within[[:>:]]' OR s RLIKE '[[:<:]]ST_X[[:>:]]' OR s RLIKE '[[:<:]]ST_Y[[:>:]]'; END| DELIMITER ; |
Again, we have to search for stored routines, views and generated columns:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION -> FROM INFORMATION_SCHEMA.ROUTINES -> WHERE contains_geographic_spatial_function(ROUTINE_DEFINITION) -> AND ROUTINE_NAME != 'contains_geographic_spatial_function' -> AND ROUTINE_SCHEMA != 'sys'\G Empty set (0,00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION -> FROM INFORMATION_SCHEMA.VIEWS -> WHERE contains_geographic_spatial_function(VIEW_DEFINITION) -> AND TABLE_SCHEMA != 'sys'\G Empty set (0,00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, GENERATION_EXPRESSION -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE contains_geographic_spatial_function(GENERATION_EXPRESSION) -> AND TABLE_SCHEMA != 'sys'\G Empty set (0,00 sec) |
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 !