View Issue Details

IDProjectCategoryView StatusLast Update
0028902mantisbtdb mssqlpublic2023-10-31 16:32
Reporterbzi Assigned Toatrol  
PriorityurgentSeveritymajorReproducibilityalways
Status closedResolutionfixed 
PlatformSQL SERVER 2016OSWINDOWS SERVEROS Version2016
Product Version2.24.2 
Target Version2.26.0Fixed in Version2.26.0 
Summary0028902: APPLICATION ERROR 0000401 / Error MSSQL 4145 when view all bugs for 1000 projects or more
Description

When i want to see all bugs from all my projects, i got an error (cf. attached file).
This error occure when there is more than 999 projects. When i check the query send to my database server :

  • with less than 1000 projects : SELECT * FROM mantis_project_version_table WHERE project_id IN (@P1, @P2, @P3, ...
    ==> no error
  • with more than 999 projects : SELECT * FROM mantis_project_version_table WHERE (1, project_id) IN ((1,@P1), (1,@P2), (1,@P3), ...
    ==> error 4145, this query is not supported on TSQL

Why BT is not using the same query pattern ?
How can i fix this error please ?

TagsNo tags attached.
Attached Files
erreur.png (91,518 bytes)   
erreur.png (91,518 bytes)   

Relationships

related to 0024393 closeddregad APPLICATION ERROR 401 Database query failed. Error received from database was #-52: SQLState: IMSSP 

Activities

bzi

bzi

2021-07-09 07:17

reporter   ~0065682

Last edited: 2021-07-09 07:19

The problem is from an Oracle limitation in file "/core/classes/DbQuery.class.php" at "sql_in" function :

/**
 * Creates a string construction for an IN expression, providing:
 * - alias: is the name of the column as a valid identifier in the final sql query
 * - a label, or an array of values
 * If a label is provided, the values must be binded as separate calls to binding methods.
 *
 * The returned string would replace the sql part for: "alias IN (x,x,x)"
 *
 * To work around Oracle limit of 1000 elements in IN clauses, using a label
 * is recommended in situations that this number may be reached.
 * This construct will then automatically deal with a compatible syntax fix.
 * Using a label in this scenario is needed because the fix must be implemented
 * with a late binding of the array at execution time (this also allows correctly
 * rebinding the elements for the IN clause)
 *
 * @param string $p_alias           A valid sql column identifier
 * @param mixed $p_label_or_values  Label or values array
 * @return string   Constructed string to be added to query
 */
public function sql_in( $p_alias, $p_label_or_values ) {
    if( is_array( $p_label_or_values ) ) {
        if( count( $p_label_or_values ) > self::$oracle_in_limit ) {
            $t_sql = $this->helper_in_oracle_fix( $p_alias, $p_label_or_values );
        } elseif( count( $p_label_or_values ) == 1 ) {
            $t_sql = $p_alias . ' = ' . $this->param( reset( $p_label_or_values ) );
        } else {
            $t_sql = $p_alias . ' IN ' . $this->param( $p_label_or_values );
        }
    } else {
        # is a label
        # create placeholder for late binding
        $t_new_index = count( $this->late_binding_in_clause );
        $this->late_binding_in_clause[$t_new_index] = array();
        $this->late_binding_in_clause[$t_new_index]['alias'] = $p_alias;
        $this->late_binding_in_clause[$t_new_index]['label'] = $p_label_or_values;
        $t_sql = '$in' . $t_new_index;
    }

    return $t_sql;
}

The constant "$oracle_in_limit" has its value to "1000" at line 155 :
protected static $oracle_in_limit = 1000; # this could be a constant

I've modified the value to 1000000 and this fix my problem with MSSQL.

Suggestion :
You could check the database engine with "$g_db_type" before apply a fix for a spefic engine.

atrol

atrol

2021-07-10 09:26

developer   ~0065684

Thanks @bzi for creating the report and the analysis.

PR https://github.com/mantisbt/mantisbt/pull/1764

@dregad wouldn't it be better if such database special handling is treated in underlying ADOdb?

dregad

dregad

2021-07-14 12:52

developer   ~0065691

wouldn't it be better if such database special handling is treated in underlying ADOdb?

This is somewhat higher-level than what ADOdb currently does, so no small undertaking to implement.
I'm not saying no, but TBH this not something that I currently have time to spend on.

ggswhitela

ggswhitela

2022-05-03 11:43

reporter   ~0066521

Will this resolution fix 0024393? Application Error 401 but with Error Code -52.

dregad

dregad

2022-05-04 04:57

developer   ~0066523

Will this resolution fix 0024393? Application Error 401 but with Error Code -52.

@ggswhitela your best bet is simply to try... Download a nightly build and test (or just apply the patch from MantisBT master cd64d192 if you prefer)

That said, the offending query in this issue is not the same as the other one, so I'm not sure this would actually make a difference.

Related Changesets

MantisBT: master cd64d192

2021-07-10 05:20

atrol


Details Diff
Remove Oracle special handling for non-Oracle databases

Fixes 0028902
Affected Issues
0028902
mod - core/classes/DbQuery.class.php Diff File