955865 - [Oracle] The profile_search.bug_list column is limited to 4000 characters in Oracle, which makes it crash if the buglist is too large

Status: NEW

(Bugzilla :: Query/Bug List, defect)

Description

[Matthias Ludwig]

Attachment: Exception.txt

User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; MDDRJS; rv:11.0) like Gecko

Steps to reproduce:

Bugzilla installed with Oracle database
Searched for Bugs (many results)


Actual results:

Error-Screen: ORA-01461: can bind a LONG value only for insert into a LONG column (see attached file)




Expected results:

Bugzilla should list the bugs

Comment 1

[Matthias Ludwig]

The reason for the error: 
Bugzilla tries to write the whole list of bug-ids in the field into the column bug_list of the table profile_search.

The datatype for profile_search.bug_list  ist varchar2(4000 Bytes). 4000 Bytes is the max length for Oracle varchar-fields.

I changed the column-type to CLOB and everything worked fine:

select * from profile_search;


alter table profile_search add(bug_listc clob,list_orderc clob);

update profile_search set bug_listc = bug_list, list_orderc=list_order;

alter table profile_search drop column bug_list;
alter table profile_search drop column list_order;

alter table profile_search rename column bug_listc to bug_list;

alter table profile_search rename column list_orderc to list_order;

alter table profile_search modify (bug_list not null);

desc profile_search;
select * from profile_search;


You might use this in your checksetup.pl script.

Comment 2

[Frédéric Buclin]

The profile_search.bug_list column is limited to 4000 characters in Oracle:

Bugzilla::DB::Schema:         bug_list => {TYPE => 'MEDIUMTEXT', NOTNULL => 1}
Bugzilla::DB::Schema::Oracle: MEDIUMTEXT => 'varchar(4000)'

For other DB servers, this limit is much higher (e.g. 16Mb for MySQL).

Comment 3

[Craig]

There is possibly nothing new in the error text, below.

My version is 4.0.2.

Software error:
DBD::Oracle::db do failed: ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: error possibly near <*> indicator at char 7 in 'UPDATE <*>profile_search SET bug_list = :p1, list_order = :p2 WHERE id = :p3') [for Statement "UPDATE profile_search SET bug_list = ?, list_order = ? WHERE id = ?"] at Bugzilla/DB/Oracle.pm line 403
	Bugzilla::DB::Oracle::do(undef, 'UPDATE profile_search SET bug_list = ?, list_order = ? WHERE ...', undef, '1661,2064,2078,2122,2079,1593,1594,1595,1596,1619,1646,1647,1...', 'bug_status,priority,assigned_to,bug_id', 30384) called at Bugzilla/Object.pm line 387
	Bugzilla::Object::update('Bugzilla::Search::Recent=HASH(0x3548330)') called at Bugzilla/User.pm line 469
	Bugzilla::User::__ANON__() called at Bugzilla/Util.pm line 660
	Bugzilla::Util::on_main_db('CODE(0x3548060)') called at Bugzilla/User.pm line 489
	Bugzilla::User::save_last_search('Bugzilla::User=HASH(0x2e06030)', 'HASH(0x16933c0)') called at /web_sites/extranet5/bugs-bogues/buglist.cgi line 1201