Changing only the settings.version attribute in the google_sql_database_instance resource should not be shown as drift

[verdel]

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

Terraform v1.7.5
on darwin_arm64

• provider registry.terraform.io/hashicorp/google v5.24.0
• provider registry.terraform.io/hashicorp/google-beta v5.24.0

Affected Resource(s)

google_sql_database_instance

Terraform Configuration

resource "google_sql_database_instance" "default" {...}
output "settings" {
  value       = google_sql_database_instance.default.settings
}

Debug Output

No response

Expected Behavior

Changing the settings.version attribute should not cause drift in the google_sql_database_instance resource if other attributes in settings are not changed

Actual Behavior

Several users have reported cases where only the settings.version attribute was changed, without altering any other settings attributes. Since the settings.version attribute is a read-only attribute, its external modification should not be reflected as drift in the configuration of the google_sql_database_instance resource.

Here's what the output of the terraform plan command looks like in this case:

Terraform used the selected providers to generate the following execution
plan. Resource actions are indicated with the following symbols:

    create

Terraform will perform the following actions:
module.pg.google_sql_database.default[0] will be created

    resource "google_sql_database" "default" {
        charset = "UTF8"
        collation = "en_US.UTF8"
        deletion_policy = "DELETE"
        id = (known after apply)
        instance = "******-stage-db-02df5265"
        name = "******-stage-db"
        project = "******-stage"
        self_link = (known after apply)
        }

Plan: 1 to add, 0 to change, 0 to destroy.

─────────────────────────────────────────────────────────────────────────────

Note: You didn't use the -out option to save this plan, so Terraform can't
guarantee to take exactly these actions if you run "terraform apply" now.

module.pg.google_sql_database_instance.default has changed

~ resource "google_sql_database_instance" "default" { 
     id = "******-stage-db-02df5265"
     name = "******-stage-db-02df5265"
     # (14 unchanged attributes hidden)

  ~ settings {
       ~ version = 18 -> 21
          # (12 unchanged attributes hidden)

          # (5 unchanged blocks hidden)
     }

    # (1 unchanged block hidden)
}

Unless you have made equivalent changes to your configuration, or ignored the relevant attributes using ignore_changes, the following plan may include actions to undo or respond to these changes

Steps to reproduce

1. terraform apply or terraform plan

Important Factoids

No response

References

terraform-google-modules/terraform-google-sql-db#548

b/335253065

[ggtisc]

Hi @verdel!

As I understand reading the main conversation between you and imrannayer from the attached link and from this one:

You have an existing google_sql_database resource, but then when you update any other resources that aren't the google_sql_database it internally changes the settings.version

Please confirm this statement to understand your needs.

[verdel]

@ggtisc, not quite. I'm creating a google_sql_database_instance(not google_sql_database) resource, and after some time when executing terraform plan or terraform apply, I get a warning in the command output indicating that the settings.version attribute of the resource has changed. Meanwhile, no other attributes within settings change (although, based on the purpose of settings.version— which is to be an incrementing revision number of settings — this attribute should not change unless another settings key changes).

The warning that I receive in the command output:

Terraform detected the following changes made outside of Terraform since the
last "terraform apply" which may have affected this plan

[ggtisc]

Then you have the existing google_sql_database_instance resource without making changes for a long time. And one day without making any change again to that same resource or adding other new resources to that project you simply run a terraform apply or a terraform plan and it change the version of this unique resource? or were there changes to other resources in the same project?

[verdel]

@ggtisc, the Terraform code does not change between calls to terraform apply. No changes are made in the web interface or through gcloud either. Nothing changes in other resources within the project. The output of terraform plan or terraform apply indicates that only the settings.version attribute changes.

In the JSON API documentation, it is written that settingsVersion is the version of instance settings. This means that changes to this attribute(settings.version) can only occur if the settings object itself changes.

I thought that one possible reason for the change in settings.version might be that some sub-attributes present in a Google Cloud PostgreSQL instance are not being saved in the Terraform state. If this sub-attribute changes on the platform side, then settings.version will change, but since the sub-attribute is beyond the visibility for the Terraform state, in the output of terraform plan or terraform apply we will only see the change in settings.version.

I checked in the resource_sql_database_instance.go code all the sub-attributes that are saved in the Terraform state and noticed a discrepancy with the documentation only in one attribute - databaseReplicationEnabled. This is just my guess, but it is the only difference from the documentation that I see right now.

Unfortunately, I can't yet explain why the real state of a Google Cloud PostgreSQL instance might only differ in settings.version from the Terraform state.

I am currently waiting for the settings.version change to be reproduced without changes in other sub-attributes.

As I currently do not have my own data to confirm this behavior, in the process of creating this issue, I requested data from another participant(@juliusoh) in the discussion of the issue in the terraform-google-modules/terraform-google-sql-db repository.

[ggtisc]

It looks like the 'settrings.version' is being updated randomly without changing any configuration in the involved resources on a main-configuration.tf but isn't possible to replicate this scenario to be sure that it is happening. As user reported any change was made to the resources, just made a terraform plan or terraform apply without changes to be applied.

[verdel]

Let's wait a bit. As soon as I reproduce the issue, I will add additional information, including the output of the command gcloud sql instances describe <db-instance-name> before and after changing settings.version.

By comparing the outputs, we can see exactly what changes in the settings.

[verdel]

@ggtisc, I finally received confirmation that the settings.version can change without altering other attributes of the Google Cloud SQL instance.

Console output of terraform apply:

Note: Objects have changed outside of Terraform

Terraform detected the following changes made outside of Terraform since the
last "terraform apply" which may have affected this plan:

  # google_sql_database_instance.default has changed
  ~ resource "google_sql_database_instance" "default" {
        id                             = "test"
        name                           = "test"
        # (18 unchanged attributes hidden)

      ~ settings {
          ~ version                     = 99 -> 101
            # (14 unchanged attributes hidden)

            # (6 unchanged blocks hidden)
        }

        # (1 unchanged block hidden)
    }


Unless you have made equivalent changes to your configuration, or ignored the
relevant attributes using ignore_changes, the following plan may include
actions to undo or respond to these changes.

─────────────────────────────────────────────────────────────────────────────

Changes to Outputs:
  ~ primary                  = (sensitive value)

Diff between outputs of gcloud sql instances describe --format json test on version 99 and 101:

10c10
<   "etag": "c95ddf6f008ad754520321e587f5fa4d763a0e744c9494c96d70347c9fe79837",
---
>   "etag": "c7862a289e42ba50bed4e68c11bc0f6f1cef281b1250f3449335b1d3c6373747",
95c95
<     "settingsVersion": "99",
---
>     "settingsVersion": "101",

The diff shows that only the settingsVersion changes, which in terraform provider is converted into the settings.version attribute, and etag, an unused attribute in the terraform provider that is deprecated and was previously used instead of settingsVersion.

If necessary, I can provide the debug output of terraform apply and the full output of gcloud sql instances describe --format json.

[dullest]

I have faced exactly same diff like following several times.
I also have not changed the terraform codes for it.

Note: Objects have changed outside of Terraform

Terraform detected the following changes made outside of Terraform since the
last "terraform apply" which may have affected this plan:

  # module.postgresql.google_sql_database_instance.default has changed
! resource "google_sql_database_instance" "default" {
        id                             = "foo-252027d3"
        name                           = "foo-252027d3"
        # (14 unchanged attributes hidden)

!       settings {
!           version                     = 46 -> 48
            # (12 unchanged attributes hidden)

            # (4 unchanged blocks hidden)
        }

        # (1 unchanged block hidden)
    }


Unless you have made equivalent changes to your configuration, or ignored the
relevant attributes using ignore_changes, the following plan may include
actions to undo or respond to these changes.

[averbuks]

Can anyone provide the exact TF Provider Google version and full configuration of the resource they're using? I've run multiple tests with the latest version and an older one (5.24.0), but I'm unable to reproduce the issue.

Here's what I've been doing:

• Deploying a PostgreSQL CloudSQL instance and changing unrelated parameters (like labels) via gcloud. This updates the settings.version outside of Terraform. When I run terraform plan/apply, I see no changes, but the new value is reflected in the state.
• Manually changing the version in the Terraform state to a lower value (no changes to the actual instance). Again, after plan/apply, I see the correct value in the state, but Terraform shows no changes.

In my tests, changing the version never results in configuration drift. Instead, the correct value is silently updated in the Terraform state, which is the expected behavior.

[verdel]

@averbuks

The provider version used at the time of creating the issue is mentioned in the issue description along with the Terraform configuration:

Terraform v1.7.5
on darwin_arm64
+ provider registry.terraform.io/hashicorp/google v5.24.0

I have now rechecked that the issue still exists. I am using the following versions and configuration:

> terraform version

Terraform v1.9.5
on darwin_arm64
+ provider registry.terraform.io/hashicorp/google v6.1.0

terraform {
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "6.1.0"
    }
  }
}

resource "google_sql_database_instance" "main" {
  name             = "test-instance"
  database_version = "POSTGRES_15"
  project          = "test-project"
  region           = "europe-west1"

  settings {
    tier = "db-f1-micro"
    insights_config {
      query_insights_enabled  = false
      query_plans_per_minute  = 0
      query_string_length     = 1024
      record_application_tags = false
      record_client_address   = false
    }

    maintenance_window {
      day  = 1
      hour = 0
    }
  }
}

output "version" {
  value = google_sql_database_instance.main.settings[0].version
}

The issue arises because of the output in the configuration that references settings.version. The problem with the google_sql_database_instance resource was initially noticed in terraform-google-modules/terraform-google-sql-db. In that module, the entire google_sql_database_instance resource is passed to the output.

If you don’t create an output in the configuration and modify the google_sql_database_instance via gcloud, the value of settings.version simply updates in the state, and no drift message appears in the terraform plan/apply. However, if an output is present in the configuration, terraform still "computes" the attributes involved in the output and displays them as a diff if they change.

Here is my comment on the suggestion to use ignore_changes in the issue in terraform-google-modules/terraform-google-sql-db.

Here’s what the output of terraform apply looks like when you add an output with settings:

google_sql_database_instance.main: Refreshing state... [id=test-instance]

Note: Objects have changed outside of Terraform

Terraform detected the following changes made outside of Terraform since the last "terraform apply" which may have affected this plan:

  # google_sql_database_instance.main has changed
  ~ resource "google_sql_database_instance" "main" {
        id                             = "test-instance"
        name                           = "test-instance"
        # (18 unchanged attributes hidden)

      ~ settings {
          ~ version                     = 9 -> 11
            # (14 unchanged attributes hidden)

            # (5 unchanged blocks hidden)
        }
    }


Unless you have made equivalent changes to your configuration, or ignored the relevant attributes using ignore_changes, the following plan may include actions to undo or respond to these
changes.

──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Changes to Outputs:
  ~ version = 9 -> 11

You can apply this plan to save these new output values to the Terraform state, without changing any real infrastructure.

[averbuks]

@verdel Ok, I see it now. In fact you see the configuration drift just because you output the version value

output "version" {
  value = google_sql_database_instance.main.settings[0].version
}

If you comment the output, you will see no changes even if version got bumped, you may try it by simply adding labels to the instance with gcloud or UI, which bumps the version.
Now, given that you output the version value, terraform shows you that there is a change to output and why is that. The same time there is no real change to the resource done if you run apply, just output value will change in the state.
It's also stated in the TF plan you attached:

You can apply this plan to save these new output values to the Terraform state, without changing any real infrastructure.

May you try it out?

[verdel]

Yes, I mentioned this in my previous comment. If the settings.version attribute were not in the output, Terraform would not report changes to the output and the resource.

In the issue I opened in the terraform-google-modules/terraform-google-sql-db module, I suggested excluding settings.version from the output, but I was told that the problem lies with the provider and was directed to open an issue here.

There are two problem: first, why does settings.version change even when none of the parameters have been altered on the Google Cloud side? The changes are not being made through Terraform, and I’m not doing anything through the Google Cloud UI or gcloud. Yet, from time to time, settings.version changes.

The second issue is that Terraform reports changes to both the resource and the output. However, as I understand it, this is just how it works.

So, is there anything that can be done in the provider code to ensure that a single change to settings.version is never reported as drift?

[averbuks]

Changes to the settings.version is truly concerning, but it's not related to terraform or provider, it's rather question to the CloudSQL product, so I would recommend raising a support ticker in the GCP UI. I believe that it happens after any updates/changes done by the product itself during the maintenance window.

The second issue is that Terraform reports changes to both the resource and the output. However, as I understand it, this is just how it works.
Yeah, this is just how it works, if the output changes, it shows the reason.

Excluding the settings.version from the output is possible, but usually we are trying to keep inputs and outputs consistent with the API as much as possible, so I would not go this way.

I imagine you don't really need the settings.version in the output, but you rather want to output the whole settings block, and in that case you still would have the aforementioned drift once in a while, I think the easiest solution would be just to drop the version from the output values like this:

output "instance_settings" {
  value = local.instance_settings_output
}

locals {
  instance_settings_output = { for k,v in google_sql_database_instance.main.settings[0]: k => v if k != "version"}
}

With that you have the settings in the output, but no version and no drift.

[verdel]

I understand. May I refer to your response as the official opinion of the provider development team in the issue I opened in the module's repository? As soon as I have additional information, I will share it here.

[averbuks]

@shuyama1 may you check if my answer above is correct and aligned with the provider team. And if yes, we may close the ticket, to my mind it works as expected.