Passport: Migrations fails

[adumskis]

• Passport Version: v8.3.0
• Laravel Version: 6.13.1
• PHP Version: 7.3.14
• Database Driver & Version: MariaDB 10.4

Description:

Migrations after fresh install fails:
PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table db.oauth_auth_codes (errno: 150 "Foreign key constraint is incorrectly formed")")

2016_06_01_000001_create_oauth_auth_codes_table.php contains a foreign key to oauth_clients table that will be created only in 2016_06_01_000004_create_oauth_clients_table.php.

Steps To Reproduce:

Require passport via composer and php artisan migrate/php artisan migrate:fresh.

[lloricode]

Migrating: 2016_06_01_000001_create_oauth_auth_codes_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `oauth_auth_codes` add constraint `oauth_auth_codes_client_id_foreign` foreign key (`client_id`) references `oauth_clients` (`id`) on delete cascade)

  at /home/vagrant/code/laravels/laravel-boilerplate/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/laravels/laravel-boilerplate/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123

  2   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/laravels/laravel-boilerplate/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121

  Please use the argument -v to see more details.
Script @php artisan migrate:fresh --seed --ansi handling the fresh event returned with error code 1

Passport Version: v8.3.0
Laravel Version: 6.13.1
PHP Version: 7.2.26
Database Driver & Version: MySQL 5.7.28-0ubuntu0.18.04.4 - (Ubuntu)

[jimmyaldape]

Same issue

Migrating: 2016_06_01_000001_create_oauth_auth_codes_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `oauth_auth_codes` add constraint `oauth_auth_codes_client_id_foreign` foreign key (`client_id`) references `oauth_clients` (`id`) on delete cascade)

  at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123

  2   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121

Passport Version: v8.3.0
Laravel Version: v6.13.0
PHP Version: 7.2.18
MySQL: 5.7.21

[jimmyaldape]

So looking at the migration, it looks like the order that the migrations happens is out of order. 2016_06_01_000001_create_oauth_auth_codes_table.php runs first then 2016_06_01_000004_create_oauth_clients_table.php runs after a couple of migrations.

They either have to be switched or the foreign has to be added after the fact

[KieronWiltshire]

So looking at the migration, it looks like the order that the migrations happens is out of order. 2016_06_01_000001_create_oauth_auth_codes_table.php runs first then 2016_06_01_000004_create_oauth_clients_table.php runs after a couple of migrations.

They either have to be switched or the foreign has to be added after the fact

this is actually my bad, im submitting a PR to fix the issue

[lloricode]

waiting to merge and release this https://github.com/laravel/passport/pull/1174

[anilcancakir]

Temporary solution.

Publish passport migrations

php artisan vendor:publish --tag=passport-migrations

Comment up function body in 2016_06_01_000004_create_oauth_clients_table.php file

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
//        Schema::create('oauth_clients', function (Blueprint $table) {
//            $table->bigIncrements('id');
//            $table->unsignedBigInteger('user_id')->nullable()->index();
//            $table->string('name');
//            $table->string('secret', 100)->nullable();
//            $table->text('redirect');
//            $table->boolean('personal_access_client');
//            $table->boolean('password_client');
//            $table->boolean('revoked');
//            $table->timestamps();
//        });
    }

Add create table code to up function body in 2016_06_01_000001_create_oauth_auth_codes_table.php file

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('oauth_clients', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id')->nullable()->index();
            $table->string('name');
            $table->string('secret', 100)->nullable();
            $table->text('redirect');
            $table->boolean('personal_access_client');
            $table->boolean('password_client');
            $table->boolean('revoked');
            $table->timestamps();
        });

        Schema::create('oauth_auth_codes', function (Blueprint $table) {
            $table->string('id', 100)->primary();
            $table->unsignedBigInteger('user_id')->index();
            $table->unsignedBigInteger('client_id');
            $table->text('scopes')->nullable();
            $table->boolean('revoked');
            $table->dateTime('expires_at')->nullable();

            $table->foreign('client_id')->references('id')->on('oauth_clients')->onDelete('cascade');
        });
    }

[lloricode]

i think just use v8.2.0, and its all good

[HamadAlsoghayer]

Commit 32d5d60 caused this issue, it references an ID on a table that is not yet migrated. Fixed this by changing the order of migrations.

[lloricode]

yes, but there are already PR, that much better

[VladSerebro]

Same issue!
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table oauth_auth_codes add constraint oauth_auth_codes_client_id_foreign foreign key (client_id) references oauth_clients (id) on delete cascade)
So, I have resolved this problem as follows:

1. Remove strings with adding constraints from all passport migrations
2. Add all constraints to last custom migration
3. It works!

[EvhenZhykov]

The same problem!

Migrating: 2016_06_01_000001_create_oauth_auth_codes_table

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'oauth_clients' (SQL: alter table oauth_auth_codes add constraint oauth_auth_codes_client_id_foreign foreign key (client_id) references oauth_clients (id) on delete cascade)

To solve this problem, you can
delete row
$table->foreign('client_id')->references('id')->on('oauth_clients')->onDelete('cascade');
from 2016_06_01_000001_create_oauth_auth_codes_table.php

delete row
$table->foreign('client_id')->references('id')->on('oauth_clients')->onDelete('cascade');
from
2016_06_01_000002_create_oauth_access_tokens_table.php

create a new migration for example
2016_06_01_000006_fix

add

Schema :: table ('oauth_auth_codes', function (Blueprint $ table) {
             $ table-> foreign ('client_id') -> references ('id') -> on ('oauth_clients') -> onDelete ('cascade');
         });

         Schema :: table ('oauth_access_tokens', function (Blueprint $ table) {
             $ table-> foreign ('client_id') -> references ('id') -> on ('oauth_clients') -> onDelete ('cascade');
         });

THIS SOLVES THE PROBLEM!

[driesvints]

We've removed the foreign keys all together in v8.3.1. Feel free to add these in your published migrations.

[ZayRTun]

I am having the same problem. Amy updates?