SQLyog Copy Table with geometry field failing with Cannot get geometry object from data you send to the GEOMETRY field

Question

I'm trying to perform a Copy Table(s) To Different Host/Database in mySql using the SQLyog IDE, and am facing the following error upon copying a table that has 2 geometry fields:

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

There are several other SO questions on this error, but most of the time the conclusive answer is that this is likely happening due an attempt to insert an empty string (this article claims geometry fields accept NULL values).

In my case, there seems to be nothing to do with NULL or empty strings anyway.

I was able to locate the 1st insert statement that fails with this error. Here's what it looks like:

(
    45,
    '2016-01-26 11:44:13',
    'a',
    '',
    0,
    0,
    3,
    100,
    1,
    1,
    -- 1st geometry field
    '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
    -- 2nd geometry field
    '\0\0\0\0\0\0\0\0\0\0\0\0\0”¯oM¿”¯oM¿”¯oM¿”¯oM?”¯oM?”¯oM?”¯oM?”¯oM¿”¯oM¿”¯oM¿',
    'Geodata',
    - 1,
    - 1,
    1,
    - 1
)

Another example of another failing insert:

(
    13853,
    '2016-01-26 11:44:13',
    'test move',
    '',
    3,
    0,
    1251,
    0,
    1,
    0,
    '\0\0\0\0\0\0\0kÛÚeA@Lˆv¡@@',
    '\0\0\0\0\0\0\0\0\0\0\0\0\0¬Q^ÓdA@Ž„ì$Ø\n@@¬Q^ÓdA@Œ\0c@@)eW^eA@Œ\0c@@)eW^eA@Ž„ì$Ø\n@@¬Q^ÓdA@Ž„ì$Ø\n@@',
    'test move',
    - 1,
    - 1,
    2913,
    - 1
)

If I compare these to prior inserts in the Copy Table that do execute without errors (I see them in the target table), here's what the first 2 look like:

    (
        31,
        '2016-01-26 11:44:13',
        'Route 1',
        '',
        2,
        0,
        3,
        0,
        1,
        1,
        '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
        '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
        'Geodata',
        - 1,
        - 1,
        1,
        - 1
    ),
    (
        32,
        '2016-01-26 11:44:13',
        'Route 2',
        '',
        2,
        0,
        3,
        0,
        1,
        1,
        '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
        '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
        'Geodata',
        - 1,
        - 1,
        1,
        - 1
    )

The only difference my eyes can see between the working inserts and the non-working ones is that the latter contain data in an I-do-not-know-what charset, whereas the working ones not (their data seems to be set to null/default value in the geometry data type terms).

There's anything wrong with the non-working inserts you can tell just by looking at them ?

P.S.: One team member claims he got over this problem with changing MySQL Server's my.ini file - by changing the setting max_allowed_packet from 4M (default) to 100M.

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=100M

I restarted my machine, but that did not help, keep getting the same error.

I tried dumping with mySql Workbench, the dump looks like sqlYog's one (and workbench's one shows the use of the setting --default-character-set=utf8, in case there is a charset issue, I am not sure if Unicode would be an option over UTF8 or not). Tried Workbench's Schema Transfer Wizard, same thing, copying this table fails with the same error on the same records. — Veverke, Nov 23 '16 at 09:41
Do those records where their insert fails look the same (i.e. strange character data) in the source database/table prior to copy? May sound like a stupid question, but answering it rules out source data. — Jonathon Ogden, Dec 04 '16 at 13:12
First outcome from your commentary is that I discovered mySql workbench has better built-in support for spatial types, in that it offers visual display for geometry types: in binary format, text (supporting some 4 standard spatial types text-notations) and image representation. So thank you for that. Will check what you suggested sometime soon. — Veverke, Dec 05 '16 at 09:49

score 0 · Accepted Answer · answered May 17 '17 at 14:01

The following does not help with performing a Copy Table(s) To Different Host/Database from the SQLYog IDE, but shows what a correct INSERT statement looks like.

So, to recap:

Problem: How does one insert records with spatial/geometry fields in mySql ?

Solution: If you are querying a table in order to copy its records into another one, you need first to make sure that you read them in a proper format, a format that can be used by mySql function GeomFromText, used to create a spatial/geometry value out of a text representation of it. In order to read spatial values in such text representation, you need to use function AsText.

Here's an illustration:

Then, the insert statements values just need to wrap these spatial objects text representations in a call to function GeomFromText, like (for a polygon)

GEOMFROMTEXT('POLYGON((-84.4920600543037 10.4982373909963,-84.4920600543037 10.4984201128037,-84.4918773324963 10.4984201128037,-84.4918773324963 10.4982373909963,-84.4920600543037 10.4982373909963))')

So now it is a simple matter to dump a table with spatial fields into another, either via SQL or a simple app to do that (what I did).

SQLyog Copy Table with geometry field failing with Cannot get geometry object from data you send to the GEOMETRY field

1 Answers1