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

root@ocp:~# mysql -u doadmin -p'BKwsQcqEGbSV3w' -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql
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. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
root@ocp:~# 

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

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Under [mysqld] section, add

sql_require_primary_key=0

Now restart MySQL server.

systemctl restart mysql

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.

SET sql_require_primary_key=0;

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