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
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection master]
drop table if exists t1,v1;
drop view if exists t1,v1;
include/sync_slave_sql_with_master.inc
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
include/sync_slave_sql_with_master.inc
select * from v1 order by a;
a
1
2
update v1 set a=3 where a=1;
select * from v1 order by a;
a
2
3
include/sync_slave_sql_with_master.inc
select * from v1 order by a;
a
2
3
delete from v1 where a=2;
select * from v1 order by a;
a
3
include/sync_slave_sql_with_master.inc
select * from v1 order by a;
a
3
alter view v1 as select a as b from t1;
include/sync_slave_sql_with_master.inc
select * from v1 order by 1;
b
3
drop view v1;
include/sync_slave_sql_with_master.inc
select * from v1 order by a;
ERROR 42S02: Table 'test.v1' doesn't exist
drop table t1;
include/sync_slave_sql_with_master.inc
---> Test for BUG#20438
---> Preparing environment...
---> connection: master
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
---> Synchronizing slave with master...
include/sync_slave_sql_with_master.inc
---> 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...
include/sync_slave_sql_with_master.inc
---> connection: master
---> Checking on slave...
SELECT * FROM t1;
c
1
---> connection: master
---> Cleaning up...
DROP VIEW v1;
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
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;
include/sync_slave_sql_with_master.inc
explain v1;
Field Type Null Key Default Extra
a int 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` (`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
select * from v1;
a b
1 6
drop table t1;
drop view v1;
include/sync_slave_sql_with_master.inc
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;
include/sync_slave_sql_with_master.inc
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 utf8mb4 utf8mb4_0900_ai_ci
include/sync_slave_sql_with_master.inc
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 utf8mb4 utf8mb4_0900_ai_ci
DROP VIEW v1;
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
End of 5.0 tests
include/rpl_end.inc
|
