Vultr Customer Portal

1. Navigate to Products and select Databases.
2. Click the target database instance.
3. Click Settings and select MySQL Configuration.
4. Then, navigate to Global SQL Modes. Add or remove any mode as required

Vultr API

1. List all the database instances by sending a GET request to the List Managed Databases endpoint and note the database ID (For example, 43b4c774-5dff-4ac0-a01f-78a23c2205b5) and the active SQL modes.
   console

   $ curl "https://api.vultr.com/v2/databases" \
       -X GET \
       -H "Authorization: Bearer ${VULTR_API_KEY}"
   

2. Send a PUT request to the Update Managed Database endpoint and specify the database ID to update the SQL modes.
   console

   $ curl "https://api.vultr.com/v2/databases/datase_id" \
       -X PUT \
       -H "Authorization: Bearer ${VULTR_API_KEY}" \
       -H "Content-Type: application/json" \
       --data '{
           "mysql_sql_modes" : [
               "ANSI",
               "ERROR_FOR_DIVISION_BY_ZERO",
               "NO_ENGINE_SUBSTITUTION",
               "NO_ZERO_DATE",
               "NO_ZERO_IN_DATE",
               "STRICT_ALL_TABLES",
               "ONLY_FULL_GROUP_BY"
           ],
           "mysql_require_primary_key":true            
       }'

Vultr CLI

1. List all database instances and note the database ID. For instance, d6ac2a3c-92ea-43ef-8185-71a23e58ad8c.
   console

   $ vultr-cli database list --summarize
   

2. Specify a database ID to add a new SQL mode to the database. For instance, ONLY_FULL_GROUP_BY.
   console

   $ vultr-cli database update database_id \
   --mysql-sql-modes "ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY" \
   --mysql-require-primary-key true

Terraform

1. Open your Terraform configuration for the existing Managed Database for MySQL resource.
2. Add or update the mysql_sql_modes and mysql_require_primary_key arguments.
   terraform

   resource "vultr_database" "mysql" {
       # ...existing fields (database_engine, region, plan, label, etc.)
   
       mysql_sql_modes = [
           "ANSI",
           "ERROR_FOR_DIVISION_BY_ZERO",
           "NO_ENGINE_SUBSTITUTION",
           "NO_ZERO_DATE",
           "NO_ZERO_IN_DATE",
           "STRICT_ALL_TABLES",
           "ONLY_FULL_GROUP_BY"
       ]
   
       mysql_require_primary_key = true
   }
   

3. Apply the configuration and observe the following output:

   Apply complete! Resources: 0 added, 1 changed, 0 destroyed.