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