php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56931 MYSQL_ATTR_USE_BUFFERED_QUERY on FreeBSD 6.0
Submitted: 2006-03-30 09:27 UTC Modified: 2006-04-09 03:27 UTC
From: hb at teleogteknik dot dk Assigned:
Status: Not a bug Package: PDO_MYSQL (PECL)
PHP Version: PHP 5.1.2 OS: FreeBSD 6.0-RELEASE
Private report: No CVE-ID: None
 [2006-03-30 09:27 UTC] hb at teleogteknik dot dk
Description:
------------
NOTICE this is on PHP 5.1.2 (cannot select it in the dropdown when reporting bug)

A large application has been developed under the Win32(XP Pro) development environment and should be migrated to FreeBSD in order to run in production. We use PDO/MySQL as database-layer. The problem is that I get unexpected behaviour  in the way PDO/PDO_MySQL handles the MYSQL_ATTR_USE_BUFFERED_QUERY mode on the FreeBSD-platform.

I have managed to write a small code that reproduces the problem.

On FreeBSD:
if $stmt2 returns nothing (hence emp_id = 2) it is like that the prepared statement gets wacked/erased to it can't be used in the following iterations (iteration 2 og 3)!

On Win32:
Works fine. The $stmt just returns nothing as expected and the prepared statement is not wasted.


Please help! 

Environment info:
FreeBSD:
OS: RELEASE FreeBSD 6.0-RELEASE
MySQL: 5.0.18
PHP: PHP 5.1.2 (cli) (built: Feb  7 2006 14:51:29)

Win32:
OS: XP Pro SP2
MySQL: 5.0.17-nt
PHP: PHP 5.1.2 (cli) (built: Jan 11 2006 16:40:00)

Reproduce code:
---------------
CREATE TABLE emp (
  emp_id      INT NOT NULL,
  name        VARCHAR(30) NOT NULL,
  PRIMARY KEY(emp_id)
);

INSERT INTO emp VALUES(1, 'Joe');
INSERT INTO emp VALUES(2, 'Wilma');
INSERT INTO emp VALUES(3, 'Franz');
---
<?php
$db = new PDO("mysql:host=localhost;dbname=fullrate", "root", "");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$stmt1 = $db->prepare("SELECT * FROM emp");
$stmt2 = $db->prepare("SELECT * FROM emp WHERE emp_id = 0");
for ($i = 0; $i < 10; $i++) {
  $res1 = $stmt1->execute();
  echo "RES1 = $res1, i = $i\n";
  print_r($stmt1->fetch());  
  
  $res2 = $stmt2->execute();
  echo "RES2 = $res2, i = $i\n";  
  print_r($stmt2->fetch());
}
?>

Expected result:
----------------
The expected result is what I get on Win32-environment:

D:\SVN\FRApps\sites>php test3.php
RES1 = 1, i = 0
Array
(
    [emp_id] => 1
    [0] => 1
    [name] => Joe
    [1] => Joe
)
RES2 = 1, i = 0
RES1 = , i = 1
Array
(
    [emp_id] => 1
    [0] => 1
    [name] => Joe
    [1] => Joe
)
RES2 = , i = 1
RES1 = , i = 2
Array
(
    [emp_id] => 1
    [0] => 1
    [name] => Joe
    [1] => Joe
)
RES2 = , i = 2


the scripts iterates 3 times where RES2 is always empty.

Actual result:
--------------
On FreeBSD / Unix I get:

hb@dhcppc38$ php test.php 
RES1 = 1, i = 0
Array
(
    [emp_id] => 1
    [0] => 1
    [name] => Joe
    [1] => Joe
    [dep_id] => 1
    [2] => 1
)
RES2 = 1, i = 0
RES1 = 1, i = 1
Array
(
    [emp_id] => 1
    [0] => 1
    [name] => Joe
    [1] => Joe
    [dep_id] => 1
    [2] => 1
)
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /usr/home/hb/test.php:12
Stack trace:
#0 /usr/home/hb/test.php(12): PDOStatement->execute()
#1 {main}
  thrown in /usr/home/hb/test.php on line 12

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /usr/home/hb/test.php:12
Stack trace:
#0 /usr/home/hb/test.php(12): PDOStatement->execute()
#1 {main}
  thrown in /usr/home/hb/test.php on line 12 

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-03-30 09:32 UTC] wez@php.net
(but note that the constant names should be PDO:: and not PDO_; that's a documentation bug...)
 [2006-03-30 09:34 UTC] hb at teleogteknik dot dk
Yes! I noticed that and uses PDO::MYSQL_ATTR_USE_BUFFERED_QUERY - using PDO_ gives an error . I think also that the documentatiopn says _UN_BUFFERED which is wrong too.
 [2006-03-30 09:40 UTC] hb at teleogteknik dot dk
I have read the documentation.

Also tried to put the array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true) as second parameter to prepare-method, but with same result.

Why does it not work on FreeBSD?

Regards,
Haktan
 [2006-03-30 09:42 UTC] hb at teleogteknik dot dk
Please read my comments and look at it again.
 [2006-04-09 03:27 UTC] wez@php.net
I can assure you that we don't have code that runs differently in the pdo_mysql driver if you're running on FreeBSD.

This sounds like a problem with your mysql environment and not a bug in PDO_MYSQL, so I'm marking this report as bogus.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Tue Nov 19 03:01:36 2019 UTC