DB error

[marcofrl1603]

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 'DESC LIMIT 0,30' at line 1

This is the error that is displayed with each request that generates a list. I can view it in files, tasks, ProjectDesigner, logs etc. In the last week I have update my system to Ubuntu server 20.04

[ajdonnison]

Can you tell me what version of MySQL or MariaDB you are using?

[marcofrl1603]

Dear Mr / Ms

thanks for your reply. My mysql version is:

mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Best regards 27-05-2020

Marco Fiorletta CEO SSOLO Ltd +44 (0)20 8938 3283 |+44 (0)7452 044284 fiorletta@ssolo.co.uk http://www.ssolo.co.uk [1] 20-22 Wenlock Road, N17GU London, United Kingdom

On Wednesday, 27-05-2020 at 9:52 Adam Donnison wrote:

Can you tell me what version of MySQL or MariaDB you are using?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub [2], or unsubscribe [3].

Links:

[1] http://www.ssolo.co.uk/ [2] https://github.com/dotproject/dotProject/issues/129#issuecomment-634492717 [3] https://github.com/notifications/unsubscribe-auth/APS5AA333S3S62DTQWJ6GH3RTTBEPANCNFSM4NBL2Z5Q

[ajdonnison]

That version of MySQL certainly supports both the DESC keyword and the LIMIT 0,30 format. It may be that the error is earlier in the SQL statement. Is it possible to check the error log and find a full error? If you have a default setup it is likely to be in the web server error logs (presumably /var/log/apache2/error.log)

[marcofrl1603]

Dear Mr / Ms

[Wed May 27 10:47:28.594520 2020] [php7:notice] [pid 132842] [client 109.53.4.3:17787] /var/www/html/pm/includes/db_adodb.php(67): Error executing:

SELECT ta.*,pr.project_name, pr.project_id, pr.project_co lor_identifier,tp.task_pinned FROM (dotp_tasks as ta) LEFT JOIN dotp_projects AS pr ON pr.project_id = ta.task_project INNER JOIN dotp_user_tasks AS ut ON ut.task_id = ta.task_id AND ut.user_id = 2 LEFT J OIN dotp_user_task_pin AS tp ON tp.task_id = ta.task_id AND tp.user_id = 2 WHERE (ta.task_percent_complete < 100 OR ta.task_percent_complete IS NULL) AND ta.task_status = 0 AND project_status 7 AND project _status != 4 AND task_dynamic != 1 AND ta.task_start_date != '' AND ta.task_start_date != '0000-00-00 00:00:00' GROUP BY ta.task_id ORDER BY ta.task_end_date,task_priority DESC (Incorrect DATETIME value: ''), referer: http://office.ssolo.co.uk/pm/ [Wed May 27 10:47:28.595047 2020] [php7:warn] [pid 132842] [client 109.53.4.3:17787] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /var/www/html/pm/modules/tasks/to do.php on line 162, referer: http://office.ssolo.co.uk/pm/

[Wed May 27 10:47:28.594520 2020] [php7:notice] [pid 132842] [client 109.53.4.3:17787] /var/www/html/pm/includes/db_adodb.php(67): Error executing: SELECT ta.,pr.project_name, pr.project_id, pr.project_co lor_identifier,tp.task_pinned FROM (dotp_tasks as ta) LEFT JOIN dotp_projects AS pr ON pr.project_id = ta.task_project INNER JOIN dotp_user_tasks AS ut ON ut.task_id = ta.task_id AND ut.user_id = 2 LEFT J OIN dotp_user_task_pin AS tp ON tp.task_id = ta.task_id AND tp.user_id = 2 WHERE (ta.task_percent_complete < 100 OR ta.task_percent_complete IS NULL) AND ta.task_status = 0 AND project_status 7 AND project _status != 4 AND task_dynamic != 1 AND ta.task_start_date != '' AND ta.task_start_date != '0000-00-00 00:00:00' GROUP BY ta.task_id ORDER BY ta.task_end_date,task_priority DESC (Incorrect DATETIME value: ''), referer: http://office.ssolo.co.uk/pm/ [Wed May 27 10:47:28.595047 2020] [php7:warn] [pid 132842] [client 109.53.4.3:17787] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /var/www/html/pm/modules/tasks/to do.php on line 162, referer: http://office.ssolo.co.uk/pm/ [Wed May 27 10:49:27.829673 2020] [php7:notice] [pid 144574] [client 109.53.4.3:18073] /var/www/html/pm/includes/db_adodb.php(67): Error executing: SELECT distinct tsk.task_id, task_parent, task_name, task _start_date, task_end_date, task_dynamic, task_pinned, pin.user_id as pin_user, task_priority, task_percent_complete, task_duration, task_duration_type, task_project, task_description, task_owner, task_status, usernames.user_username, usernames.user_id, task_milestone, assignees.user_username as assignee_username, count(distinct assignees.user_id) as assignee_count, co.contact_first_name, co.contact_last_name, count( distinct fi.file_task) as file_count, if (tlog.task_log_problem IS NULL, 0, tlog.task_log_problem) AS task_log_problem FROM (dotp_user_tasks as ut3, dotp_tasks as tsk) LEFT JOIN dotpprojects as prj ON project id = task_project LEFT JOIN dotp_users as usernames ON task_owner = usernames.user_id LEFT JOIN dotp_user_tasks as ut ON ut.task_id = tsk.task_id LEFT JOIN dotp_users as assignees ON assignees.user_id = ut.user _id LEFT JOIN dotp_contacts as co ON co.contact_id = usernames.user_contact LEFT JOIN dotp_task_log AS tlog ON tlog.task_log_task = tsk.task_id AND tlog.task_log_problem > 0 LEFT JOIN dotp_files as fi on tsk.ta sk_id = fi.file_task LEFT JOIN dotp_user_task_pin as pin ON tsk.task_id = pin.task_id AND pin.user_id = 2 WHERE project_status 7 AND task_project = prj.project_id AND ut3.user_id = 2 AND ut3.task_id = tsk.ta sk_id AND (task_percent_complete < 100 OR task_end_date = '') AND prj.project_status 7 AND prj.project_status 4 AND prj.project_status 5 AND task_status = 0 GROUP BY tsk.task_id ORDER BY project_id, ta sk_start_date (Incorrect DATETIME value: ''), referer: http://office.ssolo.co.uk/pm/index.php?m=projects&a=view&project_id=1 [Wed May 27 10:49:42.658241 2020] [php7:notice] [pid 144570] [client 109.53.4.3:18072] /var/www/html/pm/classes/query.class.php(675): query failed(SELECT SQL_CALC_FOUND_ROWS SQL_CALC_FOUND_ROWS f., f.file_id a s latest_id, fmc.file_versions , round(fmc.file_lastversion, 2) as file_lastversion,ff.* FROM (dotp_files as f) INNER JOIN dotp_files_count_max AS fmc ON (fmc.file_lastversion = f.fileversion AND fmc.file version_id = f.file_version_id AND fmc.file_project = f.file_project) LEFT JOIN dotp_file_folders AS ff ON ff.file_folder_id = f.file_folder LEFT JOIN dotp_projects AS p ON p.project_id = f.file_project LEF T JOIN dotp_tasks AS t ON t.task_id = f.file_task WHERE f.file_project = 1 GROUP BY p.project_id,f.file_version_id DESC) - error was: 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 'DESC LIMIT 0,30' at line 1, referer: http://office.ssolo.co.uk/pm/index.php?m=projectdesigner [Wed May 27 10:49:42.660029 2020] [php7:warn] [pid 144570] [client 109.53.4.3:18072] PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/pm/modules/files/index_table.php on line 260, referer: http://office.ssolo.co.uk/pm/index.php?m=projectdesigner [Wed May 27 10:49:43.150355 2020] [php7:warn] [pid 144576] [client 109.53.4.3:17845] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /var/www/html/pm/modules/projectd esigner/gantt.php on line 296, referer: http://office.ssolo.co.uk/pm/index.php?m=projectdesigner [Wed May 27 10:49:43.150403 2020] [php7:warn] [pid 144576] [client 109.53.4.3:17845] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /var/www/html/pm/modules/projectd esigner/gantt.php on line 296, referer: http://office.ssolo.co.uk/pm/index.php?m=projectdesigner [Wed May 27 10:49:43.150412 2020] [php7:warn] [pid 144576] [client 109.53.4.3:17845] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /var/www/html/pm/modules/projectd esigner/gantt.php on line 296, referer: http://office.ssolo.co.uk/pm/index.php?m=projectdesigner

Best regards 27-05-2020

Marco Fiorletta CEO SSOLO Ltd +44 (0)20 8938 3283 |+44 (0)7452 044284 fiorletta@ssolo.co.uk http://www.ssolo.co.uk [1] 20-22 Wenlock Road, N17GU London, United Kingdom

On Wednesday, 27-05-2020 at 10:22 Adam Donnison wrote:

That version of MySQL certainly supports both the DESC keyword and the LIMIT 0,30 format. It may be that the error is earlier in the SQL statement. Is it possible to check the error log and find a full error? If you have a default setup it is likely to be in the web server error logs (presumably /var/log/apache2/error.log)

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub [2], or unsubscribe [3].

Links:

[1] http://www.ssolo.co.uk/ [2] https://github.com/dotproject/dotProject/issues/129#issuecomment-634507619 [3] https://github.com/notifications/unsubscribe-auth/APS5AAYZ72GCUOQEGCPAGYTRTTESTANCNFSM4NBL2Z5Q

[Coffee-fueled-deadlines]

I ended up fixing this issue by opening up index_table.php (located in: dotProject/modules/files/

On line ~172, I commented out $q2->addGroup('f.file_version_id DESC'); and made it #$q2->addGroup('f.file_version_id DESC'); instead.

[ajdonnison]

There look to be a number of issues, mainly with invalid DATETIME values - I believe that may have been resolved in the devel branch.

[jphilapy]

I tried a simple sql statement using GROUP BY with DESC in mysql 8 and it throws the same error. So that rules out the idea that here is anything wrong with the other parts of the sql.

Modifying the code in index_table.php on line 172: from $q2->addGroup('f.file_version_id DESC');

to $q2->addGroup('f.file_version_id'); $q2->addOrder('f.file_version_id DESC');

Fixes the issue.

[Banditpong]

I tried a simple sql statement using GROUP BY with DESC in mysql 8 and it throws the same error. So that rules out the idea that here is anything wrong with the other parts of the sql.

Modifying the code in index_table.php on line 172: from $q2->addGroup('f.file_version_id DESC');

to $q2->addGroup('f.file_version_id'); $q2->addOrder('f.file_version_id DESC');

Fixes the issue.

Here is an excerpt from the MySQL site if anyone is wondering why this change is needed. https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-sql-changes

Incompatible change: As of MySQL 8.0.13, the deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher replica servers.