Hi,

While searching for the solution of above, found the below link where in they have mentioned that SQL Server Parameters per user-defined function limit is 2,100, below is the link.

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

Can you please help me to modify the query (bug_api.php) so that this issue can be resolved, where in all the data can be printed with this restriction.

function bug_get_bugnote_stats_array( array $p_bugs_id, $p_user_id = null ) {

$t_query = 'SELECT n.id, n.bug_id, n.reporter_id, n.view_state, n.last_modified, n.date_submitted, b.project_id'
. ' FROM {bugnote} n JOIN {bug} b ON (n.bug_id = b.id)'
. ' WHERE n.bug_id IN (' . implode( ', ', $t_in_clause_elems ) . ')'
. ' ORDER BY b.project_id, n.bug_id, n.last_modified';

}

Hi,

Is there any solution for the same?

Indeed I have only come across this (myself) when there are too many items returned by the Query.

Since, I don't see anyone complaining about it while using mysql, only when "MSSQL" is running, we are on "hold".
And without changing the code, this will remain unavowable.

Furthermore, since MantisBT doesn't "officially" support or recommend MSSQL we are going to sit and wait for a solution.

We use MS SQL also. This error rarely occurs for me but has happened 3x this year already, so it's becoming more frequent. The only remedy I've found is to create new projects and move all of the issues from the corrupted projects to the new projects. But creating a new project with the categories, versions, users, and custom fields of a previous project is a pain. See 0029746.

NOTE: We're running MantisBT 2.25.1, PHP 7.4.13.

image.png (75,211 bytes)   

image.png (75,211 bytes)   

I guess this could only be resolved by processing the ids_array by chunks. Something around the line:

function bug_get_bugnote_stats_array( array $p_bugs_id, $p_user_id = null ) {
    $t_id_array = array();
    foreach( $p_bugs_id as $t_id ) {
        $t_id_array[$t_id] = (int)$t_id;
    }
    if( empty( $t_id_array ) ) {
        return array();
    }

    if ( null === $p_user_id ) {
        $t_user_id = auth_get_current_user_id();
    }
    else {
        $t_user_id = $p_user_id;
    }
    if( db_is_mssql() ){
        # sqlserver has a restriction on parameters count of 2100! Process $p_bugs_id (array) in chunks.
        $t_id_array_chunked = array_chunk( $p_bugs_id, 2100, true )
    } else {
        $t_id_array_chunked = $t_id_array
    }
    $t_stats = array();
    foreach ($t_id_array as $t_id_array_chunk) {
        # Process the chunk to get it's stats
        ...
        # Merge stats
    }
    return $t_stats;
}

Anyone to fill the "..."?
AFAIK Oracle has a limit here too.

I assume this occurs in the context of a filter returning a large number of rows (Issues). It would be interesting to know more about it, i.e. the steps that led to the error, particularly the stack trace / page being loaded (OP's screenshot shows print_all_bug_page.php, @ggswhitela is this the same for you ?), info about the data set/filter, etc.

Note that the purpose of this query is to count the number of bugnotes visible to the user. As a workaround, you could try to remove bugnotes_count from the list of visible columns.

AFAIK Oracle has a limit here too.

@obmsch You are probably referring to Oracle's limit for expression lists, which can contain no more than 1000 expressions [1].

We are hitting a different limitation here, i.e. the number of parameters in a given query; MSSQL limits that to 2100, but Oracle's restriction is much higher : number of formal parameters in an explicit cursor, function, or procedure is 65536 [2].

Could you MSSQL guys have a look at the following PR, which implements the fix suggested by @obmsch in 0024393:0066522
https://github.com/mantisbt/mantisbt/pull/1805

Feedback welcome, as I'm not able to actually test this (I don't have access to a MSSQL setup).

@dregad thanks for taking this and offer a solution. I' am quite a bit short of that 2100
limit, but I will test your PR tomorrow with a smaller chunk size.

Hope the other mssql guys chime in here.

@dregad PR(d7da2c4) looks good. Tested on MantisBT 2.25.3/PHP 8.1/MSSQL 2016/ADOdb 5.22.1 with
chunk sizes 100/300. Iterations confirmed with Xdebug, Output as expected.

This error is random but the steps to reproduce are simple. We use subprojects - I know, it's a deprecated feature - as a view filter. For example, you can have a global developer project with regional subprojects. Then each set of regional users have access to their projects while the developers can uses the header project to see all regional issues.

Try this:

1. Create a set of subprojects with, say, 4000 issues (open and closed) with a header (sub)project.
2. Select the header from the project list and View Issues, Hide Status = closed. It will work 100s of times but one day throw the that Application Error 401.
3. Select the header from the project list and View Issues, Hide Status = none. It will work 100s of times but one day throw the that Application Error 401.
4. You could aggravate the system by changing the Show filter to >4000.

Once you get error 401, that header (sub)project is broken forever. My workaround is to create a new header, unlink the subprojects from the old header and re-link it to the new header. The new header then works fine. But if you View Issues on the old header, even with no linked subprojects, you still get error 401 on that header.

@ggswhitela bug_get_bugnote_stats_array is called on various places, and whenever that mssql 2100 limit is reached you are doomed.

• Just logging in on MantisBT: my_view_page.php (3 times)
• Change (sub)project: my_view_page.php (3 times)
• View (sub)project: my_view_page.php (1 time)
• Print (sub)project: print_all_bug_page.php (1 time)
  ...

I really don't understand how you could remedy this by simply creating new (sub)projects and
reparenting issues (that doesn't differ the issue count on the path).

Anyway, as you are on the MantisBT 2.25.x line, applying PR #1805 should get you out there.

Once you get error 401, that header (sub)project is broken forever

@ggswhitela From your description, I get the feeling that your "project corruption" problem is in fact not with the (sub)project itself, but with the filter that is being stored and recalled whenever you try to open the View Issues page again, hitting this bug with the 2100 limit on query parameter over and over.

So instead of recreating your project, you may want to try as another, simpler workaround, to try and clear the filter with https:/example.com/mantisbt/view_all_set.php?type=0