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.