CODEWITHSUNDEEP
snowflake-cloud-data-platformliquibasesnowflake-schema
Mohamed.Radwan -MVP
Mohamed.Radwan -MVP

Reputation: 2744

Cannot perform CREATE TABLE using Liquibase update for Snowflake as this session does not have a current database?

I am trying to run Snowflake update using Liquibase as the following command which has fully qualified name of the server, database and the schema.

liquibase --username=myusername --password=mypassword --url="jdbc:snowflake://myserver-name-europe.azure.snowflakecomputing.com/?db=mydb&schema=public" --changelog-file=/samplechangelog.snowflake.sql update

But it just gives me error that I didn't specify the database which I did. Also, the same command work with other Snowflake account? I just even copy and paste it from other project, it's just the name of the server URL and DB is the different.

Here is the error message:

Unexpected error running Liquibase: Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name. [Failed SQL: (90105) CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP_NTZ, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

enter image description here

Upvotes: 3

Views: 4021

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

I suggest setting up a default role for user "username":

ALTER USER myusername SET DEFAULT_ROLE = my_default_role;

If user does not have default warehouse assigned then:

ALTER USER myusername SET DEFAULT_WAHREOUSE = my_warehouse_name;

It is also possible to set dabatabase/schema/warehouse/... in db.properties file. Related: Specifying Properties in a Connection Profile

Upvotes: 1

Mohamed.Radwan -MVP
Mohamed.Radwan -MVP

Reputation: 2744

Yes, the issue was in the permissions for the current user that I am trying to use to connect to the Snowflake, here what I did:

ALTER USER myuser SET DEFAULT_ROLE=SYSADMIN

This lead to another error as there is no active warehouse, so I set the whorehouse in the connection string of the JDBC driver as the following:

liquibase --username=myusername --password=mypassword --url="jdbc:snowflake://myserver-name-europe.azure.snowflakecomputing.com/?&warehouse=COMPUTE_WH&db=mydb&schema=public"--changelog-file=/samplechangelog.snowflake.sql update

So, I can send any values in the connection string for the JDBC as the following:

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?user=peter&warehouse=mywh&db=mydb&schema=public

Here is the page for more info about JDBC driver connection string:

JDBC connection string to Snowflake

Upvotes: 2

Kalina_Detko
Kalina_Detko

Reputation: 11

This error is usually due to insufficient permissions - if the user cannot 'see' the database, liquibase silently sets it to null and then fails with this error. The best method of troubleshooting this is to connect via Snowflake web GUI with the same credentials (without setting any role or warehouse), then try running 'use database ...'. The solution is to grant enough permissions to the user - either directly or via a role (but then the role name needs to be passed on in the jdbc connection string as well).

Upvotes: 1

Related Questions