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

Question

I've just deployed my app to DigitalOcean using (Managed Database) and I'm getting the following error when calling php artisan migrate

SQLSTATE[HY000]: 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. (SQL: create table `sessions` (`id` varchar(255) not null,
`user_id` bigint unsigned null, `ip_address` varchar(45) null,
`user_agent` text null, `payload` text not null, `last_activity` int not null)
default character set utf8mb4 collate 'utf8mb4_unicode_ci')

It appears that Laravel Migrations doesn't work when mysql var sql_require_primary_key is set to true.

Do you have any solutions for that?

hamza ajaz · Answer 1 · 2022-07-04T13:11:19.757

73

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

edited Jul 04 '22 at 13:11

answered May 30 '22 at 17:23

hamza ajaz

978
7
6

4

Worked for me! Use this online post req maker https://reqbin.com/ if you dont have postman installed – Anand Singh Jul 20 '22 at 03:45
I aways get: Method not allowed – ofumbi Aug 06 '22 at 08:05
Which tool are you using to send the request to the API? Which request method are you using? @ofumbi – hamza ajaz Aug 09 '22 at 13:40
It needs PATCH method not PUT, maybe they changed it – lorigio Aug 14 '22 at 12:24
@hamza ajaz personally am using my terminal and sending the requests as curl but I believe you can use tools like postman or insomnia to send those request easily – stanley mbote Sep 22 '22 at 15:48
You're a life savior, I've been struggling with this since this morning. Done it with thunderclient extension in Visual Studio Code and worked flawlessly – Douglas Roos Feb 27 '23 at 21:01
when sending the patch request via postman, it's easiest if you send in the body/payload via "raw" option. thks @hamzaajaz, saved hours ;) ! – Fabus Jun 02 '23 at 19:42
@AnandSingh you are a life saver. thanks for the suggestion reqbin.com – kaushik gandhi Jun 07 '23 at 14:52

score 36 · Answer 2 · answered Apr 07 '21 at 18:46

36

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.

answered Apr 07 '21 at 18:46

Kevin Phillips

461
4
2

1

Why not just add a primary key to the tables that were missing them before importing them? – darryn.ten May 05 '21 at 08:18
Note that according to DigitalOcean "MySQL databases containing tables without a primary key and which contain more than 5000 rows may experience replication issues." – Liam May 09 '22 at 18:03
If you're seeing this in 2022 or later, [this answer](https://stackoverflow.com/a/72438019/7437737) is what you're looking for as it doesn't require a manipulation of your mysqldump file. – Zach Gollwitzer Jun 22 '22 at 12:01

score 19 · Answer 3 · edited Jun 25 '21 at 14:25

19

Add in your first migration:

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

Inside: Schema::create() function.

edited Jun 25 '21 at 14:25

Toni

1,555
4
15
23

answered Jun 24 '21 at 14:32

Usman Ayub

191
1
4

This solves the issue for session specific cases. – Qumber Mar 09 '22 at 14:58
1

You mentioned inside `Schema::create()`, however that function requires two arguments. I think you meant to put this line before `Schema::Create()` yeah? – lsimonetti Jun 21 '22 at 01:56
Yes lsimonetti you're right. – Usman Ayub Aug 11 '22 at 12:39

score 17 · Answer 4 · edited Dec 04 '20 at 07:05

17

Just add set sql_require_primary_key = off Like this

Click to view image

to your SQL file.

edited Dec 04 '20 at 07:05

Werner Banyacskay

22
5

answered Aug 12 '20 at 04:51

Tejsingh

277
2
4

7

set sql_require_primary_key =off; – Namal Aug 20 '20 at 02:03
1

This sis not allowed in DigitalOcean ManagedDatabases. – Peter Fox Feb 17 '21 at 10:33
Just FYI, you can set `sql_require_primary_key` off (set it to 0) on Digital Ocean managed databases. The default will always be on, but you can reset it for any session. Including a primary key on your table will always be easiest, otherwise the raw SQL to set this variable can be added to your laravel migration scripts for the tables that need it. – Jason Feb 16 '22 at 17:45

score 12 · Answer 5 · edited Oct 28 '21 at 18:52

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 

}

worked like magic for me – stanley mbote Sep 23 '22 at 14:55 — stanley mbote, Sep 23 '22 at 14:55
This works on Laravel 10 and DigitalOcean – Ntiyiso Rikhotso Aug 16 '23 at 13:05 — Ntiyiso Rikhotso, Aug 16 '23 at 13:05

score 6 · Answer 6 · answered Oct 19 '21 at 13:48

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

Ersoy · Accepted Answer · 2020-06-16T22:28:38.067

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

score 4 · Answer 8 · answered Aug 11 '21 at 14:21

4

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

answered Aug 11 '21 at 14:21

Ryon M

81
6

Yes but note that "MySQL databases containing tables without a primary key and which contain more than 5000 rows may experience replication issues." – Liam May 09 '22 at 18:01

score 3 · Answer 9 · answered Feb 11 '21 at 01:57

3

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

answered Feb 11 '21 at 01:57

MOUSUF C A

31
1

1

i added it to the sql file on digitalocean and it worked – Aryeh Armon Mar 01 '21 at 07:36

score 2 · Answer 10 · answered Jun 16 '20 at 22:17

When enabled, sql_require_primary_key has these effects:

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

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

@Michal did you find this answer helpful,then please mark as answer or give a upvote — Md. Amirozzaman, Jun 18 '20 at 18:40

score 1 · Answer 11 · answered Dec 10 '22 at 01:05

1

If you're importing in some SQL client, just run this query on that particular database before importing.

set sql_require_primary_key = off

Works all good for DO managed Mysql Database. Cheers!

answered Dec 10 '22 at 01:05

Usama Munir

589
9
11

score -1 · Answer 12 · answered Mar 02 '23 at 23:06

If you see this error while using laravel, it's most likely from the password_resets_table

Update the up() method in your migration to below:

  public function up()
  {
    DB::statement('SET SESSION sql_require_primary_key=0');
    Schema::create('password_resets', function (Blueprint $table) {
      ....
    });
    DB::statement('SET SESSION sql_require_primary_key=1');
  }

Note: Remember to import DB

use Illuminate\Support\Facades\DB;

score -3 · Answer 13 · answered Nov 17 '20 at 22:01

-3

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

answered Nov 17 '20 at 22:01

Mahfuz-ur Rahman

68
5

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

13 Answers13

That's all. It worked flawlessly.

Linked