ST_MakeEnvelope breaks MariaDB and fails on MySQL with SRID 4326 spatial columns

[LeftoversTodayAppAdmin]

Summary

LiveController builds the map-viewport spatial filter for the /v1/internal/live/{drivers,vehicles,places} endpoints using MySQL's ST_MakeEnvelope. This breaks the application in two ways:

1. MariaDB does not implement ST_MakeEnvelope. It is a MySQL-only convenience function (5.7.6+). Calling these endpoints on any MariaDB-backed deployment returns a 500. MariaDB is otherwise a first-class target — recent migrations gate driver-specific branches on in_array(DB::getDriverName(), ['mysql', 'mariadb']) (e.g. 2025_08_28_045009_noramlize_uuid_foreign_key_columns.php, 2025_08_28_054925_create_devices_table.php), and the bundled spatial library (fleetbase/laravel-mysql-spatial) advertises MariaDB support — so this is the one outlier blocking MariaDB.

2. On MySQL, ST_MakeEnvelope only accepts SRID 0 geometries. Fleetbase's Point, Polygon, and MultiPolygon casts (server/src/Casts/) feed geometries through Utils::createSpatialExpressionFromGeoJson → \Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson(...), which emits SRID 4326. Recent migrations such as 2025_10_27_171322_fix_device_column_names.php explicitly normalize positions to ST_SRID(POINT(0, 0), 4326). Mixing a SRID-0 envelope with SRID-4326 column data on MySQL 8 raises ER_GIS_DIFFERENT_SRIDS (3618) or ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS (3680) the moment the bounds filter actually meets a populated row, so this is a latent correctness bug on plain MySQL too.

Affected code

server/src/Http/Controllers/Internal/v1/LiveController.php, three identical call sites:

• drivers() — line 160
• vehicles() — line 202
• places() — line 245

Each is:

$query->whereRaw(
    'ST_Within(location, ST_MakeEnvelope(POINT(?, ?), POINT(?, ?)))',
    [$west, $south, $east, $north]
);

User-facing impact

• Calling GET /v1/internal/live/drivers?bounds[]=…&bounds[]=…&bounds[]=…&bounds[]=… (or the vehicles / places equivalents) returns 500 on any MariaDB deployment, and on MySQL once the spatial column has any SRID-4326 data — which it always does, because the casts and recent migrations write SRID 4326.
• This is the live map viewport filter used by the console map, so it is the primary spatial query a fresh install will hit.

Repro (documentation references)

• MariaDB miscellaneous GIS functions reference omits ST_MakeEnvelope: https://mariadb.com/docs/server/reference/sql-statements/geometry-constructors/miscellaneous-gis-functions.
• MySQL ST_MakeEnvelope documentation states it is defined for SRID 0 only and raises ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS / ER_GIS_DIFFERENT_SRIDS on non-zero-SRID inputs.
• Fleetbase storage SRID is 4326: see Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson (used by Utils::createSpatialExpressionFromGeoJson) and the explicit ST_SRID(POINT(0, 0), 4326) in 2025_10_27_171322_fix_device_column_names.php.

Proposed fix

Replace ST_MakeEnvelope(POINT(?, ?), POINT(?, ?)) with an equivalent WKT polygon passed through ST_GeomFromText(?, 4326). This preserves the ST_Within(location, …) form (so a future SPATIAL INDEX on location remains usable), pins the envelope SRID to 4326 to match the column, and runs unchanged on MySQL 8 / 8.4 and MariaDB 10.5+ / 11.x.

PR following this issue.

Environment

• Repro confirmed by reading current main (commit 9394d4f); call sites unchanged from the lines cited above.
• Reproduces on any MariaDB version; on MySQL 8.0 / 8.4 with the default cast pipeline writing SRID 4326.