4

I wrote a program to generate large .SQL files for quickly populating very large databases. I scripted it in PHP. When I started coding I was using fopen() and fwrite(). When files got too large the program would return control to the shell and the file would be incomplete.

Unfortunately I'm not sure exactly how large is 'too large'. I think it may have been around 4GB.

To solve this problem I had the file echo to stdout. I redirected it when I called the program like so:

[root@localhost]$ php generatesql.php > myfile.sql

Which worked like a charm. My output file ended up being about 10GB.

My question, then, is: Are fopen() and fwrite() limited by the file system in terms of how large a file they are capable of generating? If so; is this a limitation of PHP? Does this happen in other languages as well?

KeatsKelleher
  • 10,015
  • 4
  • 45
  • 52

4 Answers4

6

What's probably occuring is the underlying PHP build is 32bit and can't handle file pointers >4GB - see this related question.

Your underlying OS is obviously capable of storing large files, which why you're able to redirect stdout to a large file.

Incidentally, an SQL file is likely to be highly compressible, so you might like to consider using the gzip fopen wrapper to compress the file as you write it. 

$file = 'compress.zlib:///path/to/my/file.sql.gz';
$f = fopen($file, 'wb');

    //just write as normal...
    fwrite($f, 'CREATE TABLE foo (....)');

fclose($f);

Your dump will be a fraction of the original size, and you can restore it simply piping the output from zcat into an SQL client, e.g. for mysql

zcat /path/to/my/file.sql.gz | mysql mydatabase
Community
  • 1
  • 1
Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
2

Yes and no. Its not fopen() or fwrite() which are limited directly, its the files, which cannot exceed some dimensions depending on the filesystem. Have a look at Comparison of filesystems on Wikipedia.

KingCrunch
  • 128,817
  • 21
  • 151
  • 173
  • 3
    But if that's the case, how come output redirection worked in generating a ~10 GB file for the OP? – Chetan Nov 19 '10 at 21:35
  • Why not? The *f*()* ("File"-Functions) usually work on streams. So if you call *fwrite()* it buffers only up to (I assume) 4kB, before writes the output directly to the open stream (the return value of *fopen()*). Now your script runs and runs and fills the file, but at some point the filesystems blocks any further data, because of maximum file size. In fact, I dont know, if this really happens to you, because 10GB is a odd value. Anyway: Try to split it in multiple files (of lets say <4GB). You dont know exactly on FS the files get saved later. – KingCrunch Nov 19 '10 at 21:40
0

You can write more than 2GB of data in a stream, not in a file (as the fseek internal pointer of the file is exceeding PHP limits, and streams are not usually seekable)

<? $target = fopen('test.tar', 'w'); //this is a file, limited by php to 2GB $body = str_repeat("===", 1024 * 1024); while(true) fwrite($target, $test);

<? $target = popen('cat > test.tar', 'w'); //this is a stream, no limitation here $body = str_repeat("===", 1024 * 1024); while(true) fwrite($target, $test);

131
  • 3,071
  • 31
  • 32
0

Is it possible that your script is taking too long to execute and has timed out?

Alternatively, is it possible that you're reaching your memory limits within the script?

zzzzBov
  • 174,988
  • 54
  • 320
  • 367
  • it's executing with php-cli; definitely not a timeout issue. if the memory limit was reached there would have been an error message. – KeatsKelleher Nov 19 '10 at 21:40