675603 - [Oracle] Remove obsolete code from Bugzilla/DB/Oracle.pm

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement)

Description

[Rene Bauer]

User Agent: Mozilla/5.0 (Windows NT 5.1; rv:5.0) Gecko/20100101 Firefox/5.0
Build ID: 20110615151330

Steps to reproduce:

Search -> Custom Search -> Comment, contains the string, test -> Click Search


Actual results:

Got the following error message.

DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1 (DBD ERROR: error possibly near <*> indicator at char 914 in 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, bugs.op_sys  op_sys, map_assigned_to.login_name  assigned_to, bugs.short_desc  short_desc, bugs.delta_ts  changeddate FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) LEFT JOIN longdescs  longdescs_0 ON (longdescs_0.bug_id = bugs.bug_id AND longdescs_0.isprivate < 1) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (1,12,10,13,11,9,4,8,5,6,7,3,2,16,14,17,15)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6 WHERE (( bugs.bug_status IN ('NEW','ASSIGNED','REOPENED') )) AND ((INSTR(LOWER
                      (<*>DBMS_LOB.SUBSTR(longdescs_0.thetext, 4000, 1)), LOWER('test')) > 0)) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 6) OR (bugs.qa_contact = 6) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc, bugs.delta_ts, priority.sortkey, priority.value ORDER BY changeddate DESC,priority.sortkey,priority.value,bug_id') [for Statement "SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, bugs.op_sys  op_sys, map_assigned_to.login_name  assigned_to, bugs.short_desc  short_desc, bugs.delta_ts  changeddate FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) LEFT JOIN longdescs  longdescs_0 ON (longdescs_0.bug_id = bugs.bug_id AND longdescs_0.isprivate < 1) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (1,12,10,13,11,9,4,8,5,6,7,3,2,16,14,17,15)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6 WHERE (( bugs.bug_status IN ('NEW','ASSIGNED','REOPENED') )) AND ((INSTR(LOWER
                      (DBMS_LOB.SUBSTR(longdescs_0.thetext, 4000, 1)), LOWER('test')) > 0)) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 6) OR (bugs.qa_contact = 6) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc, bugs.delta_ts, priority.sortkey, priority.value ORDER BY changeddate DESC,priority.sortkey,priority.value,bug_id"] at /var/www/html/bugzilla/buglist.cgi line 908



Expected results:

get search reply

Comment 1

[Rene Bauer]

the problem arises in line 354 of Oracle.pm:

        $nonstring =~ s/\(\s*(longdescs_\d+\.thetext|attachdata_\d+\.thedata)/
                      \(DBMS_LOB.SUBSTR\($1, 4000, 1\)/ig;

BZ is trying to cut down a CLOB to 4000 byte in order to compare it with a VARCHAR2. VARCHAR2 is limit to 4000 byte in Oracle. If using a multibyte charset like UTF8 DBMS_LOB.SUBSTR will throw the reported error.

I changed the line to:
        $nonstring =~ s/\(\s*(longdescs_\d+\.thetext|attachdata_\d+\.thedata)/
                      \(DBMS_LOB.SUBSTR\($1, 3900, 1\)/ig;

which works for now, but is not really a clean solution. Can you please further investigate and implement a better solution in future releases? For instance I believe there is a way to change the limit on the buffer size to a larger value.

I am using Oracle 11.2.0.

Comment 2

[Frédéric Buclin]

We would love to help, unfortunately the developer from Oracle who implemented Oracle support in Bugzilla is no longer around. :(

Comment 3

[Rene Bauer]

Uh. Ok. Well I can live with the fix I implemented for now. But since there is no developer for Oracle Support in BZ anymore this looks for me as if I should migrate to MySQL.

Can you point me to a tool for doing that or can you provide me any other advise?

Comment 4

[Frédéric Buclin]

For now, I emailed developers asking for help in triaging and fixing bugs related to Oracle. Let's see what happens. If you want to migrate to MySQL, you could use the migrate.pl script in the bugzilla/ directory.

Comment 5

[Max Kanat-Alexander]

(In reply to comment #3)
> Can you point me to a tool for doing that or can you provide me any other
> advise?

  contrib/bzdbcopy.pl

Comment 6

[Rene Bauer]

Thank you. I will try that ASAP.

Comment 7

[Rene Bauer]

The bzdbcopy.pl script worked and I was able to migrate from my Oracle DB to MySQL :). But there were 2 things I stumbled upon:

I had to change the bzdbcopy.pl script a bit, because in my BZ version 4.0.1 it calls the _connect methode with seperate parameters for user, pw, driver, etc., but _connect expects a hash:

Original code:
#my $source_db = Bugzilla::DB::_connect(SOURCE_DB_TYPE, SOURCE_DB_HOST,
#    SOURCE_DB_NAME, undef, undef, SOURCE_DB_USER, SOURCE_DB_PASSWORD);

Modified code:
my %connect_params = ();
$connect_params{db_name} = 'MYORACEDB';
$connect_params{db_driver} = 'oracle';
$connect_params{db_user} = 'bugs';
$connect_params{db_pass} = 'xxxxx';
$connect_params{db_host} = 'localhost';
$connect_params{db_port} = 1521;

my $source_db = Bugzilla::DB::_connect(\%connect_params);

Same changes have to be made for the target db.

Also it seems to be necessary that localconfig is configured for the target db, otherwise Bugzilla/Schema/Mysql.pm will fail in line 230 (in my case because localconfig was configured to use the Oracle DB and luckily Oracle.pm does not implement bz_index_info_real() function) or even worse will drop indexes in the configured DB if possible.

Comment 8

[Max Kanat-Alexander]

(In reply to comment #7)
> I had to change the bzdbcopy.pl script a bit, because in my BZ version 4.0.1
> it calls the _connect methode with seperate parameters for user, pw, driver,
> etc., but _connect expects a hash:

  Oh yes, that's a bug, could you file it separately?

> Also it seems to be necessary that localconfig is configured for the target
> db, otherwise Bugzilla/Schema/Mysql.pm will fail in line 230 (in my case
> because localconfig was configured to use the Oracle DB and luckily
> Oracle.pm does not implement bz_index_info_real() function) or even worse
> will drop indexes in the configured DB if possible.

  There may also be a bug there that we should look into.

Comment 9

[Frédéric Buclin]

I cannot reproduce this crash in Bugzilla 4.1.3, using Oracle 10.2. The SQL query looks very different. The relevant part is now:

AND bugs.bug_id IN (SELECT bug_id FROM longdescs WHERE INSTR(LOWER(thetext), LOWER('test')) > 0 AND isprivate = 0)

mkanat, is the old code

$nonstring =~ s/\(\s*(longdescs_\d+\.thetext|attachdata_\d+\.thedata)/
                      \(DBMS_LOB.SUBSTR\($1, 4000, 1\)/ig;

still triggered in 4.2?

Comment 10

[Max Kanat-Alexander]

(In reply to Frédéric Buclin from comment #9)
> mkanat, is the old code
> 
> $nonstring =~ s/\(\s*(longdescs_\d+\.thetext|attachdata_\d+\.thedata)/
>                       \(DBMS_LOB.SUBSTR\($1, 4000, 1\)/ig;
> 
> still triggered in 4.2?

  Oh, interesting. Almost certainly not, because that was old boolean-charts code.

Comment 11

[Frédéric Buclin]

Attachment: patch, v1

Let's remove unused code. I indeed cannot find any reference to this code anymore, and we don't use CURRENT_DATE() anywhere. The only place where CURRENT_DATE is used is in whine.pl, but it doesn't use the ...() form.

Comment 12

[Frédéric Buclin]

I'm morphing the bug summary to remove the obsolete code.

Comment 13

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Oracle.pm
Committed revision 7929.