Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24460

Multiple rows result set returned from stored routine over prepared statement binary protocol is handled incorrectly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.0
    • Fix Version/s: 10.6.0
    • Component/s: Tests
    • 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.

        Attachments

          Activity

            People

            Assignee:
            shulga Dmitry Shulga
            Reporter:
            shulga Dmitry Shulga
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: