1 -- source include/not_embedded.inc
4 drop table if exists t1;
7 # Test for INSERT DELAYED INTO a <view>
8 # BUG#13683: INSERT DELAYED into a view creates an infinite loop
11 create table t1 (n int);
12 create view v1 as select * from t1;
14 insert delayed into v1 values (1);
19 # Bug #20989: View '(null).(null)' references invalid table(s)... on
20 # SQL SECURITY INVOKER
22 # this is really the fact that REPLACE ... SELECT required additional
23 # INSERT privs (on tables that are part of a view) over the related
29 connect (root,localhost,root,,meow);
32 CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
33 CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
34 CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
35 CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
36 CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
38 CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE);
39 INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
41 CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
42 INSERT INTO table_countries VALUES ('YY','Entenhausen');
44 CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
46 CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
47 INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00');
49 GRANT SELECT ON table_source TO user20989@localhost;
50 GRANT SELECT ON table_countries TO user20989@localhost;
51 GRANT SELECT ON table_stations TO user20989@localhost;
52 GRANT SELECT ON view_stations TO user20989@localhost;
53 GRANT SELECT ON table_target TO user20989@localhost;
54 GRANT SELECT ON table_target2 TO user20989@localhost;
55 GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost;
57 connect (user20989,localhost,user20989,,meow);
61 REPLACE INTO table_target
62 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
64 INNER JOIN view_stations AS stations
65 ON table_source.id = stations.icao
66 LEFT JOIN table_target AS old
70 REPLACE INTO view_target2
71 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
73 INNER JOIN view_stations AS stations
74 ON table_source.id = stations.icao
75 LEFT JOIN view_target2 AS old
79 REPLACE INTO view_target3
80 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
82 INNER JOIN view_stations AS stations
83 ON table_source.id = stations.icao
84 LEFT JOIN view_target3 AS old
90 GRANT INSERT,DELETE ON table_target TO user20989@localhost;
91 GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost;
92 GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost;
94 connect (user20989,localhost,user20989,,meow);
97 REPLACE INTO table_target
98 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
100 INNER JOIN view_stations AS stations
101 ON table_source.id = stations.icao
102 LEFT JOIN table_target AS old
106 REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
107 REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00');
109 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
111 INNER JOIN view_stations AS stations
112 ON table_source.id = stations.icao
113 LEFT JOIN view_target2 AS old
116 REPLACE INTO view_target2
117 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
119 INNER JOIN view_stations AS stations
120 ON table_source.id = stations.icao
121 LEFT JOIN view_target2 AS old
124 REPLACE INTO view_target3
125 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
127 INNER JOIN view_stations AS stations
128 ON table_source.id = stations.icao
129 LEFT JOIN view_target3 AS old
133 disconnect user20989;
135 SELECT * FROM table_target;
136 SELECT * FROM view_target2;
137 SELECT * FROM view_target3;
139 DROP VIEW view_stations;
140 DROP TABLE table_source;
141 DROP TABLE table_countries;
142 DROP TABLE table_stations;
143 DROP TABLE table_target;
144 DROP TABLE table_target2;
145 DROP TABLE table_target3;
146 DROP VIEW view_target2;
147 DROP VIEW view_target3;
148 DROP USER user20989@localhost;
157 # Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
159 --echo connection: default
160 set low_priority_updates=1;
162 drop table if exists t1;
164 create table t1 (a int, b int, unique key t1$a (a));
166 connect (update,localhost,root,,);
168 --echo connection: update
169 set low_priority_updates=1;
170 show variables like 'low_priority_updates';
171 let $ID= `select connection_id()`;
172 --send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
174 # we must wait till the insert opens and locks the table
176 select count(*) = 1 from information_schema.processlist
177 where state = "Locked" and id = $ID;
178 --source include/wait_condition.inc
179 connect (select,localhost,root,,);
180 --echo connection: select
183 --echo connection: default
189 set low_priority_updates=default;