Debian Codegraph @

mysql-test/suite/rpl/r/rpl_view.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 drop table if exists t1,v1;
7 drop view if exists t1,v1;
8 include/sync_slave_sql_with_master.inc
9 create table t1 (a int);
10 insert into t1 values (1);
11 create view v1 as select a from t1;
12 insert into v1 values (2);
13 select * from v1 order by a;
14 a
15 1
16 2
17 include/sync_slave_sql_with_master.inc
18 select * from v1 order by a;
19 a
20 1
21 2
22 update v1 set a=3 where a=1;
23 select * from v1 order by a;
24 a
25 2
26 3
27 include/sync_slave_sql_with_master.inc
28 select * from v1 order by a;
29 a
30 2
31 3
32 delete from v1 where a=2;
33 select * from v1 order by a;
34 a
35 3
36 include/sync_slave_sql_with_master.inc
37 select * from v1 order by a;
38 a
39 3
40 alter view v1 as select a as b from t1;
41 include/sync_slave_sql_with_master.inc
42 select * from v1 order by 1;
43 b
44 3
45 drop view v1;
46 include/sync_slave_sql_with_master.inc
47 select * from v1 order by a;
48 ERROR 42S02: Table 'test.v1' doesn't exist
49 drop table t1;
50 include/sync_slave_sql_with_master.inc
51
52 ---> Test for BUG#20438
53
54 ---> Preparing environment...
55 ---> connection: master
56 DROP TABLE IF EXISTS t1;
57 DROP VIEW IF EXISTS v1;
58
59 ---> Synchronizing slave with master...
60 include/sync_slave_sql_with_master.inc
61
62 ---> connection: master
63
64 ---> Creating objects...
65 CREATE TABLE t1(c INT);
66 /*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */;
67
68 ---> Inserting value...
69 INSERT INTO t1 VALUES(1);
70
71 ---> Checking on master...
72 SELECT * FROM t1;
73 c
74 1
75
76 ---> Synchronizing slave with master...
77 include/sync_slave_sql_with_master.inc
78 ---> connection: master
79
80 ---> Checking on slave...
81 SELECT * FROM t1;
82 c
83 1
84
85 ---> connection: master
86
87 ---> Cleaning up...
88 DROP VIEW v1;
89 DROP TABLE t1;
90 include/sync_slave_sql_with_master.inc
91 create table t1(a int, b int);
92 insert into t1 values (1, 1), (1, 2), (1, 3);
93 create view v1(a, b) as select a, sum(b) from t1 group by a;
94 include/sync_slave_sql_with_master.inc
95 explain v1;
96 Field Type Null Key Default Extra
97 a int YES NULL
98 b decimal(32,0) YES NULL
99 show create table v1;
100 View Create View character_set_client collation_connection
101 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` (`a`,`b`) AS select `t1`.`a` AS `a`,sum(`t1`.`b`) AS `sum(b)` from `t1` group by `t1`.`a` utf8mb4 utf8mb4_0900_ai_ci
102 select * from v1;
103 a b
104 1 6
105 drop table t1;
106 drop view v1;
107 include/sync_slave_sql_with_master.inc
108 CREATE TABLE t1(a INT);
109 CREATE VIEW v1 AS SELECT * FROM t1;
110 CREATE VIEW v1 AS SELECT * FROM t1;
111 ERROR 42S01: Table 'v1' already exists
112 DROP VIEW v1;
113 DROP TABLE t1;
114 include/sync_slave_sql_with_master.inc
115 CREATE TABLE t1 (a INT);
116 # create view as output from mysqldump 10.11 (5.0.62)
117 /*!50001 CREATE ALGORITHM=UNDEFINED */
118 /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
119 /*!50001 VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` < 3) */
120 /*!50002 WITH CASCADED CHECK OPTION */;
121 SHOW CREATE VIEW v1;
122 View Create View character_set_client collation_connection
123 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` < 3) WITH CASCADED CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
124 include/sync_slave_sql_with_master.inc
125 SHOW CREATE VIEW v1;
126 View Create View character_set_client collation_connection
127 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` < 3) WITH CASCADED CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
128 DROP VIEW v1;
129 DROP TABLE t1;
130 include/sync_slave_sql_with_master.inc
131 End of 5.0 tests
132 include/rpl_end.inc