1

Here is my code:

$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$category = $sth->fetchAll(PDO::FETCH_ASSOC);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$tags = $sth->fetchAll(PDO::FETCH_ASSOC);

It throws this error:

Fatal error: Uncaught PDOException: 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:\xampp\htdocs\myweb\others\user.php:71 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(71): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 71

Also I've used closeCursor() right after fetchAll(), based on this solution. But sadly it throws a new error:

Warning: Packets out of order. Expected 1 received 9. Packet size=7 in C:\xampp\htdocs\myweb\others\user.php on line 72

Warning: PDO::prepare(): MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php on line 72

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php:72 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(72): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 72

Any idea how can I fix the problem?


Noted1: Each of queries above work separately. I mean, when I call a single procedure, it works as well.

Noted2: Each procedure returns a result set. I mean there is a SELECT statement in those procedures, which may return multiple rows.

1

You need $sth->close() after finishing the execute and fetch* calls.

2

The quick and dirty answer here would be to do the following

  • prepare each query once
  • prepare each query in separate statement handles
  • call your execute and save result sets in separate arrays

LIke this

$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$topcat_sth = $this->dbh->prepare($query);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$toptag_sth = $this->dbh->prepare($query);

$topcat_sth->execute([$user_id]);
$topcat_rs = $topcat_sth->fetchAll(PDO::FETCH_ASSOC);

$toptag_sth->execute([$user_id]);
$toptag_rs = $toptag_sth->fetchAll(PDO::FETCH_ASSOC);

...
  • I don't think this will help for the second time. close() is missing. – Rick James Aug 21 '17 at 22:30
  • Thank you ... but there is still the same error. – stack Aug 22 '17 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.