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

replicating a table created from a view loses default value

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.1.21, 10.1(EOL)
    • N/A
    • Views
    • any

    Description

      When creating a table from a view and replicating the table creation from 10.0 to 10.1 a default value is lost.

      replicated from (A)10.0.19 >(B) 10.0.19 >(C) 10.1.21.

      On A:

      MariaDB [test]> create table t1(
          -> `field1` bigint(20) NOT NULL,
          -> `field2` bigint(20) NOT NULL,
          -> `text1` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
          -> `type1` enum('Broad','Exact','Phrase','WebSite','Content','SuperBroad','TrueExact','PhraseCore','PhraseExact','PhraseSynonym','Custom') DEFAULT NULL)
          -> engine=innodb charset=utf8
          -> ;
      Query OK, 0 rows affected (0.41 sec)
      

      	MariaDB [test]> create view testview as
          -> select t1.`field1` as t1_f1,
          -> t1.`field2` as t1_f2,
          -> 'value',
          -> t1.`text1` as t1_text1,
          -> t1.`type1` as t1_type1
          -> from t1;
      Query OK, 0 rows affected (0.90 sec)
      

      MariaDB [test]> show create view testview;
      +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      | View     | Create View                                                                                                                                                                                                                               | character_set_client | collation_connection |
      +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      | testview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `t1`.`field1` AS `t1_f1`,`t1`.`field2` AS `t1_f2`,'value' AS `value`,`t1`.`text1` AS `t1_text1`,`t1`.`type1` AS `t1_type1` from `t1` | cp850                | cp850_general_ci     |
      +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      1 row in set (0.03 sec)
      

      MariaDB [test]> create table t2 as select * from testview;
      Query OK, 0 rows affected (1.38 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      MariaDB [test]> show create table t2;
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                      |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `t1_f1` bigint(20) NOT NULL,
        `t1_f2` bigint(20) NOT NULL,
        `value` varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT '',
        `t1_text1` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
        `t1_type1` enum('Broad','Exact','Phrase','WebSite','Content','SuperBroad','TrueExact','PhraseCore','PhraseExact','PhraseSynonym','Custom') CHARACTER SET utf8 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      On B:

      MariaDB [test]> show create table t2;
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                      |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `t1_f1` bigint(20) NOT NULL,
        `t1_f2` bigint(20) NOT NULL,
        `value` varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT '',
        `t1_text1` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
        `t1_type1` enum('Broad','Exact','Phrase','WebSite','Content','SuperBroad','TrueExact','PhraseCore','PhraseExact','PhraseSynonym','Custom') CHARACTER SET utf8 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      on C:

      MariaDB [test]> show create table t2;
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `t1_f1` bigint(20) NOT NULL,
        `t1_f2` bigint(20) NOT NULL,
        `value` varchar(5) CHARACTER SET cp850 NOT NULL,
        `t1_text1` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
        `t1_type1` enum('Broad','Exact','Phrase','WebSite','Content','SuperBroad','TrueExact','PhraseCore','PhraseExact','PhraseSynonym','Custom') CHARACTER SET utf8 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      The default value for `value` field is lost.

      The servers are all default values except for statement based replication is used.

      Attachments

        Activity

          It's unrelated to replication. The basic test case is this:

          create table t1(f INT);
          create view v1 as select 'value' from t1;
          create table t2 as select * from v1;
           
          show create table t2;
          

          10.0

          MariaDB [test]> show create table t2;
          +-------+------------------------------------------------------------------------------------------------------------------------+
          | Table | Create Table                                                                                                           |
          +-------+------------------------------------------------------------------------------------------------------------------------+
          | t2    | CREATE TABLE `t2` (
            `value` varchar(5) CHARACTER SET utf8 NOT NULL DEFAULT ''
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------------------------------------+
          1 row in set (0.00 sec)
          

          10.1

          MariaDB [test]> show create table t2;
          +-------+-------------------------------------------------------------------------------------------------------------+
          | Table | Create Table                                                                                                |
          +-------+-------------------------------------------------------------------------------------------------------------+
          | t2    | CREATE TABLE `t2` (
            `value` varchar(5) CHARACTER SET utf8 NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+-------------------------------------------------------------------------------------------------------------+
          1 row in set (0.00 sec)
          

          The change appears to be intentional, introduced by this commit:

          commit 6b20342651bb5207b6c125d2d11b664a1bebcc41
          Author: Monty <monty@mariadb.org>
          Date:   Tue Aug 18 00:42:08 2015 +0300
           
              Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not specified and if not timestamp or auto_increment
          

          I'll assign to serg to confirm (or not) whether this particular effect with views was expected/desirable.

          elenst Elena Stepanova added a comment - It's unrelated to replication. The basic test case is this: create table t1(f INT ); create view v1 as select 'value' from t1; create table t2 as select * from v1;   show create table t2; 10.0 MariaDB [test]> show create table t2; + -------+------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `value` varchar (5) CHARACTER SET utf8 NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | + -------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 10.1 MariaDB [test]> show create table t2; + -------+-------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+-------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `value` varchar (5) CHARACTER SET utf8 NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | + -------+-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) The change appears to be intentional, introduced by this commit: commit 6b20342651bb5207b6c125d2d11b664a1bebcc41 Author: Monty <monty@mariadb.org> Date: Tue Aug 18 00:42:08 2015 +0300   Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not specified and if not timestamp or auto_increment I'll assign to serg to confirm (or not) whether this particular effect with views was expected/desirable.

          This is the intended behavior and not a bug.

          serg Sergei Golubchik added a comment - This is the intended behavior and not a bug.

          The problem is that this breaks replication between 10.0 master and 10.1 slave (with SBR).

          Is it possible to have this be "optional", so-to-speak?

          ccalender Chris Calender (Inactive) added a comment - The problem is that this breaks replication between 10.0 master and 10.1 slave (with SBR). Is it possible to have this be "optional", so-to-speak?
          serg Sergei Golubchik added a comment - - edited

          Okay. I think we can fix the replication case. Like, if this is the replication slave thread and master is 10.0 (or earlier), use the old backward-compatible behavior.

          This won't work for mysqlbinlog|mysql combo, though.

          serg Sergei Golubchik added a comment - - edited Okay. I think we can fix the replication case. Like, if this is the replication slave thread and master is 10.0 (or earlier), use the old backward-compatible behavior. This won't work for mysqlbinlog|mysql combo, though.

          greenman, could you, please, document that and then reassign the issue back to me?

          serg Sergei Golubchik added a comment - greenman , could you, please, document that and then reassign the issue back to me?
          greenman Ian Gilfillan added a comment - Documented in: https://mariadb.com/kb/en/library/upgrading-from-mariadb-100-to-mariadb-101/ https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-101/ https://mariadb.com/kb/en/library/primary-keys-with-nullable-columns/

          People

            serg Sergei Golubchik
            kjoiner Kyle Joiner (Inactive)
            Votes:
            3 Vote for this issue
            Watchers:
            8 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.