Query works in MySQL but not via Lumen/Laravel - Numeric value out of range: 1416

Question

I am stumped... Quite simply, I am trying to update a geometry record in a MySQL database and for some reason, it is failing. However, when Lumen throws the QueryException, it shows the query that is being executed;

UPDATE `user_locations` SET `current_location` = ST_GeomFromText('POINT(1 1)') WHERE user_id = 1

The above query works when I execute it in MySQL via command line, but for some reason, it does not work via Laravel/Lumen/PDO/Eloquent (not sure which one is causing the issue).

This is the error that is thrown:

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field (SQL: update `user_locations` set `current_location` = ST_GeomFromText('POINT(1 1)') where `user_id` = 1)

I have tried everything that I can think of;

Downgrading MySQL (I saw others having similar issues with Mysql 5.7)
Dumping the query and bindings directly from Eloquent to see what is actually being sent to MySQL (see below)
Changing the engine of the database (it is currently InnoDB)
Removing the spatial index on the current_location column

What else could be causing this? It cannot be MySQL as it works via command line, so it has to be something within Laravel/Eloquent/PDO surely?

Additional Information

I am running MySQL 5.7 (I have also tried 5.6 and it threw exactly the same error)
I am using Lumen but don't think this is relevant as both Laravel and Lumen use Eloquent
I am using https://github.com/grimzy/laravel-mysql-spatial and this (I believe) is the relevant code:
Update - I don't think this is relevant, but there is another point field on this particular table but as I am not updating that field, it should be completely ignored in the query, thus, not relevant...

Builder extends Illuminate\Database\Eloquent\Builder

/**
 * Update a record in the database.
 *
 * @param   array $values
 * @return  int
 */
public function update(array $values) : int
{
    foreach ($values as $key => &$value) {
        if ($value instanceof GeometryInterface) {
            $value = $this->asWKT($value);
        }
    }

    return parent::update($values);
}

/**
 * Set the MySQL for the geometry field.
 *
 * @param   GeometryInterface $geometry
 * @return  string
 */
protected function asWKT(GeometryInterface $geometry) : string
{
    return $this->getQuery()->raw("ST_GeomFromText('" . $geometry->toWKT() . "')");
}

In addition, I have also tried the following:

Illuminate\Database\Query\Builder

public function update(array $values)
{
    $sql = $this->grammar->compileUpdate($this, $values);

    dd($sql, $values, $this->cleanBindings(
        $this->grammar->prepareBindingsForUpdate($this->bindings, $values)
    ));

    return $this->connection->update($sql, $this->cleanBindings(
        $this->grammar->prepareBindingsForUpdate($this->bindings, $values)
    ));
}

This outputs:

string(70) "update `user_locations` set `current_location` = ? where `user_id` = ?"
array(1) {
  ["current_location"]=>
  &string(47) "ST_GeomFromText('POINT(1 1)')"
}
array(2) {
  [0]=>
  &string(47) "ST_GeomFromText('POINT(1 1)')"
  [1]=>
  int(1)
}

user_1046010 · Answer

See here:

Illuminate\Database\Query

/**
 * Create a raw database expression.
 *
 * @param  mixed  $value
 * @return \Illuminate\Database\Query\Expression
 */
public function raw($value)
{
    return $this->connection->raw($value);
}

The return of raw is not a string, it is \Illuminate\Database\Query\Expression. Therefore, by casting the return value of asWkt, to a string, it is forcing Eloquent to wrap it with quotes, thus throwing the error we are seeing.

Query works in MySQL but not via Lumen/Laravel - Numeric value out of range: 1416

Additional Information

1 Answers