4

I've been upgrading a website from Mysql 5.6 to 5.7. When restoring a backup from mysqldump, which has worked for 10 years (unchanged) under Mysql 5.1-5.6, it no longer works under MySQL 5.7.

Specifically, the first row of geometry data fails the restore:

ERROR 1416 (22003) at line 1580 Cannot get geometry object from data you send to the GEOMETRY field

So, this is valid geometry, but not any more.

Remedies attempted:

  • Switch mysqldump to --hex-blob
  • Try using astext( <some geometry> ) before importing
  • Tired hand loading various geometry rows from the backup, all fail
Joseph Lust
  • 19,340
  • 7
  • 85
  • 83

1 Answers1

5

It looks like MySQL 5.7 is more strict in Geometry types than MySQL 5.6. As such, data that was valid in 5.6 is now invalid in 5.7.

This was the fix to MySQL Bug #76337, in release MySQL 5.7.8.

In this case, a LINESTRING was being stored in a column of type POINT. This worked for nearly a decade, but no more. Changing the column to type LINESTRING fixed the above loading error.

-- The Fix - run on MySQL 5.6 database before Upgrade/Export
ALTER TABLE routes MODIFY COLUMN route_path LINESTRING;

Other Failure Modes

This bug also manifest on geometric columns where they were able to persist NULL geometries (but not being officially NULL). MySQL IS NULL would say not null, but asText( myGeo ) returned NULL under MySQL 5.7. Exporting these to a string in MySQL 5.6 returned '', empty string. Thus the '' geometry output from 5.6 was and invalid input for 5.7.

The fix was to null these out.

-- Convert NULL geometries to actual NULL's
UPDATE myTable SET myGeo = NULL WHERE asText(myGeo) IS NULL;
Joseph Lust
  • 19,340
  • 7
  • 85
  • 83
  • I am having this exact problem! I just can't solve it! I have even scaled it back to 1 record so I know that it is POINT data and I know that it is not null. – Antony D'Andrea Jan 25 '18 at 16:24
  • asText on 5.6 produces 'POINT(54.89731 -12.884215)', the field type is POINT in both 5.6 and 5.7. But something happens in the mysqldump – Antony D'Andrea Jan 25 '18 at 16:28
  • @AntonyD'Andrea - 2 years later! what about `ST_IsEmpty`. It will return '1' if the geometry value is invalid or NULL if it is null. 0 if it is valid. – Anthony Nov 08 '19 at 22:24