Bug #107951 Execute hangs on stored procedure call through MySQL router 8.0.29

• Submitted: 23 Jul 2022 5:35
• Modified: 28 Sep 2022 11:09
• Reporter: MindYour OwnBusiness
• Status: Closed
• Category: MySQL Router
• Severity: S2 (Serious)
• Version: 8.0.29
• OS: Windows
• CPU Architecture: x86

[23 Jul 2022 5:35] MindYour OwnBusiness

Description:
Prepared statement execution hangs on calling a stored procedure through MySQL Router 8.0.29
There is no issue if either:
-Connection is made to database directly OR
-MySQL Router 8.0.28 is used instead

How to repeat:
1. Set up a cluster of 3 MySQL Server 8.0.29 instances
2. Set up MySQL Router 8.0.29
3. Define test stored procedures:

CREATE DEFINER=`root`@`%` PROCEDURE `testProc1Rs`( IN a int)
BEGIN
SELECT a;
END

CREATE DEFINER=`root`@`%` PROCEDURE `testProc2Rs`( IN a int)
BEGIN
SELECT a;
SELECT a+1 b;
END

4. Test with node/mysql2

const mysql = require('mysql2/promise');
let options= {
        host: "",
        port: "",
        user: "",
        password: "",
        database: "",
}

async function run(){
	let conn=await mysql.createConnection(options);
	conn.connect();
	//This works
	let o1 = await conn.query('CALL testProc1Rs(?)',[2]);
	console.log(o1[0][0]);
	//hangs here
	let o2 = await conn.execute('CALL testProc1Rs(?)',[2]);
	console.log(o2[0][0]);
	conn.end();
}
run();
console.log('ran');
setTimeout(()=>{console.log('1 sec later')},1000);

5. Test with php 7.4.9 and PDO
<?php
$host='';
$user='';
$pass='';
$db='';
$charset='utf8';
$dsn="mysql:host=$host;dbname=$db;charset=$charset";
$opt= [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false
    //Problem goes away if emulation is on
    ];
try{
	$db = new PDO($dsn, $user, $pass,$opt);
	$SQL='CALL testProc2Rs(:a);';
	$stmt = $db->prepare($SQL);
	$stmt->bindValue(':a', 2, PDO::PARAM_INT);
	
	$success= $stmt->execute();
	$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
	echo $results[0]['a'];
	//hangs here
	$stmt->nextRowset();
	$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
	echo $results[0]['b'];
} catch (Exception $e){
	echo 'Error'.$e->getMessage();
}

[26 Jul 2022 1:20] MySQL Verification Team

Hi,

If I understand correctly, you see this only with router 8.0.29 -> MySQL Server 8.0.29 ?

thanks

[26 Jul 2022 1:24] MindYour OwnBusiness

I downgraded to MySQL router 8.0.28 and that solved the problem. I did not test other versions of MySQL Server than 8.0.29.

[26 Jul 2022 1:59] MySQL Verification Team

Hi,

Thanks, I verified it just now, thanks for the bug report!

kind regards

[28 Sep 2022 11:09] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Router 8.0.31 release notes:

MySQL Router stopped responding when calling a stored procedure with a prepared statement.