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

Replication of big5, cp932, gbk, sjis strings makes wrong values on slave

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2, 5.5.30, 5.1.67
    • Fix Version/s: 10.0.3, 5.5.31
    • Component/s: None
    • Labels:
      None

      Description

      This does look to be a legitimate bug. This would apply to any character
      set where charset_info_st field escape_with_backslash_is_dangerous is
      true, which currently is: big5, cp932, gbk, sjis.

      The problem here is that string parameters coming from prepared
      statements are being converted into 0xHHHH form indiscriminately in
      append_query_string, which is producing the string to be binlogged for
      statement-based replication. While that works okay for insertion of
      strings into string fields, it causes the
      conversion-from-string-to-integer which is happening on the master for
      insertion of a string into an integer field to not be happening on the
      slave, since 0xHHHH form is more properly an integer than a string.

      This can be captured by setting a breakpoint at str_to_hex and running
      this test case:

      DROP TABLE IF EXISTS t;
      CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT, a INT, PRIMARY KEY(id));
      SET NAMES gbk;
      PREPARE STMT FROM 'INSERT INTO t (a) VALUES (?)';
      SET @a = '1';
      EXECUTE STMT USING @a;
      

      Using SHOW BINLOG EVENTS shows that the problem is from the server (binlogging) side:

          Log_name: 0.000001
               Pos: 450
        Event_type: Query
         Server_id: 1
      End_log_pos: 544
              Info: use `test`; INSERT INTO t (a) VALUES (0x31) 
      

      0xHHHH is a MySQL extension. It's a hybrid thing.
      It can behave as a number and a string depending on context.

      Binary log could use the X'HHHH' notation instead:
      INSERT INTO t1 VALUES (a) VALUES (X'31');

      which is an SQL standard thing, and which must always be a string.

      However, it seems the behaviour of X'HHHH' and of 0xHHHH
      is exactly the same, and X'HHHH' can also act as a number:

      drop table if exists t1;
      create table t1 (id int);
      insert into t1 values (x'31'),(0x31),(concat(0x31));
      select * from t1;
      +------+
      | id   |
      +------+
      |   49 | <-- wrong
      |   49 | <-- ok
      |    1 | <-- ok
      +------+ 
      

      Proposed fix:
      1. Fix X'HHHH' to work always as string.
      2. Fix binlog to use X'HHHH'

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            bar Alexander Barkov added a comment -

            A patch sent for review.

            Show
            bar Alexander Barkov added a comment - A patch sent for review.
            Hide
            bar Alexander Barkov added a comment -

            Approved. Pushed into mariadb-5.5.31.

            Show
            bar Alexander Barkov added a comment - Approved. Pushed into mariadb-5.5.31.

              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: