Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533

    Setting PDO::ATTR_EMULATE_PREPARES equal to false causes errors


    As Jacques1 and Northie indicated, I should not allow PHP to emulate prepared statements, but should delegate the task to MySQL by setting PDO::ATTR_EMULATE_PREPARES equal to false. I have done so, however, now received the following error.
    Code:
    Error in query:<br />UNKNOWN SQL<br />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.<br />
                File Name: /var/www/application/components/com_upload/models/contacts.php<br />
                Line: 433<br />
                Time of Error: Friday May 31, 2013, 8:56:01 PDT
    Before I arbitrarily enable query buffering, I would like to understand what is happening. Line 433 is the second line from the end. Thank you

    PHP Code:
    $db = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));

    $sql 'CREATE TEMPORARY TABLE accounts_temp(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT,id INT UNSIGNED NOT NULL, name VARCHAR(90) NOT NULL,address VARCHAR(90) NULL,phone CHAR(10) NULL,fax CHAR(10) NULL,cities_id INT UNSIGNED NULL,zipcodes_id CHAR(5) NULL,temp_city VARCHAR(45) NULL,temp_state CHAR(2) NULL, changed TINYINT NOT NULL,PRIMARY KEY (temp_id) )';
    db::db()->exec($sql);

    $sql ='SELECT temp_id,id FROM accounts_temp WHERE name=:name AND cities_id=:cities_id AND address=:address';
    $stmt db::db()->prepare($sql);

    $stmt->execute(array('name'=>$row['account_name'],'address'=>$row['address'],'cities_id'=>$row['cities_id']));
    $account $stmt->fetch(PDO::FETCH_ASSOC); 
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,002
    Rep Power
    9398
    You're mixing a $db with this db::db thing?

    You can't execute a SELECT (or other query that returns results), leave parts of it unfetched, and issue another SELECT (et al) query with PDO (by default). I don't think a CREATE TEMPORARY TABLE will do that so are you using any other SELECT queries earlier?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    Originally Posted by requinix
    You're mixing a $db with this db::db thing
    I tried to simplify my script so it would highlight my issues. You are right about mixing the $db::db thing with $db. I actually use a singleton $db::db, but meant to swap them out to $db as it doesn't effect my error. Let me please start over...

    PHP Code:
    class db {
        private static 
    $instance NULL;
        private function 
    __construct() {}   //Make private
        
    private function __clone(){}   //Make private
        
    public static function db() //Get instance of DB
        
    {
            if (!
    self::$instance)
            {
                try{
    self::$instance = new PDO("mysql:host=dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                catch(
    PDOException $e){die(library::sql_error($e));}
            }
            return 
    self::$instance;
        }
    }

    $sql 'CREATE TEMPORARY TABLE accounts_temp(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT,id INT UNSIGNED NOT NULL, name VARCHAR(90) NOT NULL,address VARCHAR(90) NULL,phone CHAR(10) NULL,fax CHAR(10) NULL,cities_id INT UNSIGNED NULL,zipcodes_id CHAR(5) NULL,temp_city VARCHAR(45) NULL,temp_state CHAR(2) NULL, changed TINYINT NOT NULL,PRIMARY KEY (temp_id) )';
    db::db()->exec($sql);

    //Insert some data into accounts_temp...

    $sql ='SELECT temp_id,id FROM accounts_temp WHERE name=:name AND cities_id=:cities_id AND address=:address';
    $stmt db::db()->prepare($sql);
    $stmt->execute(array('name'=>$row['account_name'],'address'=>$row['address'],'cities_id'=>$row['cities_id']));
    $account $stmt->fetch(PDO::FETCH_ASSOC); 
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    Okay, I've isolated the problem, but am stumped

    The first foreach loop goes without errors.

    On the remaining foreach loops, $stmt2->execute() results in the following error:
    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.
    The following will get rid of this error:
    1. Get rid of "PDO::ATTR_EMULATE_PREPARES=>false"
    2. Change "$rs1 = $stmt1->fetch(PDO::FETCH_ASSOC);" to "$rs1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);"
    3. Get rid of "$rs2 = $stmt2->fetch(PDO::FETCH_ASSOC);"


    Where is an unbuffered query? It appears to be "SELECT COUNT(*)...", but using COUNT() should only return one row.

    Please help

    PHP Code:
    <?php
        
    function sql_error($e,$sql=NULL){return('<h1>Error in query:</h1><p>'.$sql.'</p><p>'.$e->getMessage().'</p><p>File Name: '.$e->getFile().' Line: '.$e->getLine().'</p>');}

        class 
    db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    try{
    self::$instance = new PDO("mysql:host=localhost;dbname=xxx;charset=utf8",'xxx','xxx',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                    catch(
    PDOException $e){echo(sql_error($e));}
                }
                return 
    self::$instance;
            }
        }

        
    $row=array(
            
    'zipcodes_id'=>'55555',
            
    'cities_id'=>123
        
    );
        
    $data=array($row,$row,$row,$row);

        
    $sql 'CREATE TEMPORARY TABLE temp1(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (temp_id) )';
        
    db::db()->exec($sql);

        
    $sql='SELECT COUNT(*) AS valid FROM cities_has_zipcodes WHERE cities_id=? AND zipcodes_id=?';
        
    $stmt1 db::db()->prepare($sql);

        
    $sql ='SELECT temp_id FROM temp1';
        
    $stmt2 db::db()->prepare($sql);

        foreach(
    $data AS $row)
        {
            try
            {
                
    $stmt1->execute(array($row['zipcodes_id'],$row['cities_id']));
                
    $rs1 $stmt1->fetch(PDO::FETCH_ASSOC);
                
    syslog(LOG_INFO,'$rs1: '.print_r($rs1,1).' '.rand());
                
    $stmt2->execute();
                
    $rs2 $stmt2->fetch(PDO::FETCH_ASSOC);
                
    syslog(LOG_INFO,'$rs2: '.print_r($rs2,1).' '.rand());
            }
            catch(
    PDOException $e){echo(sql_error($e));}            
        }
        echo(
    'done');
    ?>
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,002
    Rep Power
    9398
    Unless you explicitly tell it not to (which you didn't) PDO will use unbuffered queries for everything.

    For your solution, I doubt #1 had any effect and that #2 is what really solved the problem. But you're still executing $stmt2?
    Have you also tried closing the various statements?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    Thank you requinix, My actual script was much longer and complicated than what I just posted in my previous post, and it took me all morning to condense the script so that I can isolate the scope which is causing the error. Unlike my previous two posts which I included script that I thought was causing the error, the latest script has been verified to create the error.

    Originally Posted by requinix
    Unless you explicitly tell it not to (which you didn't) PDO will use unbuffered queries for everything.

    For your solution, I doubt #1 had any effect and that #2 is what really solved the problem. But you're still executing $stmt2?
    Have you also tried closing the various statements?
    But #1 did have an effect. Remove PDO::ATTR_EMULATE_PREPARES=>false and I do not get the error. I am using PHP Version 5.3.18 and MySQL 5.5.28. Maybe a bug and maybe fixed in current versions?

    And why #3 removing $rs2 = $stmt2->fetch(PDO::FETCH_ASSOC); has an effect, I have no idea, but it does.

    Why do you think #2 solved the problem the problem? Shouldn't $stmt1 just return one value since it is using the aggregate COUNT()?

    I am only executing $stmt2 the first time through the loop. Future loops are caught before they get to it.

    I haven't tried closing the various statements, and do not really wish to do so as I need to perform the prepared statement multiple times. What are your thoughts?

    Thank you!
  12. #7
  13. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,002
    Rep Power
    9398
    It should be that you execute one query that produces results, read everything, possibly close the statement, and begin the next query. Emulating prepared statements shouldn't affect it* and whether you call fetch() a first time shouldn't affect it**.

    I suppose my question is what is a minimal sample script that doesn't work, and what changes do you make to resolve it?

    * Real prepared statements should happen out-of-band, and emulated ones shouldn't involve any communication at all.
    ** Because the data is there waiting for you regardless of whether you try to retrieve it or not.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    requinix,

    Do you mind trying the below code? I changes all tables to temporary tables so you can try it with any database and it won't effect anything. Try changing the three items which I identified, and see the effect. Or, if there is some sort of online simulation site which can include MySQL, let me know what it is and I will set up a demo.

    I believe I am executing one query that produces results, reading everything, (not close the statement), and beginning the next query.

    I agree emulating prepared statements shouldn't affect it and whether i call fetch() a first time shouldn't affect it, but it seems to do so.

    I don't understand your question regarding what is a minimal sample script that doesn't work, and what changes do you make to resolve it. Please elaborate.

    Thank you

    PHP Code:
    <?php

        
    function sql_error($e,$sql=NULL){return('<h1>Error in query:</h1><p>'.$sql.'</p><p>'.$e->getMessage().'</p><p>File Name: '.$e->getFile().' Line: '.$e->getLine().'</p>');}

        class 
    db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    try{
    self::$instance = new PDO("mysql:host=localhost;dbname=xxx;charset=utf8",'xxx','xxx',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                    
    //try{self::$instance = new PDO("mysql:host=localhost;dbname=xxx;charset=utf8",'xxx','xxx',array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                    
    catch(PDOException $e){echo(sql_error($e));}
                }
                return 
    self::$instance;
            }
        }

        
    $row=array(
            
    'id1'=>'55555',
            
    'id2'=>123
        
    );
        
    $data=array($row,$row,$row,$row);

            try
            {
        
    $sql 'CREATE TEMPORARY TABLE temp1(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (temp_id) )';
        
    db::db()->exec($sql);
        
    $sql 'CREATE TEMPORARY TABLE temp2 (id1 INT UNSIGNED NOT NULL,id2 CHAR(5) NOT NULL,
    PRIMARY KEY (id1,id2))'
    ;
        
    db::db()->exec($sql);
        
    $sql 'INSERT INTO temp2 (id1,id2) VALUES (1,2)';
        
    db::db()->exec($sql);
        
    $sql 'INSERT INTO temp2  (id1,id2) VALUES (2,2)';
        
    db::db()->exec($sql);
        
    $sql 'INSERT INTO temp2  (id1,id2) VALUES (3,2)';
        
    db::db()->exec($sql);

        
    $sql='SELECT COUNT(*) AS valid FROM temp2 WHERE id1=? AND id2=?';
        
    $stmt1 db::db()->prepare($sql);

        
    $sql ='SELECT temp_id FROM temp1';
        
    $stmt2 db::db()->prepare($sql);
            }
            catch(
    PDOException $e){echo(sql_error($e,$sql));}            

        foreach(
    $data AS $row)
        {
            try
            {
                
    $stmt1->execute(array($row['id1'],$row['id2']));
                
    $rs1 $stmt1->fetch(PDO::FETCH_ASSOC);
                
    syslog(LOG_INFO,'$rs1: '.print_r($rs1,1).' '.rand());
                
    $stmt2->execute();
                
    $rs2 $stmt2->fetch(PDO::FETCH_ASSOC);
                
    syslog(LOG_INFO,'$rs2: '.print_r($rs2,1).' '.rand());
            }
            catch(
    PDOException $e){echo(sql_error($e));}            
        }
        echo(
    'done');
    ?>
  16. #9
  17. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,002
    Rep Power
    9398
    It worked fine for me...
    Code:
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs1: Array#012(#012    [valid] => 0#012)#012 953495044
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs2:  1260083059
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs1: Array#012(#012    [valid] => 0#012)#012 672521866
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs2:  1993230892
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs1: Array#012(#012    [valid] => 0#012)#012 1097285012
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs2:  890305424
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs1: Array#012(#012    [valid] => 0#012)#012 728995412
    Jun  3 13:55:50 ip-10-248-78-33 php: $rs2:  2145181168
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    What PHP and MySQL version are you running?
  20. #11
  21. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,002
    Rep Power
    9398
    PHP 5.4.near latest, MySQL 5.5.

    Realized I can test with 5.3.10 too and I do get an error with that. Seems the issue is that you have to fetch all the results and then past the end before MySQL closes out the resultset.

    Further testing indicates that the problem goes away with mysqlnd, but that doesn't necessarily mean it's just the driver or the extension at fault (as the extension uses different code for mysqlnd than with vanilla mysql). It's too unfamiliar territory for me to crawl through the source code looking for an explanation but I would guess that it's like I said: something doesn't clean up after itself (1) with true prepared statements* (2) in the normal (ie, MySQL's own) mysql library (3) until the driver knows it ran out of results**. You can test this by throwing in another $stmt1->fetch(): the first to get the only result, the second to trigger the cleanup.

    The way I would work around that is with a $stmt1-> and $stmt2->closeCursor() when you're done with each. That keeps the prepared-ness alive but closes the individual resultset.

    * The API for regular and prepared statements is different so the driver code must also be different. I guess the latter has the bug while the former does not.
    ** See, that's the thing with unbuffered queries: it doesn't know how many results there are so it will insist you keep reading the one resultset until the results really do run out.
  22. #12
  23. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I have always needed to call closeCursor when not using fetchAll.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    So, you just "realized" you can test with 5.3.10 too? What other undiscovered capabilities do you have?

    Thank you very much for your help! This was driving me crazy. Not finding a workaround, but not knowing what caused it and fearing that it would plague me again as it was one of the more difficult errors I've come across to troubleshoot. Comforting to know that the problem was just not me!

    Until I upgrade software, I will checkout closeCursor(), or use $stmt1->fetchAll() if it doesn't work for me.

    Thanks again
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,938
    Rep Power
    533
    Originally Posted by E-Oreo
    I have always needed to call closeCursor when not using fetchAll.
    Always? I've never had this issue until now, and have never called closeCursor. But then again, I've just recently set PDO::ATTR_EMULATE_PREPARES=>false.

    I assume you do not have a need to do so when you use a while loop to iterate through the results, right?

    Would you recommend I go through my code and add it regardless whether I am encountering errors?
  28. #15
  29. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I don't recall whether it was necessary when doing a while loop as well or only when retrieving a single result, and I'm not running 5.3 anymore, but I still use it in both cases. I am using emulated prepared queries so I don't know that that setting actually has any effect on it.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo