5

I recently created a database server in Digital Ocean, and it only supports MySQL 8. When I try to import a database of my Laravel project it reports this error:

Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.

So I tried to change the sql_require_primary_key to OFF in mySQL server by running the command,

set sql_require_primary_key = off;

And it changed successfully, but after that it automatically returned to the previous setting.

In Laravel, some primary keys are set after creating the table, so it showing error while migrating. It's my live project so that I can not modify the migrations I already created.

Anyone knows how to change the sql_require_primary_key permanently on MySQL 8?

James Risner
  • 5,451
  • 11
  • 25
  • 47
Midhun Babu
  • 119
  • 1
  • 10
  • 3
    Are you using a DigitalOcean managed database? – AlbinoDrought Feb 11 '21 at 06:35
  • @AlbinoDrought yes. – Midhun Babu Feb 11 '21 at 06:37
  • primary keys are really recommended. Are you sure your tables don't have a logical primary key? They have implication for shared databases which is why DO have probably set this. [invisible columns](https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html) of a primary key might be a easy way to add one. – danblack Feb 11 '21 at 06:47
  • @danblack As I mentioned in I'm using laravel project and by default it sets the primary keys after creating the table. – Midhun Babu Feb 11 '21 at 12:58
  • There's a big discussion [here](https://github.com/laravel/framework/issues/33238) with a few work arounds. – danblack Feb 11 '21 at 22:28
  • [What is the XY problem?](https://meta.stackexchange.com/q/66377/266284) – philipxy Feb 11 '21 at 23:03
  • 1
    Does this answer your question? [Unable to create or change a table without a primary key - Laravel DigitalOcean Managed Database](https://stackoverflow.com/questions/62418099/unable-to-create-or-change-a-table-without-a-primary-key-laravel-digitalocean) – philipxy Feb 11 '21 at 23:13
  • 1
    @philipxy I think I've clearly stated the problem here, can you please tell me if there's any confusions in my question ! – Midhun Babu Feb 17 '21 at 05:20
  • The link I gave seems to ask the same question as you & the answer is you can't. If you think the answer doesn't apply then say why, including explaining your system architecture & problem in detail. I gave a link to the notion of XY problem because it seems likely that you actually have some more relevant unaddressed goal that you are trying to achieve by (possibly wrongly or needlessly) setting this mode. If possible give a [mre]. – philipxy Feb 17 '21 at 10:46
  • 1
    @philipxy As I mentioned my product is live, so I can't modify the migrations that I created at the beginning. The issue can be recreated by the following method. (hope you're familiar with laravel). Start new laravel project and create migration, don't mention the primary key, then create another migration to alter the table to make primary key for the table column. Then create DO database and add configuration to the laravel project, then migrate the DB, you'll get the error. Hope it helps. – Midhun Babu Feb 17 '21 at 12:27
  • Please clarify via edits, not comments. PS Please act on my last comment. – philipxy Feb 17 '21 at 16:29

1 Answers1

5

A temporary workaround is to add turn sql_require_primary_key off for a Laravel project, you may add a statement for each database connection.

Inside Illuminate/Session/Console/stubs/database.stub, add this above Schema::create():

use Illuminate\Support\Facades\DB;
DB::statement('SET SESSION sql_require_primary_key=0');
Schema::create('sessions' ...

Once you have done the migration and restore, you can remove this change.

If you use Digital Ocean's API, there documentation on the API here. Alternatively, you may contact their support to turn that requirement off for your server.

James Risner
  • 5,451
  • 11
  • 25
  • 47