SQL modes define the default data validation check that MySQL performs to match the application requirement. For instance, the global NO_ZERO_DATE
mode instructs MySQL only to allow legal dates and restrict invalid values like 0000-00-00
. Each managed database can have its own set of SQL modes. Always match the managed databases' SQL modes with the development servers.
Follow this guide to manage global SQL modes for Vultr Managed Database for MySQL using Vultr Customer Portal, API, CLI, or Terraform.
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.
$ curl "https://api.vultr.com/v2/databases" \
-X GET \
-H "Authorization: Bearer ${VULTR_API_KEY}"
Send a PUT
request to the Update Managed Database endpoint and specify the database ID to update the SQL modes.
$ 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
}'
List all database instances and note the database ID. For instance, d6ac2a3c-92ea-43ef-8185-71a23e58ad8c
.
$ vultr-cli database list --summarize
Specify a database ID to add a new SQL mode to the database. For instance, ONLY_FULL_GROUP_BY
.
$ 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
Open your Terraform configuration for the existing Managed Database for MySQL resource.
Add or update the mysql_sql_modes
and mysql_require_primary_key
arguments.
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
}
Apply the configuration and observe the following output:
Apply complete! Resources: 0 added, 1 changed, 0 destroyed.
No comments yet.