framework: sql_require_primary_key Causes Tables With String Primary Key To Fail

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.

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.

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?

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.

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.

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

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.

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

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

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

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.

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

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 😃

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

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

Should be fixed by: https://github.com/laravel/framework/pull/49374

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.

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

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.

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:

1. Create an API token if you don’t have one already: https://cloud.digitalocean.com/account/api/tokens
2. 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"

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

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 😄

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.

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

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.

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.

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 ?

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 😁

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.

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

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.

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

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.

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.

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

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.

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;