Back up a table with a GEOMETRY column using mysqldump?

Question

I recently created a MySQL table with a column of type GEOMETRY.

When I back up the table with mysqldump, it outputs my geometry column as a quoted string, containing some escaped characters like \0, and also some characters that look like raw binary bytes in the upper-ASCII range.

When I try to restore the dump to another database it fails with an error:

"Cannot get GEOMETRY object from the data you send to the Geometry field".

I tried adding --hex-blob to my command line but this does not change the output or fix the problem.

I'm sure someone didn't create a data type in MySQL and forget to include a way to back it up. What am I missing?

Thanks.

Predestine

Answer 1

In my case, this error appeared specifically with empty geometry values in a non-null geometry column.

In my case, the empty geometries were legitimate cases of unknown geometry, so I addressed this by changing the column to allow null values, and then running UPDATE ... SET geom = NULL WHERE IsEmpty(geom);

After this, I was able to re-run mysqldump and successfully import the resulting sql into a separate database.

(To be honest, I'm not sure how the empty geometry values got there in the first place - I don't even know the syntax to create an empty geometry value)

Rubio

Answer 2

Frank, this appears to be a long-standing (and still open) bug with mysqldump. See http://bugs.mysql.com/bug.php?id=43544.

As a workaround, you may be able to use the ogr2ogr tool to export the data to a shapefile, and then import it back into the database. See http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

Eyepiece

Answer 3

I can confirm that this problem doesn't occur when using the Export / Import Data functions in MySQL Workbench. http://www.mysql.com/products/workbench/

Gourmandise

Answer 4

in my case, it was because the GEOMETRY dumped as a binary_ 'XXX', in that case I re-dump it using mysqldump --hex-blob and then I can retore it without any issue

List

Answer 5

Recently encountered what appeared on the surface to be this exact issue. Exporting using mysqldump from version 5.7.33 and importing using SQL Workbench into a fresh version 5.7.33 database. In my case the error message was identical but it related to a column of type POINT.

Eventually identified that the source file had been amended using VSCode which had partially and subtly scrambled the _binary 'zzzzzz' values.

Alarm

Answer 6

I once faced this issue but manage to passthrough by using gzip. Please check my sample commands: Export:

mysqldump -u root -p db_name | gzip > dump.sql.gz

Import:

pv dump.sql.gz | gunzip | mysql -u root -p other_db

Difficulty

And why would gzip/gunzip help? – Xylina

I confirm that it was useless in my case – Vibrato