MySQL Polygon (Spatial) Insert Statement works sporadically

Question

I know that MySQL doesn't have great spatial support at the moment, but is it that buggy that my insert statements would work sporadically? In general, longer coordinates seem to be failing on insert (there is no notice of them failing though, they just insert as NULL or show 0 rows affected on update). I believe though that I've seen large ones work and small ones fail. Here's my insert statement:

UPDATE places SET geom = PolygonFromText('POLYGON((-122.225255 37.890342,-122.22343 37.891445,-122.220923 37.89297...)),1') WHERE id=16

The only semi-logical things I can think of pertain to size/memory, or maybe invalid coordinates? Anyone have any ideas?

Answer 1

By default, MySQL has a maximum packet size of 1 MB. This can easily cause INSERT statements with complex polygons to fail. You can check your maximum size in bytes using

SHOW VARIABLES LIKE 'max_allowed_packet'

The MySQL docs explain a few different ways to adjust this parameter.

Answer 2

From the example you linked, it looks you're giving MySQL a non-closed polygon. MySQL is happily accepting it and inserting null into the table instead. Here's an example:

CREATE TABLE testing (geom geometry);
INSERT INTO testing (geom) VALUES 
    (PolygonFromText('POLYGON ((0 0, 0 1, 1 1, 1 0, 0.00000003 0))'));

Running a SELECT on this table shows that the geometry field is null. However, fixing the polygon results in a successful INSERT:

INSERT INTO testing (geom) VALUES 
    (PolygonFromText('POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))'));