php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #63896 Can't fetch more than 100 records on relational queries
Submitted: 2013-01-04 00:00 UTC Modified: 2017-04-01 21:20 UTC
Votes:7
Avg. Score:4.9 ± 0.3
Reproduced:5 of 6 (83.3%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: scossu at artic dot edu Assigned:
Status: Wont fix Package: PDO_4D (PECL)
PHP Version: 5.4.10 OS: Linux
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: scossu at artic dot edu
New email:
PHP Version: OS:

 

 [2013-01-04 00:00 UTC] scossu at artic dot edu 
Description:
------------
Performing relational queries, either by: 
SELECT * FROM a, b WHERE a.Pkey=b.a_Fkey 
or by:
SELECT * FROM a, [INNER|OUTER LEFT...] JOIN b ON a.Pkey=b.a_Fkey 
the script segfaults if there are more than 100 results. 
Adding a LIMIT statement for 100 records or less returns the expected results. 

PHP version: 5.4.8
NOTE: This package was patched and compiled manually due to compiler error; the 
problem, however, has been reported by other users previously.

Test script:
---------------
<?php
$dsn = '4D:host=localhost;charset=UTF-8;port=19812';
$user = 'user';
$pass = 'pass';
$db = new PDO($dsn, $user, $pass);
$qryStr= "SELECT i.Pkey, i.Image_Type_Fkey, it.Label FROM Images i, Image_Types it WHERE i.Image_Type_Fkey = it.Pkey LIMIT 10000";
$stmt = $db->prepare($qryStr);
$stmt->execute();
$res=$stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($res); 
?>

Expected result:
----------------
print results from query

Actual result:
--------------
Browser error: 
Error 324 (net::ERR_EMPTY_RESPONSE): The server closed the connection without 
sending any data.

Apache log: 
[notice] child pid 719 exit signal Segmentation fault (11)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-01-11 07:25 UTC] roger dot stegeby at gmail dot com 
This has been a very annoying problem since it limits the web-development to make 
very ugly workarounds..
 [2014-11-28 21:48 UTC] israel at ravnalaska dot net 
This may not be related, or could completely explain this issue: In trying to use the core lib4d_sql of this project independently, I discovered that when any query returns more than 100 results, the paging code does not properly populate the result set for the second (and subsequent) page. What I found is that results 101-200 return null pointers (which could certainly cause heartburn if the PHP driver doesn't handle it properly, and definitely won't give the data desired), and the library throws an error when trying to access any result rows above 200.

The reason I'm thinking this is related is due to the magic number 100, which is the same as the number of results returned per page by default.

While I wasn't able to "solve" this issue directly (it appears to me to be a server-side issue), I was able to find a workaround, however: you can specify how many results to return per page in the code that actually sends the query to the server. By setting this value to something higher than the number of results you actually get, then all the results are returned in a single page, and the faulty paging code shouldn't cause problems.

In a nutshell, the workaround is to change all the lines in the lib4d_sql that start with "sprintf_s(msg,2048,"%03d EXECUTE-STATEMENT\r\n" to add the statement "FIRST-PAGE-SIZE:xxx" where xxx is whatever page size you want. Like all the other statements in the string, this one should be delimited by \r\n.

Of course, this is not an ideal solution, since in the general case you don't know how many results may be returned, but I've found that just setting it to an arbitrarily large value that far exceeds the number of results I am expecting works around the paging issue for me.
 [2017-04-01 21:20 UTC] tpunt@php.net
-Status: Open +Status: Wont fix
 [2017-04-01 21:20 UTC] tpunt@php.net 
Due to this extension not seeing any activity since 2009, this issue will not be fixed. We are therefore closing this now.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun Dec 22 01:01:30 2024 UTC