View Issue Details

IDProjectCategoryView StatusLast Update
0007912mantisbtdb mssqlpublic2018-07-20 02:15
Reporterrzg Assigned Tograngeway  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Fixed in Version1.1.0a4 
Summary0007912: Time tracking doesn't work- SQL syntax error
Description

When you click "Get time tracking information" on any bug, you get this error. It is because the statement generated in bugnote_api.php around line 473 is not valid SQL. (This was part of my bug report on 0004428 ):

Time tracking
Start Date:
APPLICATION ERROR 0000401

Database query failed. Error received from database was 0008120: Column 'u.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. for the query: SELECT username, SUM(time_tracking) sum_time_tracking
FROM mantis_user_table u, mantis_bugnote_table bn
WHERE u.id = bn.reporter_id AND
bn.bug_id = '1289'
AND bn.date_submitted >= '2004-05-21 00:00:00' AND bn.date_submitted <= '2007-04-22 23:59:59'
GROUP BY u.id

Please use the "Back" button in your web browser to return to the previous page. There you can correct whatever problems were identified in this error or select another action. You can also click an option from the menu bar to go directly to a new section.

TagsNo tags attached.

Activities

vboctor

vboctor

2007-04-25 21:38

manager   ~0014399

The query looks fine to me. Anyone knows what is wrong with this query?

seiji

seiji

2007-04-25 22:33

reporter   ~0014400

See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html

MySQL extends the use of GROUP BY so that you can use non-aggregated columns
or calculations in the SELECT list that do not appear in the GROUP BY clause.
:
In standard SQL, you would have to add customer.name to the GROUP BY clause.
In MySQL, the name is redundant.

so

GROUP BY u.id
->
GROUP BY u.id, u.username

rzg

rzg

2007-04-30 13:55

reporter   ~0014407

Last edited: 2007-04-30 13:55

What he said. If that query works, it is a mysql-ism that doesn't extend to other DBMSes.

grangeway

grangeway

2007-07-22 17:29

reporter   ~0015163

sorry - test bugnote1

grangeway

grangeway

2007-07-22 17:30

reporter   ~0015164

sorry - test bugnote2

grangeway

grangeway

2014-05-16 15:00

reporter   ~0040411

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul