Whew, this turned out to be quite problematic for one of my oldest databases with spatial data. The problem turned out to be that MySQL5.6 was quite flexible with how you used spatial datatypes. I had a table with a POLYGON data type, but I had stored a bunch of LINESTRING geometries instead. MySQL5.6 … no problem! MySQL8.0 … big problem!
The solution was a giant search/replace on the phpmyadmin exported SQL file replacing LINESTRING with a POLYGON instead. It was a bit tricky though as shown below:
‘LINESTRING(48.8988768 -180,78.758606 -180,78.758606 -108,48.8988768 -108,48.8988768 -180)’
becomes
ST_GeomFromText(‘POLYGON((48.8988768 -180,78.758606 -180,78.758606 -108,48.8988768 -108,48.8988768 -180)’)
Note the extra call to ST_GeomFromText and the addition left paren before the start of the old linestring as well as the additional closing right paren at the very end AFTER the apostrophe.
Also complicating this search/replace was that some of the data was indeed already POLYGON data, which also was in the export from phpmyadmin.
For my other smaller databases without spatial data, or where the spatial data matched the declared data type, I did not run into any problems. The strategy was an export from mysqldump into an sql file which I then sourced on the target server.
For my large databases (including the one from this post), I did have problems. For this one, it was mainly the spatial data. For my di2stats.com database, it was the sheer size of the database that was problematic.
2 comments