Resource Progress, Project Progress ?

[jproch]

Hello, we use Mantis (v. 1.2.15) with Project Management (v.1.4.1) on Windows for a while. Many Thanks!!!! Everything seems to work fine with exceptions of "Resource Progress" and "Project Progress". Click to either of those just produce an error message:

APPLICATION ERROR #401
Database query failed. Error received from database was #1111: Invalid use of group function for the query: SELECT pp.id as parent_project_id, pp.name as parent_project,
pc.id as project_id, pc.name as project_name, c.id as category_id, c.name as category_name,
b.sponsorship_total as weight, b.due_date,
b.id, b.handler_id, w.work_type, w.minutes_type, sum(w.minutes) as minutes, max(w.book_date) as book_date
FROM mantis_bug_table b
JOIN mantis_project_table pc ON b.project_id = pc.id
JOIN mantis_category_table c ON b.category_id = c.id
LEFT OUTER JOIN mantis_project_hierarchy_table h ON pc.id = h.child_id
LEFT OUTER JOIN mantis_project_table pp ON h.parent_id = pp.id
LEFT OUTER JOIN mantis_plugin_ProjectManagement_work_table w ON b.id = w.bug_id
WHERE (b.target_version = '00.05'
AND 1=1 ) GROUP BY pp.id, pp.name, pc.id, pc.name, c.id, c.name, b.id, b.handler_id, w.work_type, w.minutes_type,
b.sponsorship_total, b.due_date
ORDER BY handler_id, b.resolution DESC, CASE WHEN MAX(b.due_date) = 1 THEN 9999999999 ELSE MAX(b.due_date) END, weight DESC, id.

Why that could be ? what do we missing ?
Thanks a lot for any advice!
Best Regards,
Ian

[vincentsels]

That's strange, there seems to be nothing wrong with the query. Which database are you using - mysql ? Can you copy/paste the query in your database query tool and try to execute it there ? That might shed some more light on what's wrong with it.

[jproch]

Thanks for quick response. We tried to get the required info. So, we run at Windows XP box with 2GB RAM (couldn't that be the reason? I should put 4GB there) MySQL version 4.1 and the response for query returns error 1064. What else could we try ?
mysql> SELECT pp.id as parent_project_id, pp.name as parent_project,
-> pc.id as project_id, pc.name as project_name, c.id as category_id, c.name
as category_name,
-> b.sponsorship_total as weight, b.due_date,
-> b.id, b.handler_id, w.work_type, w.minutes_type, sum(w.minutes) as minute
s, max(w.book_date) as book_date
-> FROM mantis_bug_table b
-> JOIN mantis_project_table pc ON b.project_id = pc.id
-> JOIN mantis_category_table c ON b.category_id = c.id
-> LEFT OUTER JOIN mantis_project_hierarchy_table h ON pc.id = h.child_id
-> LEFT OUTER JOIN mantis_project_table pp ON h.parent_id = pp.id
-> LEFT OUTER JOIN mantis_plugin_ProjectManagement_work_table w ON b.id = w.
bug_id
-> WHERE (b.target_version = '00.05'
-> AND 1=1 ) GROUP BY pp.id, pp.name, pc.id, pc.name, c.id, c.name, b.id, b.
handler_id, w.work_type, w.minutes_type,
-> b.sponsorship_total, b.due_date
-> ORDER BY handler_id, b.resolution DESC, CASE WHEN MAX(b.due_date) = 1 THE
N 9999999999 ELSE MAX(b.due_date) END, weight DESC, id.;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 14

[jproch]

Couldn't the issue be caused by any wrong/missing/not expected data somewhere in our data ?
Is there anything else that we could try to find out the cause ?
I really do not want to go back to M$ Project :-(

[vincentsels]

So the query seems to fail on the last line, which is the ORDER BY. You might try the following, in the file ProjectManagementAPI.php, change line 764 to this:

              ORDER BY handler_id, b.resolution DESC, weight DESC, id";

So you remove the part with the CASE and the max(b.due_date)... I don't understand why I'm ordering by the max of the due date when that's not a selected column, that doesn't seem right. When I execute the query here (MySQL 5.6) it doesn't cause a problem, but v4.1 might not accept that...

Don't have test data anymore so can't test whether everything still works correctly with this change...

[jproch]

Thank you!!! Did not do much thorough testing yet, but the the imminent crash was fixed by the change.