Tuesday, July 26, 2011

General error: Can't create/write to file

This error is very commonly appear when we are working with huge MySQL Database with inno DB storage engine.

To resolve this error we needs to update following variable in my.ini file.
(my.ini file is available in mysql folder)

Variable Default Required
innodb_buffer_pool_size 15M 500M

After update above variable restart Mysql server your issue has been resolved. To get more detail also refer following link

InnoDB Startup Options and System Variables

This technique will work for all version of MySQL

Tuesday, July 12, 2011

Maximum file size, 7087104, exceeded.

This is very common problem we face while generating huge Excel file using PEAR, Php-Excel, php excel writer class . I spent many hours to find out solution and finally found something very useful.

There is some limitation comes with excel file generation.

Maximum number of chars in a string32767
Maximum number of columns256
Maximum number of rows65536
Maximum chars in a sheet name31
Maximum chars in a header/footer254

There are two ways to resolve this issue.
(1) Generate Excel file as per below code.

For huge file use HTML to excel conversion, It works many times for me and it works fine in huge file too.

$strOutput="<table width="100%" border="1" cellspacing="0" cellpadding="0"><tr>
<td>Value of Column1 </td>
<td>Value of Column2 </td>

$file = 'myxlsfile.xls';
//export start
header("Content-type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=" . $file);
echo $strOutput;

Note: Generated file using this method may show some warning when we open in excel.

2) Install OLE::Storage_Lite module 

Thanks for reading my post