sql_require_primary_key Causes Tables With String Primary Key To Fail

[CalumTowers]

• Laravel Version: 7
• PHP Version: 7.3.14
• Database Driver & Version: MySQL 8

Description:

When MySQL has sql_require_primary_key enabled, migrations that call ->primary() on a column during table creation fail because it appears as though two queries are ran. One to create the table, and then a second to alter the table and add the primary key.

For context, DigitalOcean recently enforced primary keys on all newly created tables in their managed databases.

Steps To Reproduce:

1. Ensure sql_require_primary_key is enabled on the MySQL server
2. Create a migration that creates a new table with a string as the primary key (we used $table->string('string')->primary();) and does not have a default $table->id(); column
3. Run the migration

The below error is generated when attempting to run the first query to create table.

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.

[driesvints]

Hmm this is a tricky one. Apparently the SQL generated to create the table doesn't immediately adds the primary key and thus indeed causes this error. I'm not sure how (or if) this can be solved.

[CalumTowers]

Hmm this is a tricky one. Apparently the SQL generated to create the table doesn't immediately adds the primary key and thus indeed causes this error. I'm not sure how (or if) this can be solved.

Thanks for taking a look!

I know it doesn't help in finding a resolution, but it appears that this also affects Passport install. See here: https://github.com/laravel/passport/blob/9.x/database/migrations/2016_06_01_000001_create_oauth_auth_codes_table.php

$table->id(); appears to function without any issues, I think because of this logic.

[erwinweber96]

In my case I use Digital Ocean as well, but this problem happened for pivot tables only and I had to edit the migrations by adding $table->id(); and $table->unique(['table1_id', table2_id']);.

[driesvints]

@erwinweber96 the problem is that that won't work for string based primary identifiers. Thinking password resets table, database notifications, passport, etc.

[erwinweber96]

I agree that this needs to be solved, however I added that comment in case someone stumbles upon the issue (like I did) regarding sql_require_primary_key and laravel. There's very few info online on this matter atm.

[Ekion-1]

I'm experiencing the same issue. Unsure if it was a recent change to DigitalOcean's config but Laravel applications utilising Passport are effectively incompatible with DigitalOcean Managed Databases at the moment.

[kaphert]

update below solution won't work if you run tests in sqlite for example. See #33238 (comment) for a simpler workaround.

Same issue here, it affects the sessions, password_resets and the two Telescope tables telescope_entries_tags and telescope_monitoring as far as I know at this point.

For anyone that needs a fix, this is my workaround for now:

• Run the default Laravel migrations (add a primary key where necessary) on a machine where the flag is turned off (Homestead for example)
• Open your SQL editor and copy the CREATE TABLE ... statement. (TablePlus: open table, at the bottom: [Structure] and then the button [Info] on the right).
• Open your migration file, comment your original Laravel Schema (keep it as a reference).
• Run the CREATE TABLE ... statement with: \DB::statement($query);

sessions

\Illuminate\Support\Facades\DB::statement('CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');

password_resets
You could probably also choose email as a primary key but in my case I have some extra (tenant related) columns in this table.

\Illuminate\Support\Facades\DB::statement('CREATE TABLE `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;')

Laravel Telescope

See https://laravel.com/docs/7.x/telescope#migration-customization on how to use your own migrations instead of the default ones

telescope_entries_tags

\Illuminate\Support\Facades\DB::statement('CREATE TABLE `telescope_entries_tags` (
  `entry_uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`entry_uuid`,`tag`),
  KEY `telescope_entries_tags_tag_index` (`tag`),
  CONSTRAINT `telescope_entries_tags_entry_uuid_foreign` FOREIGN KEY (`entry_uuid`) REFERENCES `telescope_entries` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');

telescope_monitoring

DB::statement('CREATE TABLE `telescope_monitoring` (
  `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;');

[mgmgpyaesonewin]

Current workaround is setting
SET sql_require_primary_key=0, and I set the sql variable.

And I change to

alter table `TABLE_NAME` add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

[Ekion-1]

Current workaround is setting
SET sql_require_primary_key=0, and I set the sql variable.

And I change to

alter table `TABLE_NAME` add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

Unable to do this on managed database instances (DigitalOcean) as far as I'm aware?

[CalumTowers]

Yep, this is following a recent change to their config
If it wasn't for DO mandating it, I'd have disabled the setting and moved on. Because they are mandating it, I thought our team wouldn't be the only ones having issues.

From the tagging of this issue (by Dries as a bug and help wanted), I think there is an acceptance that this needs to be resolved in the framework in some way. Although I acknowledge that is is a fairly sizeable job to sort!

[taylorotwell]

If someone wants to submit a PR to somehow make this one query behind the scenes be our guest 😄

Currently, this is not a "bug" in Laravel. It is a bug in DigitalOcean's configuration they are forcing upon you.