Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: Duncan.Garland [...] motortrak.com
Cc:
AdminCc:

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



Subject: FW: Class::Storage::Oracle::Generic.pm
Date: Thu, 5 May 2011 16:45:07 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 3.2k
From: Duncan Garland Sent: 05 May 2011 16:40 To: 'bug-DBIx-Class@rt.cpan.org.' Subject: Class::Storage::Oracle::Generic.pm Hi, I think there's a bug in this module. We've set up our system so that there are two users, each with their own schema, on the same Oracle instance. One is motrak and one is mbfl2_training. The config file tells it which user to log in as: mbfl2.conf: <Model::DB> schema_class MBFL2SCHEMA <connect_info> dsn dbi:Oracle:ORCL user motrak password XXXXXXXXXXX on_connect_do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' on_connect_do ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' LongReadLen 2000000 </connect_info> </Model::DB> mbfl2_training.conf: <Model::DB> schema_class MBFL2SCHEMA <connect_info> dsn dbi:Oracle:ORCL user mbfl2_training password XXXXXXXXXXXXXXX on_connect_do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' on_connect_do ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' LongReadLen 2000000 </connect_info> </Model::DB> This has been working quite well, but we've suddenly started getting insert errors on one table in particular: [05/May/2011:15:27:22 +0100] WARN:www.mbfl2-training.com:[FCGI: /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/mbfl2_training_fastcgi.pl]: [error] 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 14 in 'SELECT MOTRAK.<*>s_mbfl2_dealer_package_models.currval FROM DUAL') [for Statement "SELECT MOTRAK.s_mbfl2_dealer_package_models.currval FROM DUAL"] at /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/../lib/mbfl2/Controller/DealerAdmin.pm line 675 This is bizarre because the user should be logged in as mbfl2_training and, as far as I can tell, is logged in as mbfl2_training. MOTRAK.s_mbfl2_dealer_package_models exists but the user does not have access to it. He should be accessing MBFL2_TRAINING.s_mbfl2_dealer_package_models or just s_mbfl2_dealer_package_models. The problem seems to be in Generic:: _dbh_get_autoinc_seq(). It derives the schema name by selecting the trigger name and the owner name from all_triggers. All_triggers returns triggers which belong to both MOTRAK and MBFL2_TRAINING and which contain the sequence s_mbfl2_dealer_package_models. The MOTRAK trigger is returned first, so the schema is set incorrectly. When I change the statement to use USER_TRIGGER is works. MBFL2_TRAINING has select access on some of MOTRAK's tables (and thus their triggers) because I copied some data across a couple of month ago. If I revoke the select access, I think the original code (using ALL_TRIGGERS) will work. However, I have worked on systems in the past where the user genuinely needs access to two tables with the same name. For example, on development systems it is common to read from a central set of tables and create a local copy of any tables which need to be written to. Oracle will correctly select the table in the current schema in preference to one in another schema, but your code might not. I hope this feedback is useful. All the best. Duncan
Which DBIx::Class version do you use? There where several fixed for this in 0.08125.
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Thu, 5 May 2011 17:39:04 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 716b
Module id = DBIx::Class CPAN_USERID ARCANEZ (Justin Hunter <justin.d.hunter@gmail.com>) CPAN_VERSION 0.08191 CPAN_FILE F/FR/FREW/DBIx-Class-0.08191.tar.gz MANPAGE DBIx::Class - Extensible and flexible object <-> relational mapper. INST_FILE /usr/lib/perl5/site_perl/5.8.8/DBIx/Class.pm INST_VERSION 0.08123 Show quoted text
-----Original Message----- From: Alexander Hartmaier via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 05 May 2011 17:37 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > Which DBIx::Class version do you use? There where several fixed for this in 0.08125.
Download (untitled) / with headers
text/plain 1.1k
On Thu May 05 11:45:24 2011, Duncan.Garland@motortrak.com wrote: Show quoted text
> > The problem seems to be in Generic:: _dbh_get_autoinc_seq(). It > derives the schema name by selecting the trigger name and the owner > name from all_triggers. All_triggers returns triggers which belong > to both MOTRAK and MBFL2_TRAINING and which contain the sequence > s_mbfl2_dealer_package_models. > > The MOTRAK trigger is returned first, so the schema is set > incorrectly. When I change the statement to use USER_TRIGGER is > works. >
So do I understand correctly that the heuristics should be adjusted to first scan USER_TRIGGER and then if nothing is found re-scan ALL_TRIGGERS? I am not very well versed in the vagaries of Oracle, so it's on you as the user to come up with a sensible design (and hopefully a patch :P). Let us know what exactly will work for you, and we'll be happy to fix it. As a workaround for the time being you can specify an explicit 'sequence' in your column_info of the particular PK, which will preempt the entire heuristics. Of course fixing this on the DBIC level is always preferable :) Cheers!
Download (untitled) / with headers
text/plain 301b
I don't see how this situation is possible with the code in master. One of the select criteria on ALL_TRIGGERS is OWNER, which is set to the schema of the table or to the logged in user. It would only pick up the MOTRAK sequence if your ->table call was something like: ->table('MOTRAK.table_name')
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 18 May 2011 10:45:14 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.9k
Hi Rafael, The problem definitely is repeatable. I solved the problem by revoking the permission. As soon as I give mbfl2_training select permission on motrak's table: Show quoted text
SQL> GRANT SELECT ON mbfl2_dealer_package_models TO mbfl2_training;
mbfl2_training starts to throw errors because it is getting confused about which sequence to use: [18/May/2011:10:29:29 +0100] WARN:www.mbfl2-training.com:[FCGI: /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/mbfl2_training_fastcgi.pl]: [debug] Check: 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 14 in 'SELECT MOTRAK.<*>s_mbfl2_dealer_package_models.currval FROM DUAL') [for Statement "SELECT MOTRAK.s_mbfl2_dea [18/May/2011:10:29:29 +0100] WARN:www.mbfl2-training.com:[FCGI: /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/mbfl2_training_fastcgi.pl]: ler_package_models.currval FROM DUAL"] at /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/../lib/mbfl2/Controller/DealerAdmin.pm line 675 It should be selecting from mbfl2_training.s_mbfl2_dealer_package_models or just s_mbfl2_dealer_package_models. It can't see motrak.s_mbfl2_dealer_package_models which is good because it's the wrong sequence. It's possible that my diagnosis is wrong. I removed my debug messages from Generic.pm. I'll re-instate them and try again. Regards Duncan Show quoted text
-----Original Message----- From: Rafael Kitover via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 14 May 2011 09:16 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > I don't see how this situation is possible with the code in master. One of the select criteria on ALL_TRIGGERS is OWNER, which is set to the schema of the table or to the logged in user. It would only pick up the MOTRAK sequence if your ->table call was something like: ->table('MOTRAK.table_name')
Download (untitled) / with headers
text/plain 132b
I'd suggest that you enable DBI debugging (not DBIC!) and show us the exact sql queries that get executed to determine the sequence.
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 18 May 2011 11:35:14 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 5.5k
Hi Rafael, The marked up code is now: sub _dbh_get_autoinc_seq { my ($self, $dbh, $source, $col) = @_; print STDERR "DMG In _dbh_get_autoinc_seq\n"; my $sql_maker = $self->sql_maker; my $source_name; if ( ref $source->name eq 'SCALAR' ) { $source_name = ${$source->name}; } else { $source_name = $source->name; } print STDERR "DMG source_name=$source_name\n"; $source_name = uc($source_name) unless $sql_maker->quote_char; print STDERR "DMG source_name=$source_name\n"; # trigger_body is a LONG local $dbh->{LongReadLen} = 64 * 1024 if ($dbh->{LongReadLen} < 64 * 1024); # disable default bindtype local $sql_maker->{bindtype} = 'normal'; # look up the correct sequence automatically my ( $schema, $table ) = $source_name =~ /(\w+)\.(\w+)/; print STDERR "DMG schema=$schema table=$table\n"; my ($sql, @bind) = $sql_maker->select ( 'ALL_TRIGGERS', ['trigger_body', 'table_owner'], { $schema ? (owner => $schema) : (), table_name => $table || $source_name, triggering_event => { -like => '%INSERT%' }, status => 'ENABLED', }, ); my $sth = $dbh->prepare($sql); $sth->execute (@bind); while (my ($insert_trigger, $schema) = $sth->fetchrow_array) { print STDERR "DMG trigger_body:\n$insert_trigger\n"; my ($seq_name) = $insert_trigger =~ m!("?[.\w"]+?"?)\.nextval!i; print STDERR "DMG seq_name=$seq_name\n"; next unless $seq_name; if ($seq_name !~ /\./) { $seq_name = join '.' => map $self->sql_maker->_quote($_), $schema, $seq_name; } print STDERR "DMG returning seq_name=$seq_name\n"; return $seq_name; } $self->throw_exception("Unable to find a sequence %INSERT% trigger on table '$source_name'."); } This produces: [18/May/2011:11:02:39 +0100]: DMG In _dbh_get_autoinc_seq [18/May/2011:11:02:39 +0100]: DMG source_name=mbfl2_dealer_package_models [18/May/2011:11:02:39 +0100]: DMG source_name=MBFL2_DEALER_PACKAGE_MODELS [18/May/2011:11:02:39 +0100]: Use of uninitialized value in concatenation (.) or string at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI/Oracle/Generic.pm line 138. [18/May/2011:11:02:39 +0100]: Use of uninitialized value in concatenation (.) or string at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI/Oracle/Generic.pm line 138. [18/May/2011:11:02:39 +0100]: DMG schema= table= [18/May/2011:11:02:39 +0100]: DMG trigger_body: [18/May/2011:11:02:39 +0100]: BEGIN [18/May/2011:11:02:39 +0100]: SELECT s_mbfl2_dealer_package_models.NEXTVAL INTO :new.id [18/May/2011:11:02:39 +0100]: FROM dual; [18/May/2011:11:02:39 +0100]: END; [18/May/2011:11:02:39 +0100]: DMG seq_name=s_mbfl2_dealer_package_models [18/May/2011:11:02:39 +0100]: DMG returning seq_name=MOTRAK.s_mbfl2_dealer_package_models The data in the database is: 1 SELECT trigger_name, trigger_body, table_owner FROM all_triggers 2 WHERE table_name = 'MBFL2_DEALER_PACKAGE_MODELS' 3 AND triggering_event LIKE '%INSERT%' 4* AND status = 'ENABLED' Show quoted text
SQL> /
TRIGGER_NAME TRIGGER_BODY TABLE_OWNER ------------------------------ -------------------------------------------------------------------------------- ------------------------------ T_MBFL2_DEALER_PACKAGE_MODELS BEGIN MOTRAK SELECT s_mbfl2_dealer_package_models.NEXTVAL INTO :new.id FROM dual; END T_MBFL2_DEALER_PACKAGE_MODELS BEGIN MBFL2_TRAINING SELECT s_mbfl2_dealer_package_models.NEXTVAL INTO :new.id FROM dual; END T_INTEGRITY_MBFL2_DPM DECLARE MOTRAK x NUMBER; e_DUPLICATE_ROW EXCEPTION; TRIGGER_NAME TRIGGER_BODY TABLE_OWNER ------------------------------ -------------------------------------------------------------------------------- ------------------------------ BEGIN SELECT t1.id INTO x FRO T_INTEGRITY_MBFL2_DPM DECLARE MBFL2_TRAINING x NUMBER; e_DUPLICATE_ROW EXCEPTION; BEGIN SELECT t1.id INTO x FRO 4 rows selected. So I was more or less correct in what I said before. The first trigger returned belongs to a table owned by motrak. This causes the schema to be set to motrak. All the best. Duncan Show quoted text
-----Original Message----- From: Rafael Kitover via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 14 May 2011 09:16 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > I don't see how this situation is possible with the code in master. One of the select criteria on ALL_TRIGGERS is OWNER, which is set to the schema of the table or to the logged in user. It would only pick up the MOTRAK sequence if your ->table call was something like: ->table('MOTRAK.table_name')
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 18 May 2011 12:34:38 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 2.8k
Hi Peter, It would have worked correctly using all_triggers if the schema had defaulted correctly. Something like: 1 SELECT trigger_name, trigger_body, table_owner FROM all_triggers 2 WHERE table_name = 'MBFL2_DEALER_PACKAGE_MODELS' 3 AND triggering_event LIKE '%INSERT%' 4 AND status = 'ENABLED' 5* AND table_owner = NVL( :schema, USER ) I think it's reasonable for it to default to the current user. However, it's quite common for the tables to be owned by another user. In such cases it's important that the schema is set correctly. I assume that's done elsewhere in DBIC. It's possible that there could genuinely be more than one insert trigger and that a trigger could contain more than one sequence. I don't think the heuristics should stop when they find a sequence. I think they should carry on, read all the triggers, and error if there is not exactly one matching sequence. Similarly, I think your regular expression should be made to find all the sequences in the trigger and error if there is more than one. In the future, it might be useful if the configuration file could contain the regular expression which would be used to identify the sequence which is used for the primary key. On this project the sequence name is simply "s_$table_name". I suppose on another project the naming convention might be "s_pkey_$table_name". Let me know if there are any other Oracle issues you would like me to comment on. All the best. Duncan Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 07 May 2011 14:08 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Thu May 05 11:45:24 2011, Duncan.Garland@motortrak.com wrote:
> > The problem seems to be in Generic:: _dbh_get_autoinc_seq(). It > derives the schema name by selecting the trigger name and the owner > name from all_triggers. All_triggers returns triggers which belong > to both MOTRAK and MBFL2_TRAINING and which contain the sequence > s_mbfl2_dealer_package_models. > > The MOTRAK trigger is returned first, so the schema is set > incorrectly. When I change the statement to use USER_TRIGGER is > works. >
So do I understand correctly that the heuristics should be adjusted to first scan USER_TRIGGER and then if nothing is found re-scan ALL_TRIGGERS? I am not very well versed in the vagaries of Oracle, so it's on you as the user to come up with a sensible design (and hopefully a patch :P). Let us know what exactly will work for you, and we'll be happy to fix it. As a workaround for the time being you can specify an explicit 'sequence' in your column_info of the particular PK, which will preempt the entire heuristics. Of course fixing this on the DBIC level is always preferable :) Cheers!
Download (untitled) / with headers
text/plain 1.9k
On Wed May 18 07:34:49 2011, Duncan.Garland@motortrak.com wrote: Show quoted text
> Hi Peter, > > It would have worked correctly using all_triggers if the > schema had defaulted correctly. > > Something like: > > 1 SELECT > trigger_name, trigger_body, table_owner FROM all_triggers > 2 > WHERE table_name = 'MBFL2_DEALER_PACKAGE_MODELS' > 3 AND > triggering_event LIKE '%INSERT%' > 4 AND status = 'ENABLED' > 5* > AND table_owner = NVL( :schema, USER ) > > I think it's reasonable > for it to default to the current user. However, it's quite common > for the tables to be owned by another user. In such cases it's > important > that the schema is set correctly. I assume that's done > elsewhere in DBIC. > > It's possible that there could genuinely be > more than one insert trigger and that a trigger could contain more > than one sequence. I don't think the heuristics should stop when > they find a sequence. I think they should carry on, read all the > triggers, and error if there is not exactly one matching sequence. > Similarly, I think your regular expression should be made to find > all the sequences in the trigger and error if there is more than > one.
I think I am getting confused by this. Is it possible for you to provide us a patch that adjusts the heuristics to work in a way that suits your situation? Show quoted text
> > In the future, it might be useful if the configuration file > could contain the regular expression which would be used to > identify the sequence which is used for the primary key. On this > project the sequence name is simply "s_$table_name". I suppose on > another project the naming convention might be > "s_pkey_$table_name".
I think that's going into "configuration hell" territory. We already provide a way to specify an explicit sequence (bypassing all heuristics) for esoteric schema designs. I find it very hard to justify another independent (and quite non-transparent at that) way to influence the sequence selecting process.
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Fri, 20 May 2011 09:18:19 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 3.2k
Hi Peter, What is it that's confusing you? Is it that I haven't expressed myself clearly or is it that you don't agree with what I've said? I'm not an expert on DBIC, I hadn't used it at all until we began the current project last summer. We only use it for simple DML statements, partly because I'm not sure I understand all the concepts. (We use DBI directly for the more complicated stuff.) I have worked with Oracle for twenty years, so I feel that I can comment on that with a bit more authority. What is this method supposed to do? What does it have to do to be consistent with the equivalent methods used for other databases (Postgres, MySQL, etc)? If we can agree on that then I'll have a go at writing a patch. I'm quite keen to help with some of this stuff. However, I can't do it now. I spent all yesterday morning investigating this and writing those three emails. I'm going to get shot by the project manager if I don't go back to his project. All the best. Duncan Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 20 May 2011 01:58 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Wed May 18 07:34:49 2011, Duncan.Garland@motortrak.com wrote:
> Hi Peter, > > It would have worked correctly using all_triggers if the > schema had defaulted correctly. > > Something like: > > 1 SELECT > trigger_name, trigger_body, table_owner FROM all_triggers > 2 > WHERE table_name = 'MBFL2_DEALER_PACKAGE_MODELS' > 3 AND > triggering_event LIKE '%INSERT%' > 4 AND status = 'ENABLED' > 5* > AND table_owner = NVL( :schema, USER ) > > I think it's reasonable > for it to default to the current user. However, it's quite common > for the tables to be owned by another user. In such cases it's > important > that the schema is set correctly. I assume that's done > elsewhere in DBIC. > > It's possible that there could genuinely be > more than one insert trigger and that a trigger could contain more > than one sequence. I don't think the heuristics should stop when > they find a sequence. I think they should carry on, read all the > triggers, and error if there is not exactly one matching sequence. > Similarly, I think your regular expression should be made to find > all the sequences in the trigger and error if there is more than > one.
I think I am getting confused by this. Is it possible for you to provide us a patch that adjusts the heuristics to work in a way that suits your situation?
> > In the future, it might be useful if the configuration file > could contain the regular expression which would be used to > identify the sequence which is used for the primary key. On this > project the sequence name is simply "s_$table_name". I suppose on > another project the naming convention might be > "s_pkey_$table_name".
I think that's going into "configuration hell" territory. We already provide a way to specify an explicit sequence (bypassing all heuristics) for esoteric schema designs. I find it very hard to justify another independent (and quite non-transparent at that) way to influence the sequence selecting process.
On Fri May 20 04:18:30 2011, Duncan.Garland@motortrak.com wrote: Show quoted text
> Hi Peter, > > What is it that's confusing you? Is it that I haven't > expressed myself clearly or is it that you don't agree with what > I've said? > > I'm not an expert on DBIC, I hadn't used it at all > until we began the current project last summer. We only use it for > simple DML statements, partly because I'm not sure I understand all > the concepts. (We use DBI directly for the more complicated stuff.) > I have worked with Oracle for twenty years, so I feel that I can > comment on that with a bit more authority.
Sorry for the late reply, was away from OSS for a while. What is confusing me is your explanation, no disagreement of any sort :) Is it possible for you to pop on IRC (http://chat.mibbit.com/#dbix-class@irc.perl.org) so we can quickly figure this out? If you prefer communication by email - I'll reply with a more in-depth explanation of what I fail to understand (and hence can't reliably implement). Cheers!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Tue, 7 Jun 2011 11:44:13 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.5k
Hi Peter, An email would be better, I think. I managed to sit down for a couple of hours a Sunday or two back and do some work on a possible patch, but I haven't had any spare time since then. All the best. Duncan Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 07 June 2011 11:34 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Fri May 20 04:18:30 2011, Duncan.Garland@motortrak.com wrote:
> Hi Peter, > > What is it that's confusing you? Is it that I haven't > expressed myself clearly or is it that you don't agree with what > I've said? > > I'm not an expert on DBIC, I hadn't used it at all > until we began the current project last summer. We only use it for > simple DML statements, partly because I'm not sure I understand all > the concepts. (We use DBI directly for the more complicated stuff.) > I have worked with Oracle for twenty years, so I feel that I can > comment on that with a bit more authority.
Sorry for the late reply, was away from OSS for a while. What is confusing me is your explanation, no disagreement of any sort :) Is it possible for you to pop on IRC (http://chat.mibbit.com/#dbix-class@irc.perl.org) so we can quickly figure this out? If you prefer communication by email - I'll reply with a more in-depth explanation of what I fail to understand (and hence can't reliably implement). Cheers!
Download (untitled) / with headers
text/plain 985b
On Tue Jun 07 06:44:24 2011, Duncan.Garland@motortrak.com wrote: Show quoted text
> Hi Peter, > > An email would be better, I think. > > I managed to sit > down for a couple of hours a Sunday or two back and do some work on a > possible patch, but I haven't had any spare time since then.
Hi Duncan! I re-read your diagnostics and then your reply when asked which version you are running. It seems everyone in the thread got confused by: Module id = DBIx::Class CPAN_USERID ARCANEZ (Justin Hunter <justin.d.hunter@gmail.com>) CPAN_VERSION 0.08191 CPAN_FILE F/FR/FREW/DBIx-Class-0.08191.tar.gz MANPAGE DBIx::Class - Extensible and flexible object <-> relational mapper. INST_FILE /usr/lib/perl5/site_perl/5.8.8/DBIx/Class.pm INST_VERSION 0.08123 And we all assumed you are running 0.08191, whereas in fact you were running the very old 0.08123. I am 99.9% sure that the issue you described is fixed in the 0.0819x series. Please verify this when time permits so I can close this ticket. Thank you!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 10:18:01 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 2.5k
Hi Peter, OK, I've upgraded DBIx::Class but my initial test has failed. I'm still getting: [15/Jun/2011:10:04:45 +0100] WARN:www.mbfl2-training.com:[FCGI: /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/mbfl2_training_fastcgi.pl]: [debug] Check: 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 14 in 'SELECT MOTRAK.<*>s_mbfl2_dealer_package_models.currval FROM DUAL') [for Statement "SELECT MOTRAK.s_mbfl2_dea [15/Jun/2011:10:04:45 +0100] WARN:www.mbfl2-training.com:[FCGI: /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/mbfl2_training_fastcgi.pl]: ler_package_models.currval FROM DUAL"] at /usr/local/etc/httpd/fcgi/catalyst/mbfl2/script/../lib/mbfl2/Controller/DealerAdmin.pm line 675 The correct user is mbfl2_training. This error occurs when I allow mbfl2_training to see the table motrak.mbfl2_dealer_package_models. Show quoted text
SQL> GRANT SELECT ON mbfl2_dealer_package_models TO mbfl2_training;
Grant succeeded. As soon as I do that, motrak's trigger becomes visible and your code gets confused because it can see two triggers with sequences but it only has access to one of the sequences. I only upgraded DBIx::Class. Should I have specifically upgraded some of the sub-packages? Regards Duncan Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 07:54 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Tue Jun 07 06:44:24 2011, Duncan.Garland@motortrak.com wrote:
> Hi Peter, > > An email would be better, I think. > > I managed to sit > down for a couple of hours a Sunday or two back and do some work on a > possible patch, but I haven't had any spare time since then.
Hi Duncan! I re-read your diagnostics and then your reply when asked which version you are running. It seems everyone in the thread got confused by: Module id = DBIx::Class CPAN_USERID ARCANEZ (Justin Hunter <justin.d.hunter@gmail.com>) CPAN_VERSION 0.08191 CPAN_FILE F/FR/FREW/DBIx-Class-0.08191.tar.gz MANPAGE DBIx::Class - Extensible and flexible object <-> relational mapper. INST_FILE /usr/lib/perl5/site_perl/5.8.8/DBIx/Class.pm INST_VERSION 0.08123 And we all assumed you are running 0.08191, whereas in fact you were running the very old 0.08123. I am 99.9% sure that the issue you described is fixed in the 0.0819x series. Please verify this when time permits so I can close this ticket. Thank you!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 11:09:57 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.3k
Hang fire on that. The problem may be at this end. I'm digging a bit more. Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 07:54 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Tue Jun 07 06:44:24 2011, Duncan.Garland@motortrak.com wrote:
> Hi Peter, > > An email would be better, I think. > > I managed to sit > down for a couple of hours a Sunday or two back and do some work on a > possible patch, but I haven't had any spare time since then.
Hi Duncan! I re-read your diagnostics and then your reply when asked which version you are running. It seems everyone in the thread got confused by: Module id = DBIx::Class CPAN_USERID ARCANEZ (Justin Hunter <justin.d.hunter@gmail.com>) CPAN_VERSION 0.08191 CPAN_FILE F/FR/FREW/DBIx-Class-0.08191.tar.gz MANPAGE DBIx::Class - Extensible and flexible object <-> relational mapper. INST_FILE /usr/lib/perl5/site_perl/5.8.8/DBIx/Class.pm INST_VERSION 0.08123 And we all assumed you are running 0.08191, whereas in fact you were running the very old 0.08123. I am 99.9% sure that the issue you described is fixed in the 0.0819x series. Please verify this when time permits so I can close this ticket. Thank you!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 11:51:38 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.7k
Hi Peter, I can't make it fail so perhaps it is fixed. The operation of the whole DBIx::Class setup seems to have changed. _dbh_get_autoinc_seq used to run on every insert and I was able to add some debug statements. I've tried to do the same thing this time, but I can't detect it running and I can't see my debug messages. In fact, I can create rows even when I put a "die" statement in _dbh_get_autoinc_seq. Anyway, I'm not getting the error any more. You can close the ticket. Regards Duncan Show quoted text
-----Original Message----- From: Peter Rabbitson via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 07:54 To: Duncan Garland Subject: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > On Tue Jun 07 06:44:24 2011, Duncan.Garland@motortrak.com wrote:
> Hi Peter, > > An email would be better, I think. > > I managed to sit > down for a couple of hours a Sunday or two back and do some work on a > possible patch, but I haven't had any spare time since then.
Hi Duncan! I re-read your diagnostics and then your reply when asked which version you are running. It seems everyone in the thread got confused by: Module id = DBIx::Class CPAN_USERID ARCANEZ (Justin Hunter <justin.d.hunter@gmail.com>) CPAN_VERSION 0.08191 CPAN_FILE F/FR/FREW/DBIx-Class-0.08191.tar.gz MANPAGE DBIx::Class - Extensible and flexible object <-> relational mapper. INST_FILE /usr/lib/perl5/site_perl/5.8.8/DBIx/Class.pm INST_VERSION 0.08123 And we all assumed you are running 0.08191, whereas in fact you were running the very old 0.08123. I am 99.9% sure that the issue you described is fixed in the 0.0819x series. Please verify this when time permits so I can close this ticket. Thank you!
Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 14:08:01 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <rabbit [...] rabbit.us>
Duncan Garland via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > I can't make it fail so perhaps it is fixed. > > The operation of the whole DBIx::Class setup seems to have changed. _dbh_get_autoinc_seq used to run on every insert and I was able to add > some debug statements. I've tried to do the same thing this time, but I can't detect it running and I can't see my debug messages. In fact, I can create rows even when I put a "die" statement in _dbh_get_autoinc_seq. > > Anyway, I'm not getting the error any more. You can close the ticket.
It runs only once and only if you have not already supplied a sequence in your column_info (add_columns). The code in question lives here: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm;h=12a14d3f429b8f0bce41708ead3148b7523726e7;hb=HEAD#l125 Can you verify that the sequence is properly detected the first time please?
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 13:27:00 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.8k
Hi Peter, When does it run? I can create rows when I mark the code up as follows and restart the server: sub _dbh_get_autoinc_seq { my ($self, $dbh, $source, $col) = @_; die "BANG!"; my $sql_maker = $self->sql_maker; my ($ql, $qr) = map { $_ ? (quotemeta $_) : '' } $sql_maker->_quote_chars; The module itself is loaded when the row is created because I can see statements and/or force it to die by putting statements at the top of the module. I can't do any more on this today. I'll do some more tracing tomorrow or Friday. Regards Duncan Show quoted text
-----Original Message----- From: rabbit@rabbit.us via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 13:08 To: Duncan Garland Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > Duncan Garland via RT wrote:
> Queue: DBIx-Class > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > I can't make it fail so perhaps it is fixed. > > The operation of the whole DBIx::Class setup seems to have changed. _dbh_get_autoinc_seq used to run on every insert and I was able to add > some debug statements. I've tried to do the same thing this time, but I can't detect it running and I can't see my debug messages. In fact, I can create rows even when I put a "die" statement in _dbh_get_autoinc_seq. > > Anyway, I'm not getting the error any more. You can close the ticket.
It runs only once and only if you have not already supplied a sequence in your column_info (add_columns). The code in question lives here: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm;h=12a14d3f429b8f0bce41708ead3148b7523726e7;hb=HEAD#l125 Can you verify that the sequence is properly detected the first time please?
Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 14:35:49 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <rabbit [...] rabbit.us>
Download (untitled) / with headers
text/plain 372b
Duncan Garland via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > When does it run?
Note the ||= in the code I linked in my last email. If your column_info specifies a 'sequence' value it will never run at all. Please check your add_columns statement in the corresponding ResultClass. Thanks!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 16:26:11 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.2k
Hi Peter, The method you are referring to doesn't run either because putting a die statement in it has no effect: sub _dbh_last_insert_id { my ($self, $dbh, $source, @columns) = @_; my @ids = (); die "BANG!"; foreach my $col (@columns) { my $seq = ($source->column_info($col)->{sequence} ||= $self->get_autoinc_seq($source,$col)); my $id = $self->_sequence_fetch( 'CURRVAL', $seq ); push @ids, $id; } return @ids; } I don't specify the sequence name in the result set definition file. Do these methods get over-ridden somewhere? Regards Duncan Show quoted text
-----Original Message----- From: rabbit@rabbit.us via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 13:36 To: Duncan Garland Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > Duncan Garland via RT wrote:
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > When does it run?
Note the ||= in the code I linked in my last email. If your column_info specifies a 'sequence' value it will never run at all. Please check your add_columns statement in the corresponding ResultClass. Thanks!
Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 18:19:10 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <rabbit [...] rabbit.us>
Download (untitled) / with headers
text/plain 768b
Duncan Garland via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter,
Hi Show quoted text
> > The method you are referring to doesn't run either because putting a die statement in it has no effect:
Ooooooohhhhhh. This is because newer DBIC versions support INSERT...RETURNING, so Oracle gives back everything right after the statement executed, making subsequent queries (and hence sequence detections) unnecessary. You can revert back to the original behavior by doing: $schema->storage->_use_insert_returning(0); (this is an unofficial API, subject to massive change). Can you please see if the sequence is detected correctly? Sorry for wasting your time on this, I should have thought of it sooner. Cheers!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 17:22:26 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 3.4k
Hi Peter, I can trace the create statement as far as: sub _prefetch_autovalues { my ($self, $source, $to_insert) = @_; my $colinfo = $source->columns_info; my %values; print STDERR "_prefetch_autovalues\n"; use Data::Dumper; for my $col (keys %$colinfo) { print STDERR Dumper( $col ); print STDERR "auto_nextval is true\n" if $colinfo->{$col}{auto_nextval}; print STDERR "to_insert->{col} exists.\n" if exists $to_insert->{$col}; print STDERR "to_insert->{col} is a scalar\n" if ( ref $to_insert->{$col} eq 'SCALAR' ); print STDERR "to_insert->{col} ????" if (ref $to_insert->{$col} eq 'REF' and ref ${$to_insert->{$col}} eq 'ARRAY'); if ( $colinfo->{$col}{auto_nextval} and ( ! exists $to_insert->{$col} or ref $to_insert->{$col} eq 'SCALAR' or (ref $to_insert->{$col} eq 'REF' and ref ${$to_insert->{$col}} eq 'ARRAY') ) ) { print STDERR "Get the sequence.\n"; $values{$col} = $self->_sequence_fetch( 'NEXTVAL', ( $colinfo->{$col}{sequence} ||= $self->_dbh_get_autoinc_seq($self->_get_dbh, $source, $col) ), ); } } \%values; } This produces the output: _prefetch_autovalues $VAR1 = 'date_to'; $VAR1 = 'dealer_group_id'; to_insert->{col} exists. $VAR1 = 'package_id'; to_insert->{col} exists. $VAR1 = 'dealer_id'; to_insert->{col} exists. $VAR1 = 'date_from'; $VAR1 = 'left_by'; $VAR1 = 'description_id'; to_insert->{col} exists. $VAR1 = 'joined_by'; to_insert->{col} exists. $VAR1 = 'price'; to_insert->{col} exists. $VAR1 = 'id'; This means that _dbh_get_autoinc_seq is not called because the conditions aren't met. The row is created correctly and my $created = $c->model( 'DB::Mbfl2DealerPackageModel' )->create( { package_id => $current->package_id, dealer_id => $dealer_id, dealer_group_id => $dealer_group_id, # baumuster1_3 => $current->baumuster1_3, # baumuster4_6 => $current->baumuster4_6, description_id => $current->description_id, price => $new_price, joined_by => $c->user->get( 'id' ) } ); $c->log->debug( "id=" . $created->id ); Prints the correct id. ........ Just got your email about INSERT - RETURNING. I'll do what you've asked. Duncan Show quoted text
-----Original Message----- From: rabbit@rabbit.us via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 13:36 To: Duncan Garland Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > Duncan Garland via RT wrote:
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > When does it run?
Note the ||= in the code I linked in my last email. If your column_info specifies a 'sequence' value it will never run at all. Please check your add_columns statement in the corresponding ResultClass. Thanks!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 17:45:11 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 1.1k
Hi Peter, Yes, it works now. Thanks. Duncan Show quoted text
-----Original Message----- From: rabbit@rabbit.us via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 17:19 To: Duncan Garland Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > Duncan Garland via RT wrote:
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter,
Hi
> > The method you are referring to doesn't run either because putting a die statement in it has no effect:
Ooooooohhhhhh. This is because newer DBIC versions support INSERT...RETURNING, so Oracle gives back everything right after the statement executed, making subsequent queries (and hence sequence detections) unnecessary. You can revert back to the original behavior by doing: $schema->storage->_use_insert_returning(0); (this is an unofficial API, subject to massive change). Can you please see if the sequence is detected correctly? Sorry for wasting your time on this, I should have thought of it sooner. Cheers!
Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 18:49:40 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <rabbit [...] rabbit.us>
Download (untitled) / with headers
text/plain 322b
Duncan Garland via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > Yes, it works now. >
By "it works" I assume you mean "DBIC 0.0819x correctly guesses the sequence name"? Thank you so much for taking time to get to the bottom of this. Cheers!
Subject: RE: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm
Date: Wed, 15 Jun 2011 17:50:58 +0100
To: "bug-DBIx-Class [...] rt.cpan.org" <bug-DBIx-Class [...] rt.cpan.org>
From: Duncan Garland <Duncan.Garland [...] motortrak.com>
Download (untitled) / with headers
text/plain 624b
Yes. Show quoted text
-----Original Message----- From: rabbit@rabbit.us via RT [mailto:bug-DBIx-Class@rt.cpan.org] Sent: 15 June 2011 17:50 To: Duncan Garland Subject: Re: [rt.cpan.org #67969] FW: Class::Storage::Oracle::Generic.pm <URL: https://rt.cpan.org/Ticket/Display.html?id=67969 > Duncan Garland via RT wrote:
> Queue: DBIx-Class > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=67969 > > > Hi Peter, > > Yes, it works now. >
By "it works" I assume you mean "DBIC 0.0819x correctly guesses the sequence name"? Thank you so much for taking time to get to the bottom of this. Cheers!
Confirmed fixed in 0.08191


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.