General error 2014 with PDO buffer attribute set to true

Question

I attempted to ask this here, but it didn't come out so well. I considered editing, but given the answers already received, I thought it better to start over.

I am receiving the following error:

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 C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\LTOO_test.php:348 Stack trace: #0 C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\LTOO_test.php(348): PDOStatement->execute() #1 C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\ga.php(119): totalSI(Object(gaParent), 1) #2 C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\ga.php(266): GA->fitness(Object(gaParent), 'totalSI') #3 C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\LT in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\sustainable_water_allocation\LTOO_test.php on line 348

Here is the connection setup:

$this->dbh = new PDO($dsn, $user, $password, array(
            PDO::ATTR_PERSISTENT => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =>true
            ));

Here is the MySQL general log file:

    1 Connect
        1 Query select rva from demand_nodes
    1 Query select * from demand_nodes
    1 Query select * from source_nodes
    1 Query TRUNCATE TABLE  `results_demand`;
    1 Query TRUNCATE TABLE  `results_link_input`;
    1 Query TRUNCATE TABLE  `results_source_state`;
    1 Query TRUNCATE TABLE  `results_supply`
    1 Quit

The next query to run generates the error.

The queries are generated when an object is being instantiated, like this:

$allSources = new sources();

For good measure, here are all of the listed queries:

$sql = "select rva from demand_nodes";
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);

if ($stmt->execute()) {
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $rVAs = $rVAs + $row['rva'];
        $numOfDemands = $numOfDemands + 1;
    }
    $stmt->closeCursor(); 
}

$sql = "select * from demand_nodes";
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);
$stmt->execute();
$row = $stmt->fetchAll();

foreach($row as $i=>$value){
    $this->id[] = $row[$i]['id'];
    $this->label[] = $row[$i]['label'];
    $this->initialDelta[] = $row[$i]['initial_delta'];
    $this->initialRate[] = $row[$i]['initial_rate'];
    $this->deltaMin[] = $row[$i]['delta_min'];
    $this->deltaMax[] = $row[$i]['delta_max'];
    $this->rateMin[] = $row[$i]['rate_min'];
    $this->rateMax[] = $row[$i]['rate_max'];
    if($row[$i]['si_weight'] != 0){
        $this->siWeight[] = $row[$i]['si_weight'];
    }else{
        $this->siWeight[] = 1/($numOfDemands + $rVAs); 
    }
    $this->rva[] = $row[$i]['rva'];
}

And

$sql = "select * from source_nodes";
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);
$stmt->execute();
$row = $stmt->fetchAll();
foreach($row as $i=>$value){
    $this->id[] = $row[$i]['id'];
    $this->label[] = $row[$i]['label'];
    $this->state[] = $row[$i]['initial_state'];
}

Stumped. I am not sure what else to add, but if I missed something, please let me know.

This time you did it better but again: how can we know what is going on when `an object is being instantiated, like this:$allSources = new sources();`? How we can? Also, error message contains a stack trace which can help to trace the error source. It is **omitted**. **Again**. To get a reliable answer one have to ask a reliable question, mind you. — Your Common Sense, Aug 10 '13 at 07:38
By the way, your idea of filling dozens of separate arrays like `$this->id[]` is *quite* a strange one. Also, **two** queries to `demand_nodes` looks *quite* a strange too. Also, such a design where you need **all** records from every table looks *quite* a strange again. — Your Common Sense, Aug 10 '13 at 07:41
Strange is as strange does I suppose. Is this causing the problem? As I mentioned in the prior post I have little formal programming training. This is not a web site, it is a water allocation and optimization model I am developing. It makes sense to me to do it this way, but if it is affecting runtime performance or causing errors than I don't mind changing it. Feel free to ask for anything needed; I included what I thought was important. All I care about is a solution. — user2663120, Aug 10 '13 at 11:02
this error means some upper level query were unfinished. Use stack trace to detemine the caller code, inside oh which loop this one is called. — Your Common Sense, Aug 10 '13 at 11:26
I am not sure how to do that, but I will take a look. In the meantime - the general mysql log is showing all of the queries. All of the query setups are posted in the question. Is there anything wrong with any of the queries that would cause this error? — user2663120, Aug 10 '13 at 14:55
I found a solution - evidently the `Truncate table` query required a `closeCursor()`. I posted this as an answer but I am going to remove the answer because I have no idea why this is the case. If someone cares to address the 'why' and create an answer I would be grateful. In the meantime I will spend some additional time researching and perhaps I will stumble upon the 'why' myself. — user2663120, Aug 10 '13 at 23:26
It seems that `unset` (or `$stmt = null`)on the connection after the `truncate` also does the job; but I am not sure which is better or why. Additional discussion: http://stackoverflow.com/questions/12843886/when-should-i-use-closecursor-for-pdo-statements & http://stackoverflow.com/questions/1046614/do-sql-connections-opened-with-pdo-in-php-have-to-be-closed. — user2663120, Aug 10 '13 at 23:54

user2663120 · Accepted Answer · 2013-08-11T15:23:54.520

Answer:

Turns out the truncate statements needed a closed cursor:

$sql = "TRUNCATE TABLE  `results_demand`;
    TRUNCATE TABLE  `results_link_input`;
    TRUNCATE TABLE  `results_source_state`;
    TRUNCATE TABLE  `results_supply`;";
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);
$stmt->execute();
$stmt->closeCursor();

As to the why:

I readily admit that I don't understand why the closeCursor() is required on the truncate. MySQL indicates that truncate is mapped to delete for InnoDB; but there was nothing in any of the documentation I reviewed that suggested a 'Why?'.

Perhaps someone else can speak to this.

40 + hours on this... =/ But it is resolved!

score 0 · Answer 2 · answered Oct 05 '16 at 17:27

0

For future reference I also required a closeCursor() call after performing a DROP TABLE so the issue isn't limited to TRUNCATE.

answered Oct 05 '16 at 17:27

Anonymous Coward

1

General error 2014 with PDO buffer attribute set to true

2 Answers2