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

RBR replication loses data silently ignoring important column attributes

    Details

      Description

      RBR replication does not store enough column attributes, which leads to silent data loss.

      These important attributes are known not to be stored:

      • The UNSIGNED flag for INT-alike columns
      • The character set for STRING columns

      It should be fixed to store all important attributes in the binary log.
      Note, we'll need to store the precise data type name for pluggable data types soon (MDEV-4912). It would be nice to fix this problem at once.

      Detailed description:

      The UNSIGNED flag

      In the following test I create a table with an INT UNSIGNED column on the master, but on the slave I intentionally remove the UNSIGNED flag, to make fields not fully compatible.

      I create a test file AAA-INT.test with the following content:

      --source include/master-slave.inc
       
      connection master;
      CREATE TABLE t1 (a INT UNSIGNED);
      sync_slave_with_master;
      connection slave;
       
      SET GLOBAL slave_type_conversions='';
       
      ALTER TABLE t1 MODIFY a INT;
      connection master;
       
      INSERT INTO t1 VALUES (0);
      INSERT INTO t1 VALUES (0xFFFFFFFF);
       
      sync_slave_with_master;
      connection slave;
      SELECT * FROM t1;
      SET GLOBAL slave_type_conversions=DEFAULT;
       
      connection master;
      DROP TABLE t1;
      sync_slave_with_master;
       
      --source include/rpl_end.inc
      

      Now if I run "./mtr AAA-INT,row", the test works without errors and displays the following output:

      ..
      connection slave;
      SELECT * FROM t1;
      a
      0
      -1
      ..
      

      Notice:

      • The slave did not refuse such conversion, ignoring the fact that ALL_LOSSY is not enabled in slave_type_conversions.
      • It silently re-interpreted a huge unsigned value to -1.

      Note, if I run "./mtr AAA-INT,stmt", the test returns on an error as expected:

      Last_Error	Error 'Out of range value for column 'a' at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (0xFFFFFFFF)'
      

      The character set

      The same problem happens with string fields having different character sets.
      I create a test file AAA-STR.test with the following content:

      --source include/master-slave.inc
       
      connection master;
      SET NAMES utf8;
      CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1);
      sync_slave_with_master;
       
      connection slave;
      SET NAMES utf8;
      SET GLOBAL slave_type_conversions='';
      ALTER TABLE t1 MODIFY a CHAR(10) CHARACTER SET latin2;
      connection master;
      INSERT INTO t1 VALUES (_latin1 0xC0);
      SELECT a FROM t1;
       
      sync_slave_with_master;
      connection slave;
      SELECT * FROM t1;
      SET GLOBAL slave_type_conversions=DEFAULT;
       
      connection master;
      DROP TABLE t1;
      sync_slave_with_master;
       
      --source include/rpl_end.inc
      

      Now I run "./mtr AAA-STR,row", the test works without errors and displays the following output:

      ...
      connection master;
      INSERT INTO t1 VALUES (_latin1 0xC0);
      SELECT a FROM t1;
      a
      À
      connection slave;
      connection slave;
      SELECT * FROM t1;
      a
      Ŕ
      ...
      

      Notice:

      • The slave did not refuse such conversion, ignoring the fact that ALL_LOSSY is not enabled in slave_type_conversions.
      • It silently re-interpreted U+00C0 LATIN CAPITAL LETTER A WITH GRAVE to U+0154 LATIN CAPITAL LETTER R WITH ACUTE

      Note, if I run "./mtr AAA-STR,stmt", the test returns on an error as expected:

      Last_Error	Error 'Incorrect string value: '\xC0' for column `test`.`t1`.`a` at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (_latin1 0xC0)'
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sachin.setiya.007 Sachin Setiya
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: