No response for executing the stored procedure output with select statement.

wjp1975 commented 2 months ago

If you are submitting a reproducible bug report, please provide:

[ ] A clear description of the issue Using the node mysql2 library to invoke the stored procedure with select statement in the mysql database through proxysql proxy, there is no response and the connection is disconnected. This is normal when a stored procedure is called without a select statement.

node version: v18.16.1 node mysql2 lib version: "mysql2": "^3.9.2"

mysql version: "5.7.44-log"

[ ] ProxySQL version v2.7.0
[ ] OS version Debian 10
[ ] The steps to reproduce the issue step 1. create procedure

stored procedure 1:

DELIMITER $$ CREATE PROCEDURE GetSquare_OK(IN input_number INT) BEGIN DECLARE output_result INT DEFAULT 0; SET output_result = input_number * input_number;
END $$ DELIMITER ;

stored procedure 2:

DELIMITER $$ CREATE PROCEDURE GetSquare_fault(IN input_number INT) BEGIN DECLARE output_result INT DEFAULT 0; SET output_result = input_number * input_number; -- No response when using select output SELECT output_result AS SquareValue; END $$ DELIMITER ;

step 2. test code: const mysql = require('mysql2/promise');

async function test(){ var poolCluster
let selector = "RR" poolCluster = mysql.createPoolCluster({ removeNodeErrorCount: 10, defaultSelector: selector });

poolCluster.add('clusterA',{
  host: '192.168.3.170',
  port: 16033,  //proxysql port      
  user: 'user26778',
  password: 'pass99257',
  database: 'demo',
  debug: true,
  charset: "UTF8MB4_UNICODE_CI",
  trace: true      
});

let connection = await poolCluster.getConnection();
if (connection){
    try {
        //case 1: it's normal with "call demo.GetSquare_OK(?)"
        var callProcedureSQL1 = "call demo.GetSquare_OK(?)"
        var values = [6]
        var [rows, fields] = await connection.execute(callProcedureSQL1, values);
        console.log("case1: call procedure sql=", callProcedureSQL1, ",result rows=", rows, ",fields=", fields);

        //case 2: no response with "call demo.GetSquare_fault(?)"
        var callProcedureSQL2 = "call demo.GetSquare_fault(?)"  //it's normal with "select 1788 as num", the proxysql and mysql configurations are correct
        var values = [6]
        var [rows, fields] = await connection.execute(callProcedureSQL2, values);
        console.log("case2: call procedure sql=", callProcedureSQL2, ",result rows=", rows, ",fields=", fields);        

    } catch(err) {
        console.log(err);        
        console.log("$$$ error reason=",err.message);
    } finally{
        connection.release();
    }
}
poolCluster.end()

}

test();

step 3. the problem has been reproduce The stored procedure has actually been executed, but there is no response.

[ ] The full ProxySQL error log (default location: /var/lib/proxysql/proxysql.log)

If the above information is not provided, this issue is likely to be closed.

Please use markdown to format any code or SQL: https://guides.github.com/features/mastering-markdown/

Thank you!

Sejcek1 commented 2 months ago

We found similar issue some time ago, but now we done deeper investigation about it.

Description of the Issue:

When connecting to MariaDB via ProxySQL, we encountered a situation where MariaDB J client correctly sets both the Multiple results and PS Multiple results flags, but ProxySQL, when creating the connection to the backend server, sets Multiple results: Set but PS Multiple results: Not set. This causes errors when a stored procedure returns multiple result sets like SELECT NOW() executed within the procedure.

Error Encountered:

The following error appears during query execution after running a stored procedure that includes intermediate SELECT statements:

2024-09-14 02:01:00 MySQL_PreparedStatement.cpp:424:update_metadata(): [WARNING] Updating metadata for stmt 44 , user app_xx, query call `xx`.`xxDeleleByValidTo` (?) 
2024-09-14 02:01:04 MySQL_Session.cpp:4810:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (50,ipmydbxxx,3306,2565046) , FD (Conn:125 , MyDS:125) , user app_xx , last_used 4090ms ago : 2014, Commands out of sync; you can't run this command now
2024-09-14 02:01:04 MySQL_Session.cpp:4821:handler_minus1_ClientLibraryError(): [WARNING] Disabling query retry because SERVER_MORE_RESULTS_EXIST is set
2024-09-14 10:01:03 MySQL_Thread.cpp:4199:process_all_sessions(): [WARNING] Closing killed client connection 127.0.0.1:59082

obrazek START - ping before send query 1 - Execute of prepared statement 2 - response with SELECT NOW() 3 - result of stored procedure 4 - bad handled response to client by ProxySQL with SERVER_MORE_RESULTS_EXIST flag to client 5 - reset of backend connection with "Detected a broken connection" ERROR, but client still waiting for data for 8 hours 6 - Close of client's connection on ProxySQL frontend with application error after mysql-wait_timeout

Steps to Reproduce:

Set up a stored procedure in MariaDB that executes multiple SELECT statements, such as SELECT NOW() or other queries returning intermediate results.
Connect to the MariaDB server through ProxySQL.
Execute the procedure using a client such as the MariaDB Java Connector or other client with binary mysql protocol.
Observe the errors.

Possible Impact:

This issue is likely to affect any users running stored procedures that return multiple result sets while using prepared statements through ProxySQL. It can lead to query synchronization errors and incomplete result processing so ProxySQL is not compatible with intermediate results throught stored procedures with binary mysql protocol. With text protocol (like CLI client), there is no problem because ProxySQL handles it like text.

Expected Behavior:

ProxySQL should pass along the PS Multiple results flag to the backend MariaDB server in order to support multiple result sets from prepared statements and handle the results correctly.

Environment:

ProxySQL Version: 2.5.x, 2.6.x
MariaDB Version: 10.4.x, 10.11.x
Client: MariaDB Java Client 3.3.3
Operating System: RHEL8

renecannao commented 2 months ago

Hi @Sejcek1 . Thank you for the report and for the analysis. Can you please share the pcap file , to verify your claim?

Thanks

Sejcek1 commented 2 months ago

Hi @renecannao, Thank you for your response. I sent you used pcap to your info mail to check. Just write if you need to supply anything else.

renecannao commented 2 months ago

Hi @Sejcek1 . Thank you for the pcap file! After review it, I understood what you specified in the ticket, but it was very clear: the stored procedure is called using prepared statement!

This is a duplicate of #1752 . As described in #1752 , "Multi-resultset is not supported with prepared statements, but only with text protocol"

Sejcek1 commented 2 months ago

Hi @renecannao, I'm sorry for the inaccurate description.

It's a shame that the ProxySQL doesn't allow multiresult from stored procedure and that it doesn't report the missing support more clearly in the error message.

Is there any plan to support multiresults from stored procedures in the future releases? Or do you mean the multiresult from stored procedures are supported if there is no prepare statements used?

At this time we do not need to use multiresult and we will modify the procedure so that it does not print the intermediate result, but in the future we plan to use procedures for some tasks. It is mainly about the resultsets where the database can do this more efficiently than the application, and the modification/tuning of this process is easier by modifying the procedure by DBA than by the development of the application.

renecannao commented 2 months ago

Hi @Sejcek1 .

ProxySQL doesn't allow multiresult from stored procedure

Please note this statement is inaccurate. ProxySQL allows multiresult from stored procedures! Multi-results is not supported in prepared statements. Also, for what is worth, MySQL doesn't even support multi-statements in prepared statement, thus the use of multi-results when multi-statements is not supported is an edge case.

Is there any plan to support multiresults from stored procedures in the future releases? Or do you mean the multiresult from stored procedures are supported if there is no prepare statements used?

Correct, multi-results from stored procedures are supported if there is no prepared statements: text protocol only.

My recommendation is to avoid using stored procedures in prepared statements and only use text protocol (no prepared statements). I don't see any significant advantage of using prepared statements (binary protocol) for executing stored procedures.

Sejcek1 commented 2 months ago

Hi @renecannao, thank you for the explanation.

I don't see any significant advantage of using prepared statements (binary protocol) for executing stored procedures.

This is related to one bug we dealt internally two years ago when implementing ProxySQL between the DB and one of our applications. In short - the application at some point wants to do something like: insert into tab(id) VALUES (xxx),(yyy),(zzz); When the mariadb J client connects directly to the DB server, it makes a prepare statement at that moment, otherwise it does not use them. However, when ProxySQL is placed between the client and the DB, the application has logged the prepare statement execute, but it done three solo insert statements (every value in own statement) without waiting for the result. It ended up with "Unexpected packet from client" error from ProxySQL and "java.sql.BatchUpdateException" from the application. After adding "useServerPrepStmts=true" to the connection string, the application works fine, but the connector makes a prepare statement for all queries except "SET".

I'm not sure if we are able to suppres that, but we'll try it... Thank you for your support.

sysown / proxysql