Sqitch docker deployment from jenkins is failing

[Rajkumar-vnix]

I have a working Sqitch deployment running in the production with no issues. But the issue is in the lower environment where the target snowflake URL consists an region and it is not able to create a registry while running the sqitch. I have verified the /etc/odbc.ini will have the sf_account and sf_region inside the container, related changes in Dockerfile also updated. No issue with the image but running the sqitch deploy --registry sqitch --set DATABASE=$SNOWSQL_DATABASE "db:snowflake://$SNOWSQL_USER:$SNOWSQL_PWD@$SNOWSQL_ACCOUNT.$SNOWSQL_REGION.snowflakecomputing.com/$SNOWSQL_DATABASE?Driver=Snowflake;warehouse=$SNOWSQL_WAREHOUSE;role=$SNOWSQL_ROLE"
is returning an error
003041 (42710): SQL compilation error: Schema 'SQITCH’ already exists, but the current role has no privileges on it. If “snowsql” unexpectedly returned exit value 5
Able to create/drop other schemas as well as

sqitch/lib/App/Sqitch/Engine/snowflake.sql

Line 1 in 1fb6713

CREATE SCHEMA IF NOT EXISTS &registry;

with the user and role permission in snowflake UI but it is failing from sqitch docker container to create sqitch own schema registry to store its data.

[Rajkumar-vnix]

Few other errors
002002 (42710): SQL compilation error: Object 'SQITCH.RELEASES' already exists. "snowsql" unexpectedly returned exit value 5

SQL compilation error: Object does not exist, or operation cannot be performed. (SQL-02000)

[Rajkumar-vnix]

If I have created a manual sqitch registry schema it is using the schema and failing in the next step where it is not finding changes table in sqitch registry.
If I have to use sqitch deploy command, it is failing to create its own registry. I can vouch that switch user has enough permission to create/drop/and do anything in DB

[theory]

Given that it's working in production, it seems like a configuration or permissions issue in your non-prod environment. You're certain that $SNOWSQL_USER has the permissions it needs, and that $SNOWSQL_ROLE is set to the right role for that user to realize those permissions?

[Rajkumar-vnix]

$SNOWSQL_USER and $SNOWSQL_ROLE have enough permission to perform the deployment in non-prod.

Running Sqitch docker in a Jenkins env, using different pipelines for the deployment. The docker image created for production has only one argument "sf_account" we can see it inside the container under "connection"(var/.snowsql/config) and on "odbc.ini"(/etc/odbc.ini).

The docker image created for non- production has extra argument along "sf_region" along with the argument "sf_account" we can see it inside the container under "connection"(var/.snowsql/config) and on "odbc.ini"(/etc/odbc.ini).

The weirdest thing is, the non-prod created docker image container runs sqicth deploy to non-prod DB with the region and we can see the history where sqitch related registries logs "odbc 2.21" in the history and can see the "snowsql" run happening on prod DB, not on non-prod DB.

Can we use env variables like $SNOWSQL_NP_USER and $SNOWSQL_NP_ACCOUNT in the docker container?

withCredentials([string(credentialsId: 'SNOWSQL_NP_ USER', variable: 'SNOWSQL_USER'), string(credentialsId: 'SNOWSQL_NP_PWD', variable: 'SNOWSQL_PWD'), string(credentialsId: 'SNOWSQL_NP_ACCOUNT', variable: 'SNOWSQL_ACCOUNT'), string(credentialsId: 'SNOWSQL_NP_DATABASE', variable: 'SNOWSQL_DATABASE'), string(credentialsId: 'SNOWSQL_NP_REGION', variable: 'SNOWSQL_REGION'), string(credentialsId: 'SNOWSQL_NP_WAREHOUSE', variable: 'SNOWSQL_WAREHOUSE')])

And the deploy command inside the docker container will be sqitch deploy --registry sqitch --set DATABASE=$SNOWSQL_DATABASE "db:snowflake://$SNOWSQL_USER:$SNOWSQL_PWD@$SNOWSQL_ACCOUNT.$SNOWSQL_REGION.snowflakecomputing.com/$SNOWSQL_DATABASE?Driver=Snowflake;warehouse=$SNOWSQL_WAREHOUSE;role=SNOWSQL_ROLE"

[theory]

The environment variables supported by Sqitch are documented in sqitch-environment; SNOWSQL_NP_USER and SNOWSQL_NP_ACCOUNT are not among them.

I have not noticed use of SQITCH_ROLE in your examples, so it's not clear to me. Something is different about the configuration, one way or another.

sf_region is not a variable in the ODBC config in the Docker image; is that something you added?

I honestly don't know what to tell you that might help here; the original error, Schema 'SQITCH’ already exists, but the current role has no privileges on it., does not sound like a Sqitch issue per-se, but I will admit that configuring connections to Snowflake have been pretty challenging at times.

[Rajkumar-vnix]

I just missed out on using $SNOWSQL_ROLE in the above example of JenkinsFile.
withCredentials([string(credentialsId: 'SNOWSQL_NP_ USER', variable: 'SNOWSQL_USER'), string(credentialsId: 'SNOWSQL_NP_PWD', variable: 'SNOWSQL_PWD'), string(credentialsId: 'SNOWSQL_NP_ACCOUNT', variable: 'SNOWSQL_ACCOUNT'), string(credentialsId: 'SNOWSQL_NP_DATABASE', variable: 'SNOWSQL_DATABASE'), string(credentialsId: 'SNOWSQL_NP_REGION', variable: 'SNOWSQL_REGION'), string(credentialsId: 'SNOWSQL_NP_WAREHOUSE', variable: 'SNOWSQL_WAREHOUSE'), string(credentialsId: 'SNOWSQL_NP_ROLE', variable: 'SNOWSQL_ROLE'])

Yes, added sf_region in the Dockerfile to add region in the config file,
ARG sf_region && perl -i -pe "s/SF_REGION/$sf_region/g" odbc.ini \ && echo "[connections]\naccountname = $sf_account\nregion = $sf_region\n\n[options]\nnoup = true" > /var/snowsql/.snowsql/config

While creating a second docker image, in the Jenkins server where the production docker image existing facing a security error (i.e: E: The repository 'http://security.debian.org/debian-security stable/updates Release' does not have a Release file.) while building the docker image. To overcome this added an extra line in Dockerfile (i.e: RUN sed -i 's/stable/updates/stable-security/g' /etc/apt/sources.list). Then Able to create a docker image for non-prod, but the issue is while running the sqitch deploy scripts it is trying to deploy into the production DB rather than non-prod DB.

Any insights on using variables in two different docker images in a server, still not able to figure out how the non-prod image with sf_region is not recognizable during deployment.

The non-prod URL will be abc.nonprod.us-west-1.snowflakecomputing.com and the Prod URL will be abc.snowflakecomputing.com

[Rajkumar-vnix]

I am still trying to get help to find more evidence from snowsql_rt.log during non-prod deployment from the Jenkins server. And the whole setup of non-prod docker image from local is happening perfectly without any issues.

[theory]

I'm afraid there is way more context for your particular environment than I can follow. Have you got it working? If so, what was the key to make it work?

Personally I don't use the sf_ stuff in the Dockerfile, but just include the account name and region in the target URL like this.

[Rajkumar-vnix]

@theory But the URL we are trying to connect from Jenkins instance is something like abc.def.us-west-2.snowflakecomputing.com. Does sqitch support this kind of URL while connecting with snowsql? And found in the snowsql_rt.log it is not looking for the override config file to look for the new URL mentioned above rather it is using default abc.snowfalkecomputing.com

[theory]

Yes, Sqitch supports that kind of url; see here for an example:

SQITCH_TEST_SNOWFLAKE_URI: db:snowflake://${{ secrets.SNOWFLAKE_USERNAME }}:${{ secrets.SNOWFLAKE_PASSWORD }}@sra81677.us-east-1/sqitchtest?Driver=Snowflake;warehouse=compute_wh

The host name is sra81677.us-east-1.

It could be that the extra subdomains in your example are messing with things, though I'm not sure how. It should be account_name.$region; abc.def.us-west-2 has too many subdomains in it.

[theory]

Ah, it could cause problems because the format of the URL, as far as I understand is, is

<account_name>.<region_id>.<cloud_platform>.snowflakecomputing.com

So in your example, abc would be the account name, def the region ID, and us-west-2 the cloud platform. Which obviously isn't right.

[theory]

Looking at the docs, it may be that you need to use a dash instead of a dot between abc and def:

abc-def.us-west-2.snowflakecomputing.com

That's assuming that abc is an org name and def is an account name. Or, in your original example:

abc.nonprod.us-west-1.snowflakecomputing.com

That's assuming that abc is an org name and nonprod is an account name.