Framework: Migrations tries to re-create migration table (postgress db)

[mikevrind]

I think this is the same issue as #47 and #2043.
When I try to run a migration for the second time I receive an error that the migrations table already exists. Currently running a fresh Laravel installation.

SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "migrations" already exists (SQL: create table "public"."migrations" ("migration" varchar(255) not null, "batch" integer not null))

[taylorotwell]

Will need more information I think. Are the tables prefixed? Etc? Do you have anymore info?

[mikevrind]

The table's aren't prefixed. @taylorotwell What more info can I give you to be of any assistance?

• The migrations table is located in the public scheme (which is also set in the db config)
• When I run 'php artisan migrate' for the first time, all migration files are executed without errors.
• A second try of 'php artisan migrate' results in the above SQL error.

[taylorotwell]

I'm not sure really. I haven't experienced this error on Postgres or MySQL. I would ask on the forums and maybe paste more code for your configuration.

[FractalizeR]

That happens (for me on Postgres) if you prefix migration table name in config/database.php with a schema name. It looks like #2043, but it reproduces on latest Laravel official version.

    /*
    |--------------------------------------------------------------------------
    | Migration Repository Table
    |--------------------------------------------------------------------------
    |
    | This table keeps track of all the migrations that have already run for
    | your application. Using this information, we can determine which of
    | the migrations on disk haven't actually been run in the database.
    |
    */

    'migrations' => 'public.migrations',

This will cause error on ./artisan migrate:refresh. If you remove 'public.', everything will be ok.

[matyunin]

@FractalizeR +1

[natecoolidge]

I agree with @FractalizeR though I had to take it a step further and specify the schema in the config/database.php connection like this:
'schema' => 'public'

[bunlongheng]

@FractalizeR : I have the same issue, and trying to follow what you suggested, but in Laravel 5.1. I didn't see the public prefix.'migrations' => 'public.migrations',. Do you have any other suggestion ?

[bunlongheng]

@mikevrind + @taylorotwell : Did you guys ever find the solution for this ? I'm facing this exact same issue right now. Any direction on this will be great appreciated !

[themsaid]

@bunlongheng can you please describe your issue in detail?

[bunlongheng]

• Laravel Version: 5.1
• PHP Version:5.6
• Database Driver & Version: psql

Description:

I run php artisan migrate the first time, and it work perfectly fine.

I got

Migration table created successfully.
Migrated: 2016_01_05_112442_create_logs_table
Migrated: 2016_01_07_152852_create_run_logs_table
Migrated: 2016_01_12_132740_create_users_table
Migrated: 2016_01_19_180935_alter_users_table
Migrated: 2016_01_27_145414_create_web_policies_table
Migrated: 2016_01_27_145522_create_notifications_table
Migrated: 2016_01_27_160545_create_web_policy_types_table
Migrated: 2016_01_28_144245_create_devices_table
Migrated: 2016_02_03_102001_create_caches_table
Migrated: 2016_02_04_160339_create_device_groups_table
Migrated: 2016_02_04_160625_create_policies_table
Migrated: 2016_02_04_161150_alter_notifications_table
Migrated: 2016_02_10_151001_create_curls_table
Migrated: 2016_02_16_133453_create_externals_table
Migrated: 2016_02_25_140200_truncate_devices_table
Migrated: 2016_03_02_194207_alter_devices_table
Migrated: 2016_03_03_132258_create_cloud_securities
Migrated: 2016_03_10_092458_create_captive_portals_table
Migrated: 2016_09_20_095217_alter_captive_portals_table_9_20_2016

It added all the schemas into my psql database.

Then:

I've added 1 more migration script to alter one of my table.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterCaptivePortalsTable9202016 extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('captive_portals', function($table)
        {
            $table->string('type')->after('id')->nullable()->default(NULL);
            $table->string('selected')->after('type')->nullable()->default(NULL);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('captive_portals', function($table)
        {
            $table->dropColumn('type');
            $table->dropColumn('selected');
        });
    }

}

As soon as I ran php artisan migrate, I started to see this error.

---

[Illuminate\Database\QueryException]                                                                                                                                             
  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "migrations" already exists (SQL: create table "migrations" ("migration" varchar(255) not null, "batch" integer not null))  



  [PDOException]                                                                    
  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "migrations" already exists 

---

I have this setting in my database.php

'pgsql'       => [
    'driver'      => 'pgsql',
    'host'        => env('DB_HOST'),
    'database'    => env('DB_DATABASE'),
    'username'    => env('DB_USERNAME'),
    'password'    => env('DB_PASSWORD'),
    'charset'     => 'utf8',
    'collation'   => 'utf8_unicode_ci',
    'prefix'      => '',
    'strict'      => false,
    ],

  'migrations' => 'migrations',

---

I have this in my .env file

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_DATABASE=db
DB_USERNAME=postgres
DB_PASSWORD=postgres

---

Error Screenshot

Please kindly let me know what else, I can provide.

[mikevrind]

@bunlongheng I just checked my database configuration and the only difference I could spot was a schema key within the pgsql driver.

'pgsql' => [
                'driver'   => 'pgsql',
                'host'     => env( 'DB_PG_HOST', 'localhost' ),
                'port'     => env( 'DB_PG_PORT', 5432 ),
                'database' => env( 'DB_PG_DATABASE', '' ),
                'username' => env( 'DB_PG_USERNAME', '' ),
                'password' => env( 'DB_PG_PASSWORD', '' ),
                'charset'  => 'utf8',
                'prefix'   => '',
                'schema'   => 'public',
            ],

The migrations key has been set to migrations.

[bunlongheng]

@mikevrind : You saved my life. :D
After adding 'schema' => 'public', to the end of my psql database settings now,
my php artisan migrate works perfectly.

[felorodri]

Ok, this is the thing... I am using L5.2 and I am having this error too. My pgsql connection have never been changed and it always looked like this:

'pgsql'      => [
      'driver'   => 'pgsql',
      'host'     => env('DB_HOST', 'localhost'),
      'port'     => env('DB_PORT', '5432'),
      'database' => env('DB_DATABASE', 'forge'),
      'username' => env('DB_USERNAME', 'forge'),
      'password' => env('DB_PASSWORD', ''),
      'charset'  => 'utf8',
      'prefix'   => '',
      'schema'   => 'public',
    ],

As you can see, schema has been there since the project starts and I have succesfully excute 31 migrations since then. Now I am trying to excute a new migration but I can't because of the "Duplicate table" (referencing migrations table) error.

Im not sure if this issue should be marked as closed at this moment.

[tylik1]

I've also just experienced the same issue with laravel 5.6
My 'schema' is set to 'public'. Once i've tried to add pivot table and then do migrate:refresh, it started to break with the same error. The solution was to add table drop command at the very top of the up() method, just before Schema::create method - e.g. Schema::dropIfExists('user_category');
Then it ran smothly. Beware it will remove your table, if you have some data in it already.

Afterwards i've removed drop command from up(), and it worked just fine, on any other migration command, with drop command being only left in down() method. Maybe will save some time to someone...

[andrey-helldar]

Laravel: 8.4.0
Database: PostgreSQL 12.4

Steps:

1. art migrate - ok
2. art migrate:fresh - failed

migration 2020_09_01_150135_modify_tasks_table_remove_auto_increment:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

final class ModifyTasksTableRemoveAutoIncrement extends Migration
{
    public function up()
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('tasks', function (Blueprint $table) {
            $table->unsignedBigInteger('id')->change();
        });

        Schema::enableForeignKeyConstraints();
    }

    public function down()
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('tasks', function (Blueprint $table) {
            $table->id()->change();
        });

        Schema::enableForeignKeyConstraints();
    }
}

[andrey-helldar]

I solved my problem.

All you had to do before changing the field type was to remove sequence:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

final class ModifyTasksTableRemoveAutoIncrement extends Migration
{
    public function up()
    {
        $this->dropSequence('tasks', 'id');

        Schema::table('tasks', function (Blueprint $table) {
            $table->unsignedBigInteger('id')->change();
        });
    }

    public function down()
    {
        Schema::table('tasks', function (Blueprint $table) {
            $table->id()->change();
        });
    }

    protected function dropSequence(string $table, string $column)
    {
        DB::select("DROP SEQUENCE IF EXISTS {$table}_{$column}_seq");
    }
}