1. 

   john, Member

   So i have a rather large report, takes a couple of minutes to run. It does output in html but fails when requesting xls. it may be a couple of hundred rows (likely between 100-300). The processing time is all mysql functions etc getting information not selecting millions of rows. There are only 14k row in our db.
   I haven't installed any of the graphing stuff in mydbr just that thing at the setup in order for mydbr to work - not sure if that makes any difference.

   The environment is a turnkey linux LAMP vm running in vmware player with 4 gigs of ram assigned. host pc is i5 with 16 gigs of ram. not the best choice but the mysql db is running as a windows service on the host machine.

   I did a bit of googling and tried changing the myphp memory in the LAMP and rebooting vm, but no effect. all other values in the LAMP are unchanged from defaults (what ever the turnkey appliance ships with)

   IN FILE
   /etc/php5/cli
   SET
   php_value memory_limit 2048M

   -
   a) Have tried clicking the download xls from the created report screen.
   b) Have tried changing the report information screen to choose output as xls.

   a) the error here is

   ( ! ) Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 16384 bytes) in /var/www/mydbr/lib/utils.php on line 4395
   Call Stack
   # Time Memory Function Location
   1 323.2002 33473600 ob_start ( ) ../utils.php:4395

   b)The error here is

   ( ! ) Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 72 bytes) in /var/www/mydbr/lib/external/phpExcel/PHPExcel/Style/Protection.php on line 272
   Call Stack
   # Time Memory Function Location
   1 203.7774 33261240 PHPExcel_Style_Alignment->setWrapText( ) ../export.php:556
   2 203.7774 33261904 PHPExcel_Style->applyFromArray( ) ../Alignment.php:377
   3 203.7775 33264448 PHPExcel_Style->__clone( ) ../Alignment.php:465
   4 203.7777 33286760 PHPExcel_Style_Protection->__clone( ) ../Alignment.php:673
   5 203.7777 33286808 get_object_vars ( ) ../Protection.php:272

   Report Code doesn't show much as it is selecting from an existing view. But here it is anyways. Note input value is not used in this code as i am modified an existing report.

   
   
   DROP PROCEDURE IF EXISTS sp_DBR12
   
   $$
   
   CREATE PROCEDURE sp_DBR12(project_name_in varchar (30))
   
   BEGIN
   
   select 'dbr.colstyle', 'Change Order', mydbr_style('redgreen01');
   
   select 'dbr.colstyle', 'Complete', mydbr_style('redgreen01');
   
   select 'dbr.hidecolumn', 1;
   
   select
   
                   c.*
   
   from test.costing5 c where c.projectname = 2014955 or c.projectname = 2014956 or c.projectname = 2014957 or c.projectname = 2014958 or c.projectname = 2014959;
   
   END
   
   $$
   
   
2. 

   john, Member

   Just to note other reports will download to xls
3. 

   myDBR Team, Key Master

   While the libary being used (PHPExcel) does require quite a lot memory, exporting couple of hundred rows should not be a problem. How many columns do you have? Could you check the memory_limit shown by phpinfo()?

   If you do need the formatting, the CSV export is very memory efficient.

   --
   myDBR Team
4. 

   john, Member

   Thanks for your response.

   My phpinfo memory limit shows 32M
   Looks like i need to find out where to change that value, although there are lots of places apparently to change php memory so this might be a challenge.

   I changed the report to output in CSV and received this error

   ( ! ) Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 64 bytes) in /var/www/mydbr/lib/external/phpExcel/PHPExcel/Style/Protection.php on line 272
   Call Stack
   # Time Memory Function Location
   1 206.7596 33485520 PHPExcel_Style_Alignment->setWrapText( ) ../export.php:556
   2 206.7597 33486152 PHPExcel_Style->applyFromArray( ) ../Alignment.php:377
   3 206.7597 33488664 PHPExcel_Style->__clone( ) ../Alignment.php:465
   4 206.7599 33510856 PHPExcel_Style_Protection->__clone( ) ../Alignment.php:673
   5 206.7599 33510904 get_object_vars ( ) ../Protection.php:272
5. 

   myDBR Team, Key Master

   Try to fix the memory limit first as it seems to be the culprit. Check the php.ini location from phpinfo() (Loaded Configuration File) so that you are editing the correct php.ini-file.

   --
   myDBR Team
6. 

   john, Member

   Summary=success, continue reading to find out how.

   Memory has been fixed, myphp info now reads
   memory_limit 2048M

   Now there is no error, but no download ever. Just stalls all connections on that pc to mydbr.
   mydbr is responsive to other pc connections

   I have a dev environment at home, exact same as production.
   Run the query from navicat or what ever, output in .78 seconds so it isn't the query.

   Result set is 35 columns

   I do have this old style i have yet to fix, wonder if this is it
   [background-color: green; color: green]true;[color:red]false ;
   Lets disable it

   html loads much quicker(1 second instead of 30).
   Different error on xls download, only takes 30 seconds to be thrown.

   Error result

   Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/mydbr/lib/external/phpExcel/PHPExcel/Style/Protection.php on line 641
   Call Stack
   # Time Memory Function Location
   1 30.9579 35527024 PHPExcel_Style_Alignment->setHorizontal( ) ../export.php:635
   2 30.9580 35527664 PHPExcel_Style->applyFromArray( ) ../Alignment.php:273
   3 30.9583 35551296 PHPExcel->getCellXfByHashCode( ) ../Style.php:468
   4 30.9583 35551296 PHPExcel_Style->getHashCode( ) ../PHPExcel.php:576

   Went back to the php configuration and changed the timeout to 300.
   2 minutes later
   Succesfull download

   Thanks for your help.
7. 

   john, Member

   That was my first style, trying to indicate true/false. I will have to look for some examples and rewrite, likely use icons.
   Wondering if icons are going to also cause failed xls creation, guess i will try it see.

   I know i saw an option disable download on a report, but can't recall seeing anything to disable xls and pdf and retain csv?

   Other notes
   pdf creation takes longer than xls (5 minutes)
   csv takes 5 seconds
   result set is 36 columns and 688 rows
   this is with offending style commented out in report code
   When i reduce the result set to 3 rows xls and pdf download is 2-3 seconds
   when i reduce the result set to 117 rows download is xls 20 seconds, and pdf 33 seconds
8. 

   myDBR Team, Key Master

   Make sure you have installed wkhtmltopdf which is used to create the PDF's.

   --
   myDBR Team

Reply

You must log in to post.