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

replicating a table created from a view loses default value

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.1, 10.1.21
    • 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

          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.