How to Set Global SQL Mode on MySQL Clusters

To set global SQL mode via the command-line, follow these steps:

1. Install doctl, the DigitalOcean command-line tool.

2. Create a personal access token, and save it for use with doctl.

3. Use the token to grant doctl access to your DigitalOcean account.

                 doctl auth init
               

4. Finally, set global SQL mode with doctl databases sql-mode set. The basic usage looks like this, but you'll want to read the usage docs for more details:

                 doctl databases sql-mode set <database-cluster-id> <sql-mode-1> ... <sql-mode-n> [flags]
               

   The following example sets the SQL mode ALLOW_INVALID_DATES for an existing database cluster with the ID ca9f591d-f38h-5555-a0ef-1c02d1d1e35. The cluster already has the modes NO_ZERO_DATE, NO_ZERO_IN_DATE, STRICT_ALL_TABLES set, but they must be included in the command to avoid being overwritten by the additional mode

                      doctl databases sql-mode set ca9f591d-f38h-5555-a0ef-1c02d1d1e35 NO_ZERO_DATE NO_ZERO_IN_DATE STRICT_ALL_TABLES ALLOW_INVALID_DATES
                   

To set global SQL mode using the DigitalOcean API, follow these steps:

1. Create a personal access token, and save it for use with the API.

2. Send a PUT request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/sql_mode

   cURL

   To set global SQL mode with cURL, call:

   
                   curl -X PUT \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
     -d '{"sql_mode":"ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE"}' \
     "https://api.digitalocean.com/v2/databases/9cdb64e5-61e4-4b30-b711-11ef66d84558/sql_mode"

   Go

   Go developers can use Godo, the official DigitalOcean V2 API client for Go. To set global SQL mode with Godo, use the following code:

   
                   import (
       "context"
       "os"
   
       "github.com/digitalocean/godo"
   )
   
   func main() {
       token := os.Getenv("DIGITALOCEAN_TOKEN")
   
       client := godo.NewFromToken(token)
       ctx := context.TODO()
   
       sqlMode := "ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE"
       _, err := client.Databases.SetSQLMode(ctx, "9cc10173-e9ea-4176-9dbc-a4cee4c4ff30", sqlMode)
   }

   Python

   
                   import os
   from pydo import Client
   
   client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))
   
   req = {
     "sql_mode": "ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE"
   }
   
   update_resp = client.databases.update_sql_mode(database_cluster_uuid="a7a8bas", body=req)