ST_MakeEnvelope breaks MariaDB and fails on MySQL with SRID 4326 spatial columns
维护者通常 1 天内回复
还没有人认领这个 Issue。
评估
- 难度
- 2/5
- 预计耗时
- 1-3 小时
- 新手友好度
- 74/100
调研方向
检查 server/src/Http/Controllers/Internal/v1/LiveController.php 以及 drivers()、vehicles() 和 places() 中的 whereRaw 调用。使用 SRID-4326 空间数据，针对 MySQL 和 MariaDB 运行受影响的 live endpoint 查询；当 bounds 过滤器在两个数据库引擎上都能正常工作且不出现 GIS 错误时，即表示完成。
由索引模型根据 Issue 内容生成。
描述
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:
-
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 onin_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. -
On MySQL,
ST_MakeEnvelopeonly accepts SRID 0 geometries. Fleetbase'sPoint,Polygon, andMultiPolygoncasts (server/src/Casts/) feed geometries throughUtils::createSpatialExpressionFromGeoJson→\Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson(...), which emits SRID 4326. Recent migrations such as2025_10_27_171322_fix_device_column_names.phpexplicitly normalize positions toST_SRID(POINT(0, 0), 4326). Mixing a SRID-0 envelope with SRID-4326 column data on MySQL 8 raisesER_GIS_DIFFERENT_SRIDS(3618) orER_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 160vehicles()— line 202places()— 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_MakeEnvelopedocumentation states it is defined for SRID 0 only and raisesER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS/ER_GIS_DIFFERENT_SRIDSon non-zero-SRID inputs. - Fleetbase storage SRID is 4326: see
Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson(used byUtils::createSpatialExpressionFromGeoJson) and the explicitST_SRID(POINT(0, 0), 4326)in2025_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(commit9394d4f); 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.
- 主要语言
- PHP
- 星标
- 36
- 派生
- 69
- 平均合并
- 1 天 6 小时
- 30 天内合并 PR
- 32
环境准备
- 没有 Dockerfile 或 Docker Compose 文件
- 没有 Pull Request 模板
- 阅读贡献指南
从这里开始
- 先读完整个 Issue，再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库，在一个分支上完成修改。
- 提交 Pull Request，并在描述里引用这个 Issue 编号。
fleetbase/fleetops 的其他 Issue
-
难度 2/5 1-3 小时 新手友好度 90/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 88/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 88/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 42/100
维护者通常 1 天内回复
-
enhancement good first issue help wanted
难度 4/5 3-5 天 新手友好度 42/100
维护者通常 1 天内回复
查看 fleetbase/fleetops 的全部 Issue
相似的 Issue
-
sync-en
难度 2/5 1-3 小时 新手友好度 76/100
维护者通常 1 天内回复
-
bug Feature: Kiosk
难度 2/5 1-3 小时 新手友好度 76/100
维护者通常 1 天内回复
-
Infrastructure: actions Module: zmscitizenapi Module: zmsentities php Type: Bug unit tests
难度 2/5 1-3 小时 新手友好度 88/100
it-at-m/eappointment#3480 ·
维护者通常 1 天内回复
-
HttpClient
难度 2/5 1-3 小时 新手友好度 72/100
维护者通常 1 天内回复
-
CI: composer install fails — league/flysystem 1.x blocked by security advisory GHSA-cxf4-7mrp-vvpr未关闭devops type: bug
难度 2/5 1-3 小时 新手友好度 84/100
维护者通常 1 天内回复