Migrations fail with mysql 8 sql_require_primary_key mode on #361
Description
Bug report
What I did:
I setup the permissionmanager according to the documentation. On my local Laravel Homestead environment it worked. While trying to deploy on my hoster I run in the below mentioned error.
After enabling sql_require_primary_key on my local mysql I face the same error.
For me it looks like there is something not working with the composite primary keys. But I'm no mysql expert so I can just guess.
Did some face a similar issue and knows how to fix it?
Note: I can't turn off the sql_require_primary_key on my hoster.
Schema::create($tableNames['model_has_permissions'], function (Blueprint $table) use ($tableNames, $columnNames, $teams) {
$table->unsignedBigInteger(PermissionRegistrar::$pivotPermission);
$table->string('model_type');
$table->unsignedBigInteger($columnNames['model_morph_key']);
$table->index([$columnNames['model_morph_key'], 'model_type'], 'model_has_permissions_model_id_model_type_index');
$table->foreign(PermissionRegistrar::$pivotPermission)
->references('id') // permission id
->on($tableNames['permissions'])
->onDelete('cascade');
if ($teams) {
$table->unsignedBigInteger($columnNames['team_foreign_key']);
$table->index($columnNames['team_foreign_key'], 'model_has_permissions_team_foreign_key_index');
$table->primary([$columnNames['team_foreign_key'], PermissionRegistrar::$pivotPermission, $columnNames['model_morph_key'], 'model_type'],
'model_has_permissions_permission_model_type_primary');
} else {
$table->primary([PermissionRegistrar::$pivotPermission, $columnNames['model_morph_key'], 'model_type'],
'model_has_permissions_permission_model_type_primary');
}
});
What I expected to happen:
The migrations runs through succesfully.
What happened:
SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. (Connection: mysql, SQL: create table `model_has_permissions` (`permission_id` bigint unsigned not null, `model_type` varchar(255) not null, `model_id` bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')
What I've already tried to fix it:
Backpack, Laravel, PHP, DB version:
Backpack 6.4.2
Laravel 10.32.1
DB: mysql 8.0.35