pop
pop copied to clipboard
Error when creating the schema_migration table on MySQL 8
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
-
With an empty database, run migrations.
-
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:
- Add PRIMARY_KEY to
schema_migration
by default. (easy option) - 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