Problem (and workaround) with Handle.pm in DBIx::SearchQuery used by RT

RT Developers
1

Hi,

First of all, if this is wrong protocol for communication issues, my apology. I’m not very familiar with open source development bug-reporting protocol.

Hi Pei,

Thanks very much for the error report.   Generally,

DBIx-SearchBuilder gets discussed on the rt-devel mailinglist, but I’ll
take good bug reports with extensive triage like yours in whatever
format and forum I can get them :wink:

I am trying to get RT running against an Oracle 9.2.0.4 db. Here is a summary of what my environment looks like:

“App” server is Redhat 8, running the following:
httpd-2.0.52/ mod_fastcgi-2.4.2/ perl-5.8.5/ rt-3.2.2/
oracle client is 9.2.0.4 for RH/linux

And I’m betting DBD::Oracle 1.16. The error you report has popped up
since the upgrade to 1.16. Backing down to 1.15 makes it go away again.
That doesn’t mean it’s not our bug, just that it wasn’t tickled before
this release.

ORA-12704 seems to surface when CLOB/NCLOB columns are used (based on information from Oracle Metalink support). The error description seems to indicate this is something that can be fixed with setting NLS_LANG in the client environment; I tried to tweak it to no avail. In any case, RT seems to hardcode the value to “.UTF8” in the code anyway.

Do you have a sense of what it should be? Everything we’ve read
implies that .UTF8 should be acceptable.

Being an Oracle DBA and an occassional perl hacker, I took the challenge to see if I can see what’s going on. After staring the code in Handle.pm ( DBIx::SearchBuilder v1.15) for a while, I uncommented one line and then I was able the get rid of the problem. The line I uncommented out is line #464

By uncommented out, you really mean “commented out”, right?

460         if ( ref( $bind_values[$bind_idx] ) eq "HASH" ) {
461             my $bhash = $bind_values[$bind_idx];
462             $bind_values[$bind_idx] = $bhash->{'value'};
463             delete $bhash->{'value'};
464             #     $sth->bind_param( $bind_idx + 1, undef, $bhash );
465         }

...

475         eval { $executed = $sth->execute(@bind_values) };

I am not a Perl guru, but it looks me line #464 is unnecessary: when $sth->execute is called at line 475, @bind_values is passed as the input. My understanding is that one should either use $sth->bind_param to set up all the bind values before calling execute(), or just pass @bind_values to $sth->execute(), but not both. It may be ok to do both (what would take precendence, then? values set up by bind_param(), or values passed to execute()?), but in some weird cases (such as the presence of CLOB columns in a table), things break.

line #464 seems to be binding ‘undef’ to @bind_values only for those elements that were a HASH/ref. So line #464 is not really performing binding for all the bind variables; it’s only setting those that were a HASH to undef. I guess I don’t understand the rationale behind that.

I’m going to have to dig a bit deeper. I can’t remember whether that
code was added to deal with oracle param binding or something else.

Does a “make regression” pass with your change?

2

Hi,

I was wondering if this problem has been resolved? I encountered the same
issue myself and ran into a correspondence between Jesse and the humble,
helpful folks at DBD::Oracle which didn’t seem to suggest a solution.

I’m unable to create new users through the web interface, I get an
ORA-12704 on the Users insert. One piece of information that may be useful
is that I AM able to create users using a command-line perl script that
calls the RT API.

Steve

At Tuesday 11/30/2004 07:19 PM, Jesse Vincent wrote:>On Tue, Nov 30, 2004 at 03:54:05PM -0800, Pei Ku wrote:

Hi,

First of all, if this is wrong protocol for communication issues, my
apology. I’m not very familiar with open source development
bug-reporting protocol.

Hi Pei,

    Thanks very much for the error report.   Generally,

DBIx-SearchBuilder gets discussed on the rt-devel mailinglist, but I’ll
take good bug reports with extensive triage like yours in whatever
format and forum I can get them :wink:

I am trying to get RT running against an Oracle 9.2.0.4 db. Here is a
summary of what my environment looks like:

“App” server is Redhat 8, running the following:

httpd-2.0.52/ mod_fastcgi-2.4.2/ perl-5.8.5/ rt-3.2.2/

oracle client is 9.2.0.4 for RH/linux

And I’m betting DBD::Oracle 1.16. The error you report has popped up
since the upgrade to 1.16. Backing down to 1.15 makes it go away again.
That doesn’t mean it’s not our bug, just that it wasn’t tickled before
this release.

ORA-12704 seems to surface when CLOB/NCLOB columns are used (based on
information from Oracle Metalink support). The error description seems
to indicate this is something that can be fixed with setting NLS_LANG in
the client environment; I tried to tweak it to no avail. In any case, RT
seems to hardcode the value to “.UTF8” in the code anyway.

Do you have a sense of what it should be? Everything we’ve read
implies that .UTF8 should be acceptable.

Being an Oracle DBA and an occassional perl hacker, I took the
challenge to see if I can see what’s going on. After staring the code in
Handle.pm ( DBIx::SearchBuilder v1.15) for a while, I uncommented one
line and then I was able the get rid of the problem. The line I
uncommented out is line #464

By uncommented out, you really mean “commented out”, right?

460         if ( ref( $bind_values[$bind_idx] ) eq "HASH" ) {
461             my $bhash = $bind_values[$bind_idx];
462             $bind_values[$bind_idx] = $bhash->{'value'};
463             delete $bhash->{'value'};
464             #     $sth->bind_param( $bind_idx + 1, undef, $bhash );
465         }

...

475         eval { $executed = $sth->execute(@bind_values) };

I am not a Perl guru, but it looks me line #464 is unnecessary: when
$sth->execute is called at line 475, @bind_values is passed as the
input. My understanding is that one should either use $sth->bind_param
to set up all the bind values before calling execute(), or just pass
@bind_values to $sth->execute(), but not both. It may be ok to do both
(what would take precendence, then? values set up by bind_param(), or
values passed to execute()?), but in some weird cases (such as the
presence of CLOB columns in a table), things break.

line #464 seems to be binding ‘undef’ to @bind_values only for those
elements that were a HASH/ref. So line #464 is not really performing
binding for all the bind variables; it’s only setting those that were a
HASH to undef. I guess I don’t understand the rationale behind that.

I’m going to have to dig a bit deeper. I can’t remember whether that
code was added to deal with oracle param binding or something else.

Does a “make regression” pass with your change?

At any rate, now I am able to create new users after commenting out
that line. I don’t know if I created new problems by making this change.

thanks!

Pei L. Ku
Manager, Application and Database Services
ATC
pku@autotradecenter.com
office: 650 532 6318

–

Rt-devel mailing list
Rt-devel@lists.bestpractical.com
The rt-devel Archives

Stephen Turner
Senior Programmer/Analyst - Client Support Services
Information Services and Technology (IS&T)

sturner@mit.edu

3

Hi,

I was wondering if this problem has been resolved? I encountered the same
issue myself and ran into a correspondence between Jesse and the humble,
helpful folks at DBD::Oracle which didn’t seem to suggest a solution.

I’m unable to create new users through the web interface, I get an
ORA-12704 on the Users insert. One piece of information that may be useful
is that I AM able to create users using a command-line perl script that
calls the RT API.

I don’t have a working oracle test instance to do the debugging on
myself, which sort of shut down my attempts to get confirmation of the
bug. Backing down to 1.15 will fix it for now, but I’d love it if you
could generate standalone tests to isolate the issue. I wasn’t able to
reproduce the issue on Oracle 10g. So, I think it’s a 9i interacting
with DBD::Oracle 1.15 interacting with SearchBuilder issue. It looks
like I’m going to need to build a redhat box to get a copy of Oracle 9i
that won’t fail to link :confused:

Jesse
4

At Tuesday 12/14/2004 01:31 PM, Jesse Vincent wrote:

but I’d love it if you
could generate standalone tests to isolate the issue

I’m not sure what a standalone test entails - how would I generate one and
what would you need to see from it? I’m definitely willing to try!

Thanks,
Steve

5

At Tuesday 12/14/2004 01:31 PM, Jesse Vincent wrote:

but I’d love it if you
could generate standalone tests to isolate the issue

I’m not sure what a standalone test entails - how would I generate one and
what would you need to see from it? I’m definitely willing to try!

Replicating the failure with a tiny DBI script that has the bare minimum
code. The current guess is that it’s something with our $sth parameter
binding.

6

At Tuesday 12/14/2004 04:53 PM, Jesse Vincent wrote:

Replicating the failure with a tiny DBI script that has the bare minimum
code. The current guess is that it’s something with our $sth parameter
binding.

I wasn’t able to replicate the failure with a DBI script (included below).
In fact, the insert worked as expected. I can’t come up with an idea for
why this would behave differently from the web interface.

One other test I did was to type in these URLs directly into the browser:

https://server/Admin/Users/Modify.html?id=new&Name=tester3&Comments=none

https://server/Admin/Users/Modify.html?id=new&Name=tester4

The first one failed, the second succeeded - probably to be expected.

Steve

#!/bin/perl -I $ENV{RT_HOME}/lib

BEGIN {
die “RT_HOME environment variable is not defined”
if ! defined $ENV{RT_HOME};
}

use lib $ENV{RT_HOME}.“/etc”;
use lib $ENV{RT_HOME}.“/lib”;

use RT::Interface::CLI qw (CleanEnv GetCurrentUser);
use RT::User;
use RT::Interface::Web;

Do RT API initialization:

CleanEnv();
RT::LoadConfig();
RT::Init();

$RT::Handle->BeginTransaction();

my ($val, $msg) = ();

my $UserObj = new RT::User(RT::SystemUser);
($val, $msg) = $UserObj->Create( Name => ‘pedro’,
EmailAddress => ‘pedro@mets.com’,
RealName => ‘Pedro Martinez’,
);

print “Attempt 1 (no comment): $msg\n”;

$UserObj = new RT::User(RT::SystemUser);
($val, $msg) = $UserObj->Create( Name => ‘pedro2’,
EmailAddress => ‘pedro@redsox.com’,
RealName => ‘Pedro Martinez’,
Comments => ‘Gone’
);

print “Attempt 2 (comment): $msg\n”;

$RT::Handle->Commit();
$RT::Handle->Disconnect();

exit;

7

At Tuesday 12/14/2004 04:53 PM, Jesse Vincent wrote:

Replicating the failure with a tiny DBI script that has the bare minimum
code. The current guess is that it’s something with our $sth parameter
binding.

I wasn’t able to replicate the failure with a DBI script (included below).
In fact, the insert worked as expected. I can’t come up with an idea for
why this would behave differently from the web interface.

It has something to do with bind_params and CLOBs and UTF8. Try toggling
the utf8ness of your string?