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
|
1 2 3 4 |
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
|
1 |
vi /etc/mysql/mysql.conf.d/mysqld.cnf |
Under [mysqld] section, add
|
1 |
sql_require_primary_key=0 |
Now restart MySQL server.
|
1 |
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.
|
1 |
SET sql_require_primary_key=0; |
This should be added as first SQL statement. Now do restore again, it will work.