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

Column ... cannot be converted from type 'varchar(20)' to type 'varchar(20)'

Details

    Description

      Row based replication stopped with 

      Last_Errno: 1677
      		 
      Last_Error: Column 23 of table 'HALLEY.ANANCHC' cannot be converted from type 'varchar(20)' to type 'varchar(20)'

      unless 

      slave_type_conversion=ALL_NON_LOSSY

      was enabled.

      The types in the error message are both `varchar(20)`, and `SHOW CREATE TABLE` output and even `.frm` file contents are exactly the same on master and slave.

      There was a BINLOG'...' provided that indeed contained encoded length for the col #23 as 20 (bytes). The actual column on slave has it 60, due to UTF8.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Elkin Andrei Elkin added a comment - - edited

          After decoding that BINLOG'...' to discover 20 bytes for a column declared as
          `a_field` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ' ', I can only think of
          that BINLOG'...' was created against a different column definition, one that would clearly lack utf8 charset.

          Per direct verification 10.2.15 in particular creates BINLOG for the field with the value of 60 for its length, therefore
          no conversion issue would happen, in contrast to the claim.

          Elkin Andrei Elkin added a comment - - edited After decoding that BINLOG'...' to discover 20 bytes for a column declared as `a_field` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ' ' , I can only think of that BINLOG'...' was created against a different column definition, one that would clearly lack utf8 charset . Per direct verification 10.2.15 in particular creates BINLOG for the field with the value of 60 for its length, therefore no conversion issue would happen, in contrast to the claim.
          Elkin Andrei Elkin added a comment -

          sujatha.sivakumar,
          I think the case would be easier to understand and to resolve should we have the following
          bugfixes cherry-picked from the upstream. I am resigning for your favor to implement that.
          Thanks!

          commit 47bd3f7cf3c8518f62b1580ec65af2ba7ac13b95
          Author: Sujatha Sivakumar <sujatha.sivakumar@oracle.com>
          Date: Fri Feb 24 10:53:23 2017 +0530

          Bug#25135304: RBR: WRONG FIELD LENGTH IN ERROR MESSAGE

          Description:
          ============
          In row based replication, when replicating from a table with
          a field with character set set to UTF8mb3 to the same table
          with the same field set to character set UTF8mb4 I get a
          confusing error message:

          Elkin Andrei Elkin added a comment - sujatha.sivakumar , I think the case would be easier to understand and to resolve should we have the following bugfixes cherry-picked from the upstream. I am resigning for your favor to implement that. Thanks! commit 47bd3f7cf3c8518f62b1580ec65af2ba7ac13b95 Author: Sujatha Sivakumar <sujatha.sivakumar@oracle.com> Date: Fri Feb 24 10:53:23 2017 +0530 Bug#25135304: RBR: WRONG FIELD LENGTH IN ERROR MESSAGE Description: ============ In row based replication, when replicating from a table with a field with character set set to UTF8mb3 to the same table with the same field set to character set UTF8mb4 I get a confusing error message:
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment -

          Cherry-pick is in progress.

          This will fix confusing error messages like shown below.

          Case 1:
          ======

          --source include/master-slave.inc
          		 
          --source include/have_binlog_format_row.inc
          		 
           
          		 
          SET SQL_LOG_BIN=0;
          		 
          CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3');
          		 
          SET SQL_LOG_BIN=1;
          		 
           
          		 
          --source include/rpl_connection_slave.inc
          		 
           
          		 
          SET SQL_LOG_BIN=0;
          		 
          CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4');
          		 
          SET SQL_LOG_BIN=1;
          		 
           
          		 
          --source include/rpl_connection_master.inc
          		 
           
          		 
          INSERT INTO t1 VALUES ('a');
          		 
           
          		 
          DROP TABLE t1;
          		 
           
          		 
          --source include/sync_slave_sql_with_master.inc
          		 
           
          		 
          --source include/rpl_end.inc

          2. Run the test case:

          perl mysql-test-run.pl --mem rpl_x.test

          3. Watch the test fail and look at the error message:

          [...]
          Last_Error Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)'
          [...]

          Case 2:
          ======

          --connection master
          		 
          SET SQL_LOG_BIN=0;
          		 
          CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3');
          		 
          SET SQL_LOG_BIN=1;
          		 
           
          		 
          --connection slave
          		 
          CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4');
          		 
           
          		 
          --connection master
          		 
          INSERT INTO t1 VALUES ('a');
          		 
           
          		 
          --connection slave
          		 
          --let $slave_sql_errno= 1677
          		 
          --source include/wait_for_slave_sql_error.inc

          1. Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'char(0)' to type 'char(1)'
          2. Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'char(3(bytes))' to type 'char(4(bytes) utf8mb4)'

          Case 3:
          ======

          --connection master
          		 
          SET SQL_LOG_BIN=0;
          		 
          CREATE TABLE t1 (c1 LONGBLOB);
          		 
          SET SQL_LOG_BIN=1;
          		 
           
          		 
          --connection slave
          		 
          CREATE TABLE t1 (c1 TINYBLOB);
          		 
           
          		 
          --connection master
          		 
          INSERT INTO t1 VALUES ('a');
          		 
           
          		 
          --connection slave
          		 
          --let $slave_sql_errno= 1677
          		 
          --source include/wait_for_slave_sql_error.inc

          1. Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob'
          2. Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob'
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Cherry-pick is in progress. This will fix confusing error messages like shown below. Case 1: ====== --source include/master-slave.inc --source include/have_binlog_format_row.inc   SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); SET SQL_LOG_BIN=1;   --source include/rpl_connection_slave.inc   SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); SET SQL_LOG_BIN=1;   --source include/rpl_connection_master.inc   INSERT INTO t1 VALUES ('a');   DROP TABLE t1;   --source include/sync_slave_sql_with_master.inc   --source include/rpl_end.inc 2. Run the test case: perl mysql-test-run.pl --mem rpl_x.test 3. Watch the test fail and look at the error message: [...] Last_Error Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)' [...] Case 2: ====== --connection master SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3'); SET SQL_LOG_BIN=1;   --connection slave CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4');   --connection master INSERT INTO t1 VALUES ('a');   --connection slave --let $slave_sql_errno= 1677 --source include/wait_for_slave_sql_error.inc Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'char(0)' to type 'char(1)' Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'char(3(bytes))' to type 'char(4(bytes) utf8mb4)' Case 3: ====== --connection master SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 LONGBLOB); SET SQL_LOG_BIN=1;   --connection slave CREATE TABLE t1 (c1 TINYBLOB);   --connection master INSERT INTO t1 VALUES ('a');   --connection slave --let $slave_sql_errno= 1677 --source include/wait_for_slave_sql_error.inc Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob' Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob'
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment -

          Hello Andrei,

          Please review the fixes for MDEV-19925.

          There is a small change in the patch apart from the cherry-pick.
          I adapted the tests as per MariaDB- 10.2. Fixed test result failures.
          Please find the following additional changes in code apart from the
          cherry-pick.

          sql/rpl_utility.cc

          + (field->real_type() != MYSQL_TYPE_ENUM &&
          + field->real_type() != MYSQL_TYPE_SET) &&

          Patch has been tested in build bot.
          BB link: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.2-sujatha
          Patch: https://github.com/MariaDB/server/commit/6aa04bcdd94da4dee23df48c7c53d8a0082a415f

          Thank you.

          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello Andrei, Please review the fixes for MDEV-19925 . There is a small change in the patch apart from the cherry-pick. I adapted the tests as per MariaDB- 10.2. Fixed test result failures. Please find the following additional changes in code apart from the cherry-pick. sql/rpl_utility.cc + (field->real_type() != MYSQL_TYPE_ENUM && + field->real_type() != MYSQL_TYPE_SET) && Patch has been tested in build bot. BB link: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.2-sujatha Patch: https://github.com/MariaDB/server/commit/6aa04bcdd94da4dee23df48c7c53d8a0082a415f Thank you.
          bar Alexander Barkov added a comment -

          Hi Sujatha,

          The patch
          https://github.com/MariaDB/server/commit/88945a168f679239cca2e597235e731ccc778999
          looks ok to push for me.

          Thanks!

          bar Alexander Barkov added a comment - Hi Sujatha, The patch https://github.com/MariaDB/server/commit/88945a168f679239cca2e597235e731ccc778999 looks ok to push for me. Thanks!
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment -

          Fix for this issue is implemented in 10.2.27.
          Fix has been tested on higher versions.

          Please find
          Patches for 10.3: https://github.com/MariaDB/server/commit/f5bed83a42f616e297380c8bcc2379316fe4d0d8

          10.4 changes: https://github.com/MariaDB/server/commit/dc0f2438b8d71ef6301f43009a0c8de54a8e945f

          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Fix for this issue is implemented in 10.2.27. Fix has been tested on higher versions. Please find Patches for 10.3: https://github.com/MariaDB/server/commit/f5bed83a42f616e297380c8bcc2379316fe4d0d8 10.4 changes: https://github.com/MariaDB/server/commit/dc0f2438b8d71ef6301f43009a0c8de54a8e945f

          People

            sujatha.sivakumar Sujatha Sivakumar (Inactive)
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.