Migrations fail with mysql 8 sql_require_primary_key mode on

[hagealex]

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

[welcome]

Hello there! Thanks for opening your first issue on this repo!

Just a heads-up: Here at Backpack we use Github Issues only for tracking bugs. Talk about new features is also acceptable. This helps a lot in keeping our focus on improving Backpack. If you issue is not a bug/feature, please help us out by closing the issue yourself and posting in the appropriate medium (see below). If you're not sure where it fits, it's ok, a community member will probably reply to help you with that.

Backpack communication mediums:

• Bug Reports, Feature Requests - Github Issues (here);
• Quick help (How do I do X) - Gitter Chatroom;
• Long questions (I have done X and Y and it won't do Z wtf) - Stackoverflow, using the backpack-for-laravel tag;

Please keep in mind Backpack offers no official / paid support. Whatever help you receive here, on Gitter, Slack or Stackoverflow is thanks to our awesome awesome community members, who give up some of their time to help their peers. If you want to join our community, just start pitching in. We take pride in being a welcoming bunch.

Thank you!

--
Justin Case
The Backpack Robot

[hagealex]

Nevermind. After an Update of my Laravel to the current 10.* release the error didn't occur anymore.