Lost Connection after calling Stored Procedure for ProxySQL v2.0.4

[jonathanvx]

DELIMITER $$
CREATE PROCEDURE tester()
  SQL SECURITY INVOKER
  BEGIN
    -- A multi select
    SELECT 1;
    SELECT 2;
    SELECT 3;
    SELECT 4;

  END$$

DELIMITER ;

mysql> call tester();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

+---+
| 4 |
+---+
| 4 |
+---+
1 row in set (0.00 sec)

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

[renecannao]

Bug verified, and a patch is ready. PR #2060

[renecannao]

Fixed in 2.0.5 .
Thank you for the report

[qed-motore]

We confirm you the bug is solved for console app in 2.0.5. But if you try to do the same by using C-API or other connectors like Ruby/mysql2, zdb..., any client queries with stored procedure go to halting. In case of C-API, when mysql_stmt_next_result method is called the system freezes.

[renecannao]

Re-opening until verified/fixed.
@qed-motore: if you could provide a reproducible test case it would be appreciated.
Thanks

[qed-motore]

@renecannao
Simple test code to reproduce halting state after mysql_stmt_next_result calling

/*
 * main.cpp
 *
 *  Created on: Jul 22, 2019
 *
 *  this code works if it is launched against real db
 *  but it fails if it is used with proxysql (v.2.0.5)
 *  process goes to halting when "mysql_stmt_next_result" is called
 *
 *	the sp used to reproduce is very simple, i think the malfunction
 *	happens with any sp
 *
 *  DELIMITER \\
 *  CREATE PROCEDURE `proxysql_dummySP` ( IN id INT )
 *  BEGIN
 *   SELECT id;
 *  END
 *  \\
 *
 *
 * target system: ubuntu16.04 - gcc version 5.4.0 20160609 (Ubuntu 5.4.0-6ubuntu1~16.04.11)
 * g++ -O0 -g3 -Wall -c -fmessage-length=0 -MMD -MP -MF"main.d" -MT"main.o" -o "main.o" "../main.cpp"
 * g++  -o "proxysql_dummySP"  ./main.o   -lmysqlclient
 *
 */

#include <iostream>
#include <sstream>
#include <string.h>
#include <mysql/mysql.h>


void bindMyUINT( MYSQL_BIND* bind, unsigned int* parametro, unsigned long* length, my_bool* is_null )
{
	enum_field_types type = MYSQL_TYPE_LONG;

	memset(bind, 0, sizeof(*bind));
    bind->buffer_type = (*is_null) ? MYSQL_TYPE_NULL : type;

    bind->buffer_length = 0;
	switch( type )
	{
	case MYSQL_TYPE_LONG:			bind->buffer_length = sizeof( int );		break;	// 4
	}

    bind->buffer = (void*)parametro;
    bind->is_null = is_null;
    bind->length = length;
	bind->is_unsigned = (my_bool)1;
}


int main( int argc, char* argv[] )
{

	if( argc < 5 )
	{
		std::cout << argv[ 0 ] << " <dbhost> <dbuser> <dbpass> <dbname> <dbport>" << std::endl;
		return 1;
	}



	std::string dbhost = argv[ 1 ];
	std::string dbuser = argv[ 2 ];
	std::string dbpass = argv[ 3 ];
	std::string dbname = argv[ 4 ];
	unsigned int dbport = atoi( argv[ 5 ] );




	MYSQL* dbh = mysql_init( NULL );
	if( ! dbh )
	{ std::cerr << "mysql_init" << std::endl; return 1; }

	dbh = mysql_real_connect( dbh, dbhost.c_str(), dbuser.c_str(), dbpass.c_str(), dbname.c_str(), dbport, 0, 0 );
	if( ! dbh )
	{ std::cerr << "mysql_real_connect" << std::endl; return 1; }


	unsigned int param = 999;
	{
		std::string sql( "CALL proxysql_dummySP( ? )" );
		MYSQL_STMT* stmt = mysql_stmt_init( dbh );
		if( ! stmt )
		{ std::cerr << "mysql_stmt_init" << std::endl; return 1; }

		int init_in_begin_retcode = 0;
		if( (init_in_begin_retcode = mysql_stmt_prepare( stmt, sql.c_str(), sql.length() )) )
		{
			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_prepare" << std::endl; return 1; }
		}

		MYSQL_BIND bind[ 1 ];
		unsigned long bind_length[ 1 ];
		my_bool bind_is_null[ 1 ];

		for( unsigned int i = 0; i < 1; i++ )
		{
			bind_length[ i ]	= 0;
			bind_is_null[ i ]	= 0;
		}

		bindMyUINT( & bind[ 0 ], & param, & bind_length[ 0 ], & bind_is_null[ 0 ] );

		int init_in_end_retcode = 0;
		if( (init_in_end_retcode = mysql_stmt_bind_param( stmt, bind )) )
		{
			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_bind_param" << std::endl; return 1; }
		}

		unsigned int result = 0;
		int update_exec_retcode = 0;
		if( ! (update_exec_retcode = mysql_stmt_execute( stmt )) ) {
			result = 1;
		}
		else {

			std::string reason;
			{
				std::stringstream f;
				f << "update exec [" << update_exec_retcode << " - " << mysql_stmt_error( stmt ) << "]";
				reason = f.str();
			}

			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_execute" << std::endl; return 1; }
		}




		MYSQL_RES* results = mysql_stmt_result_metadata( stmt );
		if( ! results )
		{
			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_result_metadata" << std::endl; return 1; }
		}

		MYSQL_BIND bind_select[ 1 ];
		unsigned long bind_select_length[ 1 ];
		my_bool bind_select_is_null[ 1 ];

		for( unsigned int i = 0; i < 1; i++ )
		{
			bind_select_length[ i ]		= 0;
			bind_select_is_null[ i ]	= 0;
		}

		param = 0;
		bindMyUINT( & bind_select[ 0 ], & param, & bind_select_length[ 0 ], & bind_select_is_null[ 0 ] );


		if( mysql_stmt_bind_result( stmt, bind_select ) )
		{
			mysql_free_result( results );
			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_bind_result" << std::endl; return 1; }
		}

		if( mysql_stmt_store_result( stmt ) )
		{
			mysql_free_result( results );
			mysql_stmt_close( stmt );
			{ std::cerr << "mysql_stmt_store_result" << std::endl; return 1; }
		}

		if( mysql_stmt_field_count( stmt ) > 0 )
			while( true )
			{
				int status = mysql_stmt_fetch( stmt );
				if( status == 1 || status == MYSQL_NO_DATA )
					break;
			}

		mysql_free_result( results );
		if( mysql_more_results( dbh ) )
			while( true )
			{
				bool isOk = mysql_stmt_free_result( stmt );
				int status = mysql_stmt_next_result( stmt );

				if( status < 0 )
					break;
				else if( status > 0 )
				{ std::cerr << "mysql_stmt_next_result" << std::endl; return 1; }
				else
				{}
			}

		int update_end_retcode = mysql_stmt_close( stmt );
		if( update_end_retcode != 0 )
		{
			{ std::cerr << "mysql_stmt_next_result" << std::endl; return 1; }
		}

		if( ! result )
		{ std::cerr << "update error" << std::endl; return 1; }
	}


	if( dbh )
		mysql_close( dbh );

	return 0;
}

[qed-motore]

@renecannao: the same code written in ruby.
If parameters point to proxysql, "dbh.next_result" calling doesn't return.
Code works fine when parameters point to real db.

`#!/usr/bin/env ruby
require 'mysql2'

raise "#{FILE} " if ARGV.size < 5

dbhost = ARGV[ 0 ]
dbuser = ARGV[ 1 ]
dbpass = ARGV[ 2 ]
dbname = ARGV[ 3 ]
dbport = ARGV[ 4 ].to_i

begin
dbh = Mysql2::Client.new( host: dbhost, username: dbuser, password: dbpass, database: dbname, port: dbport )

    stmt = dbh.prepare 'CALL proxysql_dummySP( ? )'
    res = stmt.execute 999
    res.each{ |row|
            puts row
    }

    dbh.next_result

    res = dbh.query( 'CALL proxysql_dummySP( 999 )' )
    res.each{ |row|
            puts row
    }

rescue Exception => e
puts e.to_s
end
`

[qed-motore]

@renecannao

my previous the statement:

any client queries with stored procedure go to halting

is not correct. You have to use stored procedure and prepare statement together if you want to reproduce the malfunction. The following C-code without prepare statement works.

if( mysql_query( dbh, "CALL proxysql_dummySP( 999 )" ) )
	return 0;

MYSQL_RES* result = mysql_store_result( dbh );
if( ! result )
	return 0;

unsigned int num_fields = mysql_num_fields( result );
MYSQL_ROW row = 0;
while( row = mysql_fetch_row( result ) )
{
	if( num_fields == 1 )
		unsigned int aValue= atoi( row[ 0 ] );
}
mysql_free_result( result );

if( mysql_more_results( dbh ) )
	while( true )
	{
		int status = mysql_next_result( dbh );
		if( status < 0 )
			break;
		else if( status > 0 )
			return 0;
		else
		{}
	}

[renecannao]

@qed-motore : thanks for the sample code.
I reviewed it and confirm the issue: this is a duplicate of #1744 .

[proxima-b-alpha]

@renecannao Any updates on this issue? Due to this we are not able to take proxysql to our production!!

[teewhey]

I'm facing this issue too. Calling storedproc to proxysql via PHP failed for me as well.

[scouture-bw]

Dealing with the same issue here as well. Whenever the client issues a CALL() query, it gets no answer from ProxySQL.

[ivan1221]

This problem seems not to be solved. I'm currently dealing with it in a golang app. Any update?

@renecannao

[anuragagarwal561994]

@renecannao do we know how this can be solved, this seems like an important ask. While exploring proxy-sql, it fits almost all of our use cases except for this one for now.