BigQuery column ordering change causes destroy

[daisy1754]

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 the modular-magician user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If an issue is assigned to a user, that user is claiming responsibility for the issue. If an issue is assigned to hashibot, a community member has claimed the issue already.

Terraform Version

Terraform v0.12.29

Affected Resource(s)

google_bigquery_table

Terraform Configuration Files

resource "google_bigquery_table" "customers_table" {
  dataset_id = google_bigquery_dataset.business.dataset_id
  table_id   = "customers"

  time_partitioning {
    type                     = "DAY"
    field                    = "timestamp"
    require_partition_filter = false
  }

  schema = file("../schemas/customers.schema")

}

schema

[
 {
  "name": "customer",
  "type": "RECORD",
  "mode": "NULLABLE",
  "fields": [
   {
    "name": "id",
    "type": "STRING",
    "mode": "NULLABLE"
   },
   {
    "name": "created_at",
    "type": "STRING",
    "mode": "NULLABLE",
   },
   {
    "name": "some_complex_object",
    "type": "RECORD",
    "mode": "REPEATED",
    "fields": [
      {
        "name": "some_field",
        "type": "STRING",
        "mode": "NULLABLE",
      }
    ]
   }
  ]
 },
 {
  "name": "request_id",
  "type": "STRING",
  "mode": "NULLABLE"
 }
]

Issue happens when we reorder (we autogenerate schame and something changed in auto generated schema) like below:

[
 {
  "name": "customer",
  "type": "RECORD",
  "mode": "NULLABLE",
  "fields": [
   {
    "name": "id",
    "type": "STRING",
    "mode": "NULLABLE"
   },
   {
    "name": "some_complex_object",
    "type": "RECORD",
    "mode": "REPEATED",
    "fields": [
      {
        "name": "some_field",
        "type": "STRING",
        "mode": "NULLABLE",
      }
    ]
   }
  ]
 },
 {
  "name": "request_id",
  "type": "STRING",
  "mode": "NULLABLE"
 },
   {
    "name": "created_at",
    "type": "STRING",
    "mode": "NULLABLE",
   }
]

Debug Output

Panic Output

Expected Behavior

I should see no diff when bigquery column order changes in schema file

Actual Behavior

I saw diff in older version of terraform-provider-google (and I was just ignoring).
After I update plugin from 3.25.0 to 3.54.0, now terraform try to destroy the table, ie

Error: Instance cannot be destroyed

  on ../modules/my.tf line 13:
  13: resource "google_bigquery_table" "customers_table" {

Resource module.my.google_bigquery_table.customers_table has
lifecycle.prevent_destroy set, but the plan calls for this resource to be
destroyed. To avoid this error and continue with the plan, either disable
lifecycle.prevent_destroy or reduce the scope of the plan using the -target
flag.

Steps to Reproduce

1. terraform apply with old schema
2. change order of column in schema
3. terraform apply with new schema

Important Factoids

References

• #0000

[venkykuberan]

@daisy1754 can you please add the plan output here

[rileykarson]

See https://github.com/hashicorp/terraform-provider-google/pull/8325/files where we've reverted the behaviour causing Terraform to attempt to destroy the table. This release should go out midday (PST) Monday.

[daisy1754]

@rileykarson @venkykuberan can you reopen this? even after revert this would cause permadiff (at least it did with 3.25.0)

[daisy1754]

@venkykuberan I don't have diff handy but it was essentially trying to delete created_at field from certain position and trying to add created_at to certain position

[rileykarson]

Sorry, there's a whole network of bugs here and I probably should have done some context links. From my perspective the new part about this bug is the "ordering changes causes destroy" part based on a few other pieces of context below.

It was true for a long time that the provider didn't take reorderings into consideration: #4143. I believe it's expected that 3.25.0 doesn't do so.

That behaviour was resolved in GoogleCloudPlatform/magic-modules#3751 which was released in 3.32.0. At this point the provider should have taken ordering properly into account. Note that there was an edge case issue introduced (#7193) fixing in 3.39.0.

However while fixing another minor bug (#7979) a regression was introduced where ordering changes were no longer properly taken into account. That's captured in #8282.

That regression was innocuous until the recreation behaviour introduced in 3.54.0. The 3.53.0 regression caused Terraform to believe that there were modifications to the table's schema and the 3.54.0 change caused it to believe they required it to recreate the cluster.

Where we are now is:

• We've reverted the recreation behaviour for 3.55.0 however the 3.53.0 regression will still be present.
• We've additionally created a fix for 3.56.0 where we believe that we're able to both introduce the 3.54.0 behaviour and fix the 3.53.0 regression that caused diffs when schema fields were reordered.

---

tl;dr: The permadiff in earlier versions was captured in #4143 and the permadiff in 3.53.0+ is captured in #8282. Both are believed to be resolved on our development branch, and we expect this to be working in 3.56.0 on Feb 8th.

Edit: I wrote up how we can roll out this change more safely in this comment by adding deletion_protection as a field on this resource similar to some other databases / database-like resources.

In the meantime for any users who see this before getting affected, I'd recommend applying lifecycle.prevent_destroy on the resource to prevent Terraform being able to delete it.

[daisy1754]

thanks for clarification!

[stephanepham-sib]

Hello,
We still can't update the schema in version v3.58.0, is it expected?
I'm facing the same issue when Terraform tries to recreate my table.

Regards,

[ghost]

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 feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!