I host my sites on DigitalOcean and use their managed MySQL instances. One of the items that I regularly run into is their requirement of primary keys. When I run through the setup, I get the following error in the storage/laravel.log log.
ERROR 3750 (HY000) at line 1285: 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.
I did some searching and found this forum post, Error sql_require_primary_key DigitalOcean Managed Databases and then I looked at the GitHub issue that was referenced in that post and it looks like it was resolved on the Laravel side.
opened 07:56AM - 17 Jun 20 UTC
closed 01:40PM - 22 Jun 20 UTC
bug
help wanted
- Laravel Version: 7
- PHP Version: 7.3.14
- Database Driver & Version: MySQL … 8
### Description:
When MySQL has `sql_require_primary_key` enabled, migrations that call `->primary()` on a column during table creation fail because it appears as though two queries are ran. One to create the table, and then a second to alter the table and add the primary key.
For context, [DigitalOcean recently enforced primary keys on all newly created tables](https://www.digitalocean.com/docs/databases/#5-june-2020) in their managed databases.
### Steps To Reproduce:
1. Ensure `sql_require_primary_key` is enabled on the MySQL server
2. Create a migration that creates a new table with a string as the primary key (we used `$table->string('string')->primary();`) and does not have a default `$table->id();` column
3. Run the migration
The below error is generated when attempting to run the first query to create table.
```
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.
```
If this has been resolved on the Laravel side, what do I need to do to resolve the installation issue I’m having now?
Thank you!
hillel
January 8, 2025, 8:36pm
2
Hi,
I suggest asking in a discussion on GitHub.
1 Like