Framework: 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.

[AlexGodbehere]

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 https://github.com/laravel/framework/issues/33238#issuecomment-648071415 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;

[AlexGodbehere]

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.

[AlexGodbehere]

I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;

However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.

This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

[Mic-B]

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

Can confirm this works for me as well. Thanks Alex!

[imrealashu]

Any latest update or any other solution as of now? The major tables that are affected are the pivot tables only.

[burakaktna]

I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;
However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.
This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

Thank you works for me as well. You can add this into

src/vendor/laravel/framework/src/Illuminate/Database/MigrationServiceProvider.php

so it works every time you use the migrate command.

[CrackiGuy]

I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;
However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.
This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

love you so much

[adddz]

I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;
However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.
This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

I'm trying to import an existing Laravel database (MySQL 5.x) into DigitalOcean Managed Database (MySQL 8) and I'm getting that error. How would I set that variable? At the top of the dump file? Thank you!

[krns]

I'm trying to import an existing Laravel database (MySQL 5.x) into DigitalOcean Managed Database (MySQL 8) and I'm getting that error. How would I set that variable? At the top of the dump file? Thank you!

Are you using a Tool like TablePlus? Then you can login to the database, execute SET SESSION sql_require_primary_key=0 manually and import your dump. This works because it's the same session :-)

[adddz]

It works, thanks!

And for some Laravel tables like password_resets? Should we add a primary key to a column of choice?

On 26 Sep 2020, at 08:35, Simon Hansen [e-mail] wrote:


I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;

However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.

This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

I'm trying to import an existing Laravel database (MySQL 5.x) into DigitalOcean Managed Database (MySQL 8) and I'm getting that error. How would I set that variable? At the top of the dump file? Thank you!

Are you using a Tool like TablePlus? Then you can login to the database, execute SET SESSION sql_require_primary_key=0 manually and import your dump. This works because it's the same session :-)

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or unsubscribe.

[krns]

It works, thanks!

And for some Laravel tables like password_resets? Should we add a primary key to a column of choice?

We didn't make other changes because the error only occurs while creating the table.. after that everything should work as expected. But keep in mind that you cannot create more tables without a primary key (or you have to apply the workaround again).

[provydon]

I've had the following response from DigitalOcean regarding this issue:

I understand you are getting error when trying to migrate database schemas. You can temporarily override the setting using SET SESSION sql_require_primary_key = 1;
However, You will need to add a primary key after you import or you will receive notifications at a point that adding primary key is required.
This setting is only for the current session though, it's not a permanent override.

Therefore I managed to get this to work by calling \Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0'); above my Schema::create command in the migrations causing issues.

Tnx alot!!

[kvasconcelos]

Thank you works for me as well. You can add this into

src/vendor/laravel/framework/src/Illuminate/Database/MigrationServiceProvider.php

so it works every time you use the migrate command.

Where in that file should i use it ?

[imomer]

Thank you works for me as well. You can add this into

src/vendor/laravel/framework/src/Illuminate/Database/MigrationServiceProvider.php

so it works every time you use the migrate command.

Where in that file should i use it ?

Yeah where should we add in that file?

[paihz]

Thank you works for me as well. You can add this into

src/vendor/laravel/framework/src/Illuminate/Database/MigrationServiceProvider.php

so it works every time you use the migrate command.

Where in that file should i use it ?

Yeah where should we add in that file?

Put at the migration file where didn't have primary key id, for example, 2014_10_12_100000_create_password_resets_table.php

[ioanschmitt]

I just want to add, if you use PostgreSQL, everything is working fine. Just for the people who doesn't know 😃