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