sql_require_primary_key Causes Tables With String Primary Key To Fail

Question

sql_require_primary_key Causes Tables With String Primary Key To Fail

CalumTowers opened this issue 4 years ago · 65 comments

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:

Ensure sql_require_primary_key is enabled on the MySQL server
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
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.

Answer 1 · 2020-06-18T11:48:46.000Z

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.

Answer 2 · 2020-06-18T12:09:01.000Z

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.

Answer 3 · 2020-06-18T13:27:44.000Z

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']);.

Answer 4 · 2020-06-18T13:30:21.000Z

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

Answer 5 · 2020-06-18T13:34:16.000Z

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.

Answer 6 · 2020-06-20T11:26:25.000Z

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.

Answer 7 · 2020-06-21T20:47:57.000Z

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;');

Answer 8 · 2020-06-22T11:36:35.000Z

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;

Answer 9 · 2020-06-22T11:43:52.000Z

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?

Answer 10 · 2020-06-22T13:24:59.000Z

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!

Answer 11 · 2020-06-22T13:40:00.000Z

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.

Answer 12 · 2020-06-23T10:59:30.000Z

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.

Answer 13 · 2020-06-29T20:55:48.000Z

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!

Answer 14 · 2020-08-17T18:01:06.000Z

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

Answer 15 · 2020-08-22T17:09:02.000Z

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.

Answer 16 · 2020-09-09T14:46:01.000Z

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

Answer 17 · 2020-09-25T23:26:07.000Z

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!

Answer 18 · 2020-09-26T06:35:18.000Z

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 :-)

Answer 19 · 2020-09-26T06:49:32.000Z

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 ***@***.***> 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.

Answer 20 · 2020-09-26T07:08:06.000Z

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).

Answer 21 · 2020-10-21T13:29:37.000Z

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!!

Answer 22 · 2020-11-19T11:52:51.000Z

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 ?

Answer 23 · 2020-11-24T08:57:35.000Z

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?

Answer 24 · 2020-12-14T13:30:45.000Z

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

Answer 25 · 2020-12-16T18:02:55.000Z

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

Answer 26 · 2020-12-29T07:23:46.000Z

Hello, developers!

I just faced the exact same issue.

If you are running migrations in the server, there is an even better wat, using tinker:

After configuring the connection to the remote database, go to the console in your server and use php artisan tinker.

Once there, execute DB::statement('SET SESSION sql_require_primary_key=0'); to disable the check and then execute Artisan::call('migrate --force'); to run all your migrations.

Of course, if you are using an SQL file, so put SET SESSION sql_require_primary_key=0; in the top of that file and use it.

Answer 27 · 2021-01-12T10:12:24.000Z

@adddz in order to make it compatible with both locally on MySQL 5.7 and on DigitalOcean MySQL 8 I had to add the following to the CreateSessionsTable:

use Illuminate\Support\Facades\DB;

$requirePrimaryKey = DB::selectOne('SHOW SESSION VARIABLES LIKE "sql_require_primary_key";')->Value ?? 'OFF';
if ($requirePrimaryKey === 'ON') {
    DB::statement('SET SESSION sql_require_primary_key=0');
}

This would first check if if the sql_require_primary_key exists and is enabled before trying to temporarily disable it.

Answer 28 · 2021-02-01T16:59:51.000Z

I dont understand why this is closed Surely the bug still stands? you need to a a ton of workarounds to get this working mostly to do with the wrong way the SQL statement is created. Surely the fix will be to fix the way its built?

Its also worth noting the workarounds above are not always reliable. I'm getting inconsistent results.

Answer 29 · 2021-02-05T16:38:06.000Z

In researching this issue, I came across this response from DO

I understand that you want to know if it is possible to turn off "sql_require_primary_key." Unfortunately we cannot turn off primary key for our managed database. The lack of primary keys will leads to replication problems, and replication cannot be disabled as backups are done using that. As a managed database, we guarantee backups in case of failure or data loss, and you need to restore.

So, temporarily overwriting the flag is ok, but you need to still ensure you end up with a primary key on every table ... the inference being that DO will not be responsible if you cannot recover from a server issue.

Answer 30 · 2021-02-05T16:40:33.000Z

In researching this issue, I came across this response from DO

I understand that you want to know if it is possible to turn off "sql_require_primary_key." Unfortunately we cannot turn off primary key for our managed database. The lack of primary keys will leads to replication problems, and replication cannot be disabled as backups are done using that. As a managed database, we guarantee backups in case of failure or data loss, and you need to restore.

So, temporarily overwriting the flag is ok, but you need to still ensure you end up with a primary key on every table ... the inference being that DO will not be responsible if you cannot recover from a server issue.

That's is fine. That is how its meant to work.
The problem is the way Laravel constructs the SQL query by creating the table and then adding keys after. The workaround is just that and annoyingly clearly wont be fixed on Laravel's side likely because of this edge use case.

Answer 31 · 2021-02-11T22:50:55.000Z

Or alternately for version dependant code using versioned comments
and the introduced 8.0.13 version (ref)

DB::statement('/*!80013 SET SESSION sql_require_primary_key=0*/');

Answer 32 · 2021-02-17T18:30:30.000Z

A workaround that worked for me:

Perform the DB migration on a MySql container where sql_require_primary_key is disabled.
Create a DB dump.
Temporarily disable the check by adding SET SESSION sql_require_primary_key=0 first thing in the DB dump.
Import the DB dump to DO MySql DB.

Answer 33 · 2021-02-21T03:53:50.000Z

A workaround that worked for me:

Perform the DB migration on a MySql container where sql_require_primary_key is disabled.

Create a DB dump.

Temporarily disable the check by adding "SET SESSION sql_require_primary_key=0" first thing in the DB dump.

Import the DB dump to DO MySql DB.

I did this way, I dumped database and add this line /*!80013 SET SESSION sql_require_primary_key=0*/; to the config section in the dumped file.

Answer 34 · 2021-06-18T14:41:54.000Z

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.

This solved but didn't like the solution much, forced me to switch for PostgreSQL instead. :D

Answer 35 · 2021-06-19T11:16:06.000Z

@usamamuneerchaudhary I have solved the problem in this PR #37715 .

Answer 36 · 2021-06-25T05:49:27.000Z

@usamamuneerchaudhary I have solved the problem in this PR #37715 .

Thank you for the PR. Would this affect only the new Laravel applications or also the existing one? I need to import a big database into DigitalOcean's Managed Database and I'm getting the same error.

Answer 37 · 2021-06-25T06:51:52.000Z

@adddz It is Released in 8.48.1.
However, please note that it is about Laravel Migrations and not restoring a database!

Answer 38 · 2021-06-25T06:56:52.000Z

@adddz It is Released in 8.48.1.
However, please note that it is about Laravel Migrations and not restoring a database!

Thank you. If I just dumped my database and now I need to restore it on a managed database on DO, what would I need to do after I temporarily disable the option for the primary keys? Do I need to add them manually? Thanks a lot!

Answer 39 · 2021-06-25T09:25:25.000Z

Thank you. If I just dumped my database and now I need to restore it on a managed database on DO, what would I need to do after I temporarily disable the option for the primary keys? Do I need to add them manually? Thanks a lot!

In the program that you use to connect and import the database, just run the query SET SESSION sql_require_primary_key=0; and then import the database. Since it is using the same connection for the import, this setting will be used. No need to try and append this to the database dump, just run the one line first.

After you have imported the database, make sure to set a primary key on each table.

Answer 40 · 2021-06-25T10:23:30.000Z

Thank you. If I just dumped my database and now I need to restore it on a managed database on DO, what would I need to do after I temporarily disable the option for the primary keys? Do I need to add them manually? Thanks a lot!

In the program that you use to connect and import the database, just run the query SET SESSION sql_require_primary_key=0; and then import the database. Since it is using the same connection for the import, this setting will be used. No need to try and append this to the database dump, just run the one line first.

After you have imported the database, make sure to set a primary key on each table.

Thank you, I've done that. I'm just wondering if I should set the primary key for each table manually from my client (TablePlus) or create a new Laravel migration for doing so.

Answer 41 · 2021-07-03T01:20:19.000Z

Creating a listener on Illuminate\Database\Events\MigrationsStarted with DB::statement('SET SESSION sql_require_primary_key=0'); in the handle() worked for me on my DigitalOcean dbs.

Answer 42 · 2021-07-27T00:57:57.000Z

Was this fixed in laravel migrations or still a problem using DO Managed databases?

Answer 43 · 2021-08-11T15:01:32.000Z

Hi guys!

I opened a ticket with DO and they changed the parameter for me. So no need for any "hacky" stuff ;)

Hi there,
Thanks for getting in touch with us!
I understand you will like to disable the primary requirement on your managed database. The primary requirement for your managed database ******* has been disabled.

Answer 44 · 2021-08-11T18:43:54.000Z

One simple solution is add this code at AppServiceProvider@register and listen the Migrations events

// imports 
use Illuminate\Support\Facades\Event;
use Illuminate\Database\Events\MigrationsEnded;
use Illuminate\Database\Events\MigrationsStarted;

// code in `register` method 
Event::listen(MigrationsStarted::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=0');
    }
});

Event::listen(MigrationsEnded::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=1');
    }
});

Or you can add a config variable, for example allow_disabled_pk in config file databases.php and call config('databases.allow_disabled_pk') instead of env('ALLOW_DISABLED_PK')

Controlled if you want run SET SESSION sql_require_primary_key from the .env, which normally only on servers you want to do.

Answer 45 · 2021-08-12T11:44:40.000Z

One simple solution is add this code at AppServiceProvider@register and listen the Migrations events
// imports 
use Illuminate\Support\Facades\Event;
use Illuminate\Database\Events\MigrationsEnded;
use Illuminate\Database\Events\MigrationsStarted;

// code in `register` method 
Event::listen(MigrationsStarted::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=0');
    }
});

Event::listen(MigrationsEnded::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=1');
    }
});
Or you can add a config variable, for example allow_disabled_pk in config file databases.php and call config('databases.allow_disabled_pk') instead of env('ALLOW_DISABLED_PK')

Controlled if you want run SET SESSION sql_require_primary_key from the .env, which normally only on servers you want to do.

Only don't use env() in your other files besides your config files. Or ask DO to change the sql requirement for you.

Answer 46 · 2021-08-12T14:17:27.000Z

One simple solution is add this code at AppServiceProvider@register and listen the Migrations events
// imports 
use Illuminate\Support\Facades\Event;
use Illuminate\Database\Events\MigrationsEnded;
use Illuminate\Database\Events\MigrationsStarted;

// code in `register` method 
Event::listen(MigrationsStarted::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=0');
    }
});

Event::listen(MigrationsEnded::class, function (){
    if (env('ALLOW_DISABLED_PK')) {
        DB::statement('SET SESSION sql_require_primary_key=1');
    }
});
Or you can add a config variable, for example allow_disabled_pk in config file databases.php and call config('databases.allow_disabled_pk') instead of env('ALLOW_DISABLED_PK')
Controlled if you want run SET SESSION sql_require_primary_key from the .env, which normally only on servers you want to do.
Only don't use env() in your other files besides your config files. Or ask DO to change the sql requirement for you.

Same, i use the config('database.allow_disabled_pk')

Answer 47 · 2021-09-27T11:16:30.000Z

I don't think this issue should be closed because current workarounds are not solving the root cause.
The problem is that row based replication requires you to define primary keys, so this is not only an issue for DigitalOcean managed databases. Current workarounds with session variables are error prone - you can create tables without primary keys (passwords_reset for example doesn't have a primary key).

Answer 48 · 2022-02-03T08:18:52.000Z

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']);.

so you still added the incremental id even if you have the combined primary? and it worked?

Answer 49 · 2022-02-16T14:56:08.000Z

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!!

How did you get this to work?

Answer 50 · 2022-05-12T08:54:24.000Z

I would like to say we decided to remove Telescope from the project because of missing primary keys and infinite Digital Ocean warnings.

Our systems have indicated that your MySQL cluster has tables without primary keys. We have identified that MySQL tables without primary keys can lead to service replication issues that jeopardize performance and availability. If primary keys are not present for database tables exceeding 5,000 rows, data-loss can occur.

DB::statement('SET SESSION sql_require_primary_key=0'); workaround works, but risks remain.

Sad, but nothing to do if maintainers refuse of primary keys.

Answer 51 · 2022-05-18T04:34:02.000Z

Never ever disable primary keys on HA MySQL and MariaDB clusters as this might lead to corruption of replication between server instances. Probably developers that made this migrations where not aware of how MySQL and MariaDB replication works, especially how Galera, and simply decided as they thought not to add primary keys.

Answer 52 · 2022-06-01T10:02:36.000Z

Should this issue be reopened since the fix was reverted and the issue still exists?

Answer 53 · 2022-06-02T04:42:59.000Z

Should this issue be reopened since the fix was reverted and the issue still exists?

I do so. Any table MUST have primary keys. And the framework, should test any DB transaction against most limited setup of MariaDB or MySQL, that being default Primary/Replica and Galera setups. Testing against single instance mode does not prove the framework will be trustful in HA setups.

Today is very common to deploy on HA setups, and I wouldn't find a reason not to do it so.

Answer 54 · 2022-06-02T09:07:13.000Z

@taylorotwell Would you consider reopening the issue? You say above that it's a "bug" with the Digital Ocean setup but in reality it's a recommended and increasingly common way to configure MySQL and simply turning off required primary keys is not a solution. I believe as things stand several first-party laravel packages such as Nova and Telescope contain migrations that simply can't be run in these setups, and it also makes it a pain to use uuid primary ids.

I did look at the revert commit that was merged earlier but couldn't find much context about why the fix was reverted so forgive me if there's a good reason to NOT fix this!

Answer 55 · 2022-06-18T13:07:06.000Z

DO engineers team has updated API documentation to make database configuration changes via API; you will now be able to disable sql_require_primary_key using API calls.

In certain cases, you may also want to adjust the sql_require_primary_key, which determines whether primary keys are a requirement. When set to True (default), primary keys are required. When set to False, primary keys are optional. However, we do not currently support adjusting this mode in the control panel. Instead, you can set this mode by making a configuration request via our API.

Then they add:

For clusters with more than one node, we strongly recommend keeping sql_require_primary_key as True. Setting it to False may severely impact replication and cause issues.

Answer 56 · 2022-06-18T18:33:42.000Z

The thing is I don't want to turn off this setting just in order to use uuid primary keys. This is the recommended setting for a reason and i want to retain it. I don't believe it should be necessary to use a less reliable mysql setup because of the way these migrations run under the hood

Answer 57 · 2022-06-19T02:01:28.000Z

@joe-pritchard Then so far the best solution came from @JVillator0

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        Event::listen(MigrationsStarted::class, function () {
            if (config('database.allow_disabled_pk')) {
                DB::statement('SET SESSION sql_require_primary_key=0');
            }
        });

        Event::listen(MigrationsEnded::class, function () {
            if (config('database.allow_disabled_pk')) {
                DB::statement('SET SESSION sql_require_primary_key=1');
            }
        });
    }
...

Answer 58 · 2022-06-20T07:34:18.000Z

Yes that's what I'm doing right now but it's not ideal as it essentially turns off the primary key requirement completely, since all migrations will run without it meaning there's no functional difference to just turning it off in the db's config. Again this is not a solution to the problem, it's a workaround, and I believe there is value in just fixing it at source

Answer 59 · 2022-06-20T12:51:49.000Z

Turning off the requirement is a bit like intentionally incurring technical debt - you know it's going to come back and bite you, just when you're in a rapid scaling situation.

Answer 60 · 2022-06-21T22:04:23.000Z

Note: don't use primary keys and in case of replication failure, deal with the problem and accept also the financial loses for the downtime caused.

Answer 61 · 2022-07-21T14:46:27.000Z

Laravel 8 + DigitalOcean database

💡 Just add the first statement to your migration:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class AddTableTemplates extends Migration
{
    public function up()
    {
        // fixes DigitalOcean sql_require_primary_key problem
        DB::statement('SET SESSION sql_require_primary_key=0');
        Schema::create('objects', function (Blueprint $table) {
            $table->string('id', 30)
                ->primary();
            // ...
        });
    }

    public function down()
    {
        Schema::dropIfExists('objects');
    }
}

Answer 62 · 2022-08-02T14:11:57.000Z

I encountered this issue myself recently and I think I found a better work around.  It’s pretty similar to those who suggested dumping the schema using another tool such as TablePlus but uses the new schema:dump command and works with the migration system.

 On the machine you use for development create a fresh database using (this will delete all existing tables and re-create them using your migrations):

php artisan migrate:fresh

Then dump your schema:

php artisan schema:dump

Then commit the changes and push them to the server. Now when you run the migrate command, Laravel will restore the database using the dumped schema file before running the migrations.

If you make any changes to the migrations, you will have to re-dump the schema file using this method. Once you are in production though, you shouldn’t have to do that again.

Hope this helps; sorry for commenting on an old issue; thanks Taylor and the Laravel Team for your amazing work 😁

Answer 63 · 2023-09-05T16:20:23.000Z

If anyone else who comes across this thread has a similar issue on Azure.

sql_generate_invisible_primary_key <- Make sure this one is turned off as well :D

Answer 64 · 2023-10-10T22:26:14.000Z

Digital Ocean has an API that you can use to configure the settings of your DB, which I would highly recommend over other solutions. You can do the following:

Create an API token if you don't have one already: https://cloud.digitalocean.com/account/api/tokens
Find the ID of your database cluster by querying the List all database clusters endpoint:

curl -X GET \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  "https://api.digitalocean.com/v2/databases"

Once you've found the ID of your database cluster, you can make a patch request to the update database config endpoint:

curl -X PATCH \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"config": {"sql_require_primary_key": true}}' \
  "https://api.digitalocean.com/v2/databases/$DATABASE_CLUSTER_ID/config"

Now your database cluster will be configured, permanently, without any code changes.

Answer 65 · 2023-12-14T20:09:57.000Z

Should be fixed by: #49374