Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: jjnapiork [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.08108
Fixed in: 0.08109



Subject: duplicate inserts with multicreate causing error
Download (untitled) / with headers
text/plain 921b
Please see attached test case, which should run fine as a stand alone. Basically if you try to multi-create along a 'belongs_to' to something that has a 'has_many' set of the starting table, unless you specify an empty arrayref for the has_many, you get a duplicate insert into the starting table, which generates a constrain error. If you run the test case test file attached, with DBIC_TRACE, you'll see the duplicate attempts to insert, followed by the exception message. If you uncomment the "relation_rs" bit at the end of the test, you'll see it runs fine. I put a while into this, but can't really trace it down. From what I can tell, if you don't have the 'relation_rs' bit, find_or_create tries to create a new result, but if you specify it as '[]', everything is good. If you can give me some pointers I will be happy to try again, but right now the multicreate stuff is a bit fuzzy for me. Thank you!
Subject: multicreateextra.t
Download multicreateextra.t
text/x-perl 10.3k
use Test::More; { use strict; use warnings; use_ok 'DBIx::Class','0.08108'; use_ok 'SQL::Translator', '0.09003'; package MyApp::Schema::Entry; { use base 'DBIx::Class'; __PACKAGE__->load_components(qw/PK::Auto Core/); __PACKAGE__->table('entry'); __PACKAGE__->add_columns( entry_id => { data_type=>'integer', is_auto_increment=>1, }, title => { data_type=>'varchar', }, ); __PACKAGE__->set_primary_key('entry_id'); __PACKAGE__->might_have( object => 'MyApp::Schema::Object', {'foreign.object_id'=>'self.entry_id'}, ); __PACKAGE__->might_have( subject => 'MyApp::Schema::Subject', {'foreign.subject_id'=>'self.entry_id'}, ); } package MyApp::Schema::Object; { use base 'DBIx::Class'; __PACKAGE__->load_components(qw/PK::Auto Core/); __PACKAGE__->table('object'); __PACKAGE__->add_columns( object_id => {data_type=>'integer'}, ); __PACKAGE__->set_primary_key('object_id'); __PACKAGE__->belongs_to( entry => 'MyApp::Schema::Entry', {'foreign.entry_id' => 'self.object_id'}, ); __PACKAGE__->has_many( relation_rs => 'MyApp::Schema::Relation', {'foreign.fk_object_id' => 'self.object_id'}, ); } package MyApp::Schema::Subject; { use base 'DBIx::Class'; __PACKAGE__->load_components(qw/PK::Auto Core/); __PACKAGE__->table('subject'); __PACKAGE__->add_columns( subject_id => {data_type=>'integer'}, ); __PACKAGE__->set_primary_key('subject_id'); __PACKAGE__->belongs_to( entry => 'MyApp::Schema::Entry', {'foreign.entry_id' => 'self.subject_id'}, ); __PACKAGE__->has_many( relation_rs => 'MyApp::Schema::Relation', {'foreign.fk_subject_id' => 'self.subject_id'}, ); } package MyApp::Schema::Relation; { use base 'DBIx::Class'; __PACKAGE__->load_components(qw/PK::Auto Core/); __PACKAGE__->table('relation'); __PACKAGE__->add_columns( fk_object_id => { data_type=>'integer' }, fk_subject_id => { data_type=>'integer' }, ); __PACKAGE__->belongs_to( object => 'MyApp::Schema::Object', {'foreign.object_id' => 'self.fk_object_id'}, ); __PACKAGE__->belongs_to( subject => 'MyApp::Schema::Subject', {'foreign.subject_id' => 'self.fk_subject_id'}, ); __PACKAGE__->set_primary_key(qw/ fk_object_id fk_subject_id /); } package MyApp::Schema; { use base 'DBIx::Class::Schema'; MyApp::Schema->load_classes(qw/ Entry Object Subject Relation /); } Test::More::ok my $schema = MyApp::Schema->connect('dbi:SQLite:dbname=:memory:','','') => 'Connected to sqlite in memory database'; $schema->deploy(); ## Create some top level entries. Test::More::ok my $obama = $schema->resultset('Entry')->create({title=>'obama'}) => 'new entry "obama"'; Test::More::ok my $president = $schema->resultset('Entry')->create({title=>'president'}) => 'new entry "president"'; Test::More::ok my $benedict = $schema->resultset('Entry')->create({title=>'benedict'}) => 'new entry "benedict"'; Test::More::ok my $pope = $schema->resultset('Entry')->create({title=>'pope'}) => 'new entry "pope"'; ## Totally fine Test::More::ok my $object_benedict = $schema->resultset('Object')->create({ entry => $benedict, }), 'created object benedict'; Test::More::ok my $subject_pope = $schema->resultset('Subject')->create({ entry => $pope, }), 'created subject pope'; ## Create a Relation. If this works, I imagine the next one should work as well Test::More::ok my $relation_benedict_pope = $schema->resultset('Relation')->create({ subject => $subject_pope, object => $object_benedict, }), 'multicreate via objects'; ## Create a Relation. This dies without the 'relation_rs => []' thing. Test::More::ok my $relation_obama_president = $schema->resultset('Relation')->create({ subject => { entry => $obama, ### relation_rs => [], }, object => { entry => $president, ### relation_rs => [], }, }), "multicreate m2m bug"; Test::More::done_testing(); } __END__ So this dies with the following (error message isolated for clarity) BEGIN WORK SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '2' INSERT INTO object ( object_id) VALUES ( ? ): '2' SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1' SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1' SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '1' INSERT INTO subject ( subject_id) VALUES ( ? ): '1' SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1' SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1' INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1' INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1' DBI Exception: DBD::SQLite::st execute failed: columns fk_object_id, fk_subject_id are not unique [for Statement "INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? )"] at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1025 Which is due to the double insert. However, if we change the multicreate to: $schema->resultset('Relation')->create({ subject => { entry => $obama, relation_rs => [], }, object => { entry => $president, relation_rs => [], }, }); Everything works correctly. Here's the sql that it generates: SELECT me.object_id FROM object me WHERE ( 1 = 0 ): SELECT me.subject_id FROM subject me WHERE ( 1 = 0 ): BEGIN WORK SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' INSERT INTO object ( object_id) VALUES ( ? ): '2' SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1' INSERT INTO subject ( subject_id) VALUES ( ? ): '1' INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1' COMMIT As you can see, the SQL is significantly better. For More assistance, here's what the debug with DBIC_MULTICREATE_DEBUG=1 MC MyApp::Schema::Entry=HASH(0x19f7184) fetching missing PKs entry_id MC MyApp::Schema::Relation=HASH(0x19f7178) constructing object via find_or_new_related at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122. SELECT me.object_id FROM object me WHERE ( 1 = 0 ): MC MyApp::Schema::Object=HASH(0x1a003e0) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1) MC MyApp::Schema::Relation=HASH(0x19f7178) uninserted object MyApp::Schema::Object=HASH(0x1a003e0) MC MyApp::Schema::Relation=HASH(0x19f7178) constructing subject via find_or_new_related at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122. SELECT me.subject_id FROM subject me WHERE ( 1 = 0 ): MC MyApp::Schema::Subject=HASH(0x1a004dc) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1) MC MyApp::Schema::Relation=HASH(0x19f7178) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc) BEGIN WORK MC MyApp::Schema::Relation=HASH(0x19f7178) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0) SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' MC MyApp::Schema::Object=HASH(0x1a30890) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1) MC MyApp::Schema::Object=HASH(0x1a30890) pre-reconstructing entry MyApp::Schema::Entry=HASH(0x19f7184) SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '2' MC MyApp::Schema::Object=HASH(0x1a30890) inserting (object_id, 2) INSERT INTO object ( object_id) VALUES ( ? ): '2' MC MyApp::Schema::Object=HASH(0x1a30890) re-creating relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 376. SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1' MC MyApp::Schema::Relation=HASH(0x1a318b4) uninserted object MyApp::Schema::Object=HASH(0x1a003e0) MC MyApp::Schema::Relation=HASH(0x1a318b4) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc) MC MyApp::Schema::Relation=HASH(0x1a318b4) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0) SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' MC MyApp::Schema::Relation=HASH(0x1a318b4) pre-reconstructing subject MyApp::Schema::Subject=HASH(0x1a004dc) SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1' MC MyApp::Schema::Subject=HASH(0x1a37658) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1) MC MyApp::Schema::Subject=HASH(0x1a37658) pre-reconstructing entry MyApp::Schema::Entry=HASH(0x19f7100) SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '1' MC MyApp::Schema::Subject=HASH(0x1a37658) inserting (subject_id, 1) INSERT INTO subject ( subject_id) VALUES ( ? ): '1' MC MyApp::Schema::Subject=HASH(0x1a37658) re-creating relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 376. SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1' MC MyApp::Schema::Relation=HASH(0x1a38300) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc) MC MyApp::Schema::Relation=HASH(0x1a38300) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0) SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2' MC MyApp::Schema::Relation=HASH(0x1a38300) pre-reconstructing subject MyApp::Schema::Subject=HASH(0x1a004dc) SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1' MC MyApp::Schema::Relation=HASH(0x1a38300) inserting (fk_object_id, 2, fk_subject_id, 1) INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1' MC MyApp::Schema::Subject=HASH(0x1a37658) new relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 382. MC MyApp::Schema::Relation=HASH(0x1a318b4) inserting (fk_object_id, 2, fk_subject_id, 1) INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1' DBI Exception: DBD::SQLite::st execute failed: columns fk_object_id, fk_subject_id are not unique [for Statement "INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? )"] at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1025
Download (untitled) / with headers
text/plain 1.1k
On Wed Jul 15 14:59:45 2009, JJNAPIORK wrote: Show quoted text
> Please see attached test case, which should run fine as a stand alone. > > Basically if you try to multi-create along a 'belongs_to' to something > that has a 'has_many' set of the starting table, unless you specify an > empty arrayref for the has_many, you get a duplicate insert into the > starting table, which generates a constrain error. > > If you run the test case test file attached, with DBIC_TRACE, you'll see > the duplicate attempts to insert, followed by the exception message. If > you uncomment the "relation_rs" bit at the end of the test, you'll see > it runs fine. > > I put a while into this, but can't really trace it down. From what I > can tell, if you don't have the 'relation_rs' bit, find_or_create tries > to create a new result, but if you specify it as '[]', everything is good. > > If you can give me some pointers I will be happy to try again, but right > now the multicreate stuff is a bit fuzzy for me. Thank you! >
Looks like a bug. Don't have any pointers right now. I can dig into it in 3 weeks or so, there is some other MC buggery outstanding too.


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.