0007179: limit selection required for oracle (probleme viewed at the page my_view_page.php) - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0007179	mantisbt	db oracle	public	2006-06-06 11:41	2014-12-08 00:34

Reporter	mechantflou	Assigned To	dregad
Priority	normal	Severity	minor	Reproducibility	always
Status	closed	Resolution	fixed
Product Version	1.0.3
Target Version	1.3.0-beta.1	Fixed in Version	1.3.0-beta.1

Summary	0007179: limit selection required for oracle (probleme viewed at the page my_view_page.php)
Description	limit selection required for oracle! I have to specify limit at the maximum 1000 rows. replace at the line 739 file filter_api $t_db_type = config_get( 'db_type' ); if ($t_db_type==='oci8') { $query = "SELECT * FROM (SELECT DISTINCT $t_bug_table.id as id, ROWNUM AS n $t_from $t_id_join $t_id_where ) WHERE n between 1 and 1000"; } else { $query = "SELECT DISTINCT $t_bug_table.id AS id $t_from $t_id_join $t_id_where"; }
Tags	No tags attached.

mechantflou 2006-06-07 05:14 reporter ~0012943 Last edited: 2006-06-08 09:06	the category is wrong, please change for oracle

ryandesign 2006-06-19 11:21 reporter ~0012989	Why is this change necessary? What happens?

mechantflou 2006-06-19 11:30 reporter ~0012991	Oracle doesn't support more than 1000 id in the select with a sql query. ...\core\database_api.php 130 trigger_error ( '401', 256 )

dregad 2011-09-09 12:36 developer ~0029671	Would you have a chance to test if problem still exists using oracle branch in 0013227 (https://github.com/dregad/mantisbt/commits/oracle), and submit a revised patch/pull request against that if it's the case ?

cjwfuller 2011-09-11 06:38 reporter ~0029679 Last edited: 2011-09-11 06:48	I can confirm that this is an issue in MantisBT 1.2.6. Is there a fix I can use for 1.2.6? mechantflou's fix isn't for the version of MantisBT that I am using. Also..... The problem happens when I have >999 users in my mantis_user_table. Looking through the filter api file, it also looks like this limitation might cause the same error when the number of issues increases but even when there are fewer than 1000 issues... mantis_custom_field_string_table will quicky get bigger if one has many custom fields! It depends where these 'IN' statements are being used.. any table that has >999 records and is queried with an 'IN' clause will cause this reported error.

cjwfuller 2011-09-11 09:18 reporter ~0029682 Last edited: 2011-09-11 09:23	$100 USD: My company uses MantisBT and we are about to go over the 1000 user limit that the MantisBT/Oracle combo puts in place. If this issue is fixed AND it is ensured that any table in Mantis with Oracle can have over 1000 records then we will give $100 USD in sponsorship money :D. How's that? Only catch is that we need it done by September 14th.

dregad 2011-09-12 09:37 developer ~0029690	cjwfuller, Thanks for the offer, but... September 14th... LOL - I guess you can keep your $100 ;-) I am not sure mechantflou's proposed fix would work - it does indeed avoid the problem caused by the Oracle limit to 1000 values in IN clause, but I think the returned dataset would not be identical due to capping the number of id's. In any case, the filter api has changed dramatically since 1.0.3 so the original code can't be used as is. Can you help pinpoint the problem in the current version of Mantis ? Also, if you have a working Oracle setup, any testing of the oracle branch would be appreciated; report any issues in 0013227

cjwfuller 2011-09-12 09:51 reporter ~0029691	dregad, I think you're correct about the capping of the number of ids.. I didn't want to try it. I'm not using the current version of MantisBT (version 1.2.6) because we have changed the software in a big way so we can't upgrade to future versions. The problem still occurs at my_view_page.php. The filter api is probably where the problem lies. There are a large number of statements that use the 'IN' clause. Oracle prevents putting more than 1000 items using the 'IN' clause (error code ORA-01795). I think that any use of the 'IN' clause needs to be replaced with one of the solutions in http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause or something. I think it's quite a big task though. It's the mantis_user_table that we noticed the problem with but I'm pretty sure it is going to occur with the other tables too. All the best.