Unable to create or change a table without a primary key - Laravel DigitalOcean Managed Database

I was trying to fix this problem with an import to DO Managed MySQL using a mysqldump file from a WordPress installation. I found adding this to the top of the file did work for my import.

SET @ORIG_SQL_REQUIRE_PRIMARY_KEY = @@SQL_REQUIRE_PRIMARY_KEY;
SET SQL_REQUIRE_PRIMARY_KEY = 0;

I then imported using JetBrains DataGrip and it worked without error.

Just add set sql_require_primary_key = off Like this

to your SQL file.

Add in your first migration:

\Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0');

Inside: Schema::create() function.

From March 2022, you can now configure your MYSQL and other database by making a request to digital ocean APIs. Here's the reference: https://docs.digitalocean.com/products/databases/mysql/#4-march-2022

STEPS TO FIX THE ISSUE:

Step - 1: Create AUTH token to access digital ocean APIs. https://cloud.digitalocean.com/account/api/tokens

STEP - 2: Get the database cluster id by hitting the GET request to the below URL with bearer token that you have just generated above.

URL: https://api.digitalocean.com/v2/databases

Step - 3: Hit the below URL with PATCH request along with the bearer token and payload.

URL: https://api.digitalocean.com/v2/databases/{YOUR_DATABASE_CLUSER_ID}/config

payload: {"config": { "sql_require_primary_key": false }}

That's all. It worked flawlessly.

For more information, please refer to API DOCS: https://docs.digitalocean.com/products/databases/mysql/#latest-updates

One neat solution is defined here. The solution is to add listeners to migrate scripts and turn sql_require_primary_key on and off before and after executing a migration. This solution solve the problem where one is unable modify migrations script such as when they are from a library or a framework like Voyager.

<?php

namespace App\Providers;

    use Illuminate\Database\Events\MigrationsStarted;
    use Illuminate\Database\Events\MigrationsEnded;
    use Illuminate\Support\Facades\DB;
    use Illuminate\Support\Facades\Event;
    use Illuminate\Support\ServiceProvider;
    
    class AppServiceProvider extends ServiceProvider {
        /**
         * Register any application services.
         *
         * @return void
         */
        public function register() {
    
            // check this one here https://github.com/laravel/framework/issues/33238#issuecomment-897063577
            Event::listen(MigrationsStarted::class, function (){
                if (config('databases.allow_disabled_pk')) {
                    DB::statement('SET SESSION sql_require_primary_key=0');
                }
            });
    
            Event::listen(MigrationsEnded::class, function (){
                if (config('databases.allow_disabled_pk')) {
                    DB::statement('SET SESSION sql_require_primary_key=1');
                }
            });
        } 
// rest of the class 

}

For bigger sql file, can with this command (nano editor can open in 1 week if your file size is <8GB, lol):

First :

sed  -i '1i SET SQL_REQUIRE_PRIMARY_KEY = 0;' db.sql

Second :

sed  -i '1i SET @ORIG_SQL_REQUIRE_PRIMARY_KEY = @@SQL_REQUIRE_PRIMARY_KEY;' db.sql

According to the MySQL documentation purpose of this system variable is

to avoid replication performance issues: "Enabling this variable helps avoid performance problems in row-based replication that can occur when tables have no primary key."

IMHO, there are two possible options to consider for your problem;

• Add primary key to this and every table in your migration, including temporary tables. This one is better and i think more convenient way to do it since there is no drawback to have primary key for each table.

Whether statements that create new tables or alter the structure of existing tables enforce the requirement that tables have a primary key.

• Change your provider because according to here "We support only MySQL v8."

Also here is the bug report

I contacted DigitalOcean via a ticket to ask if they want to disable the requirement and they did the next day :)

So you can just ask them

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

Unfortunately, we can't change the sql_require_primary_key value in the digital ocean MySQL database. instead, you can set the id to the primary key just by adding primary()

When enabled, sql_require_primary_key has these effects:

1. Attempts to create a new table with no primary key fail with an error. This includes CREATE TABLE ... LIKE. It also includes CREATE TABLE ... SELECT, unless the CREATE TABLE part includes a primary key definition.
2. Attempts to drop the primary key from an existing table fail with an error, with the exception that dropping the primary key and adding a primary key in the same ALTER TABLE statement is permitted.

Dropping the primary key fails even if the table also contains a UNIQUE NOT NULL index.

3. Attempts to import a table with no primary key fail with an error.

Default value is OFF , but in your case you need to set OFF from ON

IMPORTANT LINK

HOW TO SET

add this line to your migration file. $table->increments('aid');