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

Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode

Details

    Description

      Test case 1

      --source include/have_binlog_format_row.inc
      --source include/master-slave.inc
       
      CREATE TABLE t1 (a BLOB);
      INSERT INTO t1 VALUES (0);
      SET SQL_MODE= 'ORACLE';
      CREATE TABLE t2 SELECT * FROM t1;
      --sync_slave_with_master
       
      # Cleanup
      --connection master
      DROP TABLE t1, t2;
      --source include/rpl_end.inc
      

      10.3 617d34ae

      2019-05-29 15:19:53 13 [ERROR] Slave SQL: Column 0 of table 'test.t2' cannot be converted from type 'tinyblob' to type 'longblob', Gtid 0-1-3, Internal MariaDB error code: 1677
      2019-05-29 15:19:53 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 708
      

      master-bin.000001	708	Gtid	1	750	BEGIN GTID 0-1-3
      master-bin.000001	750	Query	1	862	use `test`; CREATE TABLE "t2" (
        "a" blob DEFAULT NULL
      )
      master-bin.000001	862	Annotate_rows	1	917	CREATE TABLE t2 SELECT * FROM t1
      master-bin.000001	917	Table_map	1	963	table_id: 33 (test.t2)
      master-bin.000001	963	Write_rows_v1	1	1000	table_id: 33 flags: STMT_END_F
      master-bin.000001	1000	Query	1	1073	COMMIT
      

      Same idea, different types:

      Test case 2

      --source include/have_binlog_format_row.inc
      --source include/master-slave.inc
       
      CREATE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES (NULL);
      SET SQL_MODE= 'ORACLE';
      CREATE TABLE t2 SELECT * FROM t1;
      --sync_slave_with_master
       
      # Cleanup
      --connection master
      DROP TABLE t1, t2;
      --source include/rpl_end.inc
      

      2019-05-29 19:53:05 13 [ERROR] Slave SQL: Column 0 of table 'test.t2' cannot be converted from type 'date' to type 'datetime', Gtid 0-1-3, Internal MariaDB error code: 1677
      2019-05-29 19:53:05 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 707
      

      Attachments

        Issue Links

          Activity

            Elkin Andrei Elkin added a comment - - edited

            sujatha.sivakumar: So sql_mode force the input DATE type in CREATE TABLE become DATETYPE as SHOW can prove:

            15:58:33 [test]> set @@sql_mode='oracle';
            Query OK, 0 rows affected (0.00 sec)
             
            15:58:39 [test]> CREATE TABLE t1_o (a DATE);
             
            15:58:55 [test]> show create table t1_o;
            +-------+-----------------------------------------------------+
            | Table | Create Table                                        |
            +-------+-----------------------------------------------------+
            | t1_o  | CREATE TABLE "t1_o" (
              "a" datetime DEFAULT NULL
            ) 
            

            Notice CREATE-LIKE when run in oracle mode also converts (NO, it does not, the created one should have been LIKE t1 not what is below):

            15:58:55 [test]> show create table t1_o;
            +-------+-----------------------------------------------------+
            | Table | Create Table                                        |
            +-------+-----------------------------------------------------+
            | t1_o  | CREATE TABLE "t1_o" (
              "a" datetime DEFAULT NULL
            ) 
             
            15:59:00 [test]> create table t1_o2 like t1_o;
             
            15:59:37 [test]> show create table t1_o;
            +-------+-----------------------------------------------------+
            | Table | Create Table                                        |
            +-------+-----------------------------------------------------+
            | t1_o  | CREATE TABLE "t1_o" (
              "a" datetime DEFAULT NULL
            )
            

            But CREATE-SELECT does not -

            15:59:49 [test]> CREATE TABLE t1_cs_o SELECT * from t1;
             
            16:00:29 [test]> show create table t1_cs_o;
            +---------+----------------------------------------------------+
            | Table   | Create Table                                       |
            +---------+----------------------------------------------------+
            | t1_cs_o | CREATE TABLE "t1_cs_o" (
              "a" date DEFAULT NULL
            )
            

            which must be the reason of the bug.
            The fixes must make sure the conversion takes place in this case as well.
            This is not really the replication issue and if you feel clueless about how to enforce the conversion we may send the bug to runtime.

            Elkin Andrei Elkin added a comment - - edited sujatha.sivakumar : So sql_mode force the input DATE type in CREATE TABLE become DATETYPE as SHOW can prove: 15 : 58 : 33 [test]> set @ @sql_mode = 'oracle' ; Query OK, 0 rows affected ( 0.00 sec)   15 : 58 : 39 [test]> CREATE TABLE t1_o (a DATE);   15 : 58 : 55 [test]> show create table t1_o; +-------+-----------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------+ | t1_o | CREATE TABLE "t1_o" ( "a" datetime DEFAULT NULL ) Notice CREATE-LIKE when run in oracle mode also converts ( NO , it does not, the created one should have been LIKE t1 not what is below): 15 : 58 : 55 [test]> show create table t1_o; +-------+-----------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------+ | t1_o | CREATE TABLE "t1_o" ( "a" datetime DEFAULT NULL )   15 : 59 : 00 [test]> create table t1_o2 like t1_o;   15 : 59 : 37 [test]> show create table t1_o; +-------+-----------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------+ | t1_o | CREATE TABLE "t1_o" ( "a" datetime DEFAULT NULL ) But CREATE-SELECT does not - 15 : 59 : 49 [test]> CREATE TABLE t1_cs_o SELECT * from t1;   16 : 00 : 29 [test]> show create table t1_cs_o; +---------+----------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------+ | t1_cs_o | CREATE TABLE "t1_cs_o" ( "a" date DEFAULT NULL ) which must be the reason of the bug. The fixes must make sure the conversion takes place in this case as well. This is not really the replication issue and if you feel clueless about how to enforce the conversion we may send the bug to runtime.

            Andrei, I can't repeat your results.

            These two scripts:

            SET sql_mode=ORACLE;
            CREATE OR REPLACE TABLE t1 (a DATE);
            CREATE OR REPLACE TABLE t2 LIKE t1;
            DESC t2;
            

            SET sql_mode=ORACLE;
            CREATE OR REPLACE TABLE t1 (a DATE);
            CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1;
            DESC t2;
            

            both return:

            +-------+----------+------+-----+---------+-------+
            | Field | Type     | Null | Key | Default | Extra |
            +-------+----------+------+-----+---------+-------+
            | a     | datetime | YES  |     | NULL    |       |
            +-------+----------+------+-----+---------+-------+
            

            bar Alexander Barkov added a comment - Andrei, I can't repeat your results. These two scripts: SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t1 (a DATE ); CREATE OR REPLACE TABLE t2 LIKE t1; DESC t2; SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t1 (a DATE ); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1; DESC t2; both return: +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | a | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+
            Elkin Andrei Elkin added a comment -

            bar Alexander, thanks a lot for trying to help. I did not mention ROW binlog format (as being mentioned already in the desc) which is cruicial. Here is my re-try:

            18:33:27 [test]> create  table t_date (d date);
            18:33:51 [test]> show create table t_date;
            +--------+----------------------------------------------------------------------------------------+
            | Table  | Create Table                                                                           |
            +--------+----------------------------------------------------------------------------------------+
            | t_date | CREATE TABLE `t_date` (
              `d` date DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
             
            18:33:57 [test]> set @@sql_mode='oracle';
            18:34:04 [test]> set @@binlog_format=row;
             
            18:35:32 [test]> CREATE TABLE t_o_cs SELECT * from t_date /* in oracle mode */;
            Query OK, 0 rows affected (0.00 sec)
            Records: 0  Duplicates: 0  Warnings: 0
             
            18:35:41 [test]> show create table t_o_cs;
            +--------+---------------------------------------------------+
            | Table  | Create Table                                      |
            +--------+---------------------------------------------------+
            | t_o_cs | CREATE TABLE "t_o_cs" (
              "d" date DEFAULT NULL
            )
            

            Elkin Andrei Elkin added a comment - bar Alexander, thanks a lot for trying to help. I did not mention ROW binlog format (as being mentioned already in the desc) which is cruicial. Here is my re-try: 18 : 33 : 27 [test]> create table t_date (d date); 18 : 33 : 51 [test]> show create table t_date; +--------+----------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------+ | t_date | CREATE TABLE `t_date` ( `d` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |   18 : 33 : 57 [test]> set @ @sql_mode = 'oracle' ; 18 : 34 : 04 [test]> set @ @binlog_format =row;   18 : 35 : 32 [test]> CREATE TABLE t_o_cs SELECT * from t_date /* in oracle mode */ ; Query OK, 0 rows affected ( 0.00 sec) Records: 0 Duplicates: 0 Warnings: 0   18 : 35 : 41 [test]> show create table t_o_cs; +--------+---------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------+ | t_o_cs | CREATE TABLE "t_o_cs" ( "d" date DEFAULT NULL )

            If I create the table with the DATE data type with the default sql_mode, and then run CREATE..LIKE or CREATE..SELECT with sql_mode=ORACLE, both create a column of the DATE data type:

            SET sql_mode=DEFAULT;
            CREATE OR REPLACE TABLE t1 (a DATE);
            SET sql_mode=ORACLE;
            CREATE OR REPLACE TABLE t2 LIKE t1;
            DESC t2;
            

            +-------+------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------+------+-----+---------+-------+
            | a     | date | YES  |     | NULL    |       |
            +-------+------+------+-----+---------+-------+
            

            SET sql_mode=DEFAULT;
            CREATE OR REPLACE TABLE t1 (a DATE);
            SET sql_mode=ORACLE;
            CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1;
            DESC t2;
            

            +-------+------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +-------+------+------+-----+---------+-------+
            | a     | date | YES  |     | NULL    |       |
            +-------+------+------+-----+---------+-------+
            

            So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server.

            bar Alexander Barkov added a comment - If I create the table with the DATE data type with the default sql_mode, and then run CREATE..LIKE or CREATE..SELECT with sql_mode=ORACLE, both create a column of the DATE data type: SET sql_mode= DEFAULT ; CREATE OR REPLACE TABLE t1 (a DATE ); SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t2 LIKE t1; DESC t2; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | a | date | YES | | NULL | | +-------+------+------+-----+---------+-------+ SET sql_mode= DEFAULT ; CREATE OR REPLACE TABLE t1 (a DATE ); SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1; DESC t2; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | a | date | YES | | NULL | | +-------+------+------+-----+---------+-------+ So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server.
            bar Alexander Barkov added a comment - - edited

            The problem is that:

            SET sql_mode=ORACLE;
            CREATE TABLE t2 AS SELECT real_date_column FROM t1;
            

            is replicated as:

            SET sql_mode=ORACLE;
            CREATE TABLE t2 (a DATE); -- this is wrong
            INSERT INTO t2 SELECT real_date_column FROM t1;
            

            It should be something like:

            SET sql_mode=ORACLE;
            CREATE TABLE t2 (a MARIADB_DATE); -- parser independent data type name
            INSERT INTO t2 SELECT real_date_column FROM t1;
            

            bar Alexander Barkov added a comment - - edited The problem is that: SET sql_mode=ORACLE; CREATE TABLE t2 AS SELECT real_date_column FROM t1; is replicated as: SET sql_mode=ORACLE; CREATE TABLE t2 (a DATE ); -- this is wrong INSERT INTO t2 SELECT real_date_column FROM t1; It should be something like: SET sql_mode=ORACLE; CREATE TABLE t2 (a MARIADB_DATE); -- parser independent data type name INSERT INTO t2 SELECT real_date_column FROM t1;
            Elkin Andrei Elkin added a comment - - edited
            So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server.

            You're correct. Sorry for confusing, in my paste I compared CREATE from different "originals".

            Elkin Andrei Elkin added a comment - - edited So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server. You're correct. Sorry for confusing, in my paste I compared CREATE from different "originals".
            bar Alexander Barkov added a comment - - edited

            Hi Elkin,

            Serg proposed an idea.

            Why we cannot replicate this:

            SET sql_mode=ORACLE;
            CREATE TABLE t2 AS SELECT real_date_column FROM t1;
            

            as

            SET sql_mode=ORACLE;
            CREATE TABLE t2 AS SELECT real_date_column FROM t1 LIMIT 0; -- this will create DATE column as expected
            INSERT INTO t2 VALUES (row1);
            INSERT INTO t2 VALUES (rowN);
            

            instead of the current way (which involves the parser):

            SET sql_mode=ORACLE;
            CREATE TABLE t2 AS SELECT (real_date_column DATE); -- this erroneously creates a DATETIME column
            INSERT INTO t2 VALUES (row1);
            INSERT INTO t2 VALUES (rowN);
            

            Elkin, what do you think?

            bar Alexander Barkov added a comment - - edited Hi Elkin , Serg proposed an idea. Why we cannot replicate this: SET sql_mode=ORACLE; CREATE TABLE t2 AS SELECT real_date_column FROM t1; as SET sql_mode=ORACLE; CREATE TABLE t2 AS SELECT real_date_column FROM t1 LIMIT 0; -- this will create DATE column as expected INSERT INTO t2 VALUES (row1); INSERT INTO t2 VALUES (rowN); instead of the current way (which involves the parser): SET sql_mode=ORACLE; CREATE TABLE t2 AS SELECT (real_date_column DATE ); -- this erroneously creates a DATETIME column INSERT INTO t2 VALUES (row1); INSERT INTO t2 VALUES (rowN); Elkin , what do you think?
            Elkin Andrei Elkin added a comment -

            bar You must mean to place into binlog CREATE-SELECT-LIMIT-0 as the table def, instead of the plain CREATE. Then CREATE-LIKE would be a bit more suitable, right?

            Either way I think it makes sense. Another question comes should we always binlog such way or at sql_mode = 'oracle' and such?

            Elkin Andrei Elkin added a comment - bar You must mean to place into binlog CREATE-SELECT-LIMIT-0 as the table def, instead of the plain CREATE . Then CREATE-LIKE would be a bit more suitable, right? Either way I think it makes sense. Another question comes should we always binlog such way or at sql_mode = 'oracle' and such?
            Elkin Andrei Elkin added a comment -

            bar After discussing with sujatha.sivakumar, I also feel we could consider to omit sql_mode='oracle' in Query_log_event if that can help to log it correctly.

            Elkin Andrei Elkin added a comment - bar After discussing with sujatha.sivakumar , I also feel we could consider to omit sql_mode='oracle' in Query_log_event if that can help to log it correctly.

            Elkin, I don't think CREATE-LIKE would be suituble all to replicate CREATE-TABLE-SELECT, because SELECT can contain joins or unions.

            bar Alexander Barkov added a comment - Elkin , I don't think CREATE-LIKE would be suituble all to replicate CREATE-TABLE-SELECT, because SELECT can contain joins or unions.

            Elkin, no, definitely not CREATE ... LIKE it produces different results. In particular, it creates indexes that CREATE ... SELECT doesn't. And you cannot do CREATE ... LIKE on, say, SELECT 1 from t1.

            Another option, indeed, would be to log it as

            set statement sql_mode='' create table t1 (...);
            

            serg Sergei Golubchik added a comment - Elkin , no, definitely not CREATE ... LIKE it produces different results. In particular, it creates indexes that CREATE ... SELECT doesn't. And you cannot do CREATE ... LIKE on, say, SELECT 1 from t1 . Another option, indeed, would be to log it as set statement sql_mode= '' create table t1 (...);

            I wonder whether it would work, given that MDEV-14760 (Replication does not take into account SET STATEMENT) is still open.

            elenst Elena Stepanova added a comment - I wonder whether it would work, given that MDEV-14760 (Replication does not take into account SET STATEMENT) is still open.
            Elkin Andrei Elkin added a comment -

            serg set statement sql_mode='' is inferior 'cos sql_mode is a member of Query_log_event::status_var. So regardless of MDEV-14760 we should not have Query_log_event::status_var::sql_mode = 'oracle'. And setting it to '' in status_var would fix the bug being of the same tech trouble.

            Elkin Andrei Elkin added a comment - serg set statement sql_mode='' is inferior 'cos sql_mode is a member of Query_log_event::status_var . So regardless of MDEV-14760 we should not have Query_log_event::status_var::sql_mode = 'oracle' . And setting it to '' in status_var would fix the bug being of the same tech trouble.

            Hello elenst

            MDEV-14760 is (not a bug). All variables on the master are not replicated. Few variables get replicated and few of them are not. For example 'auto_increment_increment', 'sql_mode'. When these variables used with SET STATEMENT they get replicated. There are variables like 'storage_engine' which are not replicated. When these variables are associated with SET STATEMENT they are no-op. Please refer https://jira.mariadb.org/browse/MDEV-14760?focusedCommentId=128669&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-128669

            sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello elenst MDEV-14760 is (not a bug). All variables on the master are not replicated. Few variables get replicated and few of them are not. For example 'auto_increment_increment', 'sql_mode'. When these variables used with SET STATEMENT they get replicated. There are variables like 'storage_engine' which are not replicated. When these variables are associated with SET STATEMENT they are no-op. Please refer https://jira.mariadb.org/browse/MDEV-14760?focusedCommentId=128669&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-128669
            Elkin Andrei Elkin added a comment -

            sujatha.sivakumar, thanks for reminding on MDEV-14760 analysis.
            Just to make it utmost clear, Few variables get replicated - through Query_log_event::"status_var".

            Elkin Andrei Elkin added a comment - sujatha.sivakumar , thanks for reminding on MDEV-14760 analysis. Just to make it utmost clear, Few variables get replicated - through Query_log_event::"status_var" .

            sujatha.sivakumar,

            I've seen this, and I know that MDEV-14760 is leaning towards being not a bug. I was responding to the previous Sergei's comment about possible use of set statement to solve this issue. If it's ignored by the slave and is going to stay ignored, then it probably won't solve the problem.

            elenst Elena Stepanova added a comment - sujatha.sivakumar , I've seen this, and I know that MDEV-14760 is leaning towards being not a bug. I was responding to the previous Sergei's comment about possible use of set statement to solve this issue. If it's ignored by the slave and is going to stay ignored, then it probably won't solve the problem.

            The simplest solution is to introduce MARIADB_DATE as a synonym for DATE and to be used in ORACLE mode in case of SHOW CREATE when a original MariaDB date is used.

            monty Michael Widenius added a comment - The simplest solution is to introduce MARIADB_DATE as a synonym for DATE and to be used in ORACLE mode in case of SHOW CREATE when a original MariaDB date is used.
            bar Alexander Barkov added a comment - - edited

            After a discussion with Monty and Serg, we decided to fix it in 10.3 to 10.5 in the following way, so this script:

            SET sql_mode=DEFAULT;
            CREATE OR REPLACE TABLE t1 (a DATE);
            SET sql_mode=ORACLE;
            DESCRIBE t1;
            

            will display:

            +-------+--------------+------+-----+---------+-------+
            | Field | Type         | Null | Key | Default | Extra |
            +-------+--------------+------+-----+---------+-------+
            | a     | date_mariadb | YES  |     | NULL    |       |
            +-------+--------------+------+-----+---------+-------+
            

            This will automatically fix this problem.

            In 10.6 we'll possibly introduce a more generalized solution like MDEV-23023 (but this is not decided yet though).

            bar Alexander Barkov added a comment - - edited After a discussion with Monty and Serg, we decided to fix it in 10.3 to 10.5 in the following way, so this script: SET sql_mode= DEFAULT ; CREATE OR REPLACE TABLE t1 (a DATE ); SET sql_mode=ORACLE; DESCRIBE t1; will display: +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | a | date_mariadb | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ This will automatically fix this problem. In 10.6 we'll possibly introduce a more generalized solution like MDEV-23023 (but this is not decided yet though).
            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable with with sql_mode=MAXDB, which translates TIMESTAMP to DATETIME:

            --source include/have_binlog_format_row.inc
            --source include/master-slave.inc
             
            CREATE TABLE t1 (a TIMESTAMP);
            INSERT INTO t1 VALUES (NULL);
            SET SQL_MODE= 'MAXDB';
            CREATE TABLE t2 SELECT * FROM t1;
             
            --sync_slave_with_master
             
            # Cleanup
            --connection master
            DROP TABLE t1, t2;
             
            --source include/rpl_end.inc
            

            Replication fails with this error:

            Column 0 of table 'test.t2' cannot be converted from type 'timestamp' to type 'datetime'
            

            bar Alexander Barkov added a comment - - edited The same problem is repeatable with with sql_mode=MAXDB, which translates TIMESTAMP to DATETIME: --source include/have_binlog_format_row.inc --source include/master-slave.inc   CREATE TABLE t1 (a TIMESTAMP ); INSERT INTO t1 VALUES ( NULL ); SET SQL_MODE= 'MAXDB' ; CREATE TABLE t2 SELECT * FROM t1;   --sync_slave_with_master # Cleanup --connection master DROP TABLE t1, t2;   --source include/rpl_end.inc Replication fails with this error: Column 0 of table 'test.t2' cannot be converted from type 'timestamp' to type 'datetime'
            bar Alexander Barkov added a comment - Patches: 10.3: https://github.com/MariaDB/server/commit/ca7e6f8d572fa61d1d5f350664c0bc1854a7281a 10.5: https://github.com/MariaDB/server/commit/dd0485fcd795cf82f5e7675312c1755deca04f4f

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              11 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.