Debian Codegraph @

mysql-test/suite/rpl/t/rpl_view.test

raw
1 # NYI - row-based cannot use CREATE ... SELECT
2
3 source include/master-slave.inc;
4 --disable_warnings
5 drop table if exists t1,v1;
6 drop view if exists t1,v1;
7 sync_slave_with_master;
8 reset master;
9 --enable_warnings
10
11 #
12 # Check that creation drop of view is replicated, also check replication of
13 # updating of view
14 #
15 connection master;
16 create table t1 (a int);
17 insert into t1 values (1);
18 create view v1 as select a from t1;
19 insert into v1 values (2);
20 select * from v1 order by a;
21 sync_slave_with_master;
22 # view already have to be on slave
23 select * from v1 order by a;
24 connection master;
25 update v1 set a=3 where a=1;
26 select * from v1 order by a;
27 sync_slave_with_master;
28 select * from v1 order by a;
29 connection master;
30 delete from v1 where a=2;
31 select * from v1 order by a;
32 sync_slave_with_master;
33 select * from v1 order by a;
34 connection master;
35 # 'alter view' internally maped to creation, but still check that it works
36 alter view v1 as select a as b from t1;
37 sync_slave_with_master;
38 select * from v1 order by 1;
39 connection master;
40 drop view v1;
41 sync_slave_with_master;
42 #error, because view have to be removed from slave
43 -- error 1146
44 select * from v1 order by a;
45 connection master;
46 drop table t1;
47 sync_slave_with_master;
48 # Change Author: JBM
49 # Change Date: 2005-12-22
50 # Change: Commented out binlog events to work with SBR and RBR
51 #--replace_column 2 # 5 #
52 # show binlog events limit 1,100;
53
54 #
55 # BUG#20438: CREATE statements for views, stored routines and triggers can be
56 # not replicable.
57 #
58
59 --echo
60 --echo ---> Test for BUG#20438
61
62 # Prepare environment.
63
64 --echo
65 --echo ---> Preparing environment...
66 --connection master
67
68 --disable_warnings
69 DROP TABLE IF EXISTS t1;
70 DROP VIEW IF EXISTS v1;
71 --enable_warnings
72
73 --echo
74 --echo ---> Synchronizing slave with master...
75
76 --sync_slave_with_master
77
78 --echo
79 --connection master
80
81 # Test.
82
83 --echo
84 --echo ---> Creating objects...
85
86 CREATE TABLE t1(c INT);
87
88 /*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */;
89
90 --echo
91 --echo ---> Inserting value...
92
93 INSERT INTO t1 VALUES(1);
94
95 --echo
96 --echo ---> Checking on master...
97
98 SELECT * FROM t1;
99
100 --echo
101 --echo ---> Synchronizing slave with master...
102
103 --sync_slave_with_master
104
105 SELECT * FROM t1;
106
107 # Cleanup.
108
109 --connection master
110
111 --echo
112 --echo ---> Cleaning up...
113
114 DROP VIEW v1;
115 DROP TABLE t1;
116
117 --sync_slave_with_master
118 --connection master
119
120 #
121 # BUG#19419: "VIEW: View that the column name is different
122 # by master and slave is made".
123 #
124 connection master;
125 create table t1(a int, b int);
126 insert into t1 values (1, 1), (1, 2), (1, 3);
127 create view v1(a, b) as select a, sum(b) from t1 group by a;
128
129 sync_slave_with_master;
130 explain v1;
131 show create table v1;
132 select * from v1;
133
134 connection master;
135 drop table t1;
136 drop view v1;
137
138 sync_slave_with_master;
139
140 #
141 # BUG#28244 CREATE VIEW breaks replication when view exists
142 #
143 connection master;
144 CREATE TABLE t1(a INT);
145 CREATE VIEW v1 AS SELECT * FROM t1;
146 --error ER_TABLE_EXISTS_ERROR
147 CREATE VIEW v1 AS SELECT * FROM t1;
148 DROP VIEW v1;
149 DROP TABLE t1;
150 sync_slave_with_master;
151
152 #
153 # Bug#32575 Parse error of stmt with extended comments on slave side
154 # Verify that 'CREATE VIEW' with comments is properly logged to binlog
155 connection master;
156 CREATE TABLE t1 (a INT);
157 --echo # create view as output from mysqldump 10.11 (5.0.62)
158 /*!50001 CREATE ALGORITHM=UNDEFINED */
159 /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
160 /*!50001 VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` < 3) */
161 /*!50002 WITH CASCADED CHECK OPTION */;
162 SHOW CREATE VIEW v1;
163 sync_slave_with_master;
164 SHOW CREATE VIEW v1;
165 connection master;
166 DROP VIEW v1;
167 DROP TABLE t1;
168 sync_slave_with_master;
169
170 --echo End of 5.0 tests
171 --source include/rpl_end.inc