Export to Excel takes crazy RAM? (possible endless loop) [#154901]

Hi guys,

I've got a site where the client is trying to export the survey results to excel, but it's failing. The error from the HTTPD logs is:

[Wed Jun 27 10:56:48 2007] [error] [client 218.185.73.2] PHP Fatal error: Allowed memory size of 104857600 bytes exhausted (tried to allocate 40 bytes) in /home/httpd/html/drupal/includes/common.inc on line 540, referer: http://mydomain.com/node/35/responses

Yep, that's right, it's using 100 meg of RAM. Isn't this a little... excessive?

And before you ask, there's only 116 surveys with about 10 answers per entry. Not the sort of load you'd expect to eat up 100MB of RAM! I've checked out the database manually, it all seems pretty normal, so... any ideas?

Comments

Comment #1

codewatson commented 18 July 2007 at 14:22

I get the same, and there are only 2 responses..... 50M limit...

Comment #2

codewatson commented 18 July 2007 at 14:32

forgot to mention, it happens in the module file, not in includes, and the strange thing is, if you read the error, its not actualy trying to use all that memory, its using less than 100 bytes.. it only uses 14 bytes for me..

Comment #3

codewatson commented 18 July 2007 at 15:22

Title:	Export to Excel takes crazy RAM?	» Export to Excel takes crazy RAM? (possible endless loop)
Priority:	Normal	» Critical

After looking at the code, i discovered this in the survey_excel function on line 414:

while ($field->ffid != $response->ffid) {
  $row[] = '';
  $field = array_shift($fields);
}

Not sure what that was supposed to do, but that seems to be causing the problems, if you comment out that code, it seems to work ok. My only guess is that statement is causing an endless loop? I am going to keep looking into it, as a site i'm working on could really use this module.

Comment #4

themselves commented 31 July 2007 at 23:41

Yep, I traced the issue down to that as well. What I did to get around is replace this:

    $field = array_shift($fields);
    while ($field->ffid != $response->ffid) {
      $row[] = '';
      $field = array_shift($fields);
    }

with this...

    $i = 0;
    $field = array_shift($fields);
    while ($field->ffid != $response->ffid) {
        #resetting the $fields array.
        if ($i == 0) {
                $fields = $survey->form->fields;
                $i++;
                $row[] = '';
        }
        $field = array_shift($fields);
    }

it allows the loop to get past whatever it is that is killing it, and outputs a slightly broken excel that can be manually fixed in just a few seconds.

I never actually resolved what was breaking the loop. The code is pretty straight forward, it's meant to insert a blank record in to the array wherever there isn't a submitted value in the survey, it's just it seems there are cases that can break it. Perhaps it's something to do with the order of values in the database not matching what is expected or something. I really don't know. Perhaps the module author can shed some light on this situation? In the meanwhile though, the above solution lets you hack around it to at least export the data, you can always revert the code to the original after you've subsequently purged it.

Comment #5

neopoet commented 12 November 2007 at 19:38

The #4 solution also works for me.

However it causes some problems-- Excel reports that the downloaded file is not in the appropriate format. And if a submitter leaves a value blank, then their responses don't match up to the column descriptions. This means that the resulting Excel file is all but useless-- it's impossible to tabulate results because columns don't match to values.

Comment #6

dharmatech commented 13 December 2007 at 20:27

The basic problem is in survey_excel(), where a loop matches the fields on the survey form, as represented in $fields[], with the actual responses in the database. The match works by comparing the ffid value for each element in $fields[] with the ffid value in the $response object, and generating a blank column in the output spreadsheet for any element in $fields[] which does not have a matching element in $response.

This logic works on the assumption that the elements in $fields[] and the elements in $response are ordered by ffid. Unfortunately, there is no code to guarantee this. The fix is to sort $fields[] by ffid and also retrieve the responses ordered by rid,ffid. It would also be good to terminate the loop when there are no elements left, rather than looping infinitely.

If the maintainer doesn't get a patch out to fix this I'll try to find time to do it.

-- Walt

Comment #7

mandclu commented 11 January 2008 at 19:08

I ran into a similar issue, and decided to change the structure around. Here's the function I ended up with:

function survey_excel() {
  $survey = node_load(array('nid' => arg(1)));
  if (!$survey->nid) {
    drupal_not_found();
  }
  $res = db_query("SELECT u.name, r.*, f.* FROM {survey_responses} r INNER JOIN {survey_fields} f ON f.rid=r.rid INNER JOIN {form_fields} ff ON ff.ffid=f.ffid LEFT JOIN {users} u ON r.uid=u.uid WHERE r.nid=%d ORDER BY r.rid ASC, ff.weight ASC, ff.title ASC", $survey->nid);

  $header = array(t('Submitted by'), t('Date'));
  
  $field_structure['name'] = '';
  $field_structure['date'] = '';
  foreach ($survey->form->fields as $field) {
    $header[] = $field->title;
    $field_structure[$field->ffid] = '';
  }

  $rows = array();
  $row = array();
  $rid = 0;
  while ($response = db_fetch_object($res)) {
    // first data cell for this row, set format and first two cells
    if (!isset($data[$response->rid])) {
      $data[$response->rid] = $field_structure;
      $data[$response->rid]['date'] = format_date($response->created);
      if ($response->name) {
        $name = $response->name;
      } else {
        $name = variable_get("anonymous", "Anonymous");
      }
      $data[$response->rid]['name'] = $name;
    }
    // populate the current cell
    $data[$response->rid][$response->ffid] = $response->value;
  }
  header('Content-type: application/vnd.ms-excel');
  header('Content-Disposition: attachment; filename="'.$survey->title.' Results '.date('Y-m-d').'.xls"');
  $output = '<html>';
  $output .= '<head><meta http-equiv=Content-Type content="text/html; charset=utf-8"></head>';
  $output .= '<body>';
  $output .= theme('table', $header, $data);
  $output .= "</body></html>";

  print $output;
  exit;
}

AFAIK this alleviates the issues with looping endlessly, and the columns not lining up. It should be a little faster, too, since it doesn't loop though the $fields array for every cell.