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