Upgrading to MySQL 8.0 with Spatial Data

The big change from MySQL 5.7 to 8.0 when it comes to spatial data, is the support for multiple spatial reference systems and geographic computations. This means that the SRIDs of geometries actually have meaning and affect computations. In 5.7 and earlier, however, the SRIDs are ignored, and all computations are Cartesian. This causes a few incompatibilities when upgrading to 8.0.

Incompatible Changes

Incompatibility and change in behavior is always a tough decision. In this case, we didn’t really have a choice. We had to change behavior to support geography. But geography support is not complete in 8.0 — there are still functions that only support Cartesian data — so in some cases we did have a choice whether to do break things now or later. We decided that it’s best to make a clean break now and avoid trickling changes over the next versions.

To summarize, the incompatible changes when going from MySQL 5.7 to 8.0 are:

1. A large number of spatial functions will start computing results on an ellipsoid instead of on a flat plane (if the geometry data is on an ellipsoid). In 5.7 and earlier, everything was computed on a flat Cartesian plane. Also, the order of coordinate axes matter more in 8.0. (See the Geography in MySQL 8.0  and Axis Order in Spatial Reference Systems blog posts for detailed info.)
2. A number of spatial functions will start raising errors instead of returning a result if given geographic input. This is done so that we later can enable geographic computations for these functions without causing further breakage. (The Geography in MySQL 8.0  blog post has an example at the end.)
3. A number of aliases for spatial functions that were deprecated in 5.7 have been removed in 8.0. Calling these functions will result in an error. (See the Spring Cleaning in the GIS Namespace  blog post for detailed info.)
4. Spatial indexes will not be used unless the indexed column has an SRID specifier. The specifier is new in 8.0, so indexes created before the upgrade will not be used by the optimizer. (See the Geographic Indexes in InnoDB  blog post for detailed info.)

That’s quite a list. However, these are, with the exception of number 3 and 4, restricted to cases where the SRID is non-zero. If all data and all queries use only SRID 0, behavior is the same as before.

Before Upgrading

The main culprit of behavior change between 5.7 and 8.0 is SRIDs other than 0. MySQL 5.7 doesn’t really support any other SRIDs — they are simply ignored, and SRID 0 is assumed. If all geometries are changed to SRID 0 before upgrade, a lot of the potential problems become non-issues, and the upgrade process is much easier.

Unfortunately, the ST_SRID(geom, 0) function call to set the SRID is not available in 5.7, but there is a workaround: ST_GeomFromWKB(ST_AsBinary(geom)) . Assuming that we have a table with a geometry column:

mysql> SHOW CREATE TABLE points\G
*************************** 1. row ***************************
Table: points
Create Table: CREATE TABLE `points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `pos` point NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0,00 sec)

We want to set the SRID of all geometries in this column to 0:

mysql> UPDATE points SET pos = ST_GeomFromWKB(ST_AsBinary(pos));
Query OK, 0 rows affected (0,00 sec)
Rows matched: 10 Changed: 0 Warnings: 0

What happens here, is that ST_AsBinary exports the geometry to a binary format that doesn’t contain the SRID, and then ST_GeomFromWKB constructs a geometry from that representation. Since we don’t specify any SRID, it defaults to SRID 0.

Upgrading

Incompatibilities have consequences when upgrading. Some are simple, e.g., getting an error when calling a function that has been removed, but other are more complex:

• Some queries will return new results (because of incompatible change number 1) or raise errors (because of 2 and 3) after upgrade. This may also affect stored routines and views.
• Stored generated columns using spatial functions may store incorrect values after upgrade (because of 1). In some cases the function expression can’t be evaluated (because of 2 and 3).
• Indexes on generated columns (stored or virtual) may store incorrect values after upgrade (because of 1). In some cases the function expression can’t be evaluated (because of 2 and 3).
• After the upgrade, it is necessary to do ALTER TABLE MODIFY COLUMN to change all indexed spatial columns to include an SRID specifier (because of 4).
• After the upgrade (and after modifying the columns), all spatial indexes must be dropped and recreated (because of 4).
• After the upgrade, geographic (ellipsoidal) data can only be indexed in InnoDB, not in MyISAM (because of 4, and the fact that MyISAM doesn’t support geographic indexes).

We can group these issues into two: functions that are renamed or change behavior, and spatial indexes.

In an earlier post, we covered steps to discover stored routines, views and generated columns that use spatial functions . There is no simple way to detect that a function has changed behavior in a way that affects a particular expression, so evaluating these is a manual process. The good thing is that this check can be done before upgrading.

In another post, we looked at upgrading spatial indexes . This may or may not be a candidate for automation. Fixing problems where an indexed column contains geometries in several SRIDs is a manual job, but setting SRID restrictions and dropping and recreating indexes is a job that can be automated. Some of these steps have to be done after upgrading.

After Upgrading

After completing the upgrade, we are in the new and wonderful world of MySQL 8.0 spatial support. There is consistency, so spatial reference systems that are used by indexes can’t be changed in any substantial way, and geometries can only be created in existing spatial reference systems. This prevents similar problems in future upgrades.

The functions that don’t yet support geography will raise errors if called with geographic input. Later, when geography support is added, there is no change of behavior for other statements than those that used to fail with an error message.

In summary, 8.0 is much better suited for future upgrades without affecting existing data and queries. The upgrade to 9.0, whenever that happens, should be much more straight forward.

Thank you for using MySQL !