Why does PDO::PARAM_LOB cause an Allowed memory size exhausted error?

Question

I have been able to find very little information about what goes on under the hood here. I am using the Yii 1.1 framework, but I don't believe that's related to this problem. Basically, I'm using this code to insert large files into my database (hundreds of megabytes).

$sql = "
    UPDATE {$this->tableName()}
    SET `$column` = :value
    WHERE `$pkField` = :index
";

// $stream is a resource from fopen()
$command = Yii::app()->getDb()->createCommand($sql);
$command->bindValue(':index', $id);
$command->bindValue(':value', $stream, PDO::PARAM_LOB);

$command->execute()

When I run this to insert a large, 200Mb file, I get a memory exhausted error. I thought that binding with PDO::PARAM_LOB would stream my file to the database instead of loading the whole thing into memory at once. Clearly I'm wrong :p

How does PDO::PARAM_LOB work under the hood, and in terms of memory management? Or am I just doing this wrong? The most I could glean from the docs was this statement from php.net:

This example opens up a file and passes the file handle to PDO to insert it as a LOB. PDO will do its best to get the contents of the file up to the database in the most efficient manner possible.

Answer 1

Well, seems like that's just how it works.

https://bugs.php.net/bug.php?id=40913

This bug has been open for 13 years. It converts the stream to a string and submits that in the query, since mysql has no native streaming support.

So I guess I'll try using fopen() to load in 5000 bytes or so and update the record in chunks.

UPDATE: Well spank my nekudotayim, the bug was fixed! But only for SQLite :( Apparently fixing it for MySQL would require some reworking of PDO.