Skip Menu |
 

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

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

People
Owner: abraxxa [...] cpan.org
Requestors: mwiesen [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: 0.08124
Fixed in: 0.08125



Subject: _dbh_last_insert_id
Download (untitled) / with headers
text/plain 1.6k
Pre-conditions: *Oracle 11g database *Insert row into a table with auto-increment id *2 or more database schemas (e.g. sym10 and sym30) *Authentication with sym30 user -> default schema is sym30 Attention: Bug is not present in version 0.08115 of DBIx::Class and the module DBIx::Class::Storage::DBI::Oracle::Generic didn't change between the two versions. Code snippet that fails: $dbh->resultset('Cfgproperty')->create( $data_entry ); Module DBIx::Class::Storage::DBI::Oracle::Generic determines the maximum sequence number of the auto-increment column (_dbh_last_insert_id). The auto-increment trigger is defined as follows: create or replace trigger ai_cfgproperty BEFORE INSERT ON cfgproperty for each row DECLARE NUMROWS INTEGER; BEGIN select cfgid.nextval into :NEW.cfgid FROM dual; END; _dbh_get_autoinc_seq fetches two records. One for each schema. The column owner will not be considered if the schema name is not given at the insert statement. Hence, the database command insert into cfgproperty ... (sym30, because default schema of the user) instead of insert into sym30.cfgproperty is created. Because of the declaration of the trigger, the schema of the table is still unknown. The following statement then fails: Eval hat eine Exception abgefangen: 'DBIx::Class::ResultSet::create(): DBI Exception: DBD::Oracle::db selectrow_array failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 13 in 'SELECT SYM10.<*>cfgid.currval FROM DUAL') [for Statement "SELECT SYM10.cfgid.currval FROM DUAL"] at .... The statement is addressing the cfgid column of the schema sym10, but the insert should be done on the table within sym30.
From: mwiesen [...] gmail.com
Download (untitled) / with headers
text/plain 287b
Am Mi 01. Dez 2010, 06:30:47, ABRAXXA schrieb: Show quoted text
> This is already fixed in the git repo: > http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx- > Class.git;a=commitdiff;h=6f5f880dacae5dcad72178f33916cce0e662a9f9 > > Please test if git master works for you.
Still doesn't work.
From: mwiesen [...] gmail.com
Download (untitled) / with headers
text/plain 319b
Sorry, here is the exception: Eval hat eine Exception abgefangen: 'DBIx::Class::ResultSet::create(): Unable to reliably select a BEFORE INSERT trigger for column CFGPROPERTY.cfgid (possibilities: 'AI_CFGPROPERTY', 'AI_CFGPROPERTY'). You need to specify the correct 'sequence' explicitly in 'cfgid's column_info. at ...
Download (untitled) / with headers
text/plain 1.8k
Hi, sorry for not replying sooner. On Wed Dec 01 04:22:49 2010, mwiesen wrote: Show quoted text
> Pre-conditions: > *Oracle 11g database > *Insert row into a table with auto-increment id > *2 or more database schemas (e.g. sym10 and sym30) > *Authentication with sym30 user -> default schema is sym30 > > Attention: Bug is not present in version 0.08115 of DBIx::Class and the > module DBIx::Class::Storage::DBI::Oracle::Generic didn't change between > the two versions.
Of course it did :) Show quoted text
> Code snippet that fails: > $dbh->resultset('Cfgproperty')->create( $data_entry ); > > Module DBIx::Class::Storage::DBI::Oracle::Generic determines the maximum > sequence number of the auto-increment column (_dbh_last_insert_id). > > The auto-increment trigger is defined as follows: > > create or replace trigger ai_cfgproperty > BEFORE INSERT > ON cfgproperty > > for each row > > DECLARE NUMROWS INTEGER; > BEGIN > select cfgid.nextval > into :NEW.cfgid > FROM dual; > END; > > > _dbh_get_autoinc_seq fetches two records. One for each schema. > The column owner will not be considered if the schema name is not given > at the insert statement. > > Hence, the database command > insert into cfgproperty ... (sym30, because default schema of the user) > instead of > insert into sym30.cfgproperty > is created. > > Because of the declaration of the trigger, the schema of the table is > still unknown.
^^ I am a bit confused here. Are you saying that you have identically named sequences in two schemas, and dbic gets back the wrong one? And are you saying that it *did* work correctly on 08115? You are sure it wasn't silently reading from the wrong sequence? My main question remaining after reading your report is "if I do not know the schema, how can I possibly select the correct sequence?" Maybe I am overlooking something obvious, please share :)
From: mwiesen [...] gmail.com
Download (untitled) / with headers
text/plain 2.4k
Am Mo 13. Dez 2010, 11:09:43, RIBASUSHI schrieb: Show quoted text
> Hi, sorry for not replying sooner. > > On Wed Dec 01 04:22:49 2010, mwiesen wrote:
> > Pre-conditions: > > *Oracle 11g database > > *Insert row into a table with auto-increment id > > *2 or more database schemas (e.g. sym10 and sym30) > > *Authentication with sym30 user -> default schema is sym30 > > > > Attention: Bug is not present in version 0.08115 of DBIx::Class and the > > module DBIx::Class::Storage::DBI::Oracle::Generic didn't change between > > the two versions.
> > Of course it did :) > >
> > Code snippet that fails: > > $dbh->resultset('Cfgproperty')->create( $data_entry ); > > > > Module DBIx::Class::Storage::DBI::Oracle::Generic determines the maximum > > sequence number of the auto-increment column (_dbh_last_insert_id). > > > > The auto-increment trigger is defined as follows: > > > > create or replace trigger ai_cfgproperty > > BEFORE INSERT > > ON cfgproperty > > > > for each row > > > > DECLARE NUMROWS INTEGER; > > BEGIN > > select cfgid.nextval > > into :NEW.cfgid > > FROM dual; > > END; > > > > > > _dbh_get_autoinc_seq fetches two records. One for each schema. > > The column owner will not be considered if the schema name is not given > > at the insert statement. > > > > Hence, the database command > > insert into cfgproperty ... (sym30, because default schema of the user) > > instead of > > insert into sym30.cfgproperty > > is created. > > > > Because of the declaration of the trigger, the schema of the table is > > still unknown.
> > ^^ I am a bit confused here. Are you saying that you have identically > named sequences in two schemas, and dbic gets back the wrong one? And > are you saying that it *did* work correctly on 08115? You are sure it > wasn't silently reading from the wrong sequence? > > My main question remaining after reading your report is "if I do not > know the schema, how can I possibly select the correct sequence?" Maybe > I am overlooking something obvious, please share :)
Hi, yes that might be right. The sequence could be determined out of the wrong schema. We didn't mention that, because the schemas are used for versioning. I.E. Verison 1 sym10, version 2 sym20 and version 3 sym30. The data will be migrated to the new schema and the write access to the old one will be removed. Hence we didn't mention the bug in 80115. I don't know, if I'm right, but you can determine the schema by the default schema of the user which is logged in. The user name is the default schema, if nothing other is defined.
Download (untitled) / with headers
text/plain 3.1k
On Tue Dec 14 02:53:05 2010, mwiesen wrote: Show quoted text
> Am Mo 13. Dez 2010, 11:09:43, RIBASUSHI schrieb:
> > Hi, sorry for not replying sooner. > > > > On Wed Dec 01 04:22:49 2010, mwiesen wrote:
> > > Pre-conditions: > > > *Oracle 11g database > > > *Insert row into a table with auto-increment id > > > *2 or more database schemas (e.g. sym10 and sym30) > > > *Authentication with sym30 user -> default schema is sym30 > > > > > > Attention: Bug is not present in version 0.08115 of DBIx::Class
and the Show quoted text
> > > module DBIx::Class::Storage::DBI::Oracle::Generic didn't change
between Show quoted text
> > > the two versions.
> > > > Of course it did :) > > > >
> > > Code snippet that fails: > > > $dbh->resultset('Cfgproperty')->create( $data_entry ); > > > > > > Module DBIx::Class::Storage::DBI::Oracle::Generic determines the
maximum Show quoted text
> > > sequence number of the auto-increment column (_dbh_last_insert_id). > > > > > > The auto-increment trigger is defined as follows: > > > > > > create or replace trigger ai_cfgproperty > > > BEFORE INSERT > > > ON cfgproperty > > > > > > for each row > > > > > > DECLARE NUMROWS INTEGER; > > > BEGIN > > > select cfgid.nextval > > > into :NEW.cfgid > > > FROM dual; > > > END; > > > > > > > > > _dbh_get_autoinc_seq fetches two records. One for each schema. > > > The column owner will not be considered if the schema name is not
given Show quoted text
> > > at the insert statement. > > > > > > Hence, the database command > > > insert into cfgproperty ... (sym30, because default schema of the
user) Show quoted text
> > > instead of > > > insert into sym30.cfgproperty > > > is created. > > > > > > Because of the declaration of the trigger, the schema of the table is > > > still unknown.
> > > > ^^ I am a bit confused here. Are you saying that you have identically > > named sequences in two schemas, and dbic gets back the wrong one? And > > are you saying that it *did* work correctly on 08115? You are sure it > > wasn't silently reading from the wrong sequence? > > > > My main question remaining after reading your report is "if I do not > > know the schema, how can I possibly select the correct sequence?" Maybe > > I am overlooking something obvious, please share :)
> > Hi, yes that might be right. The sequence could be determined out of the > wrong schema. We didn't mention that, because the schemas are used for > versioning. I.E. Verison 1 sym10, version 2 sym20 and version 3 sym30. > The data will be migrated to the new schema and the write access to the > old one will be removed. Hence we didn't mention the bug in 80115. > > I don't know, if I'm right, but you can determine the schema by the > default schema of the user which is logged in. The user name is the > default schema, if nothing other is defined.
This is indeed a sane way forward: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commit;h=676705fafba4d0006ca1ced87a071bb403b3182c I expect this to release some time this week, but you can test it immediately by checking out git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git branch people/abraxxa/oracle_returning (which also requires an unreleased git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git master to run). Cheers


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.