Sqitch deploy fails on Oracle Oracle Autonomous Database

[Mouad-El-Asri]

Hi,

I'm encountering the following error when attempting to deploy changes to an Oracle Autonomous Database using Sqitch:

Undef did not pass type constraint "Str" (in $args->{"message"}) at /usr/local/share/perl5/Throwable.pm line 75
    "Str" is a subtype of "Value"
    "Value" is a subtype of "Defined"
    Undef did not pass type constraint "Defined" (in $args->{"message"})
    "Defined" is defined as: (defined($_))

Steps to reproduce:

• Configure your Oracle ATP instance and download the client wallet.
• Set the TNS_ADMIN environment variable to point to the wallet location.
• Set up a Sqitch project with Oracle deployment scripts.
• Run:

sqitch deploy --target db:oracle://<oracle_username>@<oracle_tns_alias>

Could you please help with any possible solutions or guidance on how to resolve this error?

[theory]

Huh, some error is failing to be thrown because it's missing an error message. Can you apply this patch, try again, and paste the output? It will add a stack trace so we can see where the caller is failing to provide an error message.

--- a/lib/App/Sqitch/X.pm
+++ b/lib/App/Sqitch/X.pm
@@ -38,6 +38,8 @@ has '+previous_exception' => (init_arg => 'previous_exception')
     if Throwable->VERSION < 0.200007;
 
 sub hurl {
+    use Carp;
+    local $SIG{__DIE__} = &Carp::confess;
     @_ = (
         __PACKAGE__,
         # Always pass $@, as Throwable is unreliable about getting it thanks

[Mouad-El-Asri]

Here’s the full output after applying the patch:

Use of uninitialized value $error[1] in join or string at /usr/share/perl5/vendor_perl/Carp.pm line 279.
DEV at /usr/local/share/perl5/App/Sqitch/X.pm line 42.
        App::Sqitch::X::hurl('DEV', undef) called at /usr/local/lib64/perl5/DBI.pm line 695
        DBI::__ANON__(undef, undef) called at /usr/local/lib64/perl5/DBI.pm line 752
        DBI::connect('DBI', 'dbi:Oracle://ocatestinstance_tp/', 'oca_service_user', undef, 'HASH(0x3db6148)') called at /usr/local/share/perl5/App/Sqitch/Engine/oracle.pm line 120
        App::Sqitch::Engine::oracle::__ANON__('App::Sqitch::Engine::oracle=HASH(0x3c53678)') called at (eval 445) line 52
        App::Sqitch::Engine::oracle::dbh('App::Sqitch::Engine::oracle=HASH(0x3c53678)') called at /usr/local/share/perl5/App/Sqitch/Role/DBIEngine.pm line 96
        App::Sqitch::Role::DBIEngine::try {...} () called at /usr/local/share/perl5/Try/Tiny.pm line 97
        eval {...} called at /usr/local/share/perl5/Try/Tiny.pm line 93
        Try::Tiny::try('CODE(0x374a7f0)', 'Try::Tiny::Catch=REF(0x374a700)') called at /usr/local/share/perl5/App/Sqitch/Role/DBIEngine.pm line 100
        App::Sqitch::Role::DBIEngine::registry_version('App::Sqitch::Engine::oracle=HASH(0x3c53678)') called at /usr/local/share/perl5/App/Sqitch/Engine.pm line 1190
        App::Sqitch::Engine::_check_registry('App::Sqitch::Engine::oracle=HASH(0x3c53678)') called at /usr/local/share/perl5/App/Sqitch/Engine.pm line 443
        App::Sqitch::Engine::verify('App::Sqitch::Engine::oracle=HASH(0x3c53678)', undef, undef) called at /usr/local/share/perl5/App/Sqitch/Command/verify.pm line 105
        App::Sqitch::Command::verify::execute(undef) called at /usr/local/share/perl5/App/Sqitch.pm line 207
        App::Sqitch::try {...} () called at /usr/local/share/perl5/Try/Tiny.pm line 97
        eval {...} called at /usr/local/share/perl5/Try/Tiny.pm line 93
        Try::Tiny::try('CODE(0x21b5230)', 'Try::Tiny::Catch=REF(0x336b8f0)') called at /usr/local/share/perl5/App/Sqitch.pm line 227
        App::Sqitch::go('App::Sqitch') called at /usr/local/bin/sqitch line 9

[theory]

Thank you. Looks like something in the connected callback is failing but not propagating an error:

sqitch/lib/App/Sqitch/Engine/oracle.pm

Lines 106 to 119 in 1386588

	connected => sub {
	my $dbh = shift;
	$dbh->do("ALTER SESSION SET $_='YYYY-MM-DD HH24:MI:SS TZR'") for qw(
	nls_date_format
	nls_timestamp_format
	nls_timestamp_tz_format
	);
	if (my $schema = $self->registry) {
	$dbh->do("ALTER SESSION SET CURRENT_SCHEMA = $schema")
	or $self->_handle_no_registry($dbh);
	}
	return;
	},
	},

Super weird. Can you run those queries manually and see which if any break?

[theory]

And by queries I mean:

ALTER SESSION SET nls_date_format ='YYYY-MM-DD HH24:MI:SS TZR';
ALTER SESSION SET nls_timestamp_format ='YYYY-MM-DD HH24:MI:SS TZR';
ALTER SESSION SET nls_timestamp_tz_format ='YYYY-MM-DD HH24:MI:SS TZR';
ALTER SESSION SET CURRENT_SCHEMA=sqitch;

Change the name of the current schema from sqitch if you're using a different registry name.

[Mouad-El-Asri]

I ran the session statements manually. Here's what I found:

• Both nls_timestamp_tz_format and CURRENT_SCHEMA=sqitch worked.
• Both nls_date_format and nls_timestamp_format failed with:
  ORA-01821: date format not recognized

[theory]

Interesting. I guess those have been removed in some version of Oracle? It's still documented in Release 23 and listed in the autonomous database docs.

If you comment out those two parameters does Sqitch work properly?

[theory]

Oh, it doesn't like the date format. What happens if you omit TZR?

[theory]

Or perhaps this patch:

--- a/lib/App/Sqitch/Engine/oracle.pm
+++ b/lib/App/Sqitch/Engine/oracle.pm
@@ -105,10 +105,10 @@ has dbh => (
             Callbacks         => {
                 connected => sub {
                     my $dbh = shift;
-                    $dbh->do("ALTER SESSION SET $_='YYYY-MM-DD HH24:MI:SS TZR'") for qw(
+                    $dbh->do("ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS TZR'");
+                    $dbh->do("ALTER SESSION SET $_='YYYY-MM-DD HH24:MI:SS'") for qw(
                         nls_date_format
                         nls_timestamp_format
-                        nls_timestamp_tz_format
                     );
                     if (my $schema = $self->registry) {
                         $dbh->do("ALTER SESSION SET CURRENT_SCHEMA = $schema")

[theory]

Hi @Mouad-El-Asri, have you had a chance to try this patch?