php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #50755 PDO DBLIB Fails with OOM
Submitted: 2010-01-14 19:09 UTC Modified: 2010-05-31 20:40 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: ssufficool at gmail dot com Assigned: ssufficool (profile)
Status: Closed Package: PDO related
PHP Version: 5.2 / 5.3 OS: Linux 2.6.30-gentoo-r2
Private report: No CVE-ID: None
 [2010-01-14 19:09 UTC] ssufficool at gmail dot com
Description:
------------
When querying large tables (> 800,000 rows) with PDO DBLIB I get out of memory.

The same select query works fine using:

linux# tsql -H host -U user -P pass 
SELECT * from aVeryLargeTable
go
quit

on the command line using the freetds (dblib) library without consuming client-side memory.

Reproduce code:
---------------
$pdo = new PDO('dblib:host=host','user','pass');
echo "Creating table...\n";
$pdo->query("CREATE TABLE large_table (field_1 nvarchar(4000))");
$pdo->query("DECLARE @n int;
set @n = 0;
WHILE (@n < 50000) BEGIN
  insert into large_table values( replicate(4000,'-') );
  set @n = @n + 1;
END");
echo "Prepare\n";
$rs = $pdo->prepare("SELECT * FROM large_table");
echo "Execute\n";
/*********OOM HERE**************/
$rs->execute( );


Expected result:
----------------
A valid handle to a resultset in $rs


Actual result:
--------------
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 48 bytes) in /home/ssufficool/tds_test.php on line 12

It looks like the guts of ext/pdo_dblib/dblib_stmt.c:pdo_dblib_stmt_execute()
at "/* let's fetch all the data */"

Should be moved to:
  pdo_dblib_stmt_fetch()

and only when a scrollable cursor is requested should the data be buffered at the client (not required for ct-lib)

Patches

pdo_dblib_unbuffer-5.3.patch (last revision 2010-04-05 17:35 UTC by ssufficool at gmail dot com)
pdo_dblib_unbuffer-6.patch (last revision 2010-03-09 22:24 UTC by ssufficool at gmail dot com)
pdo_dblib_unbuffer-5.2.patch (last revision 2010-03-09 22:23 UTC by ssufficool at gmail dot com)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-01-15 00:21 UTC] ssufficool at gmail dot com
I have a patch that removes client side buffering and allows for large rowset queries without memory consumption. Compiled and tested http://svn.php.net/repository/php/php-src/branches/PHP_5_2

SVN Revision: 293557

I can send patch via e-mail.
 [2010-01-20 20:50 UTC] ssufficool at gmail dot com
Patch sent to wez@php.net waiting response
 [2010-03-09 23:48 UTC] ssufficool at gmail dot com
Affects all versions of PHP, patches attached.
 [2010-03-20 00:32 UTC] ssufficool at gmail dot com
Patch revised:
------------------------------------------------
1. Reverted driver always registering as dblib.

Question: Should the user really have to know the library the extension was compiled against? Seems like we should settle on a constant registration since you really can't mix and match.

2. Reverted whitespace modifications. Removed spurrious comments. Reverted DBSETOPT --> dbsetopt.

3. Reverted SYB* --> SQL* define deletions. These are required for compile against the depreciated MS DBLIB.

4. Removed automagic compute column naming (which was clobbering library memory). Just return what the server returns including empty strings. The user will need to alias in their sql query as "select 1+1 as oneplusone" instead of just "select 1+1" magically returning array('compute1'=>'2'). 

Question: Who if anyone relies on this behavior? I don't see other drivers doing this.

Some unrelated/unmentioned "fixes"
------------------------------------------------
Allow multiple rowsets with varying column definitions. This was implemented incorrectly.
Include the recent update to SQLMONEY formatting.

Tested against SQL Server 2008 Express, PHP-5.3 svn-296442, FreeTDS 0.64, Linux 2.6.30 - i686.
 [2010-03-28 22:10 UTC] eric at pineconehill dot com
we use freetds on debian with sql server 2005, so i'm following this patch with 
some interest. just curious, why freetds 0.64? 0.82 is the latest stable and fixes 
quite a few issues. it's been out for almost 2 years now (whereas 0.63 is 5 years 
old in a couple of weeks).
 [2010-03-29 07:22 UTC] ssufficool at gmail dot com
This has also been tested with latest and greatest FreeTDS 0,82 on Ubuntu x86 & amd64. Same results, out of memory. The memory allocation is on the PDO side, not libtds.
 [2010-05-30 22:45 UTC] golgote at gmail dot com
This patch makes pdo_dblib finally usable with large databases, it would be nice 
if devs could review it. Eventually, I suggest you also post a bug report on PECL 
if you think you don't have enough visibility here.
 [2010-05-31 20:38 UTC] ssufficool@php.net
Automatic comment from SVN on behalf of ssufficool
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=300002
Log: Fix bug #50755 &amp; Enable multiple rowsets [DOC]
 [2010-05-31 20:40 UTC] ssufficool@php.net
-Status: Open +Status: Closed -PHP Version: 5.2 / 5.3 / 6 +PHP Version: 5.2 / 5.3 -Assigned To: +Assigned To: ssufficool
 [2010-05-31 20:40 UTC] ssufficool@php.net
Fixed in revision 300002.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 10:01:30 2024 UTC