rpl_view.result 2.62 KB
Newer Older
1 2 3 4 5 6 7 8
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop table if exists t1,v1;
drop view if exists t1,v1;
9
reset master;
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
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
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
select * from v1 order by a;
a
2
3
delete from v1 where a=2;
select * from v1 order by a;
a
3
select * from v1 order by a;
a
3
alter view v1 as select a as b from t1;
select * from v1 order by 1;
b
3
drop view v1;
select * from v1 order by a;
ERROR 42S02: Table 'test.v1' doesn't exist
drop table t1;
unknown's avatar
unknown committed
46
show binlog events limit 1,100;
47 48 49
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
slave-bin.000001	#	Query	1	#	use `test`; create table t1 (a int)
slave-bin.000001	#	Query	1	#	use `test`; insert into t1 values (1)
50
slave-bin.000001	#	Query	1	#	use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select a from t1
51 52 53
slave-bin.000001	#	Query	1	#	use `test`; insert into v1 values (2)
slave-bin.000001	#	Query	1	#	use `test`; update v1 set a=3 where a=1
slave-bin.000001	#	Query	1	#	use `test`; delete from v1 where a=2
54
slave-bin.000001	#	Query	1	#	use `test`; ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select a as b from t1
55 56
slave-bin.000001	#	Query	1	#	use `test`; drop view v1
slave-bin.000001	#	Query	1	#	use `test`; drop table t1
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

---> Test for BUG#20438

---> Preparing environment...
---> connection: master
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;

---> Synchronizing slave with master...

---> 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: master

---> Checking on slave...
SELECT * FROM t1;
c
1

---> connection: master

---> Cleaning up...
DROP VIEW v1;
DROP TABLE t1;
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
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;
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
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`
select * from v1;
a	b
1	6
drop table t1;
drop view v1;
unknown's avatar
unknown committed
109
End of 5.0 tests