php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #70786 mysqli_stmt objects can not be processed in the nested iterations
Submitted: 2015-10-25 20:08 UTC Modified: 2022-02-15 13:43 UTC
From: it_poz at interia dot eu Assigned: dharman (profile)
Status: Wont fix Package: MySQLi related
PHP Version: irrelevant OS: irrelevant
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: it_poz at interia dot eu
New email:
PHP Version: OS:

 

 [2015-10-25 20:08 UTC] it_poz at interia dot eu
Description:
------------
The environment:
MS Win Vista Business 64-bit, MySQL 5.6.26, PHP 7.0RC5 on IIS 7.0 via FastCGI, mysqli ext installed.


The mysqli_stmt objects can not be processed in the nested iterations – looks like, they influence to each other.

Probably the second mysqli_stmt object (see the example script) doesn’t work at all due to some collision to the first one.
Same problem I have observed in the earlier PHP versions like PHP 5.6.14, 5.5.30, 5.4.45 and 5.3.29.

In the example I have used the SELECT query, but even more strange behavior have been noticed for the other statement queries like UPDATE or INSERT and it is very difficult find the reason of that (see the debug info in the example code).



Similar task, but realized within the MySQL client (5.6.26 for MS Windows) works well and as expected. It is possible to PREPARE and EXECUTE prepared statements in any order and the defined statements can coexist and don’t influence to each other.

Test script:
---------------
Let’s consider very simple example of two tables in the data base (MySQL):

tbl_1
---------------------------
| id_tbl_1 |  name_tbl_1  |
---------------------------
|      1   |     ntbl1_1  |
|      2   |     ntbl1_2  |
---------------------------

tbl_2
---------------------------
| id_tbl_2 |  name_tbl_2  |
---------------------------
|      1   |     ntbl2_1  |
|      2   |     ntbl2_2  |
---------------------------

It doesn’t matter whether the tables are somehow related or not.


The PHP code:

// the MySQL connection is established and is correct for the right user with adequate privileges – the connection handler is $db_res

$stmt_1 = $db_res->stmt_init();
$stmt_1->prepare('SELECT name_tbl_1 FROM tbl_1 WHERE id_tbl_1 = ?');
$stmt_2 = $db_res->stmt_init();
$stmt_2->prepare('SELECT name_tbl_2 FROM tbl_2 WHERE id_tbl_2 = ?');

for ($i = 1; $i <= 2; $i++) {
  $stmt_1->bind_param('i', $i);      // returns TRUE here
  $stmt_1->execute();                // returns TRUE here
  $stmt_1->bind_result($name_1);     // returns TRUE here
  $stmt_1->fetch();                  // returns TRUE here
  echo $name_1.'<br />';
  for ($j = 1; $j <= 2; $j++) {
    $stmt_2->bind_param('i', $j);    // returns TRUE here
    $stmt_2->execute();              // returns TRUE here
    $stmt_2->bind_result($name_2);   // returns TRUE here
    $stmt_2->fetch();                // returns FALSE here
    echo '------'.$name_2.'<br />';
  }
}
$stmt_1->close();
$stmt_2->close();

// close data base connection

Expected result:
----------------
The expected result should be something like that:

ntbl1_1
------ntbl2_1
------ntbl2_2
ntbl1_2
------ntbl2_1
------ntbl2_2

Actual result:
--------------
but the received result, unfortunately is like that:

ntbl1_1
------
------
ntbl1_2
------
------

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-10-26 12:30 UTC] it_poz at interia dot eu
-Operating System: MS Windows Vista Business 64-bit +Operating System: irrelevant -PHP Version: 7.0.0RC5 +PHP Version: irrelevant
 [2015-10-26 12:30 UTC] it_poz at interia dot eu
Checked on Linux Fedora 22 and PHP 5.6.14 - the same problem occures, so the bug is OS and PHP version irrelevant.
 [2021-09-15 16:11 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2021-09-15 16:11 UTC] cmb@php.net
I can confirm the reported behavior with PHP-7.4 on Windows using
MySQL 8.0.26.
 [2022-02-15 13:43 UTC] dharman@php.net
It's true that there is an exception thrown on $stmt_2->execute(); but I don't think we can fix this. At least, it's working as per the design. 

You have two prepared statements, but they are both using the same connection. The issue isn't that the prepared statements are colliding with each other, but rather that the link to MySQL is busy doing something else. Here's more or less how it works:

PS1 sends PREPARE => MySQL returns stmt_id and awaits further commands
PS2 sends PREPARE => MySQL returns stmt_id and awaits further commands
PS1 sends EXECUTE => MySQL executes the statement and sets connection status to FETCHING_RESULTS
PS1 asks for a single row from the result set => MySQL returns the row, status remains the same
PS2 tries to send EXECUTE command => mysqlnd sees the connection status is not READY and triggers "Commands out of sync; you can't run this command now"

Now, if PHP would keep on reading the rows from the result set, at some point MySQL would say no more rows exist and the connection status would be set back to READY. This happens when you use store_result() for example, PHP reads it all into PHP internal memory, which makes the connection free to do other stuff. With unbuffered results, you must remember that you can't perform any other action on the connection until MySQL says it's ready to accept new commands. 

Of course, I oversimplified the logic a little bit. In reality, mysqlnd receives https://dev.mysql.com/doc/internals/en/binary-protocol-resultset.html when a SELECT statement is executed and it won't be able to send any other commands on that link until it receives EOF_Packet without SERVER_MORE_RESULTS_EXISTS flag. Basically, MySQL sends s series of packets and mysqlnd knows that until it receives the last packet, the connection cannot be used for anything else. 

To avoid this problem, use buffered results.
 [2022-02-15 13:43 UTC] dharman@php.net
-Status: Verified +Status: Wont fix -Assigned To: +Assigned To: dharman
 
PHP Copyright © 2001-2023 The PHP Group
All rights reserved.
Last updated: Mon Feb 06 12:03:42 2023 UTC