Cannot view Project tasks in Project module [8.2.3] “Database failure. Please refer to suitecrm.log for details.”

Cadej · 20 February 2023 06:36

Fresh install (formated disk)
Ubuntu 22.04
SuiteCRM 8.2.3
php 8.0
MySQL 8.0

Install SuiteCRM. Go to Projects, create a project. In the project overview page, create a new project task. After I click save in that area I get the message “Database failure. Please refer to suitecrm.log for details.”

What could be wrong?

I have even tried other versions of SuiteCRM. All giving me the error.
.:.:.
Fresh install (formated disk)
Ubuntu 22.04
SuiteCRM 8.2.2
php 8.0
MySQL 8.0
.:.:.
Fresh install (formated disk)
Ubuntu 22.04
SuiteCRM 8.2.1
php 8.0
MySQL 8.0
.:.:.
Fresh install (formated disk)
Ubuntu 22.04
SuiteCRM 8.2.0
php 8.0
MySQL 8.0
.:.:.

I have also noticed that when I finish the installation, log in for the first time, go to “project module” and view the “project → project tasks” that came with demo data, I also get this error “Database failure. Please refer to suitecrm.log for details.”

What could be wrong?
Why do I get this error message?

------ suitecrm.log when I just view the project task ------

Sun Feb 19 20:39:47 2023 [39696][1][FATAL] Mysqli_query failed.
Sun Feb 19 20:39:47 2023 [39696][1][FATAL] Error retrieving Project list:  Query Failed: (SELECT project_task.id ,  project_task.name ,  project_task.date_start ,  project_task.date_finish ,  project_task.assigned_user_id  ,  'projecttask' panel_name  FROM project_task  INNER JOIN  project projecttask_rel ON project_task.project_id=projecttask_rel.id AND projecttask_rel.deleted=0

  where ( project_task.project_id='47e3a2a7-a9ef-1f19-d001-63f28858a389') AND project_task.deleted=0) ORDER BY project_task.project_task_id asc LIMIT 0,10: MySQL error 1250: Table 'project_task' from one of the SELECTs cannot be used in global ORDER clause
Sun Feb 19 20:39:47 2023 [39696][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

------ end suitecrm.log ------

------ suitecrm.log when I create and view the project task ------

Fri Feb 17 20:50:43 2023 [39950][1][FATAL] Exception handling in /var/www/crm/public/legacy/include/MVC/Controller/SugarController.php:397
Fri Feb 17 20:50:43 2023 [39950][1][FATAL] Exception in Controller: There is no action by that name: record
Fri Feb 17 20:50:52 2023 [41649][1][FATAL] Mysqli_query failed.
Fri Feb 17 20:50:52 2023 [41649][1][FATAL] Error retrieving Project list:  Query Failed: (SELECT project_task.id ,  project_task.name ,  project_task.date_start ,  project_task.date_finish ,  project_task.assigned_user_id  ,  'projecttask' panel_name  FROM project_task  INNER JOIN  project projecttask_rel ON project_task.project_id=projecttask_rel.id AND projecttask_rel.deleted=0

  where ( project_task.project_id='58a909b5-411e-2694-d362-63efda5b06e0') AND project_task.deleted=0) ORDER BY project_task.project_task_id asc LIMIT 0,10: MySQL error 1250: Table 'project_task' from one of the SELECTs cannot be used in global ORDER clause
Fri Feb 17 20:50:52 2023 [41649][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Fri Feb 17 20:53:15 2023 [39948][1][FATAL] Mysqli_query failed.
Fri Feb 17 20:53:15 2023 [39948][1][FATAL] Error retrieving Project list:  Query Failed: (SELECT project_task.id ,  project_task.name ,  project_task.date_start ,  project_task.date_finish ,  project_task.assigned_user_id  ,  'projecttask' panel_name  FROM project_task  INNER JOIN  project projecttask_rel ON project_task.project_id=projecttask_rel.id AND projecttask_rel.deleted=0

  where ( project_task.project_id='58a909b5-411e-2694-d362-63efda5b06e0') AND project_task.deleted=0) ORDER BY project_task.project_task_id asc LIMIT 0,10: MySQL error 1250: Table 'project_task' from one of the SELECTs cannot be used in global ORDER clause
Fri Feb 17 20:53:15 2023 [39948][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Fri Feb 17 20:53:36 2023 [39954][1][FATAL] Exception handling in /var/www/crm/public/legacy/include/MVC/Controller/SugarController.php:397
Fri Feb 17 20:53:36 2023 [39954][1][FATAL] Exception in Controller: There is no action by that name: record
Fri Feb 17 20:54:17 2023 [41648][1][FATAL] Mysqli_query failed.
Fri Feb 17 20:54:17 2023 [41648][1][FATAL] Error retrieving Project list:  Query Failed: (SELECT project_task.id ,  project_task.name ,  project_task.date_start ,  project_task.date_finish ,  project_task.assigned_user_id  ,  'projecttask' panel_name  FROM project_task  INNER JOIN  project projecttask_rel ON project_task.project_id=projecttask_rel.id AND projecttask_rel.deleted=0

  where ( project_task.project_id='63074cde-f61f-9b66-d976-63efdb6109a2') AND project_task.deleted=0) ORDER BY project_task.project_task_id asc LIMIT 0,10: MySQL error 1250: Table 'project_task' from one of the SELECTs cannot be used in global ORDER clause
Fri Feb 17 20:54:17 2023 [41648][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

------ end suitecrm.log ------

------ error.log ------

[Fri Feb 17 20:50:43.190745 2023] [php:warn] [pid 39947] [client 192.168.1.25:65414] PHP Warning:  Undefined array key "tabindex" in /var/www/crm/public/legacy/include/Smarty/plugins/function.sugar_field.php on line 104, referer: http://192.168.1.55/public/

[Fri Feb 17 20:54:17.035307 2023] [php:warn] [pid 41648] [client 192.168.1.25:65470] PHP Warning:  Trying to access array offset on value of type bool in /var/www/crm/public/legacy/modules/ProjectTask/updateDependencies.php on line 61, referer: http://192.168.1.55/public/legacy/index.php

------ end error.log ------
.
.
.

Cadej · 21 February 2023 12:32

The cause:
In MySQL 8.0.31 something has been changed so the SQL queries that pull data for subpannels fail. The current code in SuiteCRM adds the outer ( ) to the query. If the outer ( ) is removed from the query, the SQL query will complete normally.

I hope in future releases [>8.2.3] of SuiteCRM this bug is fixed - so that it works on all MySQL 8.0.xx versions.

This fix worked for me

https://github.com/salesagility/SuiteCRM/issues/9788

As pstevens71 wrote:

In file
/public/legacy/data/SugarBean.php

comment out:

$query = '(' . $query . ')';

AND

remove the brackets and quotations:

$final_query = '(' . $tmp_final_query . ')';

to

$final_query = $tmp_final_query;

jdlev · 23 February 2023 18:35

Hey @Cadej
I tried the fix you mentioned, but had no luck - are there any additional steps you have to perform? So far, I’ve taken the following actions:

I updated the SugarBean.php file and confirmed the changes
Restarted my apache server
Run the quick fix in the admin panel
Cleared my browser cache

I’m not a developer so not sure what else I should try, but so far I keep getting the “Database failure, check suitecrm.log…” Thanks for any tips.

EDIT: I did just notice something that might cause it to not work…Checked my MySQL version and I’m running version 8.0.32 instead of the version you have - 8.0.31… hopefully mysql hasn’t reinvented the problem elsewhere

Cadej · 23 February 2023 21:46

I haven’t said I’m using MySQL 8.0.31. I just said that something has been changed in MySQL 8.0.31. I also have 8.0.32. I was still hoping that “they” would fix it in .32. I think that this SuiteCRM bug will still be a bug in all future releases of MySQL. So SuiteCRM developers have to fix it in their code.

I also noticed that in other places I get “undefined” notifications. If something does not work in SuiteCRM, a pop up shows with the word “undefined” in it.

With that said I’m currently looking into manual installation of MySQL 8.0.30 (or even older). So that this current bug in SuiteCRM will not be an issue. If you manage to manually install MySQL 8.0.30 (or even older) and test my assumption, please post here and let everyone know.