View Issue Details

This bug affects 1 person(s).
 8
IDProjectCategoryView StatusLast Update
17409Bug reportsImport/Exportpublic2021-09-06 10:59
ReporterMazi Assigned Top_teichmann  
PriorityhighSeverityblock 
Status closedResolutionfixed 
Product Version4.4.12 
Summary17409: Exporting 3000 encrypted results (200 questions) to Excel requires too many ressources, export fails
Description

A custmer is running a 200 question survey and tries to export 3000 responses. This fals due to insufficient ressources (e.g. memory_limit setting). Exporting 50 data sets is doable but beyond that the export process takes too many ressources to complete and it fails with a 500 server error.

Exporting that amount of ressources seems to be working fine if data is not enctpted so my guess is that the decryption is consuming too many ressources and needs to be optimized. Because 3000 responses at a medical o market research survey is not much.

Steps To Reproduce

Search for a test survey with many questions and many encrypted responses.
Run the Excel export with a low "memory_limit" setting.

It will probably be tricky to find the bottleneck. If data will be treated with care, the customer may be able to provide the DB and decryption keys for testing. Please let us know how we can provide that.

TagsNo tags attached.
Bug heat8
Complete LimeSurvey version number (& build)4.4.12
I will donate to the project if issue is resolvedNo
Browser
Database type & versionmysql Ver 15.1 Distrib 10.2.36-MariaDB
Server OS (if known)SUSE SLES 15 SP 2
Webserver software & version (if known)
PHP Version7.4.6

Relationships

related to 17126 closedp_teichmann encrypted questions do not work when multiple groups are used 

Users monitoring this issue

galads

Activities

ollehar

ollehar

2021-06-28 10:41

administrator   ~65113

low "memory_limit" setting.

How low?

Mazi

Mazi

2021-06-28 10:52

updater   ~65114

php.ini check for setting "memory_limit" and try e.g. 128M or just 64M

ollehar

ollehar

2021-06-28 11:27

administrator   ~65115

OK, but is that a problem? 512M is a pretty normal setting by now.

ollehar

ollehar

2021-06-28 11:27

administrator   ~65116

Question is if we can handle 800 questions with 100k answers. We need a proper stress-test to figure out the limits of the system.

ollehar

ollehar

2021-06-28 11:35

administrator   ~65117

So possible subtask: Create a command to generate random responses for any survey.

Mazi

Mazi

2021-06-28 11:39

updater   ~65118

They have copied the DB to a new laptop and have significantly raised the limits in order to export the data. I think even with 1024M they falied exporting the 3000 data sets. I have asked them to prepare a DB dump and they will make it available for download later. I will email you and Patrick the download links once available.

Of course, you can not support super-large data sets if there are no sufficient ressources, but still with 1024M they can not export 3000 responses which is not much. > 20% of our projects have more responses and >5-10% come with > 10000 data sets. So we need to optimize the export if possible.

ollehar

ollehar

2021-06-28 11:41

administrator   ~65119

Yes, that's not good. :) Thanks for reporting. I did nag the original implementer (Dominik) to stress test it in different ways, but he quit before.

Mazi

Mazi

2021-07-06 11:11

updater   ~65228

@ollehar, please test the export with the DB details provided by the customer. Can you reproduce?

p_teichmann

p_teichmann

2021-07-06 12:53

administrator   ~65231

We have already reproduced and fixed it, its currently being tested

Mazi

Mazi

2021-07-07 16:40

updater   ~65247

Happy to hear that, @p_teichmann. Looking forward to see the fix at the next release so we can test that one.

Mazi

Mazi

2021-07-13 23:23

updater   ~65433

@p_teichmann, it looks like the fix is not yet available at yesterday's release (accoridng to https://github.com/LimeSurvey/LimeSurvey/blob/master/docs/release_notes.txt)? What a pity, we really need this to export data of our current project!

I also couldn't find a potential fix at the development branch so I assume this was fixed locally only? Would it be possible to provide the fix so we can test as well?

Mazi

Mazi

2021-07-14 00:42

updater   ~65434

Potential fix is available at https://github.com/LimeSurvey/LimeSurvey/commit/b5cac3393e06d6b828e55fafd386322b83e58507

ollehar

ollehar

2021-07-21 16:10

administrator   ~65536

Still working on QA test...

ollehar

ollehar

2021-07-21 16:13

administrator   ~65537

@Mazi Did you test this fix? It's branch bug/L41-T891_Exporting_3000_encryptedresults(200_questions)_to_Excel_requires_too_many_ressources,_export_fails, as you linked.

Mazi

Mazi

2021-07-21 19:53

updater   ~65563

@ollehar, we tried but with no success. But the reaosn is not the fix I guess but the bug at https://bugs.limesurvey.org/view.php?id=17126 having caused the data to be encrypted multi times.

ollehar

ollehar

2021-07-22 08:21

administrator   ~65564

Ok? We have a database dump from you we're trying to test with. Did you use the same one?

Mazi

Mazi

2021-07-24 23:05

updater   ~65605

@ollehar, please contact @p_teichmann directly, he already worked on this with the details we provided.

p_teichmann

p_teichmann

2021-08-13 08:29

administrator   ~65957

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=32530

Related Changesets

LimeSurvey: master b72beace

2021-07-02 14:14:07

p_teichmann

Details Diff
Fixed issue 17409: Exporting responses with big data sets and activated encryption will timeout php memory limit, even with high settings (512M) Affected Issues
17409
mod - application/helpers/admin/export/SurveyDao.php Diff File
mod - application/helpers/admin/exportresults_helper.php Diff File

Issue History

Date Modified Username Field Change
2021-06-28 10:15 Mazi New Issue
2021-06-28 10:35 ollehar Priority none => urgent
2021-06-28 10:35 ollehar Sync to Zoho Project => |Yes|
2021-06-28 10:40 ollehar Assigned To => p_teichmann
2021-06-28 10:40 ollehar Status new => assigned
2021-06-28 10:41 ollehar Note Added: 65113
2021-06-28 10:41 ollehar Status assigned => feedback
2021-06-28 10:52 Mazi Note Added: 65114
2021-06-28 10:52 Mazi Status feedback => assigned
2021-06-28 11:27 ollehar Note Added: 65115
2021-06-28 11:27 ollehar Note Added: 65116
2021-06-28 11:35 ollehar Note Added: 65117
2021-06-28 11:39 Mazi Note Added: 65118
2021-06-28 11:41 ollehar Note Added: 65119
2021-07-06 11:11 Mazi Note Added: 65228
2021-07-06 12:53 p_teichmann Note Added: 65231
2021-07-07 16:40 Mazi Note Added: 65247
2021-07-13 23:23 Mazi Note Added: 65433
2021-07-14 00:42 Mazi Note Added: 65434
2021-07-14 13:16 Mazi Relationship added related to 17126
2021-07-21 16:10 ollehar Note Added: 65536
2021-07-21 16:13 ollehar Note Added: 65537
2021-07-21 16:18 ollehar Status assigned => ready for testing
2021-07-21 19:53 Mazi Note Added: 65563
2021-07-22 08:21 ollehar Note Added: 65564
2021-07-22 13:01 galads Issue Monitored: galads
2021-07-24 23:05 Mazi Note Added: 65605
2021-08-04 00:08 ollehar Priority urgent => high
2021-08-04 00:08 ollehar Sync to Zoho Project Yes => |Yes|
2021-08-13 08:29 p_teichmann Changeset attached => LimeSurvey master b72beace
2021-08-13 08:29 p_teichmann Note Added: 65957
2021-08-13 08:29 p_teichmann Resolution open => fixed
2021-08-30 14:01 galads Status ready for testing => resolved
2021-09-06 10:59 c_schmitz Status resolved => closed