Error occurs when saving a point - "Incorrect parameter count in the call to native function 'ST_GeomFromText'"

[Art-Mas]

Hi! I decided to try your package in PHPUnit:

$point = new Point();
$point->point = new \Grimzy\LaravelMysqlSpatial\Types\Point(40.7484404, -73.9878441);
$point->save(); 

an error occurs when running the test:

SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'ST_GeomFromText' (SQL: insert into points (point) values (ST_GeomFromText(POINT(-73.9878441 40.7484404), 0, 'axis-order=long-lat'))

but if you delete the line ", 'axis-order=long-lat' " in the file SpatialExpression.php then saving will work fine, see screenshot:

I also tried running the raw query leaving the string ", 'axis-order=long-lat' " and it worked without an error:

DB::insert(
    "insert into `points` (`point`) values (ST_GeomFromText('POINT(-73.9878441 40.7484404), 0, axis-order=long-lat'))"
);

Please help :)

[grimzy]

What version of MySQL are you using? axis-order was added in MySQL 8.

Also, note that the query reported in the error is different than the one you tested (notice the quotes inside ST_GeomFromText())

[Art-Mas]

@grimzy
Thanks for the answer!

What version of MySQL are you using?

10.4.11-MariaDB

The problem was using MariaDb (delivered by default in XAMPP)
Changing to MYSQL 8 solved the problem

It would be good to add mariadb compatibility :)

[dacianb]

same here!

[grimzy]

@slave2anubis , also same version of MariaDB?

I wish this version was mentioned in https://mariadb.com/kb/en/mysqlmariadb-spatial-support-matrix/.

I'd love to add better MariaDB compatibility. Did you have any issues with the migrations?

For now, if this is blocking you, I believe that "upgrading" to your database engine to MySQL 8 or downgrading the package to the v2.x branch could fix your issue.

[dacianb]

Sorry but i removed Mariadb and added Mysql server. The version was 10.4.xx i am running on a linux machine with default mariadb packages.

[mwargan]

I'm on MySQL 5.7 - what is the max supported version of this package for that version of MySQL?

[blindpenguin]

I'm running MariaDB 10.3.12 (via WAMP 3.1.7) and i have the same issue. Migration runs just fine for me. The previous version of your package works just fine.

[grimzy]

@mwargan , for MySQL 5.7, please use v2.x.x of grimzy/laravel-mysql-spatial.

[grimzy]

For now I recommend MariaDB users to also stick to v2.x.x of this package.

[RomainMazB]

For now I recommend MariaDB users to also stick to v2.x.x of this package.

It solved my issue (can't install MySQL on my shared hosting...)

Thanks!

[andrewminion-luminfire]

I’m using v4.0 for Laravel 8 compatibility, and override just the one file to fix MariaDB compatibility:

In composer.json:

{
    "autoload": {
        "psr-4": {
            "Grimzy\\LaravelMysqlSpatial\\Eloquent\\": "vendor-custom/myprefix/laravel-mysql-spatial/src/Eloquent/"
        }
}

And the src/Eloquent/SpatialExpression.php file:

<?php

namespace Grimzy\LaravelMysqlSpatial\Eloquent;

use Illuminate\Database\Query\Expression;

/**
 * Removes the the ST_GeomFromText 'axis-order=long-lat' argument that fails on MariaDB.
 */
class SpatialExpression extends Expression
{
    public function getValue()
    {
        return "ST_GeomFromText(?, ?)";
    }

    public function getSpatialValue()
    {
        return $this->value->toWkt();
    }

    public function getSrid()
    {
        return $this->value->getSrid();
    }
}

---

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

[grimzy]

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

Yes! It's also quite difficult to maintain at the moment. Any suggestions on how to implement this?
I was thinking maybe we could have Traits for each database engine (mysql 5.6, 5.7, 8, Maria DB...). What do you think?

[andrewminion-luminfire]

I was thinking maybe we could have Traits for each database engine (mysql 5.6, 5.7, 8, Maria DB...). What do you think?

That makes sense…I think. Are you talking about a trait for the Eloquent\SpatialExpression class to use? or the Elquent\SpatialTrait for end users to place on their models?

As a user of the package, it would be great for the package to auto-detect the database engine and use the appropriate class internally, so I don’t have to choose a MySQL vs. MariaDB model trait.

If auto-detecting the engine is too troublesome, maybe a database engine config key/value would work?

[gsusanj]

I’m using v4.0 for Laravel 8 compatibility, and override just the one file to fix MariaDB compatibility:

In composer.json:

{
    "autoload": {
        "psr-4": {
            "Grimzy\\LaravelMysqlSpatial\\Eloquent\\": "vendor-custom/myprefix/laravel-mysql-spatial/src/Eloquent/"
        }
}

And the src/Eloquent/SpatialExpression.php file:

<?php

namespace Grimzy\LaravelMysqlSpatial\Eloquent;

use Illuminate\Database\Query\Expression;

/**
 * Removes the the ST_GeomFromText 'axis-order=long-lat' argument that fails on MariaDB.
 */
class SpatialExpression extends Expression
{
    public function getValue()
    {
        return "ST_GeomFromText(?, ?)";
    }

    public function getSpatialValue()
    {
        return $this->value->toWkt();
    }

    public function getSrid()
    {
        return $this->value->getSrid();
    }
}

However, it would be great to have separate MySQL vs. MariaDB files, or conditionally return the spatial query based on the actual database engine.

In my case, I had to override the SpatialTrait.php as well.