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)
}

Answer 1

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.