MariaDB Development
  1. MariaDB Development
  2. MDEV-4489

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

    Details

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

      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'

        Activity

        Hide
        Alexander Barkov added a comment -

        A patch sent for review.

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

        Approved. Pushed into mariadb-5.5.31.

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

          People

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

            Dates

            • Created:
              Updated:
              Resolved: