user2663120 user2663120 - 1 month ago 13
MySQL Question

General error 2014 with PDO buffer attribute set to true

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.

Answer

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!

Comments