[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

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.

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.

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 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 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?

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/

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