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?
mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 3750 (HY000) at line 223: 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.
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. Sorry, something went wrong.
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. \Illuminate\Support\Facades\DB::statement ('SET SESSION sql_require_primary_key=0'); Inside: Schema::create () function.
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.
Add in your first migration:
\Illuminate\Support\Facades\DB::statement('SET SESSION sql_require_primary_key=0');
Inside: Schema::create()
function.
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.
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 }}
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;
Whether statements that create new tables or alter the structure of existing tables enforce the requirement that tables have a primary key.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With