mysqldump Cannot get geometry object from data you send to the GEOMETRY field already added --hex-blob

Question

I'm trying to do a mysql dump and heres the command I'm using:

mysqldump --hex-blob -u user -p database > dump.sql

I was told this should resolve the binary point field how i still get this error when importing using mysql database < dump.sql

Cannot get geometry object from data you send to the GEOMETRY field

Not sure what to do at this point

I'm exporting from version 5.6.45 to 5.7.27

However, the import looks good and it contains all of the records and when i select the geometry data it looks like a binary data am i okay?

// 45K records found

select * from listingsaws where ST_IsEmpty(`location`) IS NULL;

// updated 0 records

UPDATE listingsaws SET `location` = NULL WHERE ST_IsEmpty(`location`) IS NULL;

// 45K records found

select * from listingsaws where asText(`location`) IS NULL;

//updated 0 records

UPDATE listingsaws SET `location` = NULL WHERE asText(`location`) IS NULL;

This might be useful (although not specific to mysqldump) : https://dba.stackexchange.com/questions/170787/cannot-get-geometry-object-from-data-you-send-to-the-geometry-field — Anthony, Nov 08 '19 at 22:00
and also https://stackoverflow.com/questions/17480559/back-up-a-table-with-a-geometry-column-using-mysqldump — Anthony, Nov 08 '19 at 22:01
oh and this looks really promising : https://stackoverflow.com/questions/44739009/cannot-restore-geometry-backup-mysql-5-7-error — Anthony, Nov 08 '19 at 22:03

Anthony · Answer 1 · 2019-11-08T22:21:33.483

Apparently 5.7 is more strict than 5.6 with GEOMETRY type fields.

The easiest thing to try first is to ensure that any empty geometry fields are properly set to NULL, which you can do with:

UPDATE myTable 
SET myGeo = NULL 
WHERE ST_IsEmpty(myGeo) IS NULL;

Otherwise, you can look at these questions which delve into this sort of issue further:

1 Answers1