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

[sio4]

Hi @Narven,

Thank you for reporting this issue. I made a PR for this, and merged it, but not yet been released. I hope this change will fix the issue. The release with fix will be released within the next week or another, so please kindly let me know if it resolved your issue.