View Issue Details

IDProjectCategoryView StatusLast Update
0025781mantisbtreportspublic2019-06-14 02:39
Reportermchendl Assigned Tocproensa  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version2.21.0 
Target Version2.21.1Fixed in Version2.21.1 
Summary0025781: Summary statistics db error message
Description

After upgrading from 2.12.0 to 2.21.0 the summary stats without filters doesn't work anymore: DB error message.
Some reduced filters work. No reproduction detected. Query attached.

I saw 0025693 and the other features introduced in 2.20.0.
Any DB schema updates between 2.12.0 and 2.21.0?

Steps To Reproduce
  • use mantisbt with mssql ODBC Driver 13, iis and php7
  • Click on "Summary"
Additional Information

APPLICATION ERROR 0000401

Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet 0008115: SQLState: 22003
Error Code: 8115
Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int.
für die Abfrage: SELECT id, diff, SUM(diff) OVER () AS total_time, AVG(diff) OVER () AS avg_time FROM ( SELECT b.id, MAX(h.date_modified) - b.date_submitted AS diff FROM mantis_bug_table b LEFT JOIN mantis_bug_history_table h ON b.id = h.bug_id AND h.type = ? AND h.field_name = ? AND b.date_submitted <= h.date_modified WHERE b.status >= ? AND h.new_value >= ? AND h.old_value < ? AND project_id=4 AND b.id IN (SELECT mantis_bug_table.id FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled =? AND (mantis_bug_table.project_id = ?)) GROUP BY b.id,b.date_submitted ) subquery ORDER BY diff DESC.
Bitte benutzen Sie die „Zurück“-Taste Ihres Browsers, um auf die vorhergehende Seite zurückzukehren. Dort können Sie den hier angezeigten Eintrag korrigieren oder eine andere Aktion ausführen. Über das Menü können Sie auch direkt zu einer anderen Aktion wechseln.

TagsNo tags attached.

Activities

cproensa

cproensa

2019-05-22 19:04

developer   ~0062112

Please check your manage overview page, what database driver does it show?

That query runs fine in my tests:
ubuntu linux + php7.3/sqlsrv
with "mssqlnative" configured in mantis

cproensa

cproensa

2019-05-22 19:56

developer   ~0062113

So you say, that this error happens when a big number of issues are included in the summary, and with fewer issues (when filtered), it works ok?

Please can you test this modification:
https://github.com/cproensa/mantisbt/commit/6434d3738b26fe197d31162092770b2cb0258879

mchendl

mchendl

2019-05-23 02:53

reporter   ~0062115

Database driver "mssqlnative" with mssql ODBC Driver 13.
Yes, It works ok with fewer issues.
I tested the modification: the same error message.
More tests from me: I tried to find the specific issue in database which maybe caused the error by reducing the results with the filter function. It seems to depend on the amount of records, not on the content of the records.

Our db_stats:
mantis_project_file_table 0
mantis_bug_revision_table 1694
mantis_config_table 60
mantis_project_hierarchy_table 0
mantis_project_table 1
mantis_bug_file_table 8738
mantis_project_user_list_table 16
mantis_bug_history_table 59732
mantis_project_version_table 0
mantis_sponsorship_table 0
mantis_bug_monitor_table 328
mantis_api_token_table 0
mantis_bug_relationship_table 595
mantis_tokens_table 49
mantis_bug_table 3029
mantis_user_pref_table 80
mantis_bug_text_table 17122
mantis_bugnote_table 12274
mantis_user_print_pref_table 0
mantis_plugin_EmailReporting_msgids_table 7
mantis_user_profile_table 1
mantis_bugnote_text_table 12274
mantis_custom_field_project_table 4
mantis_user_table 148
mantis_custom_field_string_table 10559
mantis_custom_field_table 4
mantis_email_table 0
mantis_tag_table 261
mantis_bug_tag_table 911
mantis_filters_table 141
mantis_plugin_table 2
mantis_news_table 2
mantis_category_table 7

cproensa

cproensa

2019-05-23 03:49

developer   ~0062116

my guess is that sum() overflows the default datatype, that's the reason for trying sum(cast(x as bigint))instead of sum(x)
unfortunately, i don't have a big dataset in my sqlserver instantce, to properly test this scenario.

cproensa

cproensa

2019-05-23 18:33

developer   ~0062123

looks like AVG() is also affected

@mchendl, please try this modification:
https://github.com/cproensa/mantisbt/commit/c99593757a59e1fa68e75dfd6e807808e488bc82

mchendl

mchendl

2019-05-24 04:41

reporter   ~0062127

Looks great! It works like a charm!

cproensa

cproensa

2019-05-28 13:37

developer   ~0062152

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

dregad

dregad

2019-05-30 06:08

developer   ~0062167

I merged the PR, and cherry-picked the commit onto 2.21 since it's a regression introduced by that version.

Related Changesets

MantisBT: master 49539f33

2019-05-30 02:04

cproensa

Committer: dregad


Details Diff
Fix mssql summary error

When computing SUM & AVG functions, sqlserver uses the column
datatype, which is INT. This datatype can be overflowed when a big
number of issues are included, since we are adding the total number
of seconds.

In that case, use an explicit cast to BIGINT to avoid that error.
Other databases are not affected because they use a runtime data
type allowing bigger integers.

The stats array is initialized to zero, to return a properly formatted
value array when there aren't any issues.

Fixes 0025781
PR https://github.com/mantisbt/mantisbt/pull/1516
Affected Issues
0025781
mod - core/summary_api.php Diff File

MantisBT: master-2.21 5bade1d5

2019-05-30 02:04

cproensa

Committer: dregad


Details Diff
Fix mssql summary error

When computing SUM & AVG functions, sqlserver uses the column
datatype, which is INT. This datatype can be overflowed when a big
number of issues are included, since we are adding the total number
of seconds.

In that case, use an explicit cast to BIGINT to avoid that error.
Other databases are not affected because they use a runtime data
type allowing bigger integers.

The stats array is initialized to zero, to return a properly formatted
value array when there aren't any issues.

Fixes 0025781
PR https://github.com/mantisbt/mantisbt/pull/1516
Affected Issues
0025781
mod - core/summary_api.php Diff File