Creating databases ontop of shares/from replicas failing due to SQL parsing errors

[christyvp]

Provider Version
Latest. We started seeing this in 0.71 and it still continues in 0.75

Terraform Version

• v1.3.4
• v1.4.6
• v1.5.7

Describe the bug

Weird SQL syntax/parsing errors.
Creating a database from a snowflake share fails due to a syntax/parsing error.

Examples

module.snowflake-inbound-cross-region[0].snowflake_database.from_share: Creating... ╷ │ Error: error creating database CLIENT1_SHARE: 001003 (42000): SQL compilation error: │ syntax error line 1 at position 69 unexpected 'prod'. │ │ with module.snowflake-inbound-cross-region[0].snowflake_database.from_share, │ on snowflake-inbound-cross-region-subaccount/snowflake_db.tf line 1, in resource "snowflake_database" "from_share": │ 1: resource "snowflake_database" "from_share" { │ ╵ Operation failed: failed running terraform apply (exit 1)�

│ Error: error creating database CLIENT2_SHARE: 001003 (42000): SQL compilation error: │ syntax error line 1 at position 51 unexpected 'PE_PROD'. │ │ with module.snowflake-inbound-cross-region[0].snowflake_database.from_share, │ on snowflake-inbound-cross-region-subaccount/snowflake_db.tf line 1, in resource "snowflake_database" "from_share": │ 1: resource "snowflake_database" "from_share" { │ ╵ Operation failed: failed running terraform apply (exit 1)

Creating a database from replica fails due to a syntax/parsing error.
Example

module.snowflake-inbound-cross-region[0].snowflake_database.from_replica: Creating... ╷ │ Error: error creating database CLIENT3_CROSS_REGION_SHARE: 001003 (42000): SQL compilation error: │ parse error line 1 at position 153 near '<EOF>'. │ syntax error line 1 at position 153 unexpected '<EOF>'. │ │ with module.snowflake-inbound-cross-region[0].snowflake_database.from_replica, │ on snowflake-inbound-cross-region-subaccount/snowflake_db.tf line 28, in resource "snowflake_database" "from_replica": │ 28: resource "snowflake_database" "from_replica" { │ ╵ Operation failed: failed running terraform apply (exit 1)�

Expected behavior

1. Create a database on top of a share from another account
2. Create a secondary duplicate database and replicate to a third account

Code samples and commands

`resource "snowflake_database" "from_share" {
provider = snowflake.subaccount
name = "${local.client_name_upper}_SHARE"
comment = "Database on top of ${var.client_name}_SHARE - Managed by Terraform"
from_share = {
provider = "${var.client_snowflake_orgname}"."${var.client_snowflake_account_id}"
share = var.inbound_share_name
}
data_retention_time_in_days = 1
lifecycle {
ignore_changes = [data_retention_time_in_days]
}
}

resource "snowflake_database" "with_replication" {
provider = snowflake.subaccount
name = "${local.client_name_upper}_CROSS_REGION_SHARE"
comment = "Duplicate database, replicated into primary snowflake account - Managed by Terraform"
replication_configuration {
accounts = ["REDACTED"]
ignore_edition_check = true
}

depends_on = [snowflake_database.from_share]
}

// primary account setup - Create database, assign permissions
resource "snowflake_database" "from_replica" {
name = "${local.client_name_upper}_CROSS_REGION_SHARE"
comment = "Database replica from ${var.subaccount_region} "
from_replica = "REDACTED"."${local.tf_account_name_alternative}"."${snowflake_database.with_replication.name}"

depends_on = [snowflake_database.with_replication]
}`

Additional context

This is blocking a workflow in our production environment, so I would consider this a high priority.

[sfc-gh-jcieslak]

Hey 👋, so I was trying to reproduce the first case (creating a database from Share) and I'm not sure if that was intentional or a copy-paste error, but snowflake_database.from_share has an invalid identifier at from_share.provider (VSCode was underlining that and Terraform said it's an invalid attribute name), but nevertheless I've modified a bit the resource to use my data and It worked. Here's the config I've used.

Terraform version v1.5.7
Snowflake provider version v0.75.0

resource "snowflake_database" "from_share" {
  name = "SHARE_TEST_DB"
  data_retention_time_in_days = 1
  comment = "some comment"

  from_share = {
    provider = "\"ORG_NAME\".\"ACCOUNT_NAME\""
    share = "TEST_SHARE"
  }

  lifecycle {
    ignore_changes = [data_retention_time_in_days]
  }
}

But looking at the error message it seems like there might be an issue with unnecessary single quoting around identifiers or how they're specified (in config) in a way that Snowflake doesn't expect. In both cases, it's more of a Snowflake error, not a Terraform Provider error. Let me know if that's the case.

[christyvp]

hey @sfc-gh-jcieslak these are being passed in as variables so provider = "${var.client_snowflake_orgname}"."${var.client_snowflake_account_id}" would look something like the string "simon.em12345" or "simon.prod"

This has been working for several months and we are starting to see these errors as early as version 0.70.1, when we try to setup this for a new workspace in terraform.

Unfortunately we cannot downgrade the provider further as there are separate bugs.

This is a snowflake/sql error, but it only happens when running terraform. If we manually run the command directly in the UI it works.

[sfc-gh-jcieslak]

hey @christyvp, what I meant about the pasted provider is that in Terraform you cannot do two strings with the dot in between like provider = "string"."string" and you should get

│ Error: Invalid attribute name
│ 
│   on main.tf line 16, in resource "snowflake_database" "from_share":
│   16:     provider = "org"."acc"
│ 
│ An attribute name is required after a dot.

and your error message looks like it's indicating a wrong ID which most probably could come from an invalid variable value (could be empty or contain special characters which should be enclosed). Just to make sure, could you check on terraform apply if the properties are filled properly? For example

  # snowflake_database.from_share will be created
  + resource "snowflake_database" "from_share" {
      + comment                     = "some comment"
      + data_retention_time_in_days = 1
      + from_share                  = {
          + "provider" = "simon.prod"
          + "share"    = "TEST_SHARE"
        }
      + id                          = (known after apply)
      + is_transient                = false
      + name                        = "SHARE_TEST_DB"
    }

If they're not correct, then there might be some issues with variables, etc.
If they seem alright, I need more context like

1. Have you tried to hardcode the values instead of getting them from variables or other sources (similar to my example from the first response)
2. What SQL do you expect to be executed? (you can paste the one you were trying manually)
3. What values are you expecting in the resource properties? (some of the fields like IDs cannot contain special characters, etc. If they have any, they should be enclosed by double quotes)
4. What version worked for you? I've checked a few changes between different releases and nothing seemed like it could cause this error

[christyvp]

@sfc-gh-jcieslak

1. Yes - but since we are using terraform cloud we can only run the plan with hard-coded values. Which shows no errors, but then when we apply in terraform cloud we see the errors.
2. CREATE DATABASE "CLIENT_SHARE" FROM SHARE "CLIENT"."ACCOUNTNAME"."DATA_SHARE" COMMENT = 'Database on top of CLIENT_SHARE - Managed by Terraform'
3. I'm not sure what is supposed to show in the ID field - I think just an auto-generated string?
4. We've been running this terraform code since October/November last year. I'm not sure of the exact release at that point, our code indicates provider 0.47 or later. I believe we started seeing various errors introduced at version 0.56. We are using terraform cloud and have around 15 workspaces using this code. The errors always only impact a portion of them (seemingly random) even though the code is the same across all of them.

Most of the previous errors were cleared up after the 0.70.1 release. But it seems each time an error/bug is resolved a new one is introduced, and then affects random workspaces. We don't see these errors in the terraform plan, they always seem to happen when running the apply - and again only hit a few workspaces. (Sometimes workspaces that have successfully run to completion in the past, and the resources already exist). This PR related to shares was added in release 0.70.1 ... not sure if this had any impact: #2040 - because when I downgrade to 0.69 I don't get the error related to the share, I get the error related to the replica - and I can see in the snowflake UI that the database was created on top of the share.

When we downgrade we have other breaking errors so it's really hard to tell.
Examples of other errors we have received and they all affect random workspaces but not all:
#1763
/~https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/1774We were pinned to version 0.70.1 and it is working for all but 3 workspaces.

Would it be easier to setup a call / screenshare to work through this?

[sfc-gh-jcieslak]

@christyvp Hey, I've consulted with the team and maybe the best way to quickly find the issue is to see what exact configuration you're running. Could you share your configuration through the Snowflake representative you have contact with?

[sfc-gh-apatel]

@sfc-gh-jcieslak I have sent this over via slack

[sfc-gh-jcieslak]

Hey @christyvp could you confirm the issue was resovled ?

[christyvp]

We are still seeing this issue. I'll try the latest provider version

[sfc-gh-jcieslak]

Hey @christyvp, any updates on the latest version?
cc: @sfc-gh-asawicki @sfc-gh-swinkler

[christyvp]

@sfc-gh-jcieslak @sfc-gh-asawicki @sfc-gh-swinkler When trying to move to the latest provider we encounter new errors so we can't determine if it has been resolved.