Batch timeout issue

Is there anything in the MySQL server logs, is the server actually dying during the process, or is the connection just being cut mid-export?

I ran into this problem of 500 error mysql has gone away. OSX doesnt have msyql logging enabled by default so I dont have a logfile error handy. I could try to recreate if you want. I did save the ajax-popup window error to a 1.5Mb text file.

In my opinion, I see no place to set the Batch API chunk size per view export. My data is a courses database for departments at a UC University. They have descriptions and everything in this table for courses and its largeish for HTTP POST traffic. Being able to tweak the chunk size via the Views UI or programmitcally via an alter hook would be nice.

I was able to get my query to run against 1,972 records with mysql reporting a table size of basically 2.1Mb that views is trying to batch.

Views statistics reported:

Query build time    8.55 ms
Query execute time    78.19 ms
View render time    16090.64 ms

My quick fix to this problem was the following (I was also hitting the 30 sec max Views time limit, AFTER adjusting the packet size):

function courses_db_views_query_alter(&$view, &$query) {
  if ($view->name == 'dept_data_exporter' && $view->current_display == 'views_data_export_2') {
    Database::getConnection()->query("SET @max_allowed_packet=16*1024*1024");
    set_time_limit(5 * 60);
  }
}

A limited set of staff will use this functionality so this fix works for me. I can try to create a patch that allows anyone to set a "batch size chunk" ... hell even down to chunk_size = 1 .... since raw field data size is not dictated by batch chunk size ...

Thanks for this helpful module.

Followup -- actually this only fixes the Admin UI screen believe it or not. I'm not sure why but mysql still goes away if i'm not on the Views Admin UI. I will try to diff the queries from the 2 pages from mysqld.log later today ...

It should help:

edit the my.ini (my.cfg) file

[mysqld]
max_allowed_packet=64M

I see this error on drush as well. The link below makes a suggestion of upping the max packet allowed as well.

http://drupal.org/node/1014172

This only hides the problem though since eventually my export got large enough to need another increase. What in VDE is causing so large amount of data to be written to the db? If it is the export, could this be stored temporally elsewhere, like in a file instead which is better at holding increasing large amounts of data?

Kind regards

John

Number 5 is not quite correct, though max_allowed_packet needs to be high (100M about) another issue can also cause the MySQL db to go away.

When dealing with very large amounts of data the batching process can take several minutes to complete. On my site I have some 2,000 nodes being exported to xml and the process can take 5 - 6 minutes. When this happens, the XML is generated correctly, but when control passes back to the originating drush script this exception is thrown. As far as I can tell, the originating drush script is in a process that has been idle so long MySQL has closed the connection.

It is possible to insert the following command at the start of drush_views_data_export() ...
db_query('SET wait_timeout = 600');

This holds the connection open for 10 minutes (assuming MySQL db) and the exception then no longer occurs as long as the complete operation completes in less than 10 minutes.

This doesn't feel like a long term fix and also probably isn;t something views_data_export can fix, it feels more likely an issue with drush batching process.

Would be good to get your thoughts and possibly comment on this issue I found which I think is describing the same issue:
http://drupal.org/node/587250

Kind regards,
John

Sounds like the max_allowed_packet in MySQL isn't high enough for Views Data Export.

A note for anyone else who may run in to an issue like this : you may want also find this solution helpful, as it fixed the problems I was having :

#1782038: Configurable temporary file location