MySQL Bugs: #43544: mysqldump should convert GEOMETRY columns to hex with --hex-blob option

Bug #43544	mysqldump should convert GEOMETRY columns to hex with --hex-blob option
Submitted:	10 Mar 2009 20:02	Modified:	28 Aug 2014 14:46
Reporter:	Michael Newton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
I get the following error when attempting to import from a file created by mysqldump:

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

mysqldump was run with the --hex-blob option, but opening the file reveals binary data in the query.

How to repeat:

CREATE DATABASE tester;
USE tester;
CREATE TABLE t1 (c1 POINT);
INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));

From the command line:
mysqldump -necqt --compact --hex-blob tester

Results in:
INSERT INTO `t1` (`c1`) VALUES ('\0\0\0\0\0\0\0\0\0\0\0\0\0??\0\0\0\0\0\0??');

where binary characters are indicated by ??

Suggested fix:
Add MYSQL_TYPE_GEOMETRY to the type of fields that are escaped, which would result in this output from the above command:

INSERT INTO `t1` (`c1`) VALUES (0x000000000101000000000000000000F03F000000000000F03F);

Thank you for the report.

Verified as described.

patch against 5.0

Attachment: mysqldump-50.diff (application/octet-stream, text), 519 bytes.

patch against 5.1

Attachment: mysqldump-51.diff (application/octet-stream, text), 519 bytes.

patch against 5.5

Attachment: mysqldump-55.diff (application/octet-stream, text), 516 bytes.

patch against trunk

Attachment: mysqldump-trunk.diff (application/octet-stream, text), 519 bytes.

patch against 6.0

Attachment: mysqldump-60.diff (application/octet-stream, text), 519 bytes.

I think that's about it right? I'm not a C programmer, but it's been 18 months since this was verified, I'm guessing nothing's going to happen if I don't do it myself.

Thanks Michael but this fix does not work when '-T' option is used with mysqldump. In that case mysqldump uses 'SELECT ... INTO OUTFILE' to dump the tables, which is not covered by this fix. I have not found a solution yet. Any suggestion is welcomed. Thanks!

Will

This bug should be changed to critical severity since you effectively cannot backup whole databases using mysqldump since the result contains binary data. This means the geometry data can effectively not be exported without creating specialised exporting and importing functions.

Agreed, this bug should be raised to Critical.

Adds --blob-geom to mysqldump which makes --hex-blob treat GEOMETRY fields as blobs

Attachment: mysql-blobgeom.patch (text/x-diff), 2.34 KiB.

Added patch that does pretty much the same thing as Michaels patch, except only when --blob-geom parameter is added.

Please note that -T not working is *no excuse at all* to make this patch wait for another 3 years: --hex-dump doesn't actually do anything with -T.

Michael, Martin,

please sign OCA as described in the "Contributions" tab in this bug report, then register your contributions. Otherwise we (Oracle) could not include them into our code.

Noted in 5.6.21, 5.7.5 changelogs.

Internally, spatial data types such as Geometry are represented as
BLOB values, so when invoked with the --hex-dump option, mysqldump
now displays spatial values in hex.

revno: 6103
committer: mithun <mithun.c.y@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-08-01 13:56:47 +0530
message:
  Bug #11752369 : MYSQLDUMP SHOULD CONVERT GEOMETRY COLUMNS TO
                  HEX WITH --HEX-BLOB OPTION
  
  ISSUE :
  -------
  If mysqldump is executed with --hex-blob option, all the
  blob type data should be converted to hex value. The
  geometry data are also blob, and their values should be
  dumped in hexadecimal form. But mysqldump dumps geometry
  data in binary form.
  
  Fix :
  -----
  If value being dumped is of geometry type and --hex-blob is
  set then mysqldump will dump such values in hexadecimal form.

I am still having issues with the Geometry backup issue despite having use hexadecimal for blob set.

I'm having an issue with this and it's making it next to impossible to migrate a DB, is there any sort of work around for this?

For those still having issues - I encountered this problem with empty geometry values. Removing the empty geometry values solved the problem. Check for empty values with SELECT ... WHERE IsEmpty(geom).