Empty file sent after 90s

Topics: User Forum
Sep 3, 2008 at 12:21 PM
Hello everyone,

I would like to put some values from a MySQL database into a Excel template file and then save this file. Here is the Excel 2007 template, I am using: http://www.sendspace.com/file/mozg3f.
I set up the php.ini file to 600s (max_execution_time). The server is working on it and stops after about 90s and send me an empty file.

Do you know where it can come from?

Thanks
Ludo

PS: IMPORTANT -> When there is less checks (from U55 to AF217), there is no problem. The file is correctly generated. Here can you found an example of this file: http://www.sendspace.com/file/zd5zy0
Developer
Sep 3, 2008 at 12:35 PM
Not sure... Could it be a memory limit problem?
Are you getting any warnings / errors with error_reporting(E_ALL) ?
Sep 3, 2008 at 12:45 PM
Yes, I wrote at the beginning of my PHP file error_reporting(E_ALL). No problem at all...
When I save the file instead of opening it, the file's weight is 8kb.
Sep 3, 2008 at 1:10 PM
Hi,

I set up the php.ini file like that:

max_execution_time = 600     ; Maximum execution time of each script, in seconds
max_input_time = 600    ; Maximum amount of time each script may spend parsing request data
memory_limit = 128M      ; Maximum amount of memory a script may consume (16MB)

Developer
Sep 3, 2008 at 1:26 PM
Hmm... I have no ideas, but here's what I would try.

With the scripts that are working, try to use memory_get_peak_usage at the end of the script to see how close you are to the limit. (Like in 05featuredemo.php). I think even generating small files may consume large amounts of memory.

// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

Double-check with phpinfo() that php.ini settings really are in place?

Try to create a simple, large xlsx spreadsheet directly from script without reading from MySQL, but simply inserting e.g. a couple of thousand rows with dummy text and see if you run into similar problems. This would rule out that there is some subtle MySQL setting preventing the script from working.
Sep 3, 2008 at 1:33 PM
I just make another test. The problem appears when saving. Here is my test php script: http://www.sendspace.com/file/5bfp9t

15:27:21start loading
15:27:35file loaded
15:27:35do not write (test)

After about 90s,  8kb are saved in the folder.
Is it possible that when there is too many things to write, the class just give up?


Can you try to reproduce the situation on your side? Thank you very much!
Ludo
Sep 3, 2008 at 1:39 PM
Edited Sep 3, 2008 at 2:52 PM
I used what you just wrote, here are the results:


15:36:17start loading
15:36:17 Peak memory usage: 6.25 MB
15:36:31file loaded
15:36:31 Peak memory usage: 52.75 MB
15:36:31do not write (test)
15:36:31 Peak memory usage: 52.75 MB

Edit:
1) I confirm that I modified the right php.ini file.
2) I commented all my PHP script concerning the MySQL and tried it again. Same result.
3) I create a new file as you said, with a lot of lines and data written in it. Same result.
4) I edited the template and delete some checks from it, it is saving without any problems... Does the Excel Writer have any limitation?
Sep 3, 2008 at 3:17 PM
When I launched the 06largescale.php file, everything went well!

But when I tried to load my template and use the same code as the test file 06largescale.php, here is what I got:

16:52:17 Create new PHPExcel object 16:52:17 Set properties 16:52:17 Add data 16:52:17 Hide "Phone" and "fax" column 16:52:17 Set outline levels 16:52:17 Freeze panes 16:52:17 Rows to repeat at top 16:52:19 Write to Excel2007 format
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 19392 bytes) in C:\xampp\htdocs\LMS1\strategic_database\Classes\PHPExcel\Calculation\FormulaParser.php on line 112

The second time, I tried I got that:
17:13:39 load template 17:13:50 Set properties 17:13:50 Add data 17:13:50 Hide "Phone" and "fax" column 17:13:50 Set outline levels 17:13:50 Freeze panes 17:13:50 Rows to repeat at top 17:13:52 Write to Excel2007 format


The file written in the specified folder is not readable (only 57kb instead of 162kb normally).
Developer
Sep 3, 2008 at 7:54 PM
Ok, I did some testing and confirmed the issue. It really looks like you need to increase the memory_limit.

I tried to do a plain read/write of the template and 128M was just barely enough. 96 MB did not suffice. It sound likely that 128M is not enough if you are adding data. Not sure why no warning messages are seen. Try to raise the memory_limit to e.g. 256M. I think it will work.
Sep 3, 2008 at 8:35 PM
Yes, I wanted to try that too! Amazing, don't you think? Also, it is taking a long time, just to save the file.
About 10-15s, to read the content of the template.
5-10s to write something in the Excel template
and then about 1min to save the file! Did you have any idea how to reduce this time?

Thanks a lot for all the testing you did too! That was great from you!
Developer
Sep 3, 2008 at 8:46 PM
Yes, it is taking a long time and eats lot of memory. I am crossing my fingers that the developers working on the Excel2007 writer find a solution for this.
Improving performance and memory on data dumps
If it gets solved I think it will be a major breakthrough for PHPExcel.
Sep 4, 2008 at 1:03 PM
I change now the memory limit to 512MB! :) And of course now it is working! Still very long to generate the file -> about 2min!
Thank you very much concerning this issue!

PS: I will open another thread for an other issue I am currently struggling with... conditional formating!