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

Binary protocol returns wrong collation ID for SP OUT parameters

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
    • Fix Version/s: 10.3.3
    • Component/s: Stored routines
    • Labels:
      None

      Description

      I run this client program:

      #include <stdio.h>
      #include <stdlib.h>
      #include <string.h>
      #include <mysql.h>
       
       
      static void check_stmt_rc(int rc, MYSQL_STMT *stmt)
      {
        if (rc)
        {
          printf("check_stmt_rc failed\n");
          exit(0);
        }
      }
       
       
      #define FAIL_IF(expr, reason)\
      if (expr)\
      {\
        printf("Error: %s (%s: %d)\n", (reason) ? reason : "", __FILE__, __LINE__);\
        return 1;\
      }
       
       
      static int mysql_query_verbose(MYSQL *mysql, const char *query)
      {
        int rc;
        if ((rc= mysql_query(mysql, query)))
        {
          fprintf(stderr, "Query failed: rc=%d\n", rc);
          return 1;
        }
        return 0;
      }
       
       
      static void print_field_metadata(MYSQL_FIELD *field)
      {
        printf("Field: %s\n", field->name);
        printf("Type: %d\n", field->type);
        printf("Collation: %d\n", field->charsetnr);
        printf("Length: %lu\n", field->length);
      }
       
       
      static int test_bind_out(MYSQL *mysql)
      {
        MYSQL_STMT *stmt;
        MYSQL_BIND bind;
        const char *stmtstr= "CALL P1(?)";
        char res[20];
        int rc;
       
        if ((rc= mysql_query_verbose(mysql, "SET NAMES latin1")) ||
            (rc= mysql_query_verbose(mysql, "DROP PROCEDURE IF EXISTS p1")) ||
            (rc= mysql_query_verbose(mysql,
                                     "CREATE PROCEDURE p1"
                                     "("
                                     "  OUT param1 TEXT CHARACTER SET utf8"
                                     ")"
                                     "BEGIN "
                                     "  SET param1 = _utf8'test\xC3\xBF'; "
                                     "END")))
          return rc;
       
        stmt= mysql_stmt_init(mysql);
        rc= mysql_stmt_prepare(stmt, stmtstr, strlen(stmtstr));
        check_stmt_rc(rc, stmt);
       
        FAIL_IF(mysql_stmt_param_count(stmt) != 1, "expected param_count=1");
       
        bind.buffer_type= MYSQL_TYPE_NULL;
        rc= mysql_stmt_bind_param(stmt, &bind);
        check_stmt_rc(rc, stmt);
       
        rc= mysql_stmt_execute(stmt);
        check_stmt_rc(rc, stmt);
       
        memset(res, 0, sizeof(res));
        memset(&bind, 0, sizeof(bind));
        bind.buffer_type= MYSQL_TYPE_STRING;
        bind.buffer_length= sizeof(res);
        bind.buffer= res;
       
        do {
          if (mysql->server_status & SERVER_PS_OUT_PARAMS)
          {
            printf("OUT param result set:\n");
            FAIL_IF(mysql_stmt_field_count(stmt) != 1, "expected 1 columns");
            print_field_metadata(&stmt->fields[0]);
       
            rc= mysql_stmt_bind_result(stmt, &bind);
            check_stmt_rc(rc, stmt);
            rc= mysql_stmt_fetch(stmt);
            check_stmt_rc(rc, stmt);
            printf("Value: %s\n", res);
          }
          else if (mysql_stmt_field_count(stmt))
          {
            printf("sp result set\n");
          }
        } while (mysql_stmt_next_result(stmt) == 0);
       
        rc= mysql_stmt_close(stmt);
        return rc;
      }
       
       
      int main()
      {
        MYSQL mysql;
        int rc;
       
        mysql_init(&mysql);
        if (!mysql_real_connect(&mysql, "localhost", "root", "", "test", 3306,
                                "/tmp/mysql.sock", 0))
        {
          fprintf(stderr, "Connect failed: %s\n", mysql_error(&mysql));
          return 1;
        }
       
        rc= test_bind_out(&mysql);
       
        mysql_close(&mysql);
        return rc;
      }
      

      It prints the following output:

      OUT param result set:
      Field: param1
      Type: 252
      Collation: 63
      Length: 65535
      Value: testÿ
      

      Notice:

      • Collation is erroneously set to 63 (which is BINARY)
      • The value testÿ came in utf8 representation, without conversion to latin1, ignoring the SET NAMES command.

      The expected result would be to return the same metadata which is returned from this script sent to "mysql --column-type-info test":

      SET NAMES latin1;
      DROP FUNCTION IF EXISTS f1;
      CREATE OR REPLACE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8 RETURN CONCAT('test',_utf8 0xC3BF);
      SELECT f1();
      

      which returns:

      Field   1:  `f1()`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       BLOB
      Collation:  latin1_swedish_ci (8)
      Length:     65535
      Max_length: 5
      Decimals:   0
      Flags:      BLOB 
       
      +-------+
      | f1()  |
      +-------+
      | test´┐Ż  |
      +-------+
      

      Notice:

      • Collation is 8, in accordance with the SET NAMES command.
      • The value was converted from utf8 to latin1

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: