|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #43178 MySQL 5.1 prepared statements with resultset
Submitted: 2007-11-02 13:36 UTC Modified: 2007-11-12 16:22 UTC
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: dio at demis dot ru Assigned: andrey (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5.2.4 OS: Debian Linux
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Bug Type:
From: dio at demis dot ru
New email:
PHP Version: OS:


 [2007-11-02 13:36 UTC] dio at demis dot ru
Im trying to use MySQL 5.1 prepared statements that can return resultsets. Seems that mysqli can not prepare second statement even if free_result() was called for first.

MySQL 5.0 doesn't able to return resultsets from stored procedures at all.

smart:/home/dio# php --version
PHP 5.2.4-1+b1 with Suhosin-Patch (cli) (built: Oct  1 2007 10:35:56)
Copyright (c) 1997-2007 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies
smart:/home/dio# mysql --version
mysql  Ver 14.14 Distrib 5.1.22-rc, for pc-linux-gnu (i486) using  EditLine wrapper

Reproduce code:

$id = 39;
$login; $md5password; $role; $fullname;

$mysqli = new mysqli("localhost", "root", "", "tyres");

$stmtLoad = $mysqli->prepare("CALL spUserLoad(?)");
$stmtLoad->bind_param("i", $id);

$stmtLoad->bind_result($id, $login, $md5password, $fullname, $role);

var_dump($id, $login, $md5password, $fullname, $role); // Everything was loaded fine

$stmtSave = $mysqli->prepare("CALL spUserUpdate(?, ?, ?, ?, ?)");

echo $mysqli->error; // Commands out of sync; you can't run this command now


Code of stored procedures:

	SELECT id, login, md5password, fullname, role FROM users WHERE id = iId;

CREATE PROCEDURE `spUserUpdate`(IN iLogin VARCHAR(256), IN iMd5password VARCHAR(32), IN iFullname VARCHAR(1024), IN iRole INT, IN iId INT)
	IF (SELECT COUNT(*) FROM users WHERE login = iLogin AND id != iId) = 0 THEN
		UPDATE users SET login = iLogin, md5password = iMd5Password, fullname = iFullname, role = iRole WHERE id = iId;

Expected result:
The new statement should be prepared for calling. And be able to return resultsets too

Actual result:
The new statement can not be prepared due to "Commands out of sync; you can't run this command now" error :(


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2007-11-04 21:28 UTC] waltermin at hotmail dot com
I have the same problem reported here. I can't execute two prepared consecutive statements. I'm using MySQL 6.0 and PHP 5.2.4.
The error messagge is the same: "Commands out of sync; you
can't run this command now".
Thanks in advance. I'm in trouble with this.
 [2007-11-12 16:22 UTC]
just a few notes.
SP binding is not supported. If it works, by chance, still not supported. OUT SP variables are definitely not supported.

In addition,mysqli_stmt_free_result() has no meaning if  mysqli_stmt_store_result() is not called beforehand. Normal queries are "stored" by default, but PS are "used" by default (use = streaming, store = caching).

As the data has not been fetched fully, you cannot execute anything else. Stored Procedures always have at least one result set. If a SP does SELECTs than there will be more than one result sets. This obligatory result set contains status information regarding the execution of the SP. It's always the last set. Thus you need to do additional fetch. If you have called mysqli_stmt_store_result() the second fetch would have been unnecessary.

PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Apr 21 08:01:27 2024 UTC