Why do migrations not process the commands in the order provided in the up() method?

[gwidgery]

I had a strange issue today with my migration giving an error. I posted this query on stack overflow:

https://stackoverflow.com/questions/78274165/laravel-migration-failing-when-changing-primary-key-1068-multiple-primary-key-d

I have a table workflows with an auto-incrementing primary key called 'click_id'. I would like to add a field 'id' at the beginning of the table and make that the auto incrementing primary key.

This is the migration that fails:

Schema::table('workflows', function (Blueprint $table) {
    $table->unsignedBigInteger('click_id')->change();
    $table->dropPrimary('click_id');
    $table->bigIncrements('id')->first();
    $table->unique(['click_id','review_id']);
});

The error is

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (Connection: mysql, SQL: alter table workflows add id bigint unsigned not null auto_increment primary key first)

This suggests that the dropPrimary() command is not completing before laravel continues onto the next line of the migration.

The only workaround I've found to be sucessful is to split it into two separate chunks within the up() method.

public function up(): void
{
    Schema::table('workflows', function (Blueprint $table) {
        $table->unsignedBigInteger('click_id')->change();
        $table->dropPrimary('click_id');
    });

    Schema::table('workflows', function (Blueprint $table) {
        $table->bigIncrements('id')->first();
        $table->unique(['click_id','review_id']);
    });
}

Another StackOverflow user Luca did some digging and found out that the Blueprint class does NOT follow the order of the instructions. The above 4 commands are translated into these SQL queries:

2024_04_04_133407_test_change_index array:4 [
  0 => "alter table `test` modify `click_id` bigint unsigned not null"
  1 => "alter table `test` add `id` bigint unsigned not null auto_increment primary key first"
  2 => "alter table `test` drop primary key"
  3 => "alter table `test` add unique `test_click_id_unique`(`click_id`)"
]

The drop primary key is pushed later down the list causing the error.

Can someone explain why it's done this way? Or any documentation as to when and how it decides what the order is to run them?

[marius-mcp]

The simplest answer is: they where coded like that.
I noticed this a long time ago and now that I see it again, I remembered it.
I guess the solution is just to separate all like you said.

[Sophist-UK]

This could be considered a bug - and the code could be changed to resolve this. If anyone can be bothered.

[hafezdivandari]

It's not easy at all, would be huge breaking change.

[hafezdivandari]

Currently, the columns modifying runs at first, then columns adding, then the other commands.

[marius-mcp]

Until a fix is (ever) made, maybe the documentation can be amended at least.

[gwidgery]

Thanks for the info. I agree if this could be documented it could save a lot of hassle for others in the future.

[hafezdivandari]

Just sent PR #51373 to fix this.

[marius-mcp]

@hafezdivandari I see that new changes(files, properties and functions) from the pull requests are not typed but rather they stick to old laravel style...

Is there a reason to not adopt the typed params, properties, returns?
This is valid also for your PR.

[marius-mcp]

Also, will your change imply multiple queries just to preserve the order?
I mean adding 10 new columns will result in 10 queries?

[hafezdivandari]

Is there a reason to not adopt the typed params, properties, returns?

I usually stick to the same style as the scope/namespace i'm sending changes to. Taylor usually applies some formatting before merging, or asks for changes if needed.

will your change imply multiple queries just to preserve the order?

Yes, almost each command compiles to one query.

[marius-mcp]

@hafezdivandari Do you know if this multitude of queries will be slower than the actual version?
I created a new question in regards to the typed codding style #51378.

[hafezdivandari]

If you are worry about slower tests, SQLite number of queries is not changed, because SQLite doesn't support adding multiple columns in a single query. And other databases, I don't think that would make significant change, but hard to tell without doing some benchmarking.