ora-00932 inconsistent datatypes on revert with --requires

[Ben Zvan]

I'm in the requirements testing phase of implementing a database management tool and sqitch is looking pretty solid except for this error I'm running into on reverting changes.

I have two changes in my project: createTable and insertUser

I'm using the create table from the sqitch oracle tutorial since it was simple to copy and paste, then I'm inserting a user into that table with --requires createtable since the table would have to exist in order to add a row to it. Deploy works fine, and revert works fine when the only change is createTable, but when I add the insertUser change the revert fails with the following error:

  - insertUser ......... ORA-00932: inconsistent datatypes: expected - got - (DBD ERROR: error possibly near <*> indicator at char 37 in '
        SELECT (
            SELECT <*>COLLECT(dependency)
              FROM dependencies
             WHERE change_id = :p1
               AND type = 'require'
        ),
        (
            SELECT COLLECT(dependency)
              FROM dependencies
             WHERE change_id = :p2
               AND type = 'conflict'
        ) FROM dual
    ')
not ok
commit ineffective with AutoCommit enabled at /usr/local/lib/perl5/App/Sqitch/Role/DBIEngine.pm line 856.
Revert failed

Any suggestions?

[Ben Zvan]

Here's a specific example of the error occurring:

Error starting at line 1 in command:

SELECT (
SELECT COLLECT(dependency)
FROM dependencies
WHERE change_id = '73400423927ceea66f4fcda44160b8c1f7dece2b'

AND type = 'require'
),
(
SELECT COLLECT(dependency)
FROM dependencies

WHERE change_id = '73400423927ceea66f4fcda44160b8c1f7dece2b'

AND type = 'conflict'
) FROM dual

Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:   
*Action:

I'm also working with our DBA to find out where the problem is. I suspect it's related to either the SQL or the data in the DEPENDENCIES table. What's odd is that the error stuck around for a while after I had manually removed all rows from the CHANGES and DEPENDENCIES table. But now, a few days later, I can't reproduce the problem. I'm going to have to keep a close eye on this because testing our rollback procedure is part of our deploy process.

[David E. Wheeler]

On Apr 9, 2014, at 7:46 AM, Ben Zvan <ben....@gmail.com> wrote:

> I'm also working with our DBA to find out where the problem is. I suspect it's related to either the SQL or the data in the DEPENDENCIES table. What's odd is that the error stuck around for a while after I had manually removed all rows from the CHANGES and DEPENDENCIES table. But now, a few days later, I can't reproduce the problem. I'm going to have to keep a close eye on this because testing our rollback procedure is part of our deploy process.

Going back in time, I found that I worked around the same Heisenbug before:

http://stackoverflow.com/q/16407560/79202

I ended up having to dupe the query in the Oracle engine and replace the user of a placeholder with a literal value in the prepared query. Now maybe there’s another place that needs the same treatment? Annoying. Seems like an Oracle bug.

Do let us know what you and your DBA find.

Thanks,

David

[Ben Zvan]

Here's what our DBA found.

1) She said the COLLECT function seemed overly complicated and there was probably a simpler method.
2) The COLLECT function should be used with the CAST function per this documentation from Oracle:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions024.htm#SQLRF06304

On Thursday, April 3, 2014 1:59:05 PM UTC-5, Ben Zvan wrote:

[David E. Wheeler]

On Apr 9, 2014, at 10:52 AM, Ben Zvan <ben....@gmail.com> wrote:

> Here's what our DBA found.
>
> 1) She said the COLLECT function seemed overly complicated and there was probably a simpler method.

The goal is to collect or aggregate all of the row values into an array. I found COLLECT via this SO answer:

http://stackoverflow.com/q/16313631/79202

> 2) The COLLECT function should be used with the CAST function per this documentation from Oracle:
> http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions024.htm#SQLRF06304

Interesting. Does this patch fix the issue for you?

--- a/lib/App/Sqitch/Engine/oracle.pm
+++ b/lib/App/Sqitch/Engine/oracle.pm
@@ -140,7 +140,7 @@ sub _char2ts {

sub _listagg_format {
# http://stackoverflow.com/q/16313631/79202
- return q{COLLECT(%s)};
+ return q{CAST(COLLECT(%s) AS sqitch_array)};
}

sub _regex_op { 'REGEXP_LIKE(%s, ?)' }

Best,

David

[Ben Zvan]

That seems to cause another problem when running `sqitch status`

sqitchtesting bzvan$ sqitch status
# On database sqitch_dv
ORA-22814: attribute or element value is larger than specified in type (DBD ERROR: error possibly near <*> indicator at char 733 in '
        SELECT * FROM (
            SELECT c.change_id
                 , c.change
                 , c.project
                 , c.note
                 , c.committer_name
                 , c.committer_email
                 , to_char(c.committed_at AT TIME ZONE 'UTC', '"year":YYYY:"month":MM:"day":DD') || to_char(c.committed_at AT TIME ZONE 'UTC', ':"hour":HH24:"minute":MI:"second":SS:"time_zone":"UTC"') AS committed_at
                 , c.planner_name
                 , c.planner_email
                 , to_char(c.planned_at AT TIME ZONE 'UTC', '"year":YYYY:"month":MM:"day":DD') || to_char(c.planned_at AT TIME ZONE 'UTC', ':"hour":HH24:"minute":MI:"second":SS:"time_zone":"UTC"') AS planned_at
                 , CAST(<*>COLLECT(t.tag) AS sqitch_array) AS tags
              FROM changes   c
              LEFT JOIN tags t ON c.change_id = t.change_id
             WHERE c.project = 'flipr2'
             GROUP BY c.change_id
                 , 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
        ) WHERE rownum = 1
    ')

--
Ben Zvan
http://photography.zvan.net/

[David E. Wheeler]

On Apr 10, 2014, at 7:17 AM, Ben Zvan <ben....@gmail.com> wrote:

> That seems to cause another problem when running `sqitch status`

Well the sqitch_array data type was designed for storage, not aggregation in a query. So I am not surprised that collecting tags gets to be too big.

But I wonder if it will work if we skip the cast and use the implicitly-created constructor. How’s this?

index 86f57a3..4ffb589 100644

--- a/lib/App/Sqitch/Engine/oracle.pm
+++ b/lib/App/Sqitch/Engine/oracle.pm
@@ -140,7 +140,7 @@ sub _char2ts {

sub _listagg_format {
# http://stackoverflow.com/q/16313631/79202
- return q{COLLECT(%s)};

+ return q{sqitch_array(%s)};
}

sub _regex_op { 'REGEXP_LIKE(%s, ?)' }

If this doesn't work, perhaps your DBA has some idea of another way we might be able to aggregate varchar2 values in an array? Happy to integrate a proper fix!

Thanks,

David

[Tim Procter]

If it helps, I've been using this workaround which stringifies the list instead of casting it to a custom type. It would fail if the list exceeded the limit for a varchar2 but that hasn't been a problem for me.

@@ -626,13 +626,13 @@
     # Retrieve dependencies.
     my ($req, $conf) = $dbh->selectrow_array(q{
         SELECT (
-            SELECT COLLECT(dependency)
+            SELECT listagg(dependency,',') WITHIN GROUP (ORDER BY NULL)
               FROM dependencies
              WHERE change_id = ?

                AND type = 'require'
         ),
         (

-            SELECT COLLECT(dependency)
+            SELECT listagg(dependency,',') WITHIN GROUP (ORDER BY NULL)
               FROM dependencies
              WHERE change_id = ?
                AND type = 'conflict'
@@ -644,6 +644,8 @@
         'DELETE FROM changes where change_id = ?',
         undef, $change->id,
     );
+    $req = [split ',', $req || ''];
+    $conf = [split ',', $conf || ''];

     # Log it.
     return $self->_log_event( revert => $change, $del_tags, $req, $conf );

================================================================

--

[David E. Wheeler]

On Apr 27, 2014, at 11:56 AM, Tim Procter <pro...@pythian.com> wrote:

> If it helps, I've been using this workaround which stringifies the list instead of casting it to a custom type. It would fail if the list exceeded the limit for a varchar2 but that hasn't been a problem for me.

Yeah, this is how the MySQL and SQLite implementations work. I was trying to avoid it on Oracle since it has arrays, but maybe it’s not worth the heartache?

Did anyone try the last patch I sent? Sqitch already defines a custom array type, so there’s a function we can just use:

Index 86f57a3..4ffb589 100644

--- a/lib/App/Sqitch/Engine/oracle.pm
+++ b/lib/App/Sqitch/Engine/oracle.pm
@@ -140,7 +140,7 @@ sub _char2ts {

sub _listagg_format {
# http://stackoverflow.com/q/16313631/79202
- return q{COLLECT(%s)};
+ return q{sqitch_array(%s)};
}

sub _regex_op { 'REGEXP_LIKE(%s, ?)' }

Would really like to get this figured out. :-(

Thanks,

David

[Ben Zvan]

I'm planning time to look at this today. I have tried all the patches except for the stringify but have had intermittent luck. It seems that some days I can run revert against a database in any state with no problems and other days it will fail permanently. I also find that SQLDeveloper reliably fails on the original code, but Toad reliably does not. This makes me think there is a problem with the driver's support of the collection feature.

[Ben Zvan]

We got a response from Oracle on this. I'm curious about the need to use TNS to connect. The DBAs always use TNS, but the developers and apps ops never use TNS.

Action/Plan
--------------------

a. Ensure that you have OCI driver installed
Download one from OTN.

For example link for Apple MAC OS X ( 64 bit) is as

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

Option
Version 11.2.0.3.0 (64-bit)
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
Download instantclient-basic-macos.x64-11.2.0.3.0.zip (62,342,264 bytes)

Download Driver as applicable to your OS.

b. Unzip it to location where SQL Developer installed.
c. Open SQL Developer and ensure to set Thin client as per note SQL Developer 4.0.1 Crashes With 11.2.0.4 Client. (Doc ID 1637539.1)

Please note
Reference to this note is ONLY to follow steps to set "ensure to use "Oracle Client" and "OCI/Thick client" options as below on "Preferences" -> Database -> Advance Option TAB"

d. Once Done , Restart SQL Developer and connect with TNS Option.

Test by running your SQL .

[David E. Wheeler]

On Apr 28, 2014, at 5:50 AM, Ben Zvan <ben....@gmail.com> wrote:

> We got a response from Oracle on this. I'm curious about the need to use TNS to connect. The DBAs always use TNS, but the developers and apps ops never use TNS.

*BOGGLE*. What difference does the connection protocol make? I used a tnsnames.ora file when I did development, but isn't that just aliases for host/port/dbname, essentially?

>
> Action/Plan
> --------------------
>
> a. Ensure that you have OCI driver installed
> Download one from OTN.
>
> For example link for Apple MAC OS X ( 64 bit) is as
>
> http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
>
> Option
> Version 11.2.0.3.0 (64-bit)
> Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
> Download instantclient-basic-macos.x64-11.2.0.3.0.zip (62,342,264 bytes)

This is what I used. I don’t know of another option on OS X, TBH.

> Download Driver as applicable to your OS.
>
> b. Unzip it to location where SQL Developer installed.
> c. Open SQL Developer and ensure to set Thin client as per note SQL Developer 4.0.1 Crashes With 11.2.0.4 Client. (Doc ID 1637539.1)
>
> Please note
> Reference to this note is ONLY to follow steps to set "ensure to use "Oracle Client" and "OCI/Thick client" options as below on "Preferences" -> Database -> Advance Option TAB"
>
> d. Once Done , Restart SQL Developer and connect with TNS Option.
>
> Test by running your SQL .

So, are they trying to get more diagnostics here?

Best,

David

[Ben Zvan]

They're not looking for diagnostics, they're looking to see if that's fixed the problem. I can't get the native OCI driver to load in SQLDeveloper at the moment though...and I can't duplicate this problem in sqitch right now either.

So odd...

[David E. Wheeler]

On Apr 30, 2014, at 9:30 AM, Ben Zvan <ben....@gmail.com> wrote:

> They're not looking for diagnostics, they're looking to see if that's fixed the problem. I can't get the native OCI driver to load in SQLDeveloper at the moment though...and I can't duplicate this problem in sqitch right now either.
>
> So odd...

Well, let me know what you find. One way or another, I would like to get Sqitch to the point where it “just works” on Oracle the way it does on Postgres, MySQL, and SQLite.

Thanks,

David

[Tim Procter]

Okay, I have a patch that seems to work. The main trick was that we needed an internal cast to use the sqitch_array safely.

--- oracle_orig.pm    2014-01-16 18:23:30.000000000 -0500
+++ oracle.pm   2014-05-02 12:57:00.095527500 -0400
@@ -139,8 +139,8 @@
 }

 sub _listagg_format {
-    # http://stackoverflow.com/q/16313631/79202
-    return q{COLLECT(%s)};
+    ## http://stackoverflow.com/q/16313631/79202
+    return q{CAST(COLLECT(cast(%s as varchar2(512))) AS sqitch_array)};
 }

 sub _regex_op { 'REGEXP_LIKE(%s, ?)' }
@@ -622,17 +622,19 @@
         ora_type => DBD::Oracle::ORA_VARCHAR2()
     });
     $sth->execute;
+
+    my $aggcol = sprintf $self->_listagg_format, 'dependency';

     # Retrieve dependencies.
-    my ($req, $conf) = $dbh->selectrow_array(q{
+    my ($req, $conf) = $dbh->selectrow_array(qq{

         SELECT (
-            SELECT COLLECT(dependency)

+            SELECT $aggcol

               FROM dependencies
              WHERE change_id = ?
                AND type = 'require'
         ),
         (
-            SELECT COLLECT(dependency)

+            SELECT $aggcol

               FROM dependencies
              WHERE change_id = ?
                AND type = 'conflict'

--

[David E. Wheeler]

On May 2, 2014, at 10:08 AM, Tim Procter <pro...@pythian.com> wrote:

> sub _listagg_format {
> - # http://stackoverflow.com/q/16313631/79202
> - return q{COLLECT(%s)};
> + ## http://stackoverflow.com/q/16313631/79202
> + return q{CAST(COLLECT(cast(%s as varchar2(512))) AS sqitch_array)};
> }

Good news. Might it also work to use the sqitch_array function?

return q{sqitch_array(cast(%s as varchar2(512)) AS sqitch_array};

Thanks,

David

[Timothy Procter]

I don't think it'll work that way.  sqitch_array takes a list of values as a single input, but doesn't operate over a result set (aggregate).
We could create an user-defined aggregate function to hide some of the complexity at the cost of requiring a DDL change. Let me know if you want to explore that further.

--

Timothy Procter, P.Eng., CSDP
Software Development Manager

Pythian - Love your data

pro...@pythian.com

Tel: +1 613 565 8696 x 1292
Mobile: +1 613 266 3525
www.pythian.com

[David E. Wheeler]

On May 2, 2014, at 11:40 AM, Timothy Procter <pro...@pythian.com> wrote:

> I don't think it'll work that way. sqitch_array takes a list of values as a single input, but doesn't operate over a result set (aggregate).

Ah, right, of course.

> We could create an user-defined aggregate function to hide some of the complexity at the cost of requiring a DDL change. Let me know if you want to explore that further.

No, I don’t think that’s necessary. I will carve out some time next week to integrate this and test it with the developer days image.

Thanks!

David

[David E. Wheeler]

On May 2, 2014, at 1:07 PM, David E. Wheeler <da...@justatheory.com> wrote:

> No, I don’t think that’s necessary. I will carve out some time next week to integrate this and test it with the developer days image.

Tracking this issue here:

https://github.com/theory/sqitch/issues/91

Thanks,

David