Allowed Memory Size Exhausted

[freewholly]

I have a 12Mb delimited text file I need to read, parse and load into a MySQL database. The server is set to allow 8388608 bytes before it runs out. Any suggestions on how I can bypass the memory size limit? For example, can I grab just half the file at a time, or does PHP have a command to extend the allowed memory?

Thanks.
David

 

[sleipnir214]

If each line of the file contains information for only one row of the table, then read a line, parse it, load it. Then read the next line.

Use fopen to get a file handle, the use fgets to read the file a line at a time.

 

[KarveR]

if (file_exists(yourfile)){

$fcontents = file(yourfile);

while (list ($line_num, $line) = each ($fcontents)) {
// split your lines into pieces
//load into db
} ***************************************
Party on, dudes!

 

[sleipnir214]

It also just occurred to me that MySQL has the command LOAD DATA, which can populate a table from a delimited text file in one command.

I had a similar problem to yours. I had to populate a database from 29 MB fixed-width ASCII file. My best solution was a perl script that parsed the data line-by line, populating ancilliary related tables on the fly and rewriting the data to a second file in delimited form. Then the perl script issued a single LOAD DATA command to MySQL via DBI.

My initial solution was similar to yours -- populating the tables through INSERT statements. But the app took too long to run. By using LOAD DATA, I cut my runtime from ~90 seconds to ~13 seconds.

 

[freewholly]

I should have mentioned I am using fopen() to open an FTP session and read the file. This is NOT a time issue, as I am resetting the time value each pass. It is strictly a size issue, and because the server allows only about 8Mb of data, I'm getting only 2/3 of the file read and parsed. So, the issue at hand is How Do I Bypass The Predefined Space Issue?

Thanks for your comments.
David

 

[robertx2]

I'm getting something similar, but can't figure out why. There are no large file sizes, and I don't know whether to suspect server RAM, bandwidth, or what. This is the kind of message I receive periodically:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 11520 bytes) in /usr/home/path/to/page.php on line 42

Can allowed memory size be increased, and if so, how?

 

[KarveR]

Its not being limited by the size of the temp directory on the server by any chance ?
***************************************
Party on, dudes!

 

[robertx2]

Maybe, but don't think so. My /tmp is a symlink to /usr/tmp, and there's actually plenty of space. Do you think checking php.ini would be helpful? Thanks for your kind response. Robert

 

[KarveR]

Definately, as you should find a section like the following:

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

max_execution_time = 30 ; Maximum execution time of each script, in seconds
memory_limit = 8M ; Maximum amount of memory a script may consume (8MB)

Thought you would have already changed these tho. ***************************************
Party on, dudes!

 

[KarveR]

Oh, once you've changed these, you may need to restart apache to have them take effect, although its unlikely. ***************************************
Party on, dudes!

 

[robertx2]

Yup! -- that's exactly what my php.ini shows. So would this work, you think?...
max_execution_time = 120
memory_limit = 32M

btw -- thanks very very much. Robert

 

[KarveR]

you'd prolly need ore than 120 as time for a 12mb file, set to 300 if on dsl or local and more if connecting by modem or cablemodem at 128+ < 512 ***************************************
Party on, dudes!

 

[robertx2]

Thank you, thank you, thank you!
Based on your advice, and the nifty clues about looking at php.ini, I first consulted my PHP Bible (pp. 599-603). There, it points out that the function set_time_limit won't work in safe mode. Don't know if that's of any real concern, because I have save_mode = Off anyway. The author does point out that if the modification isn't working, to stop/restart httpd. I restarted anyway, just to make sure. So now, the php.ini section reads:

max_execution_time = 300
memory_limit = 32M

So far, there are no funny messages about &quot;Fatal error: Allowed memory size of 8388608 bytes exhausted&quot;. If it returns, however, I'll revisit this thread and take it up again. Thank you again for pointing in a constructive direction and for providing useful clues.

cheers!

 

[KarveR]

Happy to of been some help ***************************************
Party on, dudes!