Debian Codegraph @

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

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