View Issue Details

IDProjectCategoryView StatusLast Update
0007179mantisbtdb oraclepublic2014-12-08 00:34
ReportermechantflouAssigned Todregad 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version1.0.3 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0007179: 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";
}

TagsNo tags attached.

Relationships

related to 0013227 closeddregad Oracle DB support multiple issues 

Activities

mechantflou

mechantflou

2006-06-07 05:14

reporter   ~0012943

Last edited: 2006-06-08 09:06

the category is wrong, please change for oracle

ryandesign

ryandesign

2006-06-19 11:21

reporter   ~0012989

Why is this change necessary? What happens?

mechantflou

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

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

cjwfuller

2011-09-11 06:38

reporter   ~0029679

Last edited: 2011-09-11 06:48

View 3 revisions

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

cjwfuller

2011-09-11 09:18

reporter   ~0029682

Last edited: 2011-09-11 09:23

View 2 revisions

$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

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

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.

Issue History

Date Modified Username Field Change
2006-06-06 11:41 mechantflou New Issue
2006-06-07 05:14 mechantflou Note Added: 0012943
2006-06-08 09:06 mechantflou Note Edited: 0012943
2006-06-19 11:21 ryandesign Note Added: 0012989
2006-06-19 11:21 ryandesign Category csv => db oracle
2006-06-19 11:30 mechantflou Note Added: 0012991
2011-09-09 12:36 dregad Note Added: 0029671
2011-09-09 12:41 dregad Relationship added related to 0013227
2011-09-11 06:38 cjwfuller Note Added: 0029679
2011-09-11 06:41 cjwfuller Note Edited: 0029679 View Revisions
2011-09-11 06:48 cjwfuller Note Edited: 0029679 View Revisions
2011-09-11 09:15 cjwfuller Sponsorship Added cjwfuller: US$ 100
2011-09-11 09:15 cjwfuller Sponsorship Total 0 => 100
2011-09-11 09:18 cjwfuller Note Added: 0029682
2011-09-11 09:23 cjwfuller Note Edited: 0029682 View Revisions
2011-09-12 09:37 dregad Note Added: 0029690
2011-09-12 09:51 cjwfuller Note Added: 0029691
2013-10-17 12:52 dregad Target Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Status new => resolved
2013-10-17 12:52 dregad Fixed in Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Resolution open => fixed
2013-10-17 12:52 dregad Assigned To => dregad
2014-04-12 19:39 grangeway Issue cloned: 0017198
2014-12-08 00:34 vboctor Status resolved => closed