Bug 23685 - Exporting report may consume unlimited memory
Summary: Exporting report may consume unlimited memory
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low major
Assignee: Aleisha Amohia
QA Contact: Nick Clemens (kidclamp)
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-09-26 18:31 UTC by Paul Hoffman
Modified: 2024-12-19 18:34 UTC (History)
10 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This development allows you to control the number of records that is downloaded in the reports module (to prevent timeouts) and also includes the option to hide the ODS download option that is the most expensive one. Adding prefs GuidedReportsExportLimit and ReportingAllowsODS_Export for that reason.
Version(s) released in:
24.11.00
Circulation function:


Attachments
Bug 23685: Don't store ODS content in a variable to save memory (1.65 KB, patch)
2024-06-26 02:48 UTC, Aleisha Amohia
Details | Diff | Splinter Review
Bug 23685: Exclude guided_reports.pl from plack (1.54 KB, patch)
2024-07-06 00:44 UTC, Aleisha Amohia
Details | Diff | Splinter Review
Bug 23685: Exclude guided_reports.pl from plack (1.58 KB, patch)
2024-07-07 06:25 UTC, David Nind
Details | Diff | Splinter Review
Bug 23685: (follow-up) Control ODS exporting (3.14 KB, patch)
2024-09-06 13:11 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Dbrev for new preferences (3.55 KB, patch)
2024-09-06 13:12 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add prefs to Administration form (1.83 KB, patch)
2024-09-06 13:12 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add export limit for guided reports (5.38 KB, patch)
2024-09-06 13:12 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Control ODS exporting (3.14 KB, patch)
2024-09-06 13:12 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: Don't store ODS content in a variable to save memory (1.75 KB, patch)
2024-09-06 13:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Dbrev for new preferences (3.55 KB, patch)
2024-09-06 13:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add prefs to Administration form (1.83 KB, patch)
2024-09-06 13:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add export limit for guided reports (5.38 KB, patch)
2024-09-06 13:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Control ODS exporting (3.14 KB, patch)
2024-09-06 13:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: Don't store ODS content in a variable to save memory (1.75 KB, patch)
2024-09-09 08:23 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Dbrev for new preferences (2.59 KB, patch)
2024-09-09 08:23 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add prefs to Administration form (1.81 KB, patch)
2024-09-09 08:23 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add export limit for guided reports (5.33 KB, patch)
2024-09-09 08:23 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: (follow-up) Control ODS exporting (3.13 KB, patch)
2024-09-09 08:24 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 23685: Don't store ODS content in a variable to save memory (1.77 KB, patch)
2024-09-16 17:47 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 23685: (follow-up) Dbrev for new preferences (2.61 KB, patch)
2024-09-16 17:47 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add prefs to Administration form (1.83 KB, patch)
2024-09-16 17:47 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 23685: (follow-up) Add export limit for guided reports (5.35 KB, patch)
2024-09-16 17:47 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 23685: (follow-up) Control ODS exporting (3.15 KB, patch)
2024-09-16 17:47 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Paul Hoffman 2019-09-26 18:31:17 UTC
In guided_reports.pl, when exporting report results ($phase eq 'Export') all rows of data are fetched and then converted to the desired format.  This may consume an unlimited amount of memory; when report results are particularly large, it may consume all available memory, leading to timed-out HTTP requests, crashes, and potentially data loss.  (We have experienced Zebra index corruption as a result.)

In master, reports/guided_reports.pl supports three export types -- tab-delimited, CSV, or *.ods.  In each case, all data are loaded into memory and held there before any output is produced:

   891      $sql = get_prepped_report( $sql, \@param_names, \@sql_params );
   892          my ($sth, $q_errors) = execute_query($sql);
   ...
   895          if ($format eq 'tab') {
   896              $type = 'application/octet-stream';
   897              $content .= join("\t", header_cell_values($sth)) . "\n";
   898              $content = Encode::decode('UTF-8', $content);
   899              while (my $row = $sth->fetchrow_arrayref()) {
   900                  $content .= join("\t", @$row) . "\n";
   901              }
   902          } else {
   903              my $delimiter = C4::Context->preference('delimiter') || ',';
   904              if ( $format eq 'csv' ) {
   ...
   914                  while (my $row = $sth->fetchrow_arrayref()) {
   915                      if ($csv->combine(@$row)) {
   916                          $content .= $csv->string() . "\n";
   917                      } else {
   918                          push @$q_errors, { combine => $csv->error_diag() } ;
   919                      }
   920                  }
   921              }
   922              elsif ( $format eq 'ods' ) {
   ...
   932                  # Other line in Unicode
   933                  my $sql_rows = $sth->fetchall_arrayref();
   934                  foreach my $sql_row ( @$sql_rows ) {
   935                      my @content_row;
   936                      foreach my $sql_cell ( @$sql_row ) {
   937                          push @content_row, Encode::encode( 'UTF8', $sql_cell );
   938                      }
   939                      push @$ods_content, \@content_row;
   940                  }
   941
   942                  # Process
   943                  generate_ods($ods_filepath, $ods_content);
   944
   945                  # Output
   946                  binmode(STDOUT);
   947                  open $ods_fh, '<', $ods_filepath;
   948                  $content .= $_ while <$ods_fh>;
   949                  unlink $ods_filepath;
   950              }

The *.ods case is particularly problematic, because before any data is sent back to the user's browser, *three* copies of the full results are sitting in memory simultaneously -- @$sql_row, @$ods_content, and $content.
Comment 1 Katrin Fischer 2019-09-27 17:39:58 UTC
Hi Paul, can you share why you closed INVALID?
Comment 2 Paul Hoffman 2019-09-27 17:44:16 UTC
(In reply to Katrin Fischer from comment #1)
> Hi Paul, can you share why you closed INVALID?

I tweaked bug #23626 (memory consumption related to the charting feature) to encompass this problem, because they involve the same files and the same underlying problem -- running a report and then doing something with the results (charting or exporting) potentially consumes all available memory.

Maybe I should post a comment there with the details from this ticket?
Comment 3 Katrin Fischer 2019-09-28 06:25:27 UTC
(In reply to Paul Hoffman from comment #2)
> (In reply to Katrin Fischer from comment #1)
> > Hi Paul, can you share why you closed INVALID?
> 
> I tweaked bug #23626 (memory consumption related to the charting feature) to
> encompass this problem, because they involve the same files and the same
> underlying problem -- running a report and then doing something with the
> results (charting or exporting) potentially consumes all available memory.
> 
> Maybe I should post a comment there with the details from this ticket?

You could use 'mark as duplicate' or choose 'RESOLVED MOVED' with a comment - that would link the bugs and make this clearer to people researching bugs later. Also if you leave out the # bugzilla will show a link: bug 23626
Comment 4 Paul Hoffman 2019-10-04 14:03:40 UTC
I'm reopening this as suggested by Nick Clemens in bug 23626.
Comment 5 Didier Gautheron 2020-05-26 11:16:13 UTC
What were the rationals for using a big string rather than writing directly to SDTOUT or a temporary file?

Are theses assumptions still valid?
Comment 6 David Cook 2021-02-22 23:42:14 UTC
(In reply to Didier Gautheron from comment #5)
> What were the rationals for using a big string rather than writing directly
> to SDTOUT or a temporary file?
> 
> Are theses assumptions still valid?

It looks like it used to print out to STDOUT but it was changed in Bug 11679.

After reviewing the code, I'd say it was probably a desire to make the code more readable/easier to maintain. However, it does create this performance problem.

Fixing the "tab" and "csv" export should be fairly trivial, but the ODS will be harder since it's a more complex file format (ZIP compressed XML). 

I'll write another comment about that...
Comment 7 David Cook 2021-02-23 00:56:12 UTC
Wow looking at OpenOffice::OODoc... it hasn't been updated in over 10 years. It's amazing that it still works.

Excel::Writer::XLSX has an interesting little write up on the topic of memory usage (see https://metacpan.org/pod/Excel::Writer::XLSX#SPEED-AND-MEMORY-USAGE). 

It looks like OpenOffice::OODoc uses Archive::Zip and Archive::Zip doesn't seem to be able to stream to output...

It looks like Excel::Writer::XLSX manages memory by writing worksheets out as temporary files (https://metacpan.org/pod/Excel::Writer::XLSX#set_tempdir()). 

Of course Excel::Writer::XLSX still adds every one of those temporary files into memory when it's saving the workbook, so it would still have a memory spike.
Comment 8 David Cook 2021-02-23 01:16:56 UTC
Rewriting OpenOffice::OODoc is not really an option, but that would be the most "correct" solution I imagine.

However, realistically, we could be more optimal in our current use of OpenOffice::OODoc. As Paul mentions, it makes no sense to do a $sth->fetchall_arrayref(), plus building @$ods_content, and then having an in-memory representation in OpenOffice::OODoc. That's 3x more memory usage than we need to use.

We should just use something like $sth->fetchrow_hashref or $sth->fetchrow_arrayref and feed OpenOffice::OODoc row by row. 

--

Note too that the ODS format will have proxy issues for large exports, because guided_reports.pl will write the ZIP to disk, read the ZIP from disk, write to STDOUT, and then Plack::App::CGIBin will buffer STDOUT in a temporary file on disk, and then it will send the whole response all at once to the Apache reverse proxy.
Comment 9 David Cook 2021-02-23 01:23:33 UTC
An alternative solution might be to write a CSV file to a temporary file and then use LibreOffice's CLI tools to convert from CSV to ODS.

Example:

soffice --convert-to ods koha.csv --headless

I haven't tried that though, so I can't speak to its performance. I seem to recall Indranil saying OpenOffice or LibreOffice had some memory usage issues for large spreadsheets. It might not be any better. Plus, it would add a large dependency to Koha for just 1 thing.

I think that's all I have for ideas though.

I think the ODS format is just problematic - at least with the tools that we have to hand.
Comment 10 Liz Rea 2024-04-02 19:27:16 UTC
Just a note here to say that this is still a problem - perhaps we should only support CSV and TAB for exports?
Comment 11 Katrin Fischer 2024-04-02 19:32:14 UTC
(In reply to Liz Rea from comment #10)
> Just a note here to say that this is still a problem - perhaps we should
> only support CSV and TAB for exports?

The .ods format is what we use most as CSV is not recognized by Excel as utf8 and so umlauts are broken. You have to separately import the data which is a lot of extra steps. I'd really really like to keep it.
Comment 12 Aleisha Amohia 2024-05-09 23:43:46 UTC
Just noting that this has caused a bunch of OOMs for our libraries this week, not sure why it's happening all of a sudden but this is a real problem for us. Keen to collaborate on a solution!
Comment 13 Aleisha Amohia 2024-06-10 20:05:25 UTC
We've received sponsorship to try and fix the ODS export format.

Any ideas are welcome, we're working on this now
Comment 14 David Cook 2024-06-11 00:22:01 UTC
(In reply to Aleisha Amohia from comment #13)
> We've received sponsorship to try and fix the ODS export format.
> 
> Any ideas are welcome, we're working on this now

You'll want to go with a streaming response for all formats. Instead of putting everything into $content, you'll want to print to STDOUT line by line to save memory. 

However, I don't know why I didn't mention it before, but even if you tried to do a streaming response where each row was written out to STDOUT one by one, it won't work as expected because Koha uses Plack::App::CGIBin. You can see fully explanations in bug 8437 and bug 26791 in my comments there. Basically, the whole response gets buffered in a temporary file before it's sent to the Apache proxy to send back to the client. 

Plus, for ODS, you'll have to wait for the content of generate_ods as well. (If you want to see a possible cheeky solution for that, you can check out bug 31744. Not sure if it'll work for ODS like it works for CSV though.)

Anyway, just some thoughts...
Comment 15 Aleisha Amohia 2024-06-26 02:48:19 UTC
Created attachment 168116 [details] [review]
Bug 23685: Don't store ODS content in a variable to save memory

Just attaching this to start.

Sponsored-by: Waikato Institute of Technology
Comment 16 Aleisha Amohia 2024-06-26 02:49:15 UTC
I don't know the kind of report I need to test crashing the system when I export to ODS. Any ideas?

I've just avoided adding to a $content variable as a start and can confirm it works, but doesn't address the Plack problem that David Cook raised. Not sure the best way around that.
Comment 17 David Cook 2024-06-28 03:27:28 UTC
(In reply to Aleisha Amohia from comment #16)
> I don't know the kind of report I need to test crashing the system when I
> export to ODS. Any ideas?

Try something like this:

SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000;

I think it should make an enormous number of rows, so you can play with the limit however you like. 

> I've just avoided adding to a $content variable as a start and can confirm
> it works, but doesn't address the Plack problem that David Cook raised. Not
> sure the best way around that.

Yeah it's a hard one. For now, the best bet is probably to avoid using Plack for this. It sucks, but it's the only practical solution I can think of.

There just isn't enough community support to move forward on a Mojolicious/Plack-based controller solution yet. I've tried a few times to progress it, but it's too much for just 1 person.
Comment 18 Aleisha Amohia 2024-07-06 00:44:10 UTC
Created attachment 168570 [details] [review]
Bug 23685: Exclude guided_reports.pl from plack

When attempting to export reports in ODS format from Koha, plack can time out

Excluding the script from plack is a simple fix until we have a more permanent fix for this
issue.

To test:
1. Create a report which will generate an enormous number of rows, such as SELECT biblio.* FROM biblio CROSS JOIN biblio b; (add a limit of 10000 or something if you like i.e. SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000;)
2. Run the report
3. Apply patch, copy changes to /etc/koha/apache-shared-intranet-plack.conf
4. Restart all the things
5. Download the results in ODS format
6. Confirm the export works as expected

Sponsored-by: Waikato Institute of Technology
Comment 19 David Nind 2024-07-07 06:25:28 UTC
Created attachment 168577 [details] [review]
Bug 23685: Exclude guided_reports.pl from plack

When attempting to export reports in ODS format from Koha, plack can time out

Excluding the script from plack is a simple fix until we have a more permanent fix for this
issue.

To test:
1. Create a report which will generate an enormous number of rows, such as SELECT biblio.* FROM biblio CROSS JOIN biblio b; (add a limit of 10000 or something if you like i.e. SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000;)
2. Run the report
3. Apply patch, copy changes to /etc/koha/apache-shared-intranet-plack.conf
4. Restart all the things
5. Download the results in ODS format
6. Confirm the export works as expected

Sponsored-by: Waikato Institute of Technology
Signed-off-by: David Nind <david@davidnind.com>
Comment 20 Marcel de Rooy 2024-08-02 09:44:55 UTC
diff --git a/debian/templates/apache-shared-intranet-plack.conf b/debian/templates/apache-shared-intranet-plack.conf
index bcee399443..6fa4014929 100644
--- a/debian/templates/apache-shared-intranet-plack.conf
+++ b/debian/templates/apache-shared-intranet-plack.conf
@@ -14,6 +14,7 @@
         ProxyPass "/cgi-bin/koha/tools/export.pl" "!"
         ProxyPass "/cgi-bin/koha/tools/upload-cover-image.pl" "!"
         ProxyPass "/cgi-bin/koha/svc/cataloguing/metasearch" "!"
+        ProxyPass "/cgi-bin/koha/reports/guided_reports.pl" "!"

Can understand this choice. But it takes years to get them out of this list again? We had quite a bunch when we started with Plack in 2015 ?

commit 4adc4ee5f8d3e113b62094552be6c80942289c3e
Author: Tomás Cohen Arazi <tomascohen@theke.io>
Date:   Wed Mar 4 00:19:40 2015 +0100
    Bug 13791: Plack out-of-the-box support on packages
Comment 21 Marcel de Rooy 2024-08-02 09:49:11 UTC
(In reply to Katrin Fischer from comment #11)
> The .ods format is what we use most as CSV is not recognized by Excel as
> utf8 and so umlauts are broken. You have to separately import the data which
> is a lot of extra steps. I'd really really like to keep it.

I guess that it takes a few steps more to import in Excel correctly, but that should really be possible. Do not remember any details, but you should be able to google it :)
Comment 22 Marcel de Rooy 2024-08-02 09:53:16 UTC
Thinking about workarounds.. Cant we put some user defined limit on e.g. number of records to report / download ?
Removing ODS is a serious option too.

We need some more input here. (Moving to In Discussion wont attract many people..)
Comment 23 Marcel de Rooy 2024-08-02 09:53:42 UTC
Moving to Major
Comment 24 Katrin Fischer 2024-08-02 09:55:53 UTC
(In reply to Marcel de Rooy from comment #21)
> (In reply to Katrin Fischer from comment #11)
> > The .ods format is what we use most as CSV is not recognized by Excel as
> > utf8 and so umlauts are broken. You have to separately import the data which
> > is a lot of extra steps. I'd really really like to keep it.
> 
> I guess that it takes a few steps more to import in Excel correctly, but
> that should really be possible. Do not remember any details, but you should
> be able to google it :)

I can do it - but it's really hard to explain and train, while .ods just works fine with both.
Comment 25 Katrin Fischer 2024-08-02 09:58:49 UTC
I am quite against removing .ods as it was such an improvement for us. Our reports usually contain umlauts and with .cvs and Excel we always got reports about broken umlauts and it's really tedious to import the data correctly (many more clicks). On the other hand we didn't experience the serious issues this bug is hoping to fix, maybe because the exports are usually quite small in comparison.

I think hardcoding a limit would be totally acceptable for us if there is no better way to fix.
Comment 26 Aleisha Amohia 2024-08-04 21:27:31 UTC
Thanks all for the input. What should the limit be?
Comment 27 David Cook 2024-08-04 23:20:53 UTC
As an aside, another option would be to rewrite reports/guided_reports.pl to use a Plack/Mojolicious handler. 

Easier said than done although a lot of the core work needed is already prepared in bug 31380.

At some point, surely we need to stop using Plack::App::CGIBin and move into a real Plack/Mojo world. We can do it incrementally, but it needs to be done at some point...
Comment 28 Mathieu Saby 2024-09-04 13:04:15 UTC
We suffered from this bug recently, but please do not remove ODS !
Comment 29 Marcel de Rooy 2024-09-06 07:54:51 UTC
Coming back here, we got some feedback :) Thx for that.
Comment 30 Marcel de Rooy 2024-09-06 13:11:09 UTC
Created attachment 171136 [details] [review]
Bug 23685: (follow-up) Control ODS exporting

The pref ReportingAllowsODS_Export should be true for exporting
to ODS in Reporting.

Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 31 Marcel de Rooy 2024-09-06 13:12:32 UTC
Created attachment 171137 [details] [review]
Bug 23685: (follow-up) Dbrev for new preferences

Test plan:
Run updatedatabase.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 32 Marcel de Rooy 2024-09-06 13:12:34 UTC
Created attachment 171138 [details] [review]
Bug 23685: (follow-up) Add prefs to Administration form

Test plan:
Edit both prefs.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 33 Marcel de Rooy 2024-09-06 13:12:37 UTC
Created attachment 171139 [details] [review]
Bug 23685: (follow-up) Add export limit for guided reports

Test plan:
Set pref GuidedReportsExportLimit to some positive integer.
Test if exporting a report respects that limit.
Run t/db_dependent/Koha/Reports.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 34 Marcel de Rooy 2024-09-06 13:12:39 UTC
Created attachment 171140 [details] [review]
Bug 23685: (follow-up) Control ODS exporting

The pref ReportingAllowsODS_Export should be true for exporting
to ODS in Reporting.

Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 35 Marcel de Rooy 2024-09-06 13:16:44 UTC
Created attachment 171141 [details] [review]
Bug 23685: Don't store ODS content in a variable to save memory

Just attaching this to start.

Sponsored-by: Waikato Institute of Technology

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 36 Marcel de Rooy 2024-09-06 13:16:47 UTC
Created attachment 171142 [details] [review]
Bug 23685: (follow-up) Dbrev for new preferences

Test plan:
Run updatedatabase.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 37 Marcel de Rooy 2024-09-06 13:16:49 UTC
Created attachment 171143 [details] [review]
Bug 23685: (follow-up) Add prefs to Administration form

Test plan:
Edit both prefs.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 38 Marcel de Rooy 2024-09-06 13:16:52 UTC
Created attachment 171144 [details] [review]
Bug 23685: (follow-up) Add export limit for guided reports

Test plan:
Set pref GuidedReportsExportLimit to some positive integer.
Test if exporting a report respects that limit.
Run t/db_dependent/Koha/Reports.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 39 Marcel de Rooy 2024-09-06 13:16:54 UTC
Created attachment 171145 [details] [review]
Bug 23685: (follow-up) Control ODS exporting

The pref ReportingAllowsODS_Export should be true for exporting
to ODS in Reporting.

Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 40 Marcel de Rooy 2024-09-06 13:19:27 UTC
Hi Aleisha, since this adds four patches to your initial ones, please have a look if you agree (functionally and QA wise) with this approach. It is meant to allow flexibility as indicated in the feedback received.
If you add your signoff line to the follow-ups, I will pass QA if nobody beats me to it.
Comment 41 Marcel de Rooy 2024-09-06 13:21:52 UTC
QA note:
 FAIL   installer/data/mysql/mandatory/sysprefs.sql
   FAIL   sysprefs_order
                Not blocker: Sysprefs IntranetAddMastheadLibraryPulldown is bad placed (see bug 10610)

This warning shows that another report did a wrong insert. Not here :)
Comment 42 Katrin Fischer 2024-09-06 14:50:26 UTC
Might be controversial... but our system preferences usually have no underscores, could we remove the one here? ReportingAllowsODS_Export
Comment 43 Aleisha Amohia 2024-09-08 20:54:07 UTC
(In reply to Katrin Fischer from comment #42)
> Might be controversial... but our system preferences usually have no
> underscores, could we remove the one here? ReportingAllowsODS_Export

I agree - how about we call preference ExportFormatODS?

Also I noticed that patches saying "Reporting" to refer to the module, I think this should just be "Reports" as that's the name of the module button.

Finally, there's a typo in the DBrev patches - "Reporing"
Comment 44 Marcel de Rooy 2024-09-09 08:23:51 UTC
Created attachment 171171 [details] [review]
Bug 23685: Don't store ODS content in a variable to save memory

Just attaching this to start.

Sponsored-by: Waikato Institute of Technology

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 45 Marcel de Rooy 2024-09-09 08:23:54 UTC
Created attachment 171172 [details] [review]
Bug 23685: (follow-up) Dbrev for new preferences

Test plan:
Run updatedatabase.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 46 Marcel de Rooy 2024-09-09 08:23:56 UTC
Created attachment 171173 [details] [review]
Bug 23685: (follow-up) Add prefs to Administration form

Test plan:
Edit both prefs.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 47 Marcel de Rooy 2024-09-09 08:23:59 UTC
Created attachment 171174 [details] [review]
Bug 23685: (follow-up) Add export limit for guided reports

Test plan:
Set pref ReportsExportLimit to some positive integer.
Test if exporting a report respects that limit.
Run t/db_dependent/Koha/Reports.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 48 Marcel de Rooy 2024-09-09 08:24:01 UTC
Created attachment 171175 [details] [review]
Bug 23685: (follow-up) Control ODS exporting

The pref ReportsExportFormatODS should be true for exporting
to ODS in Reporting.

Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 49 Marcel de Rooy 2024-09-09 08:29:53 UTC
(In reply to Aleisha Amohia from comment #43)
> (In reply to Katrin Fischer from comment #42)
> > Might be controversial... but our system preferences usually have no
> > underscores, could we remove the one here? ReportingAllowsODS_Export
> 
> I agree - how about we call preference ExportFormatODS?

OK. Added prefix Reports to add context.

> Also I noticed that patches saying "Reporting" to refer to the module, I
> think this should just be "Reports" as that's the name of the module button.

Fixed

> Finally, there's a typo in the DBrev patches - "Reporing"

Fixed
Comment 50 Nick Clemens (kidclamp) 2024-09-16 17:47:40 UTC
Created attachment 171571 [details] [review]
Bug 23685: Don't store ODS content in a variable to save memory

Just attaching this to start.

Sponsored-by: Waikato Institute of Technology

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 51 Nick Clemens (kidclamp) 2024-09-16 17:47:43 UTC
Created attachment 171572 [details] [review]
Bug 23685: (follow-up) Dbrev for new preferences

Test plan:
Run updatedatabase.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 52 Nick Clemens (kidclamp) 2024-09-16 17:47:45 UTC
Created attachment 171573 [details] [review]
Bug 23685: (follow-up) Add prefs to Administration form

Test plan:
Edit both prefs.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 53 Nick Clemens (kidclamp) 2024-09-16 17:47:48 UTC
Created attachment 171574 [details] [review]
Bug 23685: (follow-up) Add export limit for guided reports

Test plan:
Set pref ReportsExportLimit to some positive integer.
Test if exporting a report respects that limit.
Run t/db_dependent/Koha/Reports.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 54 Nick Clemens (kidclamp) 2024-09-16 17:47:50 UTC
Created attachment 171575 [details] [review]
Bug 23685: (follow-up) Control ODS exporting

The pref ReportsExportFormatODS should be true for exporting
to ODS in Reporting.

Test plan:
Set pref to false value.
Check if option is no longer available on report's Download menu.
Manipulate URL with op=export&format=ods. No data expected.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 55 Nick Clemens (kidclamp) 2024-09-16 17:49:41 UTC
Old links to ODS exports will fail with a 500 if the pref is off, would be nice to have a warning, even just in the logs, of what happened, but not a blocker here.

The patch to exclude from plack was dropped, but that seems appropriate - systems can implement it locally if needed
Comment 56 Martin Renvoize (ashimema) 2024-09-20 12:58:18 UTC
This inspired me to work on bug 33635 - CSV export display broken diacritics in Excel

I had a feeling we were just missing the BOM segment on our csv's and it looks like that was indeed the case.

Well worth looking at that one too..
Comment 57 Katrin Fischer 2024-09-27 14:44:17 UTC
(In reply to Nick Clemens (kidclamp) from comment #55)
> Old links to ODS exports will fail with a 500 if the pref is off, would be
> nice to have a warning, even just in the logs, of what happened, but not a
> blocker here.
> 
> The patch to exclude from plack was dropped, but that seems appropriate -
> systems can implement it locally if needed

Could you explain a bit more about when a library would want to implement this?
Comment 58 Katrin Fischer 2024-09-27 17:41:16 UTC
Pushed for 24.11!

Well done everyone, thank you!
Comment 59 David Cook 2024-09-30 01:37:39 UTC
(In reply to Katrin Fischer from comment #57)
> (In reply to Nick Clemens (kidclamp) from comment #55)
> > Old links to ODS exports will fail with a 500 if the pref is off, would be
> > nice to have a warning, even just in the logs, of what happened, but not a
> > blocker here.
> > 
> > The patch to exclude from plack was dropped, but that seems appropriate -
> > systems can implement it locally if needed
> 
> Could you explain a bit more about when a library would want to implement
> this?

So a library could exclude from Plack if they want to have a large export of all the rows. 

Plack buffers the whole response in a temporary file before sending any data back to Apache, which can cause timeouts. Whereas CGI/excluded from Plack will theoretically send the data back immediately to Apache (for non-ODS exports), although I think in practice it's a bit more complicated. See bug 31744 for an inventory.pl example. 

So it would be addressing a timeout problem rather than a memory problem. 

Maybe before Kohacon2025 I can keep working on my Mojolicious controller replacement for CGI scripts which solve the timeout problem without excluding from Plack. 

(The ODS export is a problem regardless because of the compressed nature of the ODS format and how OpenOffice::OODoc works.)
Comment 60 Lucas Gass (lukeg) 2024-11-18 22:12:02 UTC
Doesn't apply clean to 24.05.x, rebase if needed.
Comment 61 Tomás Cohen Arazi (tcohen) 2024-12-19 18:34:43 UTC
Why didn't we make it a dropdown with multi-select for the available formats instead? With a warning about ODS being dangerous?