framework: Datatype mismatch using Eloquent Model

Laravel Version: 5.8.26
PHP Version: 7.1.30
Database Driver & Version: pgsql => 11.3

Database : Postgresql 10

Description:

When using (new Model())->save() on PostgreSQL database, the Illuminate\Database\Connection cast boolean to integer in the prepareBindings and bindValues methods.

Steps To Reproduce:

Create a table on MySql or Postgresql :

Schema::connection('postgresql')->create('test', function (Blueprint $table) {
      $table->bigIncrements('id');
      $table->boolean('column_boolean');
});

Create a Model :

use Illuminate\Database\Eloquent\Model;

class TestInsert extends Model
{
    protected $table = 'test';
    protected $primaryKey = 'id';
    protected $connection = 'postgresql';
}

Finally insert a new value : $test = new TestInsert(); $test->save();

=> it should failed converting : SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "column_boolean" is of type boolean but expression is of type integer

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 19 (6 by maintainers)

Commits related to this issue

Let users fil out if they use ATTR_EMULATE_PREPARES=true Laravel will miss-behave in multiple ways with MySQL and PgSQL as documented in various issues and even PRs, because people try to get a fix i... — committed to mfn/laravel-framework by mfn 5 years ago

Most upvoted comments

FYI: Php 8.0.5 has a bug that causes the same message when using Postgresql

daniel-de-wit on May 4, 2021

@ahmedofali As I try to fix this issue between waiting for the next version of PHP in docker official with version 7.4.19 and 8.0.6

Try to pin your base image stick to 7.4.16-xxx-xxxx and 8.0.5-xxx-xxxx to avoid this issue see the bug report

https://bugs.php.net/bug.php?id=81002

Ex. from my Dockerfile FROM php-7.4.16-fpm-alpine

Normally when you using 8.0-buster Docker will try to pick the last minor version when building an image. Let check in the container which version exactly.

Now I solved this issue by the way as I preferred until the Docker PHP releases a new version.

Hopefully, the new version fixed this bug. Let’s try it 😃

PHP: https://github.com/php/php-src/releases

Update: Docker PHP was already released 7.4.19 Let’s check | 8.0.6 (Pending) https://hub.docker.com/_/php?tab=tags&page=1&ordering=last_updated&name=7.4.19

ALTELMA on May 7, 2021

FYI: PHP 7.4.1x still has a bug that causes the same message when using Postgresql. Now we try to avoid this issue by changed the boolean to a string instead.

Ex. 'true', 't' etc

This options not working PDO::ATTR_EMULATE_PREPARES => true in Lumen

And The bug also happens in php7.4.13 need to update to PHP 8.0.6 or 7.4.19 to fix this issue.

ALTELMA on May 5, 2021

The error is caused by PDO::ATTR_EMULATE_PREPARES => true, Laravel uses false by default.

staudenmeir on Jul 2, 2019

Php image: 8.0-buster Postgres image: postgres:13.2-alpine

I can confirm that the problem still exist Datatype mismatch: 7 ERROR: column "is_allowed" is of type boolean but expression is of type integer

ahmedofali on May 6, 2021

Be aware that having ATTR_EMULATE_PREPARES=true causes other problems with MySQL on recent versions of php. See ( #23850, #27596, #26817, #25818).

ionutantohi on Jul 3, 2019