[Dbix-class] Strange statistically significant failure for DBIC with Sybase

[Phillip Moore]

First observation: clearly my Sybase database supports placeholders, since
the first traces show that them being used, and SELECT MAX trick returning
the identity columns.   So, one option I have is to switch to the
NoBindVars storage class, but before I try that I wanted to get to the
bottom of this.

I played around with programmatically enabling tracing to a file, wrapping
the database calls of interest.  Some of the trace information is pretty
intuitive.  If I make a direct, raw SQL query (bypassing DBIC to get at the
legacy data) with the syb_nsql method:

    my ($install_raw) = $dbh->syb_nsql(
        qq{select * from ENGN_install where install_id = $install_id},
        q{HASH},
    );

I can clearly see this query in the trace.

    -> syb_nsql for DBD::Sybase::db (DBI::db=HASH(0xb2f0190)~0xb2f01f0
'select * from ENGN_install where install_id = 3' 'HASH') thr#936c008

However, when I wrap the trace calls around this snippet of code:

    my ($install) = $install_rs->search( $install_args );
    $install    ||= $install_rs->create( $install_args );

(Yes, I should probably revisit using find_or_create, which I had problems
with years ago, and never rethought), the resulting trace is extremely
confusing.   First, you see the expected query against the right table,
which happens to be called ENGN_install:

    -> prepare for DBD::Sybase::db (DBI::db=HASH(0xb2f0190)~0xb2f01f0
'SELECT [me].[name], [me].[release], [me].[cnvolume], [me].[dtvolume],
[me].[dbvolume], [me].[install_id] FROM [ENGN_install] [me] WHERE ( (
[name] = ? AND [release] = ? ) )') thr#936c008

But when the create method is called, I see NO SQL query at all.  In fact,
if I wrap just the create method this way, so that the ONLY thing being
traced is the create call:

    my ($install) = $install_rs->search( $install_args );
    if ( not $install ) {
        $dbh->trace( 4,
qq{/var/tmp/vms_bootstrap_database_dbi_trace/$mpri_str} );
        $install = $install_rs->create( $install_args );
        $dbh->trace( 0 );
    }

Then this is the contents of the trace file:

    DBI::db=HASH(0xa9df4e8) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI
1.622-ithread (pid 9685)
    -> trace in DBD::_::common for DBD::Sybase::db
(DBI::db=HASH(0xa9df488)~0xa9df4e8 0) thr#8a5b008
    <- trace= ( 4 ) [1 items] at ./util/vms_bootstrap_database line 796 via
 at ./util/vms_bootstrap_database line 640

What am I doing wrong here?   Clearly, tracing is being turned on, but then
total radio silence from the create method.   Which, FWIW, *does* return a
valid $install object (most of the time).  I'm wiping out and recreating
the schema with each iteration of this bootstrap code, so I actually expect
the searched to fail (the code's been written to work incrementally, I'm
just forcing it to convert everything for now.... until it converts
correctly).

This makes debugging the problem very hard, when the tracing functionality
is silent for the specific call that is where I'm sure the issue lies.




On Mon, Feb 3, 2014 at 5:16 PM, Peter Rabbitson <rabbit+dbic at rabbit.us>wrote:

> On Mon, Feb 03, 2014 at 04:34:07PM -0500, Phillip Moore wrote:
> >
> > I'm starting to see a problem where about 2% of the DBIC objects I create
> > come back with a primary key (which is a Sybase identity column) whose
> > value has been incremented one too many times.
> > ...
> > I am working on getting more in depth debugging information, and I am
> well
> > aware that this problem report is lacking in such details, but in the
> > meantime do these symptoms sound familiar to anyone at all?
>
> Yes, the way we talk to Sybase is rather hideous:
>
> https://metacpan.org/pod/DBIx::Class::Storage::DBI::Sybase::ASE#INSERTS-WITH-PLACEHOLDERS
>
> Some debug ideas: look at the result of DBIC_TRACE=1 to see if you are
> indeed affected by this (a SELECT MAX query being fired). If not the
> case - likely something to do with how @@IDENTITY is isolated on your
> version of Sybase (likely badly). You could also DBI_TRACE=2=logfile
> (note DBI not DBIC) to see what values does DBIC see coming from DBI (to
> rule out a DBIC-level problem). Also you could potentially have issues
> with retries, though this is much less likely - set
> DBIC_STORAGE_RETRY_DEBUG=1 to see those.
>
> In either case - please please do get to the bottom of this. It looks
> like a rather serious problem, I don't want to leave it unsolved, but at
> the same time do not have easy access to a Sybase server (wink wink
> nudge nudge ;)
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140204/3173df67/attachment.htm>