mysql 不同步存储过程,mysql存储过程错误命令不同步；您现在不能运行此命令

I have a stored procedure which I call from PHP which works fine on my dev version which is running Maria DB. I transferred to production which is running mysql (5.5.53) a 'Commands out of sync; you can't run this command now' error. I have tried suggestions to make sure the connection is closed after each query. I have also tried $stmt->store_result(); & $stmt->free_result();. If I try $stmt-next_result(); I do not get this out of sync error, but my script exists the loop and I do not get any other results.

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(IN `Input_Value` INT, OUT `Out_val` VARCHAR(150))

BEGIN

SET @sql = 'SELECT sample_name FROM sample';

PREPARE stmt FROM @sql;

EXECUTE stmt;

END

PHP:

`foreach($locations as $location_name){

echo $location_name.'
';

$sp_query = "CALL test3(?,@Out_val)";

$stmt = $dbc->prepare($sp_query);

if(!$stmt){

echo mysqli_error($dbc);

}

$stmt->bind_param('i',$view);

if ($stmt->execute()){

$params = array();

$meta = $stmt->result_metadata();

print_r($meta);

while ($field = $meta->fetch_field()){

$params[] = &$row[$field->name];

}

print_r($params);

call_user_func_array(array($stmt, 'bind_result'), $params);

$header_ct = 0;

$labels = '';

while ($stmt->fetch()) {

foreach($row as $key => $value){

$key = htmlspecialchars($key);

//echo "Key:".$key."
";

//echo "Value:".$value."
";

}

}

}else{

echo mysqli_error($dbc);

echo "not working";

}

$stmt->store_result();

//$stmt->free_result();

$stmt->close();

}

?>`

解决方案

In Just seperate your queries with $select_stmt->close(); to split them (not simultaneous but procedural.

For more on this see

If you are using codeigniter Framework,

add following code into /system/database/drivers/mysqli/mysqli_result.php

For codeigniter

function next_result()

{

if (is_object($this->conn_id))

{

return mysqli_next_result($this->conn_id);

}

}

then in model when you call Stored Procedure

$query = $this->db->query("CALL test()");

$res = $query->result();

//add this two line

$query->next_result();

$query->free_result();

//end of new code

return $res;