CODEWITHSUNDEEP
mysqldatabase-migrationmysql-8.0spatial-index
Edward Hibbert
Edward Hibbert

Reputation: 137

MySQL 5.7 -> 8 migration: how to change SRID of existing MySQL geometry column?

I'm migrating a large database from MySQL 5.7 to 8. It contains geometry columns and spatial indexes. It's a live system and so downtime for the migration is an issue.

MySQL 8 ignores spatial indexes unless the column has an SRID. The recommendation here is to redefine the column to have an explicit SRID attribute and recreate the SPATIAL index.

I'm trying to do this but running into a chicken/egg situation:

mysql>  UPDATE noticeboards SET position = ST_SRID(position, 4236);
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID
of the column 'position'. The SRID of the geometry is 4236, but the
SRID of the column is 0. Consider changing the SRID of the geometry or
the SRID property of the column.

Ok, I'll try that:

mysql> ALTER TABLE noticeboards MODIFY position Geometry NOT NULL SRID 4326;
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 
'position'. The SRID of the geometry is 0, but the SRID of the column is 4326. 
Consider changing the SRID of the geometry or the SRID property of the column.

If I TRUNCATE the table, then I can do the ALTER - but some of these tables have foreign keys, and so truncating is a horrific prospect.

Am I missing a way to forcibly convert existing data from the 5.7 default SRID of 0 to another value?

Upvotes: 2

Views: 1726

Answers (1)

OSGI Java
OSGI Java

Reputation: 645

Create a new column with the desired SRID and then copy the SRID 0 data into it. Drop the SRID 0 column and rename the new column to match the original column name. You will need to also address any constraints during the process.

Upvotes: 0

Related Questions