Products Purchases - database error on display and SQL errors in Reporting

[ggraynoth]

We have just started to use Quotes in SuiteCRM 8 (8.2.3) , which work fine as far as creating and reporting . (PHP 7.4.33 MySQL 8.0.31 Apache 2.4.54.2

When we change these to closed/accepted, everything appears to update OK, with no errors.

However, when we try to display a product in one of them, the purchases section errors with a database error refer to suiteCRM log. (Fatal error shown below)

Also when trying to write a report which we think is using the same data, another Fatal is generated, resulting in the report not running. We believe that these are connected.

We understand the errors, being SQL syntax and Missing/unknown column, but can’t fathom out if this is being caused by coding or if there is a config change we can make to fix.

Errors
Purchases Display at bottom of Product Screen - Database Error - log entry below.

Sun Apr 23 09:27:19 2023 [21020][e6537c8a-d773-25bb-fb68-643d7fe5ffe1][FATAL] Mysqli_query failed.
Sun Apr 23 09:27:19 2023 [21020][e6537c8a-d773-25bb-fb68-643d7fe5ffe1][FATAL] Error retrieving AOS_Products list: Query Failed: ORDER BY aos_quotes.id asc LIMIT 0,10: MySQL error 1064: 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 ‘ORDER BY aos_quotes.id asc LIMIT 0,10’ at line 1
Sun Apr 23 09:27:19 2023 [21020][e6537c8a-d773-25bb-fb68-643d7fe5ffe1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

Reports - Trying to Add Product quote data into a working report which is base upon Products taking data from the associated quote. Report doesn’t work and log entry below

Sun Apr 23 09:56:58 2023 [21020][1][FATAL] Mysqli_query failed.
Sun Apr 23 09:56:58 2023 [21020][1][FATAL] Query Failed: SELECT count(*) c FROM (SELECT aos_products.name AS ‘Product_Name0’, aos_products.part_number AS ‘Part_Number1’, aos_products.aos_product_category_id AS ‘TEM_Categories2’, aos_products_cstm.mob_user_c AS ‘User_Name3’, aos_products:aos_products_purchases:aos_products_quotes:aos_products.name AS ‘Product_Name4’, aos_products:aos_products_purchases:aos_products_quotes:aos_products.currency_id AS ‘aos_products:aos_products_purchases:aos_products_quotes:aos_products_currency_id’, aos_products:aos_products_purchases:aos_products_quotes:aos_products.price AS ‘Price5’, aos_products.id AS ‘aos_products_id’ FROM aos_products LEFT JOIN aos_products_cstm aos_products_cstm ON aos_products.id = aos_products_cstm.id_c WHERE ( aos_products.name LIKE CONCAT(‘07’ ,‘%’) ) AND aos_products.deleted = 0 ) as n: MySQL error 1054: Unknown column ‘aos_products:aos_products_purchases:aos_products_quotes:aos_products.name’ in ‘field list’

We have checked back on old Forum posts but can’t find anything that is directly related to this.

Any help gratefully received.

[ggraynoth]

After trying a few suggested changes, we found a reply to a very old post, which appeared to address the same type of issue for the list view problem.

We made the suggested changes to the SugarBean.php code - the second change on line 930 was already done but the first line was also commented out in our code.

Database failure. Please refer to suitecrm.log for details.

Hello @Marty42 This is precisely the same issue and the fix is the same. This issue impacts subpanels in every module as all the subpanels use SugarBean.php when pulling data. Where the user first encounters the error depends on what part of the CRM they use after the MySQL environment on their server is updated. To fix: In SuiteCRM/data/SugarBean.php Replace $final_query = ‘(’ . $tmp_final_query . ‘)’; With $final_query = $tmp_final_query ; AND on line number 930 comment out: $query…

This fixed the Products Purchases list view issue in the Purchases panel at the bottom of the Products screen issue with immediate effect, after a browser refresh. We have tried it with users with full privileges and those who are restricted through security groups and roles within them and it works for all.

So always worth a deep dive into the Forum, as this was on a 2019 post which was not specifically related to Products but was related to ‘Database Failure refer to SuiteCRM logs’ but the answer was posted over 3 years after the initial post. It is rightly highlighted by @pgr that you need to be careful with such old responses and that they are not a ‘one size fits all’ solution, but in our case, it has worked.

We still have the SQL issue in reports when trying to create a report but have a workaround for this so will open as a separate discussion if we can’t resolve that ourselves.

With these, we always implement on the test system localhost, and even though we have version control on the code, copy the original source file renaming it xxxxxxxx.old before we make any changes, so that we can simply back out if it causes more problems than it solves.

[ggraynoth]

One final note - we have just upgraded to SuiteCRM v8.2.4 and the same fault still exists in there.

The same solution fixes it.