Incomplete migration using a database schema with existing migration

[d-stephane]

• Laravel Version: 8.0.4
• PHP Version: 7.3.19
• Database Driver & Version: pgsql - 12.3

Description:

Incomplete migration using a database schema with existing migration due to a sequence issue.

Steps To Reproduce:

Create "test" project :
laravel new test
Create postgresql database :
createdb test
Migrate :
php artisan migrate
Dump and prune schema :
php artisan schema:dump --prune
Make new migration and run migration :

php artisan migrate
- Migrating: 2020_09_11_064507_add_table_test
- Migrated:  2020_09_11_064507_add_table_test (14.05ms)

---

Create "test2" project :
laravel new test2
Create postgresql database :
createdb test2
Remove default migrations and copy schema directory from test/database to test2/database
Copy test/database/migrations/2020_09_11_064507_add_table_test.php to test2/database/migrations
Run migration :
php artisan migrate
Error :

Migration table created successfully.
Loading stored database schema: /www/a/test2/database/schema/pgsql-schema.sql
Loaded stored database schema. (118.41ms)
Migrating: 2020_09_11_064507_add_table_test

   Illuminate\Database\QueryException 

  SQLSTATE[23505]: Unique violation: 7 ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « migrations_pkey »
DETAIL:  La clé « (id)=(1) » existe déjà. (SQL: insert into "migrations" ("migration", "batch") values (2020_09_11_064507_add_table_test, 2))

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

      +28 vendor frames 
  29  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

[d-stephane]

I tested this modification in the file laravel/framework/src/Illuminate/Database/Schema/PostgresSchemaState.php :

    public function load($path)
    {
        //$process = $this->makeProcess('PGPASSWORD=$LARAVEL_LOAD_PASSWORD psql --file=$LARAVEL_LOAD_PATH --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER --dbname=$LARAVEL_LOAD_DATABASE');
        $process = $this->makeProcess('PGPASSWORD=$LARAVEL_LOAD_PASSWORD pg_restore --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER --dbname=$LARAVEL_LOAD_DATABASE $LARAVEL_LOAD_PATH');

        $process->mustRun(null, array_merge($this->baseVariables($this->connection->getConfig()), [
            'LARAVEL_LOAD_PATH' => $path,
        ]));
    }

    protected function baseDumpCommand()
    {
        //return 'PGPASSWORD=$LARAVEL_LOAD_PASSWORD pg_dump --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER $LARAVEL_LOAD_DATABASE';
        return 'PGPASSWORD=$LARAVEL_LOAD_PASSWORD pg_dump -Fc --host=$LARAVEL_LOAD_HOST --port=$LARAVEL_LOAD_PORT --username=$LARAVEL_LOAD_USER $LARAVEL_LOAD_DATABASE';
    }

There may be a better solution but it works.

[taylorotwell]

It's hard to see what exactly changed. Can you PR your suggested change instead?

[taylorotwell]

Closing this as a PR is now open.

[taylorotwell]

I wasn't able to reproduce this issue so don't feel comfortable changing the code at this time. Need more feedback from Pgsql users as to specifically what is happening.

[infokariim]

Hello and thank you.
I failed to install:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists (SQL: create table users (id bigint unsigned not null auto_increment primary key, name varchar(255) not null, email varchar(255) not null, email_verified_at timestamp null, password varchar(255) not null, remember_token varchar(100) null, current_team_id varchar(255) null, profile_photo_path text null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

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

1 D:\wamp64\www\livewire\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
PDOException::("SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists")

2 D:\wamp64\www\livewire\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
PDOStatement::execute()

D:\wamp64\www\livewire>

[elsuterino]

in the dump schema file you have

INSERT INTO public.migrations VALUES (1, '2014_10_12_000000_create_users_table', 1);
INSERT INTO public.migrations VALUES (2, '2014_10_12_100000_create_password_resets_table', 1);

but postgress does not update autoincrement id if the autoincrement function is not called so when you try to insert new it tries with id of 1. Looks like https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts

appending this line temp solves it
SELECT setval('public.migrations_id_seq', (SELECT MAX(id) from public.migrations));