google_sql_database_instance incorrectly handles optional insights_config

[dbason]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request.
• Please do not leave +1 or me too comments, they generate extra noise for issue followers and do not help prioritize the request.
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.
• If an issue is assigned to a user, that user is claiming responsibility for the issue.
• Customers working with a Google Technical Account Manager or Customer Engineer can ask them to reach out internally to expedite investigation and resolution of this issue.

Terraform Version & Provider Version(s)

Terraform v1.9.3
on linux_amd64

• provider registry.terraform.io/datadog/datadog v2.26.1
• provider registry.terraform.io/hashicorp/aws v2.70.4
• provider registry.terraform.io/hashicorp/google v5.39.1
• provider registry.terraform.io/hashicorp/google-beta v5.39.1

Affected Resource(s)

google_sql_database_instance

Terraform Configuration

resource "google_sql_database_instance" "bug" {
  provider            = google
  name                = "bug"
  region              = "us-east1"
  database_version    = "POSTGRES_15"
  project             = local.project_id
  deletion_protection = true

  settings {
    disk_autoresize             = true
    disk_type                   = "PD_HDD"
    tier                        = redacted
    deletion_protection_enabled = true

    activation_policy = "ALWAYS"
    availability_type = "REGIONAL"

    edition = "ENTERPRISE"

    ip_configuration {
      ipv4_enabled                                  = false
      require_ssl                                   = false
      private_network                               = data.google_compute_network.bug_project.self_link
      enable_private_path_for_google_cloud_services = true
    }

    location_preference {
      zone = "us-east1-b"
    }

    backup_configuration {
      enabled                        = true
      start_time                     = "13:00"
      point_in_time_recovery_enabled = true
      location                       = "us"
    }

    maintenance_window {
      day          = 7
      hour         = 14
      update_track = "stable"
    }

    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

Debug Output

No response

Expected Behavior

Expect the terraform plan to show no changes

Actual Behavior

Plan shows changes to be applied:

  # google_sql_database_instance.test will be updated in-place
  ~ resource "google_sql_database_instance" "bug" {
        id                             = "bug"
        name                           = "bug"
        # (18 unchanged attributes hidden)

      ~ settings {
            # (16 unchanged attributes hidden)

          - insights_config {
              - query_insights_enabled  = false -> null
              - query_plans_per_minute  = 0 -> null
              - query_string_length     = 0 -> null
              - record_application_tags = false -> null
              - record_client_address   = false -> null
            }

            # (5 unchanged blocks hidden)
        }
    }

Steps to reproduce

1. terraform plan

Important Factoids

If we set the insights_config block to be what is shown in the changes to be applied the plan errors because query_string_length must be between 256 - 4500.

If we then remove the query_string_length from the insights_config section we get the following:

 # google_sql_database_instance.test will be updated in-place
~ resource "google_sql_database_instance" "bug" {
        id                             = "bug"
        name                           = "bug"
        # (18 unchanged attributes hidden)

      ~ settings {
            # (16 unchanged attributes hidden)

          ~ insights_config {
              ~ query_string_length     = 0 -> 1024
                # (4 unchanged attributes hidden)
            }

            # (5 unchanged blocks hidden)
        }
    }

However in the documentation it states that updating the query_string_length requires an instance restart so this is not something we want to apply without verification

References

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#query_string_length

https://cloud.google.com/sql/docs/postgres/using-query-insights

b/356874854

[longpaul]

The workaround is to enable query insights via Web GUI (it doesn't mention restart on Save )
Then you can add the following config in Terraform (shouldn't make any changes)

  insights_config = {
    query_insights_enabled  = true
    query_string_length     = 1024
    record_application_tags = false
    record_client_address   = false
  }

Nevertheless... still a bug of course.

[dbason]

We want it left disabled, the major issue for us is that this is showing up as drift in our internal systems

[longpaul]

We want it left disabled, the major issue for us is that this is showing up as drift in our internal systems

Yes, unfortunately I don't see the possibility to use
query insights enabled = false
(without configuration drift)
so that definitely should be fixed.

[lra]

Same issue using the google sql db terraform module: terraform-google-modules/terraform-google-sql-db#623
Our only workaround with the module is to enable insights on every database.

[adavi92-fizco]

Another workaround that works for me is to add a block inside the resource of Terraform:

  lifecycle {
    ignore_changes = [ 
      settings[0].insights_config 
    ]
  }

[mike-code]

Another workaround that works for me is to add a block inside the resource of Terraform:

  lifecycle {
    ignore_changes = [ 
      settings[0].insights_config 
    ]
  }

This unfortunately won't work for modules

[SarahFrench]

For the person who works on this ticket, some relevant info is in this comment: #18943 (comment)

[c2thorn]

Hey folks, apologies for the perma-diff here.

I've reached out internally to see if we can get the API change that caused this rolled back. In the meantime, GoogleCloudPlatform/magic-modules#11327 should fix this in the next release.

To mitigate this now in current and previous provider versions, the lifecycle argument mentioned in #18918 (comment) is available. Unfortunately this will not apply for modules due to hashicorp/terraform#27360

[c2thorn]

v5.40.0 has the fix in the provider

The internal backend team is investigating a backend fix as well that would be able to resolve for older provider versions. (b/356874854 for internal people)

[c2thorn]

I've received word from the internal team that a fix preventing this behavior in the API has also rolled out.

[github-actions]

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.