[MDEV-12248] replicating a table created from a view loses default value - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Not a Bug
Affects Version/s: 10.1.21, 10.1(EOL)
Fix Version/s: N/A
Component/s: Views
Labels:
- view
Environment:
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

Ascending order - Click to sort in descending order

Kyle Joiner (Inactive) created issue - 2017-03-13 19:20

Kyle Joiner (Inactive) made changes - 2017-03-13 19:22

Field	Original Value	New Value
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: Query OK, 0 rows affected (0.39 sec) 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.	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: Query OK, 0 rows affected (0.39 sec) 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.
Summary	replicating a table created from a view losses default value	replicating a table created from a view loses default value

Elena Stepanova made changes - 2017-03-18 00:13

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:
Query OK, 0 rows affected (0.39 sec)

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.

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}
{code:sql}
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)
{code}
{code:sql}
MariaDB [test]> create table t2 as select * from testview;
Query OK, 0 rows affected (1.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
{code}
{code:sql}
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)
{code}
On B:
{code:sql}
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)
{code}

on C:
{code:sql}
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)
{code}

The default value for `value` field is lost.

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

Elena Stepanova made changes - 2017-03-18 13:15

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2017-03-18 13:19

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.

Elena Stepanova added a comment - 2017-03-18 13:19 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.

Elena Stepanova made changes - 2017-03-18 13:19

Assignee

Sergei Golubchik [ serg ]

Elena Stepanova made changes - 2017-03-18 13:19

Affects Version/s

10.1 [ 16100 ]

Sergei Golubchik made changes - 2017-09-08 09:42

Labels

view

Sergei Golubchik added a comment - 2017-09-08 10:14

This is the intended behavior and not a bug.

Sergei Golubchik added a comment - 2017-09-08 10:14 This is the intended behavior and not a bug.

Sergei Golubchik made changes - 2017-09-08 10:14

Fix Version/s		N/A [ 14700 ]
Resolution		Not a Bug [ 6 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Chris Calender (Inactive) added a comment - 2017-10-23 16:43

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?

Chris Calender (Inactive) added a comment - 2017-10-23 16:43 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?

Sergei Golubchik made changes - 2017-10-23 17:31

Resolution	Not a Bug [ 6 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2017-10-23 17:31

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	N/A [ 14700 ]

Sergei Golubchik made changes - 2017-10-23 17:31

Assignee

Sergei Golubchik [ serg ]

Alexey Botchkov [ holyfoot ]

Sergei Golubchik added a comment - 2017-10-23 17:33 - 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.

Sergei Golubchik added a comment - 2017-10-23 17:33 - 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.

Sergei Golubchik made changes - 2017-10-23 17:36

Component/s

Replication [ 10100 ]

Sergei Golubchik added a comment - 2017-10-24 19:46

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

Sergei Golubchik added a comment - 2017-10-24 19:46 greenman , could you, please, document that and then reassign the issue back to me?

Sergei Golubchik made changes - 2017-10-24 19:46

Assignee

Alexey Botchkov [ holyfoot ]

Ian Gilfillan [ greenman ]

Ian Gilfillan added a comment - 2017-10-30 09:10

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/

Ian Gilfillan added a comment - 2017-10-30 09:10 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/

Ian Gilfillan made changes - 2017-10-30 09:11

Assignee

Ian Gilfillan [ greenman ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2017-10-31 19:09

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2017-11-01 12:17

Priority

Major [ 3 ]

Minor [ 4 ]

Julien Fritsch made changes - 2017-12-05 14:31

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-10-13 20:43

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Not a Bug [ 6 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-10-13 20:43

Component/s

Replication [ 10100 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 79953 ]

MariaDB v4 [ 151805 ]

Jira Automation (IT) made changes - 2024-07-04 08:35

Zendesk Related Tickets

106500

People

Assignee:: Sergei Golubchik

Reporter:: Kyle Joiner (Inactive)

Votes:: 3 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2017-03-13 19:20

Updated:: 2024-07-08 00:57

Resolved:: 2021-10-13 20:43

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration