[MDEV-24460] Multiple rows result set returned from stored routine over prepared statement binary protocol is handled incorrectly Created: 2020-12-21  Updated: 2021-06-17  Resolved: 2021-02-07

Status: Closed
Project: MariaDB Server
Component/s: Tests
Affects Version/s: 10.6.0
Fix Version/s: 10.6.0

Type: Bug Priority: Major
Reporter: Dmitry Shulga Assignee: Dmitry Shulga
Resolution: Fixed Votes: 0
Labels: None


 Description   

While working on the task MDEV-16708 I've discovered incorrect behavior of client library or mysqltest client utility.
If the following test case be executed against a server implemented the task MDEV-16708 (it can be pulled from the branch 10.6-MDEV-16708) it produces incorrect results.

Test's content:

$ cat mysql-test/main/ps_ps_1.test
 
 
 
delimiter $;
 
 
 
create procedure p1(x int)
 
foo: loop
 
  if x = 0 then
 
    leave foo;
 
  end if;
 
  select 'test' AS test_column;
 
 
 
  SET @output_result=CONCAT(IFNULL(@output_result, ''), 'test ');
 
  set x = x-1;
 
end loop foo;
 
$
 
 
 
delimiter ;$
 
 
 
call p1(2);
 
 
 
SELECT @output_result;
 
drop procedure p1;

This test case creates the procedure p1() that iterates as many times as specified by its parameter value and executes the statement "select 'test' AS test_column;" on every iteration.
Additionally, the value 'test' is appended to the session variable @output_result.

So, it is expected that this test case outputs is:

test_column
 
test
 
test_column
 
test
 
SELECT @output_result;
 
@output_result
 
test test

Unfortunately,
actual result is pretty different.

Test output is below. I marked my comments with the prefix '<<<<'

create procedure p1(x int)
 
foo: loop
 
  if x = 0 then
 
    leave foo;
 
  end if;
 
  select 'test' AS test_column;
 
  SET @output_result=CONCAT(IFNULL(@output_result, ''), 'test ');
 
  set x = x-1;
 
end loop foo;
 
$
 
call p1(2);   <<<< Invocation of p1(2) should produce two rows with column name 'test_column' and columnd value 'test'. Instead the only one column is shown in output.  
test_column
 
test
 
SELECT @output_result;
 
@output_result
 
test test  <<< === Output of a value of the variable '@output_result' shows that the 'foo' loop body is executed twice as it should. 
drop procedure p1;

To determine who is blame - server or client - in wrong test output I debugged the server and and printed a content of the buffer that is sent from server to the client during execution of the CALL statement.

Logic for handling of the CALL statement is implement by the method execute() of the class Sql_cmd_call. So I ran mtr test under debugger, set breakpoint to this member and ran the test case.

When server execution under debugger hit the method Sql_cmd_call::execute
I set the new breakpoint on the function net_real_write() that sends actual data to a peer via socket.

In result I discovered two invocations of net_real_write () as it was expected (every time it was done right after finishing execution of the function execute_sqlcom_select() and it is also expected time and place to do it).

Both time the same buffer content was output to socket:

02000001010122000002036465660000000B746573745F636F6C756D6E00000C0800040000
 
00FD010027000005000003FE00000A00070000040000047465737405000005FE00000A00

Parsed representation of this buffer is below:

02000001 -- packet #1, 2 bytes
 
0101
 
 
 
22000002 -- packet #2, 34 bytes, parsed packet's content is below
 
metadata packet:
 
03646566 -- <lenenc> 'def'
 
00 -- <lenenc> empty schema
 
00 -- <lenenc> empty table alias
 
00 -- <lenenc> empty table
 
0B 746573745F636F6C756D6E -- <lenenc> column_alias 'test_column' 
00 -- <lenenc> empty column
 
00 -- length extended info
 
0C -- lenght of fixed fields (0x0C)
 
0800 --  int<2> character set number (koi8r)
 
04000000 -- int<4> max column size
 
FD -- int<1> field_type (0xFD means MYSQL_TYPE_VAR_STRING)
 
0100 -- int<2> field detail flag (0x01 means NOT_NULL)
 
27 -- decimals
 
0000 -- int<2> unused
 
 
 
EOF Packet
 
FE00000A00
 
Parsed EOF Packet:
 
FE -- EOF Packet header
 
0000 -- warning count 
0A00 -- server status (SERVER_MORE_RESULTS_EXISTS | SERVER_STATUS_AUTOCOMMIT)
 
 
 
07000004  resultset row 
00 -- header 
00 -- null bitmap
 
04 74657374 string<4> 'test'
 
 
 
EOF Packet
 
FE00000A00
 
Parsed EOF Packet:
 
FE -- EOF Packet header
 
0000 -- warning count 
0A00 -- server status (SERVER_MORE_RESULTS_EXISTS | SERVER_STATUS_AUTOCOMMIT)

So, database server processed the query correctly and sent well-formed answer message in accrodning with binary protocol.
But on the client side the second packet

02000001010122000002036465660000000B746573745F636F6C756D6E00000C0800040000
 
00FD010027000005000003FE00000A00070000040000047465737405000005FE00000A00

was mishandled or silently ignored.

 Comments   
Comment by Georg Richter [ 2020-12-21 ]

Connector/C has a bunch of tests handling stored procedures with multiple result sets and output variables which all passes.

The problem is caused by mysqltest.cc, which only handles the first result set in run_query_stmt() function instead of looping through result sets with

do {
 
  /* process result set */
 
} while (!mysql_stmt_next_result(stmt));

I didn't check other versions but likely all server versions are affected.

Generated at Thu Feb 08 09:30:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.