MySQL ERROR Unable to create or change a table without a primary key

When trying to restore a database backup to Managed DigitialOcean MySQL 8 database, i get following error

This is because one of the tables in your MySQL backup don’t have primary key defined.

Solution

There are few ways you can fix this. Proper way is to define a primary key for the table. If this is not possible, you can set sql_require_primary_key to OFF.

To set sql_require_primary_key to OFF, edit file

Under [mysqld] section, add

Now restart MySQL server.

In the case of DigitalOcean, we can’t edit MySQL configuration as it is Managed MySQL Database service, What you can do is edit the MySQL backup file. Add following code to top of the file.

This should be added as first SQL statement. Now do restore again, it will work.

Leave a Reply

Your email address will not be published. Required fields are marked *