<div dir="ltr">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.<div>
<br></div><div>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:</div>
<div><br></div><div><div> my ($install_raw) = $dbh->syb_nsql(</div><div> qq{select * from ENGN_install where install_id = $install_id},</div><div> q{HASH},</div><div> );</div></div><div><br></div><div>
I can clearly see this query in the trace.</div><div><br></div><div><div> -> syb_nsql for DBD::Sybase::db (DBI::db=HASH(0xb2f0190)~0xb2f01f0 'select * from ENGN_install where install_id = 3' 'HASH') thr#936c008</div>
</div><div><br></div><div>However, when I wrap the trace calls around this snippet of code:</div><div><br></div><div><div><div> my ($install) = $install_rs->search( $install_args );</div><div> $install ||= $install_rs->create( $install_args );</div>
</div></div><div><br></div><div>(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:</div>
<div><br></div><div><div> -> 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</div>
</div><div><br></div><div>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:</div><div><br></div><div>
<div> my ($install) = $install_rs->search( $install_args );</div><div> if ( not $install ) {</div><div> $dbh->trace( 4, qq{/var/tmp/vms_bootstrap_database_dbi_trace/$mpri_str} );</div><div> $install = $install_rs->create( $install_args );</div>
<div> $dbh->trace( 0 );</div><div> }</div></div><div><br></div><div>Then this is the contents of the trace file:</div><div><br></div><div><div> DBI::db=HASH(0xa9df4e8) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI 1.622-ithread (pid 9685)</div>
<div> -> trace in DBD::_::common for DBD::Sybase::db (DBI::db=HASH(0xa9df488)~0xa9df4e8 0) thr#8a5b008</div><div> <- trace= ( 4 ) [1 items] at ./util/vms_bootstrap_database line 796 via at ./util/vms_bootstrap_database line 640</div>
</div><div><br></div><div>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).</div>
<div><br></div><div>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.</div><div><br></div><div><br></div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Mon, Feb 3, 2014 at 5:16 PM, Peter Rabbitson <span dir="ltr"><<a href="mailto:rabbit+dbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">On Mon, Feb 03, 2014 at 04:34:07PM -0500, Phillip Moore wrote:<br>
><br>
> I'm starting to see a problem where about 2% of the DBIC objects I create<br>
> come back with a primary key (which is a Sybase identity column) whose<br>
> value has been incremented one too many times.<br>
</div>> ...<br>
<div class="im">> I am working on getting more in depth debugging information, and I am well<br>
> aware that this problem report is lacking in such details, but in the<br>
> meantime do these symptoms sound familiar to anyone at all?<br>
<br>
</div>Yes, the way we talk to Sybase is rather hideous:<br>
<a href="https://metacpan.org/pod/DBIx::Class::Storage::DBI::Sybase::ASE#INSERTS-WITH-PLACEHOLDERS" target="_blank">https://metacpan.org/pod/DBIx::Class::Storage::DBI::Sybase::ASE#INSERTS-WITH-PLACEHOLDERS</a><br>
<br>
Some debug ideas: look at the result of DBIC_TRACE=1 to see if you are<br>
indeed affected by this (a SELECT MAX query being fired). If not the<br>
case - likely something to do with how @@IDENTITY is isolated on your<br>
version of Sybase (likely badly). You could also DBI_TRACE=2=logfile<br>
(note DBI not DBIC) to see what values does DBIC see coming from DBI (to<br>
rule out a DBIC-level problem). Also you could potentially have issues<br>
with retries, though this is much less likely - set<br>
DBIC_STORAGE_RETRY_DEBUG=1 to see those.<br>
<br>
In either case - please please do get to the bottom of this. It looks<br>
like a rather serious problem, I don't want to leave it unsolved, but at<br>
the same time do not have easy access to a Sybase server (wink wink<br>
nudge nudge ;)<br>
<div class="HOEnZb"><div class="h5"><br>
_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</div></div></blockquote></div><br></div>