https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23626
--- Comment #13 from Paul Hoffman <[email protected]> --- Here's an example from our Koha instance: SELECT monthname(timestamp) AS month, year(timestamp) AS year, count(itemnumber) AS count FROM items WHERE timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY year(timestamp), month(timestamp) I have screenshots showing the steps in charting this -- I can attach them if they're useful. However, I don't want this to distract from the problem that this bug report and patch are trying to mitigate (not solve!) which is that no matter what the report is and what the user might end up doing with it -- charting, downloading as *.ods or *.csv, or just viewing page by page -- the full report results (n rows of data with n unbounded) are loaded into memory on the Koha server, converted to JSON, and then returned to the user embedded in the generated HTML. For example, I ran a query that returned a single row; it yielded HTML containing the following (some whitespace, along with the bulk of the function body, removed for clarity): -------------------------------------------------------------------------------- $('#draw-chart').click(function() { var x_elements = $('select[name="x"]').val(); var y_elements = []; var groups = []; var lines = []; var options = {}; headers = [{"cell":"month"},{"cell":"year"},{"cell":"count"}]; var results; if ($('input[name="chart-include-all"]').prop('checked')) { results = [{"cells":[{"cell":"September"},{"cell":2019},{"cell":39}]}] } else { results = [{"cells":[{"cell":"September"},{"cell":"2019"},{"cell":"39"}]}] } [...] } -------------------------------------------------------------------------------- The first line that sets the _results_ variable is where the full report results appear; if the report had yielded 10,000 rows, there would be 10,000 elements in the array. And I haven't even touched on the possibility of a query like this that fails to properly JOIN tables: SELECT foo.bar, baz.qux FROM foo, baz If foo and baz each contain 10,000 rows, that's 100 million rows returned by the DB, loaded into memory, converted to JSON, and -- well, it won't get that far, because nobody is running a Koha instance with enough RAM and swap to survive this. But I would bet that a lot of Koha instances have (staff) users with the requisite permissions to write reports but not the expertise in SQL and RDBMSes that would steer them away from writing a query like this. Stepping outside the bounds of this bug report for a moment, I'll just mention some options for truly *fixing* the underlying problem that may be worth considering; these include (a) Removing the option to chart the full report results; (b) Implementing full-data charting using callbacks (AJAX or whatever) to fetch data from Koha only as it's needed; and (c) Ripping out the charting feature altogether on the principle of "do one thing well", which entails taking the position that charting is best done in a tool (Libreoffice Calc, Excel, whatever) dedicated to that task. This last one is my favorite, but I'm sure not everyone agrees! :-) -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
