Reporting -> Amount Outstanding Report, SQL error

Alexei_Mironov · November 25, 2024, 1:18pm

Trying to open Amount Outstanding Report results in SQL error

URI
/openboxes/report/amountOutstandingOnOrdersReport
Class
java.sql.SQLSyntaxErrorException
Message
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'order_adjustment_invoice_summary.order_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

jmiranda · January 7, 2025, 9:13pm

This is a bug that we’ll need to fix. In the meantime, there’s an workaround that you can apply via MySQL configuration. Just remove ONLY_FULL_GROUP_BY from sql_mode value. You can do this in the current session to see if the issue is resolved, add then add it to your MySQL configuration (requires restart).

Here’s a similar bug report (different feature) from a few years ago.

github.com/openboxes/openboxes

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘openboxes.tag.id’

opened 06:02AM - 04 Dec 18 UTC

closed 05:03AM - 26 Apr 19 UTC

jmiranda

This error occurs when loading the dashboard on instances using MySQL 5.7+. By d…efault, MySQL 5.7+ has disabled the ability to include columns in the SELECT clause that are not also in the GROUP BY clause. https://stackoverflow.com/questions/48147294/error-sqlexceptionhelper146-expression-7-of-select-list-is-not-in-group-by-c The bug should be easy to fix and will be included in an upcoming release, but you can use the following workaround in the meantime. 1. Log into MySQL as root 1. Execute the following statement ``` mysql> show variables like '%sql_mode%'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` 3. Copy the value removing `ONLY_FULL_GROUP_BY` 4. Edit /etc/mysql/mysql.cnf 5. Paste `sql_mode` value without `ONLY_FULL_GROUP_BY` to the bottom of the file ``` [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ```