Terraform: google_sql_database_instance: invalidInstanceProperty

[phanect]

I ran terraform apply and got following error message.

* google_sql_database_instance.master: Error, failed to create instance notels: googleapi: Error 400: Invalid instance property., invalidInstanceProperty

This error message tells me just the process failed and I have no idea how to solve the problem.

I'm not sure if it is bug of Terraform itself or my wrong configuration file, but even if it is my failure, I want terraform to show more detailed error message.

I have created Google Cloud SQL instance manually before I ran terraform apply.
The instance name is the same, but some other parameter should be different from terraform configuration.

Following my Cloud SQL config:

google_sql_database_instance.master: Creating...
  database_version:                                          "" => "MYSQL_5_6"
  name:                                                      "" => "notels"
  region:                                                    "" => "asia-east1"
  replica_configuration.#:                                   "" => "1"
  replica_configuration.0.connect_retry_interval:            "" => "60"
  replica_configuration.0.master_heartbeat_period:           "" => "1"
  self_link:                                                 "" => "<computed>"
  settings.#:                                                "" => "1"
  settings.0.activation_policy:                              "" => "ALWAYS"
  settings.0.backup_configuration.#:                         "" => "1"
  settings.0.backup_configuration.0.binary_log_enabled:      "" => "1"
  settings.0.backup_configuration.0.enabled:                 "" => "1"
  settings.0.backup_configuration.0.start_time:              "" => "8:00"
  settings.0.crash_safe_replication:                         "" => "1"
  settings.0.database_flags.#:                               "" => "12"
  settings.0.database_flags.0.name:                          "" => "character_set_server"
  settings.0.database_flags.0.value:                         "" => "utf8"
  settings.0.database_flags.1.name:                          "" => "default_time_zone"
  settings.0.database_flags.1.value:                         "" => "+09:00"
  settings.0.database_flags.10.name:                         "" => "slow_query_log"
  settings.0.database_flags.10.value:                        "" => "on"
  settings.0.database_flags.11.name:                         "" => "wait_timeout"
  settings.0.database_flags.11.value:                        "" => "31536000"
  settings.0.database_flags.2.name:                          "" => "event_scheduler"
  settings.0.database_flags.2.value:                         "" => "on"
  settings.0.database_flags.3.name:                          "" => "general_log"
  settings.0.database_flags.3.value:                         "" => "on"
  settings.0.database_flags.4.name:                          "" => "innodb_file_per_table"
  settings.0.database_flags.4.value:                         "" => "on"
  settings.0.database_flags.5.name:                          "" => "innodb_flush_log_at_trx_commit"
  settings.0.database_flags.5.value:                         "" => "2"
  settings.0.database_flags.6.name:                          "" => "interactive_timeout"
  settings.0.database_flags.6.value:                         "" => "31536000"
  settings.0.database_flags.7.name:                          "" => "local_infile"
  settings.0.database_flags.7.value:                         "" => "off"
  settings.0.database_flags.8.name:                          "" => "log_output"
  settings.0.database_flags.8.value:                         "" => "TABLE"
  settings.0.database_flags.9.name:                          "" => "max_allowed_packet"
  settings.0.database_flags.9.value:                         "" => "3145728"
  settings.0.ip_configuration.#:                             "" => "1"
  settings.0.ip_configuration.0.authorized_networks.#:       "" => "2"
  settings.0.ip_configuration.0.authorized_networks.0.name:  "" => "notel-app0"
  settings.0.ip_configuration.0.authorized_networks.0.value: "" => "130.211.251.139"
  settings.0.ip_configuration.0.authorized_networks.1.name:  "" => "notel-app1"
  settings.0.ip_configuration.0.authorized_networks.1.value: "" => "104.155.200.186"
  settings.0.ip_configuration.0.ipv4_enabled:                "" => "1"
  settings.0.ip_configuration.0.require_ssl:                 "" => "0"
  settings.0.location_preference.#:                          "" => "1"
  settings.0.location_preference.0.zone:                     "" => "asia-east1-b"
  settings.0.pricing_plan:                                   "" => "PACKAGE"
  settings.0.replication_type:                               "" => "ASYNCHRONOUS"
  settings.0.tier:                                           "" => "D0"
  settings.0.version:                                        "" => "<computed>"

I tried both Terraform v0.6.8 and latest master 8eb65bec95d0e7fc9e6bf49138f2bf09b8966ead.

[lwander]

@phanect, It looks like local_infile is not a supported flag. I agree, a better error message is necessary; however, in this case we rely on the Google Cloud SQL API to report these kinds of configuration errors. Arguably, Terraform could manually verify the MySQL flags, but they are prone to change and add a lot of overhead that should be taken care of by the API endpoint. I suppose this is one of the problems with relying on a beta API, but I will file an internal bug to clean up these error messages.

[phanect]

Thanks @lwander.
Year, maybe I should ask Google to return better error message.

[phanect]

I removed local_infile from my .tf.json, but still the same error happens.
I have confirmed all other flags in .tf.json are listed in the doc you referred.
I also tried to comment out settings.database_flags but the same error occured.

I found crash_safe_replication parameter, which should be supplied only for replica, is supplied for master. This may be the cause.

I'm confirming to be sure, but I got Error 409: The instance or operation is not in an appropriate state to handle the request., invalidState. According to a stackoverflow article, I may have to wait for hours. I will retry it tomorrow.

[phanect]

Maybe replica_configuration and crash_safe_replication caused the above error.
I specified those two parameters to master instance.

I comment out those two parameters and run terraform apply, then Cloud SQL instance is successfully created.

[jedipunkz]

How did you write code for multiple "database_flags" ? I can not setup multiple database_flags with such code.

    database_flags {
            name = "slow_query_log"
            value = "on"
            name = "character_set_server"
            value = "utf8mb4"
    }

and I did not understand "sublist support" at document.

[nathanleclaire]

My issue might be unrelated to OPs, but this was the first place Google landed me on a search for that error message -- in my case I had tier = "D0" like the docs indicated, but needed something like tier = "db-f1-micro" instead. Put in https://github.com/terraform-providers/terraform-provider-google/pull/783 to hopefully help with this.

[tmshn]

Just to note: in my case, the problem is setting smaller disk size than 10GB.
https://cloud.google.com/sql/docs/mysql/admin-api/v1beta4/instances

Maybe I'll add validation on that if I had a time...

[hashibot[bot]]

I'm going to lock this issue because it has been closed for _30 days_ ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.