Migrations Problem

[samcoppock]

Winter CMS Build

1.2

PHP Version

8.1

Database engine

MySQL/MariaDB

Plugins installed

No response

Issue description

When attempting to run the migrations we get this error ( note that the table/column names have been renamed )

QLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'foreign_table' (SQL: alter table current_table add constraint current_table_id_foreign foreign key (foreign_table_id) references foreign_table (id))

This is caused by the 'foreign_table' not existing when this particular migration runs.

The plugin that this migration is in has a dependency registered in its plugin.php file for the plugin which creates the missing table however this is not resulting in the other plugins migrations running first

the line of code which causes this issue is

$table->foreign(‘foreign_id')->references('id')->on(‘foreign_table’);

Note that we do have a circular dependencies issue between some plugins in this project but the two plugins in question dont have a direct circular dependency.

Steps to replicate

create a migration which has the following in it

$table->foreign(‘foreign_id')->references('id')->on(‘foreign_table’);

create another migration in a different plugin which creates the foreign_table and make sure the first plugin is dependant on the second ( set the dependency in plugin.php )

when the referenced table does not exist at the point when this migration runs

presumably any $table function which requires a different table to exist will cause this.

Workaround

as a temporary workaround I have removed

$table->foreign(‘foreign_id')->references('id')->on(‘foreign_table’);

from the migration

[bennothommo]

This is normal - if you want a MySQL foreign key constraint to be applied, the target table must exist.

If you have two plugins that need to establish a foreign key index, but one or the other plugin may not be installed, or they may be installed in a different order, I would recommend applying some conditional code in the migrations of both plugins to only establish the foreign key index when both plugins tables are available, something like...

Migration for the first plugin

<?php

namespace Plugin\One\Updates;

use Schema;
use Winter\Storm\Database\Updates\Migration;

return new class extends Migration
{
    public function up()
    {
        // ... Migration for plugin one ...

        if (Schema::hasTable('plugin_two_table')) {
            Schema::table('plugin_one_table', function ($table) {
                $table->foreign('plugin_two_id')->references('id')->on('plugin_two_table');
            });
        }
    }
}

Migration for the second plugin

<?php

namespace Plugin\Two\Updates;

use Schema;
use Winter\Storm\Database\Updates\Migration;

return new class extends Migration
{
    public function up()
    {
        // ... Migration for plugin two ...

        if (Schema::hasTable('plugin_one_table')) {
            Schema::table('plugin_one_table', function ($table) {
                $table->foreign('plugin_two_id')->references('id')->on('plugin_two_table');
            });
        }
    }
}