Error when creating the schema_migration table on MySQL 8

[Narven]

Description

This problem happens during the running of migrations when it tries to create a new schema_migration table, on an empty database on MySQL 8.

It looks like at least MySQL 8 has this flag sql_require_primary_key that does not allow table to not have PRIMARY_KEY.

This problem arises if you are not in direct control of the databases, ex: DigitalOcean Managed Database, where this flag is ON.

Steps to Reproduce the Problem

1. With an empty database, run migrations.

2. The following error will show:

CREATE UNIQUE INDEX `schema_migration_version_idx` ON `schema_migration` (`version`);: Error 3750: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.

Expected Behavior

No error.

Actual Behavior

From my point of view there are 2 options:

1. Add PRIMARY_KEY to schema_migration by default. (easy option)
2. Enable some kind of hook that would allow us to runs before any kind of migrations (even before the creation of schema_migration. This following snippet would allow the turn OFF of this flag for the current session.

SET @ORIG_SQL_REQUIRE_PRIMARY_KEY=@@SQL_REQUIRE_PRIMARY_KEY;
SET SQL_REQUIRE_PRIMARY_KEY = 0;

Info

• Ubuntu
• 20.04.1 LTS (Focal Fossa)
• github.com/gobuffalo/buffalo v0.15.5
• github.com/gobuffalo/buffalo-pop/v2 v2.3.0
• github.com/gobuffalo/pop/v5 v5.3.2
• MySQL 8