I am struggling with importing my databases to Digital Ocean database clusters. They have a setting that requires the primary key to be defined in the CREATE TABLE statement, but all my tools (phpmyadmin, mysqlworkbench, tablePlus) generate export files that split it up into first defining the columns in a CREATE TABLE and much further down in the SQL file the keys and indices are added by an ALTER TABLE statement.

Which is not allowed by Digital Ocean.

Is there any way at all (without manually editing the generated SQL output 🤯) to generate such an export? I am not comfortable with mysqldump, not sure I have it, and I definately don't have cli access to all the databases that I need to move to the DO cloud.

I have seen an instruction where, by enabling DO API, I can disable this setting. I'd like to know if there is a better solution, without lowering the integrity (Pkey) checks in DO, and especially without enabling an API that introduces a major security risk since even deletion of database clusters can be done through that API. Also, I am uncomfortable with how to carry out that procedure. Their instruction was this:

Ex :- curl -X PATCH -H "Content-Type: application/json" -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" -d '{"config": {"sql_require_primary_key": false}}' "https://api.digitalocean.com/v2/databases/$UUID/config"

I'm not even sure if that means I should run that in my own terminal, or if I should open the console on one of the droplets on DO and run that... bit out of my comfort zone. I usually export and import SQL files, set up access rules for DBs and code web apps..

  • Please provide more details on "not allowed by Digital Ocean" -- what is the error message? At what point in importing does it complain?
    – Rick James
    Jan 30, 2023 at 19:47
  • Try this for the export method mysqldump -u [user] -p [database] > [dumpfile].sql and them import that .sql into DigitalOcean since these will contain create table statements with primary keys defined, I don't see why this wouldn't work. Give it a shot and let me know if merely exporting to .sql format and then importing into DO if that's all you need. Jan 30, 2023 at 22:51


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.