Open RajanBabuS opened 3 years ago
Well now that's not an expected error. Seems like something is failing to pass a message to the Error that gets thrown. Please apply this patch and run your command again, then paste the output here.
--- a/lib/App/Sqitch/X.pm
+++ b/lib/App/Sqitch/X.pm
@@ -9,6 +9,8 @@ use Throwable 0.200009;
use Sub::Exporter -setup => [qw(hurl)];
use overload '""' => 'as_string';
+use Carp; BEGIN { $SIG{__DIE__} = \&Carp::confess }
+
# VERSION
has message => (
Appreciate your response here. Overcame the above after installing missing modules & updating the driver path I referred the section - https://metacpan.org/pod/distribution/App-Sqitch/lib/sqitchtutorial-snowflake.pod
We are facing the below error message now.
[root@ac6731c sqitch]# sqitch deploy
Adding registry tables to sf_test
250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3
"snowsql" unexpectedly returned exit value 1
[root@ac6731c sqitch]#
From the SnowFlake side, history of events:
##################################
USE ROLE accountadmin
ALTER WAREHOUSE sqitch RESUME IF SUSPENDED
USE WAREHOUSE sqitch
**USE SCHEMA sqitch -****Failed******
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING=TIMESTAMP_LTZ
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS'\
ALTER SESSION SET TIMEZONE='UTC'
**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
Our Sqitch.conf entry below
[core]
engine = snowflake
plan_file = sqitch.plan
top_dir = .
[engine "snowflake"]
target = sf_test
#registry = sqitch
client = snowsql
[user]
name = ro
email = xxx@example.com
[target "sf_test"]
uri = "db:snowflake://yyy@qv32XXX.ap-south-1.aws.snowflakecomputing.com/sf_test?Driver=snowflake;warehouse=sqitch"
If I am using snowsql to connect snowflake using the existing config file, it works perfect without any issues.
Snowsql Config ############
[connections]
accountname = 'qv32XXX.ap-south-1.aws'
username = yyy
password = zzz
dbname = sf_test
schemaname = public
warehousename = sqitch
rolename = accountadmin
--------------------------
1)Is there any other command which helps to identify connectivity apart from "deploy" command 2)If you still want me to run the patch provided- let me know how to run it
**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******
This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
As called by this code:
But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:
250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3
It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:
--- a/lib/App/Sqitch.pm
+++ b/lib/App/Sqitch.pm
@@ -368,6 +368,7 @@ sub capture {
};
return capturex ( shift, $self->quote_shell(@_) )
if ISWIN && IPC::System::Simple->VERSION <= 1.25;
+ use Data::Dump; Data::Dump::ddx(\@_);
capturex @_;
}
Hi @RajanBabuS, have you had a chance to try this? TIA!
**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******
This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
As called by this code:
But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:
250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3
It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:
--- a/lib/App/Sqitch.pm +++ b/lib/App/Sqitch.pm @@ -368,6 +368,7 @@ sub capture { }; return capturex ( shift, $self->quote_shell(@_) ) if ISWIN && IPC::System::Simple->VERSION <= 1.25; + use Data::Dump; Data::Dump::ddx(\@_); capturex @_; }
where do we should apply this patch, I am also getting similar error, thought of trying this way out as well. Thanks
To the file lib/App/Sqitch.pm
, the location of which depends on how you installed Sqitch.
Hi,
Thanks for your response.
I have used below command from Visual studio's Bash terminal on windows 10 machine.
env DIR=snowflake REGISTRY=sqitch ./build --build-arg sf_account= $ACCOUNT
docker pull sqitch/sqitch curl -L https://git.io/JJKCn -o sqitch && chmod +x sqitch ./sqitch help
Should I try to look for the file lib/App/Sqitch.pm in docker image or my local machine?
Thanks, Pooja
Oh, yeah, for the Docker image you would have to create a new one with the patch. Kind of a pain.
I got the location
however , not sure how to access image as root user, overwrite permission issue.
Other way I tried ,modfiying the docker file by adding below coomand && sed -i "3i \ use Data::Dump; Data::Dump::ddx(\@_);" /lib/perl5/App/Sqitch.pm
however it didn't add it in image not sure about the reason.
log from image build command:
Your screenshot truncates the output, so we can't see if it was part of the command.
Hi,
I added sed command in Dockerfile to edit the file inline
after that I have run below command env DIR=snowflake REGISTRY=sqitch ./build --build-arg sf_account=$ACCOUNT
The out put log does not show sed command execution and the image created by above command is not also added the desired line (use Data::Dump; Data::Dump::ddx(\@_);) in Sqitch.pm file.
Hi David Wheeler/PoojaSahu,
Sorry for the delayed response.
I didn't worked on the docker image, rather i configured on a Redhat 7 node.
Error messages were not user friendly. But took these steps to fix my issue. Ensure these steps if you are facing issue
*** you can get the installed perl modules in a list format and cross check against the link in pointer 1 Any other issue apart from this will be mostly dedicated to human errors and hope for the luck !
@theory You can close my request or keep it opened until Pooja S Sahu's issue is getting resolved
To the file
lib/App/Sqitch.pm
, the location of which depends on how you installed Sqitch.
I added the suggested patch in docker file and build the image . however I get error when I run below command
**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******
This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
As called by this code:
But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:
250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3
It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:
--- a/lib/App/Sqitch.pm +++ b/lib/App/Sqitch.pm @@ -368,6 +368,7 @@ sub capture { }; return capturex ( shift, $self->quote_shell(@_) ) if ISWIN && IPC::System::Simple->VERSION <= 1.25; + use Data::Dump; Data::Dump::ddx(\@_); capturex @_; }
Hi David, I have manged to add Data::DUMP in docker image. please see the result for sqitch deploy now.
./sqitch deploy 'db:snowflake://xxxxxxxxxx@BA05631.eu-west-2.aws/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=xxxxxxxx;UID=xxxxxxx'
Adding registry tables to db:snowflake://Pxxxxxx@BA05631.eu-west-2.aws.snowflakecomputing.com/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=xxxxxx;UID=xxxxxx
# Sqitch.pm:371: (
# "snowsql",
# "--accountname",
# "BA05631", #### Account Number not coming complete here
# "--username",
# "XXXXXX",
# "--dbname",
# "flipr",
# "--noup",
# "--option",
# "auto_completion=false",
# "--option",
# "echo=false",
# "--option",
# "execution_only=false",
# "--option",
# "friendly=false",
# "--option",
# "header=false",
# "--option",
# "exit_on_error=true",
# "--option",
# "stop_on_error=true",
# "--option",
# "output_format=csv",
# "--option",
# "paging=false",
# "--option",
# "timing=false",
# "--option",
# "results=true",
# "--option",
# "wrap=false",
# "--option",
# "rowset_size=1000",
# "--option",
# "syntax_style=default",
# "--option",
# "variable_substitution=true",
# "--variable",
# "registry=sqitch",
# "--variable",
# "warehouse=compute_wh",
# "--option",
# "quiet=true",
# "--filename",
# bless({
# dir => bless({
# dirs => ["", "bin", "..", "lib", "perl5", "App", "Sqitch", "Engine"],
# file_spec_class => undef,
# volume => "",
# }, "Path::Class::Dir"),
# file => "snowflake.sql",
# file_spec_class => undef,
# }, "Path::Class::File"),
# )
Any thoughts?
# "BA05631", #### Account Number not coming complete here
What does this mean?
Failed to initialize log. No logging is enabled: [Errno 13] Permission denied: '/snowsql_rt.log'
Do you have logging enabled in the .snowsql
file in your home directory? Tryturning that off. The Docker image reads that config file, but since it's running inside Docker, it can't access files on your host.
@theory @PoojaSSahu , I am having same error, can you guide me with the same
Object 'CHANGES' does not exist or not authorized. (SQL-42S02)' (err#1)
<- DESTROY(DBI::st=HASH(0x55e9606256b8))= ( undef ) [1 items] at ODBC.pm line 229
!! ERROR: 1 'SQL compilation error:
Object 'CHANGES' does not exist or not authorized. (SQL-42S02)' (err#1)
@jay-brillio Make sure the Snowflake user you're connecting as has permission to create the registry schema and the changes table.
@theory User is having OWNERSHIP & USAGE of the Warehouse or any other permission needs to be added, can you please suggest any troubleshooting steps for the same?
@theory from snowflake side it's showing same error, why it's trying to create, below query is causing, the issue but it's not I have run it's from sqitch side looks like
SELECT c.change_id , c.script_hash , c.change , c.project , c.note , c.committer_name , c.committer_email , to_varchar(CONVERT_TIMEZONE('UTC', c.committed_at), '"year:"YYYY":month:"MM":day:"DD":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"') AS committed_at , c.planner_name , c.planner_email , to_varchar(CONVERT_TIMEZONE('UTC', c.planned_at), '"year:"YYYY":month:"MM":day:"DD":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"') AS planned_at , listagg(t.tag, ' ') WITHIN GROUP (ORDER BY t.tag) AS tags FROM changes c LEFT JOIN tags t ON c.change_id = t.change_id WHERE c.project = ? GROUP BY c.change_id , c.script_hash , c.change , c.project , c.note , c.committer_name , c.committer_email , c.committed_at , c.planner_name , c.planner_email , c.planned_at ORDER BY c.committed_at DESC LIMIT 1
It sounds like for some reason Snowflake is not letting the user create the table. That query failing is expected; Sqitch catches that error and then knows to try to create the table.
so what's next I can try I am blocked on this since long? @theory
See if you can create a schema and a table with that user via SnowSQL. Copy the command line arguments from this example. If it fails, you'll need to figure out why. Does the user not have the access you think it has? Are you connecting as the user you think you are? Does that user, in fact, need to be granted more permissions via the Snowflake console?
HTH.
Hi ,
We are trying to use Sqitch for Snowflake. Followed the steps provided in the given reference implementation but not able to perform sqitch deploy .
Reference Link:
Where we are now?
1) we installed PERL 2) we installed APP::Sqitch and dependent modules(odbcDev package installed, DBD installed) 3) installed Snowflake ODBC connector verified the Snowflake connectivity separately : no issue here 4)installed Snowflake Snowsql and verified the Snowflake connectivity separately : no issue here 6)able to perform sqitch init 7)able to perform sqitch add : files are getting generated automatically 8)when I perform sqitch deploy, got the error message 9)I am using password stored in the ~/.snowsql/config file 10) DB URI "db:snowflake://dummy@dummy.ap-south-1.aws.snowflakecomputing.com/sf_test?Driver=Snowflake"
versions:
There were no straight forward documentations(for manual installation + configuration) available for us to have a decent installation and configurations. I am being very skeptical to propose Sqitch with the level of configuration steps involved.
Could you please help here for any possible solutions