0

We are using AWS Aurora/MySQL, engine version 5.7.mysql_aurora.2.09.0 and we have a spatial index on a table with ~10,000,000 rows:

CREATE TABLE `foo` (
  ...
  `lat` decimal(10,6) DEFAULT NULL,
  `lng` decimal(10,6) DEFAULT NULL,
  ...
  `geo_polyline` linestring DEFAULT NULL,
  ...
  `geo_location` point GENERATED ALWAYS AS (point(`lng`,`lat`)) STORED NOT NULL,
  ...
  SPATIAL KEY `geo_location_index` (`geo_location`),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When we do a query like this:

SELECT COUNT(*)  from `foo`
where
        st_intersects(st_geomfromtext('POLYGON ((16.2 48.1, 16.4 48.1, 16.4 48.2, 16.2 48.2, 16.2 48.1))'), geo_polyline)
    and
        st_contains(st_geomfromtext('POLYGON ((16.2 48.1, 16.4 48.1, 16.4 48.2, 16.2 48.2, 16.2 48.1))'), geo_location);

the server crashes. This is repeatable: it happens every time.

Has anyone else experienced anything like this and does anyone have any ideas for workarounds?

Dave L
  • 1
  • Does your server crash if you run the query with ONLY lines 1,2 and 5? – Wilson Hauck Dec 05 '20 at 16:11
  • 1
    @WilsonHauck > Does your server crash if you run the query with ONLY lines 1,2 and 5? No it doesn't. – Dave L Dec 07 '20 at 11:36
  • Could it be that you are using DATATYPEs that are not of the SPATIAL world on line 3? – Wilson Hauck Dec 07 '20 at 13:20
  • Sorry, @WilsonHauck, I don't understand how a polyline or a polygon can be "not of the SPATIAL world"? – Dave L Dec 09 '20 at 17:31
  • Could your column named geo_polyline become NOT NULL rather than DEFAULT NULL? If it could be changed, then you could have an index on the column per this URL - https://dev.mysql.com/doc/refman/5.7/en/spatial-type-overview.html and the query may not crash your server. My best guess. – Wilson Hauck Dec 09 '20 at 18:04
  • Or this wild idea of .....16.2 48.2, 16.2 48.1))'), geo_polyline) TO ..... 16.2 48.2, 16.2 48.1))'), GeoFromText(geo_polyline)) on line 3 ? Dave L - Yes, this is really just a GUESS. – Wilson Hauck Dec 09 '20 at 18:23
  • @WilsonHauck that column can't be NOT NULL because in some cases it doesn't exist. Also, your wild idea gives `ERROR 3037 (22023): Invalid GIS data provided to function st_geometryfromtext.` – Dave L Dec 14 '20 at 11:23
  • Good luck. I am out of ideas for you to try. – Wilson Hauck Dec 14 '20 at 15:54
  • Thanks @WilsonHauck. Appreciate your efforts. – Dave L Dec 15 '20 at 16:11

0 Answers0