View Issue Details

IDProjectCategoryView StatusLast Update
0024393mantisbtdb mssqlpublic2022-05-10 18:44
Reporterrafique.kakati Assigned Todregad  
PriorityhighSeveritymajorReproducibilityhave not tried
Status closedResolutionfixed 
Product Version2.11.1 
Target Version2.25.4Fixed in Version2.25.4 
Summary0024393: APPLICATION ERROR 401 Database query failed. Error received from database was #-52: SQLState: IMSSP
Description

When I am trying to Print Reports and Issue Trends of a project whose tickets are greater than 2100, i am getting below error. this is not in the case of csv or excel exports. Please can you help me regarding the same. Attaching the image for the same

APPLICATION ERROR 401

Database query failed. Error received from database was #-52: SQLState: IMSSP
Error Code: -52
Message: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.
for the query: SELECT n.id, n.bug_id, n.reporter_id, n.view_state, n.last_modified, n.date_submitted, b.project_id FROM mantis_bugnote_table n JOIN mantis_bug_table b ON (n.bug_id = b.id) WHERE n.bug_id IN (?, ?, ?,

TagsNo tags attached.
Attached Files
printissue.png (159,774 bytes)   
printissue.png (159,774 bytes)   

Relationships

related to 0028902 closedatrol APPLICATION ERROR 0000401 / Error MSSQL 4145 when view all bugs for 1000 projects or more 
related to 0030193 closeddregad PHP 5.6 support broken 

Activities

rafique.kakati

rafique.kakati

2018-05-04 11:14

reporter   ~0059738

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';

}

rafique.kakati

rafique.kakati

2018-05-16 02:20

reporter   ~0059826

Hi,

Is there any solution for the same?

rochaf

rochaf

2021-05-17 05:55

reporter   ~0065529

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.

ggswhitela

ggswhitela

2022-05-03 10:56

reporter   ~0066520

Last edited: 2022-05-03 16:10

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)   
obmsch

obmsch

2022-05-03 16:04

reporter   ~0066522

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.

dregad

dregad

2022-05-04 05:33

developer   ~0066524

Last edited: 2022-05-04 10:55

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].

dregad

dregad

2022-05-04 13:26

developer   ~0066526

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).

obmsch

obmsch

2022-05-04 14:31

reporter   ~0066527

@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.

obmsch

obmsch

2022-05-05 14:40

reporter   ~0066530

@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.

ggswhitela

ggswhitela

2022-05-05 15:43

reporter   ~0066531

Last edited: 2022-05-05 15:48

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.

obmsch

obmsch

2022-05-05 18:28

reporter   ~0066533

@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.

dregad

dregad

2022-05-06 04:24

developer   ~0066537

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

Related Changesets

MantisBT: master 810d3384

2022-05-05 10:18

dregad


Details Diff
Process bugnote stats in chunks for MSSQL

This is a workaround for SQL Server's limitation of 2100 parameters per
query [1], using DbQuery.

Fixes 0024393

[1] https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server
Affected Issues
0024393
mod - core/bug_api.php Diff File

MantisBT: master-2.25 4f4e176d

2022-05-10 08:31

dregad


Details Diff
Process bugnote stats in chunks for MSSQL

This is a workaround for SQL Server's limitation of 2100 parameters per
query [1], using DbQuery.

Fixes 0024393

Cherry picked from merge commit b4214457c30cd8c5c3fe5d053aee0ba88dded0c6
Affected Issues
0024393
mod - core/bug_api.php Diff File