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

Question

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?

Possibly setting the SRID in the 5.7 database before migration is the way to go, but I am worried that will affect the existing system. Any experience of that would be welcome. — Edward Hibbert
– Edward Hibbert, Commented Jul 13, 2021 at 11:25
You can't do this change in place, which means you need to create a target table with the same structure, just with the correct SRID and copy the data over with changing the SRIDs along the way. — Shadow
– Shadow, Commented Jul 13, 2021 at 13:06
From experimenting it looks like MySQL 5.7 lets me set the SRID on the data as an early step in the migration. Then do the upgrade to 8 and ALTER the table to impose the SRID restriction. That seems to work, though it adds migration time. What I would really like is a big "assume all SRIDs are X" config item. — Edward Hibbert
– Edward Hibbert, Commented Jul 13, 2021 at 13:12
bare in mind that st_astext for SRID 4326 will have inverted lat,lon order compared to SRID 0 — Sasa Ninkovic
– Sasa Ninkovic, Commented Jun 15, 2022 at 22:47

OSGI Java · Accepted Answer · 2023-11-07 16:50:07Z

0

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.

answered Nov 7, 2023 at 16:50

OSGI Java

64510 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related