Users Table getting duplicated after initial migrate:auto

[AlexJames-Dev]

Hello,

Whenever I run the migrate:auto command after the initial users table has been created, the command creates a new table called "table_users". I have a video demonstrating the issue happening on a fresh laravel project, (you can skip through to around 01:28 as it takes it's sweet time to create a new project on windows!): Video Link. It has the following error:

  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "table_users_email_unique" already exists (SQL: alter table "table_users" add constraint "table_users_email_unique" unique ("email"))

  at C:\Devspace\lam_error_test\vendor\laravel\framework\src\Illuminate\Database\Connection.php:692
    688▕         // If an exception occurs when attempting to run a query, we'll format the error
    689▕         // message to include the bindings with SQL, which will make this exception a
    690▕         // lot more helpful to the developer instead of just the database's errors.
    691▕         catch (Exception $e) {
  ➜ 692▕             throw new QueryException(
    693▕                 $query, $this->prepareBindings($bindings), $e
    694▕             );
    695▕         }
    696▕     }

  1   C:\Devspace\lam_error_test\vendor\laravel\framework\src\Illuminate\Database\Connection.php:485
      PDOException::("SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "table_users_email_unique" already exists")

  2   C:\Devspace\lam_error_test\vendor\laravel\framework\src\Illuminate\Database\Connection.php:485
      PDOStatement::execute()

[bastinald]

the package should be removing that "table_users" table, as its only supposed to be a temporary table created to diff the actual table.

also, make sure the user migration file that comes with laravel itself is deleted.

[AlexJames-Dev]

Yeah, the artisan make:amodel User --force command that i ran in the example video, removed the users migration automatically and i have checked in the migrations folder and it is indeed removed. After a bit of testing, it seems to only be an issue when using postgres. I changed nothing expect for the DB conntection and tested the same process that i did in the video, but on a MySQL mariaDB database and there's no such issues, which leads me to think there is some issue with this package and postgres. Cheers!

[bastinald]

Ahhh, i didn't test with postgres. I only use MySQL or MariaDB.

I'm unsure how to fix for postgres. It may be an issue with either Laravel or Doctrine in how their DTO's handle the stuff.

Closing for now as I have no time or idea how to fix this for postgres. If you figure out a solution please feel free to submit a PR.

[marstell-sw]

I'm testing this package and I have the same issue on PostgreSQL.
When you make a migration on a table with a unique index, here is the issue:

1. you create a temp "table_users" with a "table_users_email_unique" index
   in the DB there are already a table "users" and a "users_email_unique" index
2. you make the diff the temp table with the table on DB and it shows that the index name has changed
3. you apply the diffs on the table and it try to rename the "users_email_unique" index on the real table to "table_users_email_unique" and it fails because it does exists on the temp table which is still present.

The primary Key has also a index and it should have the same problem, but instead the Comparator class make a different check if the ->isPrimary attribute is set on the index.

I edited your code to manipulate the tableDiff and make it work. And it works. Here is my edit on MigrateAutoCommand.php:

private function migrate($model)
    {
        $modelTable = $model->getTable();
        $tempTable = 'table_' . $modelTable;

        Schema::dropIfExists($tempTable);
        Schema::create($tempTable, function (Blueprint $table) use ($model) {
            $model->migration($table);
        });

        if (Schema::hasTable($modelTable)) {
            $schemaManager = $model->getConnection()->getDoctrineSchemaManager();
            $modelTableDetails = $schemaManager->listTableDetails($modelTable);
            $tempTableDetails = $schemaManager->listTableDetails($tempTable);

// START OF EDIT:
            foreach ($tempTableDetails->getIndexes() as $indexName=>$indexInfo){
                if ($indexInfo->isPrimary()) continue;
                $correctIndexName=str_replace('table_','',$indexName);
                $tempTableDetails->renameIndex($indexName, $correctIndexName);
            }
// END OF EDIT

            $tableDiff = (new Comparator)->diffTable($modelTableDetails, $tempTableDetails);

// [....]

I scan all indexes on the temp table and rename the index which are NOT primary fto delete the "table_" part.
In such way the Comparator doesn't see the difference and doesn't touch the index.

I am not sure this will cover every issue with indexes but this specific problem seems resolved.

I hope you find this hint useful

Bye
Marco