Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 80015
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: Josh.Arenberg [...] morganstanley.com
lmarso [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08205



Subject: identifier stripping code bug affecting $rs->update (single query bulk update)
Download (untitled) / with headers
text/plain 8.2k
bug is new, entered after 0.8196 (running under perl-5-14.2 perlbrew; ubuntu 12.04) i described it as "when i try to do a single query bulk update, passing $rs->update( { field => 1} ), dbix::class doesn't pass in my postgres schema name. the trace says 'update table1' instead of 'update schema1.table1'. no such problem with $rs->update_all or any $rs->search followed by extraction of values, they all supply 'schema1.table1' to postgres." āžœ test ./t/update_schema_call.t 1..0 # SKIP Set $ENV{DBICTEST_PG_DSN}, _USER and _PASS to run this test # ( NOTE: This test drops and create a table called 'this_schema.item_values # as following schema: 'this_schema' so specify (e.g.) export DBICTEST_PG_DSN='dbi:Pg:dbname=my_test_db export DBICTEST_PG_USER='larry' export DBICTEST_PG_USER='some_password' run with DBIx::Class 0.8196: āžœ test ./t/update_schema_call.t ok 1 - The object isa DBIx::Class::Row ok 2 - The object isa DBIx::Class::Row ok 3 - The object isa DBIx::Class::Row ok 4 - The object isa DBIx::Class::Row ok 5 - The object isa DBIx::Class::Row ok 6 - The object isa DBIx::Class::Row ok 7 - The object isa DBIx::Class::ResultSet ok 8 - search on resultset OK ok 9 - test of single query bulk update 1..9 run with DBIx::Class 0.8198: larry@desktop test>./t/update_schema_call.t ok 1 - The object isa DBIx::Class::Row ok 2 - The object isa DBIx::Class::Row ok 3 - The object isa DBIx::Class::Row ok 4 - The object isa DBIx::Class::Row ok 5 - The object isa DBIx::Class::Row ok 6 - The object isa DBIx::Class::Row ok 7 - The object isa DBIx::Class::ResultSet ok 8 - search on resultset OK DBIx::Class::ResultSet::update(): DBI Exception: DBD::Pg::st execute failed: ERROR: relation "item_values" does not exist LINE 1: UPDATE item_values SET secondary = $1 WHERE ( item = $2 ) ^ [for Statement "UPDATE item_values SET secondary = ? WHERE ( item = ? )" with ParamValues: 1='1', 2='Item 5'] at ./t/update_schema_call.t line 64 # Tests were run but no plan was declared and done_testing() was not seen. same runs with DBIC_TRACE=1: 0.8196: āžœ test DBIC_TRACE=1 t/update_schema_call.t <<< SET search_path TO public: INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 1' ok 1 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 2' ok 2 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 3' ok 3 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 4' ok 4 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 5' ok 5 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 6' ok 6 - The object isa DBIx::Class::Row ok 7 - The object isa DBIx::Class::ResultSet SET search_path TO public: SELECT me.item, me.secondary FROM this_schema.item_values me WHERE ( item = ? ): 'Item 5' ok 8 - search on resultset OK UPDATE this_schema.item_values SET secondary = ? WHERE ( item = ? ): '1', 'Item 5' ok 9 - test of single query bulk update 1..9 0.8198: larry@desktop test>DBIC_TRACE=1 ./t/update_schema_call.t SET search_path TO public: INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 1' ok 1 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 2' ok 2 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 3' ok 3 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 4' ok 4 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 5' ok 5 - The object isa DBIx::Class::Row INSERT INTO this_schema.item_values ( item) VALUES ( ? ): 'Item 6' ok 6 - The object isa DBIx::Class::Row ok 7 - The object isa DBIx::Class::ResultSet SET search_path TO public: SELECT me.item, me.secondary FROM this_schema.item_values me WHERE ( item = ? ): 'Item 5' ok 8 - search on resultset OK UPDATE item_values SET secondary = ? WHERE ( item = ? ): '1', 'Item 5' DBI Exception: DBD::Pg::st execute failed: ERROR: relation "item_values" does not exist LINE 1: UPDATE item_values SET secondary = $1 WHERE ( item = $2 ) ^ [for Statement "UPDATE item_values SET secondary = ? WHERE ( item = ? )" with ParamValues: 1='1', 2='Item 5'] at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Schema.pm line 1101. DBIx::Class::Schema::throw_exception('Test::Schema=HASH(0x3439cc0)', 'DBI Exception: DBD::Pg::st execute failed: ERROR: relation "...') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage.pm line 112 DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Pg=HASH(0x3c94eb8)', 'DBI Exception: DBD::Pg::st execute failed: ERROR: relation "...') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 1315 DBIx::Class::Storage::DBI::__ANON__('DBD::Pg::st execute failed: ERROR: relation "item_values" do...', 'DBI::st=HASH(0x3cf0130)', undef) called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 1644 DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::Pg=HASH(0x3c94eb8)', 'DBI::db=HASH(0x3ce2cd8)', 'UPDATE item_values SET secondary = ? WHERE ( item = ? )', 'ARRAY(0x3ce0270)', 'ARRAY(0x3cef9e0)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 799 DBIx::Class::Storage::DBI::__ANON__() called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/BlockRunner.pm line 137 DBIx::Class::Storage::BlockRunner::__ANON__() called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/Try/Tiny.pm line 71 eval {...} called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/Try/Tiny.pm line 67 Try::Tiny::try('CODE(0x3cefa88)', 'Try::Tiny::Catch=REF(0x3ce09d8)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/BlockRunner.pm line 141 DBIx::Class::Storage::BlockRunner::__ANON__() called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/Context/Preserve.pm line 42 Context::Preserve::preserve_context('CODE(0x3c949f0)', 'replace', 'CODE(0x3cefff8)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/BlockRunner.pm line 219 DBIx::Class::Storage::BlockRunner::_run('DBIx::Class::Storage::BlockRunner=HASH(0x3c90fa8)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/BlockRunner.pm line 110 DBIx::Class::Storage::BlockRunner::run('DBIx::Class::Storage::BlockRunner=HASH(0x3c90fa8)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 802 DBIx::Class::Storage::DBI::dbh_do(undef, undef, 'UPDATE item_values SET secondary = ? WHERE ( item = ? )', 'ARRAY(0x3ce0270)', 'ARRAY(0x3cef9e0)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 1609 DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::Pg=HASH(0x3c94eb8)', 'update', 'DBIx::Class::ResultSource::Table=HASH(0x3c94f48)', 'HASH(0x3cd7310)', 'HASH(0x3439d20)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 2125 DBIx::Class::Storage::DBI::update('DBIx::Class::Storage::DBI::Pg=HASH(0x3c94eb8)', 'DBIx::Class::ResultSource::Table=HASH(0x3c94f48)', 'HASH(0x3cd7310)', 'HASH(0x3439d20)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/ResultSet.pm line 1775 DBIx::Class::ResultSet::_rs_update_delete('DBIx::Class::ResultSet=HASH(0x3c91278)', 'update', 'HASH(0x3cd7310)') called at /home/larry/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/Class/ResultSet.pm line 1913 DBIx::Class::ResultSet::update('DBIx::Class::ResultSet=HASH(0x3c91278)', 'HASH(0x3cd7310)') called at ./t/update_schema_call.t line 64 # Tests were run but no plan was declared and done_testing() was not seen. with kind regards,
Subject: test.tbz2
Download test.tbz2
application/x-bzip-compressed-tar 1.9k

Message body not shown because it is not plain text.

Subject: Bug with resultset delete() on db2 ( dropping schema names from database )
Date: Fri, 5 Oct 2012 00:15:41 +0000
To: <bug-DBIx-Class [...] rt.cpan.org>
From: "Arenberg, Josh" <Josh.Arenberg [...] morganstanley.com>
Download (untitled) / with headers
text/plain 2.8k
Using dbic 0.08198 on Linux DB2 with Perl 5.8. It seems that the delete() method is dropping the schema names from the queries in certain cases. Examples below: $ $schema->resultset("Resource")->search({ ResourceID => 54821 })->first(); SELECT me.ResourceID, me.ResourceTypeID, me.AvailableAsOfDate, me.CreatedBy, me.CreatedDate, me.Subpool FROM resourcepool.Resources me WHERE ResourceID = '54821' Good $ $schema->resultset("Resource")->search({ ResourceID => 54821 })->delete(); DELETE FROM Resources WHERE ResourceID = '54821' BAD! No schema name ( db2 error ). $ $schema->resultset("Resource")->search({ 'ResourceID' => 54821 })->delete_all(); SELECT me.ResourceID, me.ResourceTypeID, me.AvailableAsOfDate, me.CreatedBy, me.CreatedDate, me.Subpool FROM resourcepool.Resources me WHERE ResourceID = '54821' DELETE FROM resourcepool.Resources WHERE ResourceID = '54821' Works, but redundant ( db2 already does the cascade for us ). Also incredibly slow at scale ( we're deleting thousands of records often, and delete_all will go one row at a time ). $ $schema->resultset("Resource")->search({ 'ResourceID' => 54821 }, { prefetch => 'NASShare' })->delete(); DELETE FROM resourcepool.Resources WHERE ResourceID IN ( SELECT me.ResourceID FROM resourcepool.Resources me WHERE me.ResourceID = '54821' ) Succeeds! But it's gotta be a prefetch. This fails because it's missing the schema name: $schema->resultset("Resource")->search({ 'ResourceID' => 54822 }, { join => 'NASShare' })->delete(); DELETE FROM Resources WHERE ResourceID = '54822' But this doesn't: $schema->resultset("Resource")->search({ 'ResourceID' => 54822 }, { prefetch => 'NASShare' })->delete(); DELETE FROM resourcepool.Resources WHERE ResourceID IN ( SELECT me.ResourceID FROM resourcepool.Resources me WHERE ResourceID = '54822' ) Please let me know if I can provide any more details or help in any way, and thanks for your time. Josh -------------------------------------------------------------------------- NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing.

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 344b
As far as I know DBIC doesn't provide a way to specify any kind of schema name. Different databases use 'schema' for different things. Please tell us how you've specified the DB2 schema name. For Oracle I've prefixed all tables names but switched to issuing an alter session statement on connect to make my DBIC schema cross RDBMS compatible.
Subject: RE: [rt.cpan.org #80015] AutoReply: Bug with resultset delete() on db2 ( dropping schema names from database )
Date: Fri, 5 Oct 2012 12:51:16 +0000
To: <bug-DBIx-Class [...] rt.cpan.org>
From: "Arenberg, Josh" <Josh.Arenberg [...] morganstanley.com>
Download (untitled) / with headers
text/plain 5.9k
It's just defined as part of the table name. We do this: __PACKAGE__->table("resourcepool.Resources") in the Schema::Result classes. I did notice this "gross hack" in the ResultSet code, seems like it could be responsible for stripping the leading portion of the table name, thinking it's an alias: # if no subquery - life is easy-ish unless ( $needs_group_by_subq or keys %$relation_classifications # if any joins at all - need to wrap a subq or $self->_has_resolved_attr(qw/rows offset/) # limits call for a subq ) { # Most databases do not allow aliasing of tables in UPDATE/DELETE. Thus # a condition containing 'me' or other table prefixes will not work # at all. Tell SQLMaker to dequalify idents via a gross hack. my $sqla = $rsrc->storage->sql_maker; local $sqla->{_dequalify_idents} = 1; return $rsrc->storage->$op( $rsrc, $op eq 'update' ? $values : (), $self->{cond}, ); } Show quoted text
-----Original Message----- From: Bugs in DBIx-Class via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: Thursday, October 04, 2012 8:16 PM To: Arenberg, Josh (Enterprise Infrastructure) Subject: [rt.cpan.org #80015] AutoReply: Bug with resultset delete() on db2 ( dropping schema names from database ) Greetings, This message has been automatically generated in response to the creation of a trouble ticket regarding: "Bug with resultset delete() on db2 ( dropping schema names from database )", a summary of which appears below. There is no need to reply to this message right now. Your ticket has been assigned an ID of [rt.cpan.org #80015]. Your ticket is accessible on the web at: https://rt.cpan.org/Ticket/Display.html?id=80015 Please include the string: [rt.cpan.org #80015] in the subject line of all future correspondence about this issue. To do so, you may reply to this message. Thank you, bug-DBIx-Class@rt.cpan.org ------------------------------------------------------------------------- Using dbic 0.08198 on Linux DB2 with Perl 5.8. It seems that the delete() method is dropping the schema names from the queries in certain cases. Examples below: $ $schema->resultset("Resource")->search({ ResourceID => 54821 })->first(); SELECT me.ResourceID, me.ResourceTypeID, me.AvailableAsOfDate, me.CreatedBy, me.CreatedDate, me.Subpool FROM resourcepool.Resources me WHERE ResourceID = '54821' Good $ $schema->resultset("Resource")->search({ ResourceID => 54821 })->delete(); DELETE FROM Resources WHERE ResourceID = '54821' BAD! No schema name ( db2 error ). $ $schema->resultset("Resource")->search({ 'ResourceID' => 54821 })->delete_all(); SELECT me.ResourceID, me.ResourceTypeID, me.AvailableAsOfDate, me.CreatedBy, me.CreatedDate, me.Subpool FROM resourcepool.Resources me WHERE ResourceID = '54821' DELETE FROM resourcepool.Resources WHERE ResourceID = '54821' Works, but redundant ( db2 already does the cascade for us ). Also incredibly slow at scale ( we're deleting thousands of records often, and delete_all will go one row at a time ). $ $schema->resultset("Resource")->search({ 'ResourceID' => 54821 }, { prefetch => 'NASShare' })->delete(); DELETE FROM resourcepool.Resources WHERE ResourceID IN ( SELECT me.ResourceID FROM resourcepool.Resources me WHERE me.ResourceID = '54821' ) Succeeds! But it's gotta be a prefetch. This fails because it's missing the schema name: $schema->resultset("Resource")->search({ 'ResourceID' => 54822 }, { join => 'NASShare' })->delete(); DELETE FROM Resources WHERE ResourceID = '54822' But this doesn't: $schema->resultset("Resource")->search({ 'ResourceID' => 54822 }, { prefetch => 'NASShare' })->delete(); DELETE FROM resourcepool.Resources WHERE ResourceID IN ( SELECT me.ResourceID FROM resourcepool.Resources me WHERE ResourceID = '54822' ) Please let me know if I can provide any more details or help in any way, and thanks for your time. Josh -------------------------------------------------------------------------- NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing. -------------------------------------------------------------------------- NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing.
Download (untitled) / with headers
text/plain 296b
That's what I did before using the alter session statement. Can you work around it by also using an alter session or DB2 equivalent statement while we look into a fix? That will also enable you to use your schema for easy testing with SQLite which was my case for not prefixing all table names.
Subject: RE: [rt.cpan.org #80015] Bug with resultset delete() on db2 ( dropping schema names from database )
Date: Fri, 5 Oct 2012 21:11:40 +0000
To: <bug-DBIx-Class [...] rt.cpan.org>
From: "Arenberg, Josh" <Josh.Arenberg [...] morganstanley.com>
Download (untitled) / with headers
text/plain 1.7k
Yes, I actually got this working today. There is a 'set schema' call in db2 that does just that, which I wasn't previously aware of. Thanks again for the suggestion. Show quoted text
-----Original Message----- From: Alexander Hartmaier via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: Friday, October 05, 2012 2:35 PM To: Arenberg, Josh (Enterprise Infrastructure) Subject: [rt.cpan.org #80015] Bug with resultset delete() on db2 ( dropping schema names from database ) <URL: https://rt.cpan.org/Ticket/Display.html?id=80015 > That's what I did before using the alter session statement. Can you work around it by also using an alter session or DB2 equivalent statement while we look into a fix? That will also enable you to use your schema for easy testing with SQLite which was my case for not prefixing all table names. -------------------------------------------------------------------------- NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing.
Merging with identical ticket
Download (untitled) / with headers
text/plain 320b
On Thu Oct 04 20:15:58 2012, Josh.Arenberg@morganstanley.com wrote: Show quoted text
> > It seems that the delete() method is dropping the schema names from > the queries in certain cases. Examples below:
Please test and let us know if branch rt/80015 [1] solves your issue. [1] https://github.com/dbsrgits/DBIx-Class/tree/rt/80015
Fix dropped in master


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.