laravel 8 passport Datatype mismatch

Question

I updated laravel to version 8.

I have a problem when I run this method (with the migrate command):

\Laravel\Passport\Passport::client()->forceFill([
            'user_id' => 1,
            'name' => 'email@email.fr', <- edited
            'secret' => \Illuminate\Support\Str::random(40),
            'redirect' => '',
            'personal_access_client' => false,
            'password_client' => true,
            'revoked' => false
        ])->save();

I have the following error:

In Connection.php line 678:

SQLSTATE[42804]: Datatype mismatch: 7 ERREUR:  la colonne « personal_access_client » est de type boolean mais l'expression est de type integer
LINE 1: ...pdated_at", "created_at") values ($1, $2, $3, $4, $5, $6, $7...
                                                       ^
HINT:  Vous devez réécrire l'expression ou lui appliquer une transformation de type. (SQL: insert into "oauth_clients" ("user_id", "name", "secret", "redirect", "personal_access_client", "password_client", "revoked", "updated_at", "created_at") values (1, email@email.fr, UjnSFdG1JdQxs7LPUDA3D1TACjAa5oNVbmWOCQmX, , 0, 1, 0, 2021-05-04 10:29:48, 2021-05-04 10:29:48) returning "id")


In Connection.php line 343:

SQLSTATE[42804]: Datatype mismatch: 7 ERREUR:  la colonne « personal_access_client » est de type boolean mais l'expression est de type integer
LINE 1: ...pdated_at", "created_at") values ($1, $2, $3, $4, $5, $6, $7...
                                                       ^
HINT:  Vous devez réécrire l'expression ou lui appliquer une transformation de type.

"personal_access_client" field in the database is in Boolean format.

I delete and then recreate the tables with passport but I still have the error.

I think when I do an update() all the boolean fields no longer work.

Can you help me please?

Versions:

    "php": "^7.4|^8.0",
    "doctrine/dbal": "^3.1",
    "fideloper/proxy": "^4.4",
    "fruitcake/laravel-cors": "^2.0",
    "guzzlehttp/guzzle": "^7.0.1",
    "laravel/framework": "^8.0",
    "laravel/helpers": "^1.4",
    "laravel/passport": "^10.1",
    "laravel/tinker": "^2.0",
    "laravel/ui": "^3.0",
    "lcobucci/jwt": "^4.1",
    "nesbot/carbon": "^2.4",
    "pusher/pusher-php-server": "^5.0",
    "spatie/laravel-permission": "^4.0",
    "spatie/laravel-webhook-server": "^1.12",
    "symfony/translation": "^5.2"

Thank you

score 0 · Answer 1 · answered May 04 '21 at 12:59

You are having a sort of syntax error right now because the closing quotation of the name is missing. Replace your code snippet with the following and try.

\Laravel\Passport\Passport::client()->forceFill([
            'user_id' => 1,
            'name' => 'email@email.fr',
            'secret' => \Illuminate\Support\Str::random(40),
            'redirect' => '',
            'personal_access_client' => false,
            'password_client' => true,
            'revoked' => false
        ])->save();

score 0 · Answer 2 · answered May 04 '21 at 13:05

0

it may be because of your MySQL server version ?

I would advise to try changing true/false by 1/0 to see if it solves the problem in a first place

Otherwise you can try adding directly to the database (by avoiding Passport methods)

DB::table('oauth_clients')->insert([
            [
                 'user_id' => 1,
                 'name' => 'email@email.fr',
                 'secret' => \Illuminate\Support\Str::random(40),
                 'redirect' => '',
                 'personal_access_client' => 0,
                 'password_client' => 1,
                 'revoked' => 0
            ]
]);

answered May 04 '21 at 13:05

Maxime

159
1
7

I am using postgresql 13. I put the same version as on my development environment. dev -> it works prod -> it doesn't work – jeyGey May 04 '21 at 13:12
replace true by 1 and false by 0, it doesn't work and it doesn't work directly in postgresql – jeyGey May 04 '21 at 13:13
I just notice one thing is that on the production environment the booleans in the requests are replaced by 0 or 1 – jeyGey May 04 '21 at 13:16
I think I found it. it would come from the php version .. maybe https://stackoverflow.com/questions/67351237/cant-store-0-or-1-as-boolean-laravel-postgresql – jeyGey May 04 '21 at 13:19
I found [that issue](https://github.com/laravel/framework/issues/29023) on laravel framework repository, he seems to have a similar issue. It appears that using `'1'` instead of `1` for true solves the problem – Maxime May 04 '21 at 13:21
Thank you maxime, your solution works but i will downgrade php to 7.4.16. i was in 7.4.18.otherwise you have to upgrade to 8.0.5 I think – jeyGey May 04 '21 at 13:24

Koushik Das · Answer 3 · 2021-06-14T07:18:13.483

This is because Laravel inserts 1 or 0 to indicate if boolean ness of the the column. However, the database uses true or false to indicate it. Thus, it doesn't work. There's a workaround for it.

If you're doing it the first time after the fresh install, then edit the migration file that has the name of this

2016_06_01_000003_create_oauth_refresh_tokens_table.php

Now the date and time part may not match in your installation which is fine. Just try to look for the create_oauth_refresh_tokens_table or the migration which is responsible for creating oauth_clients table. There edit these 3 values from these

        $table->boolean('personal_access_client');
        $table->boolean('password_client');
        $table->boolean('revoked');

to these

        $table->unsignedTinyInteger('personal_access_client');
        $table->unsignedTinyInteger('password_client');
        $table->unsignedTinyInteger('revoked');

Basically, whichever tables created by passport had boolean column type, make them to unsignedTinyInteger

score 0 · Accepted Answer · answered Jun 10 '21 at 09:35

0

it was because of the version of php. You must be in version 7.4.18 or 8.0.5.

https://github.com/php/php-src/pull/6801

answered Jun 10 '21 at 09:35

jeyGey

161
3
15

laravel 8 passport Datatype mismatch

4 Answers4