Attempting to access LogEvents list page in the production instance throws an error.

[sughics]

1. In the logger-service production instance, the LogEvents List page accessible via the logger admin portal attempts to load for a while and then returns an error page. This does not occur on the test instance.

2. (11/08/22) Similar to the issue above, the LogDetail List page accessible via the logger admin portal throws same error. This only occurs when attempting to sort the table using the following columns Entity Type, Entity Uid, and Record Count.

[sughics]

Issue potentially related to #35

[sughics]

Appears to be a memory issue on the database when retrieving the LogEvent list . Latest mysql error logs show Out of sort memory error

These errors correlate with the exact times requests were made to to GET /admin/logEvent.

I was able to replicate the same error as seen above via CLI sql statements as well. Error is thrown on any attempts to sort the returned data if all columns are selected e.g.
select * from <<table_name>> ORDER BY created DESC LIMIT 1000;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

[sughics]

As suggested by @joe-lipson , indexes have not been added/set properly for the columns which are used to sort the results. Adding index correctly for the column i.e. month used to filter the default query significantly reduced query times when tested via the CLI but the error still persisted when the /admin/logEvent path was requested via the UI.

SQL logs were inspected on local db instance to get the actual SQL query made by the application. The query used by grails' scaffolded LogEvent controller implementation actually converts any selected sorting column e.g.month (default) to uppercase. e.g. select * from log_event ORDER BY uppercase(month) DESC LIMIT 10. Regardless of any indexes set for the relevant column, this query throws Out of sort memory error.

Converting the sorting column to uppercase during the query is not explicitly defined anywhere is the codebase or configuration. It seems to be set internally by Grails or Hibernate

Fix

• Changes have been made to the application to remove the default sorting specification sort 'month': 'desc' from LogEvent static mapping.

• An index() method has been added to LogEvent to override the scaffolded index method, the default sorting column is also set here, in turn , preventing the case conversion on sorting columns during /admin/logEvent query.

• Changes to the DB to add correct index to the default sorting column and other sortable columns in the log_event table have been added in ala-install.

• An index() method has been added to LogDetail controller to override the scaffolded index method, the default sorting column is also set here, in turn , preventing the case conversion on sorting columns during /admin/logDetail query.

• Changes to the DB to add correct index to the default sorting column and other sortable columns in the log_detail table have been added in ala-install.

Looking into archiving current log_event and log_detail per README.md

[sughics]

To test the above in local environment, please enable general sql logs in your local mysql instance as root and tail the generated logs as you make requests to admin/logEvent or admin/logDetail. The relevant sql logs should not include the UPPER function on the order column e.g. order by upper(logevent0_.month) desc limit 10

Please also test explicit sorting of the results using the columns in the UI for either of the pages mentioned above.

To enable SQL logging locally:

SET global general_log = 1;
SET global log_output. = 'file';
SET global general_log_file = '<path_to_file>' ;