/usr/lib/mysql-testsuite/t/insert_notembedded.test is in percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | -- source include/not_embedded.inc
--disable_warnings
drop table if exists t1;
--enable_warnings
# Test for INSERT DELAYED INTO a <view>
# BUG#13683: INSERT DELAYED into a view creates an infinite loop
#
create table t1 (n int);
create view v1 as select * from t1;
--error 1347
insert delayed into v1 values (1);
drop table t1;
drop view v1;
#
# Bug #20989: View '(null).(null)' references invalid table(s)... on
# SQL SECURITY INVOKER
#
# this is really the fact that REPLACE ... SELECT required additional
# INSERT privs (on tables that are part of a view) over the related
# REPLACE, SELECT
#
CREATE DATABASE meow;
connect (root,localhost,root,,meow);
connection root;
CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
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);
INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
INSERT INTO table_countries VALUES ('YY','Entenhausen');
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)));
CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00');
GRANT SELECT ON table_source TO user20989@localhost;
GRANT SELECT ON table_countries TO user20989@localhost;
GRANT SELECT ON table_stations TO user20989@localhost;
GRANT SELECT ON view_stations TO user20989@localhost;
GRANT SELECT ON table_target TO user20989@localhost;
GRANT SELECT ON table_target2 TO user20989@localhost;
GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost;
connect (user20989,localhost,user20989,,meow);
connection user20989;
--error 1142
REPLACE INTO table_target
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN table_target AS old
USING (mexs_id);
--error 1142
REPLACE INTO view_target2
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN view_target2 AS old
USING (mexs_id);
--error 1356
REPLACE INTO view_target3
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN view_target3 AS old
USING (mexs_id);
connection root;
disconnect user20989;
GRANT INSERT,DELETE ON table_target TO user20989@localhost;
GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost;
GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost;
connect (user20989,localhost,user20989,,meow);
connection user20989;
REPLACE INTO table_target
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN table_target AS old
USING (mexs_id);
--error 1142
REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00');
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN view_target2 AS old
USING (mexs_id);
REPLACE INTO view_target2
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN view_target2 AS old
USING (mexs_id);
REPLACE INTO view_target3
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
FROM table_source
INNER JOIN view_stations AS stations
ON table_source.id = stations.icao
LEFT JOIN view_target3 AS old
USING (mexs_id);
connection root;
disconnect user20989;
SELECT * FROM table_target;
SELECT * FROM view_target2;
SELECT * FROM view_target3;
DROP VIEW view_stations;
DROP TABLE table_source;
DROP TABLE table_countries;
DROP TABLE table_stations;
DROP TABLE table_target;
DROP TABLE table_target2;
DROP TABLE table_target3;
DROP VIEW view_target2;
DROP VIEW view_target3;
DROP USER user20989@localhost;
disconnect root;
connection default;
DROP DATABASE meow;
#
# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
#
--echo connection: default
set low_priority_updates=1;
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (a int, b int, unique key t1$a (a));
lock table t1 read;
connect (update,localhost,root,,);
connection update;
--echo connection: update
set low_priority_updates=1;
show variables like 'low_priority_updates';
let $ID= `select connection_id()`;
--send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
connection default;
# we must wait till the insert opens and locks the table
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table level lock" and id = $ID;
--source include/wait_condition.inc
connect (select,localhost,root,,);
--echo connection: select
select * from t1;
connection default;
--echo connection: default
select * from t1;
connection default;
disconnect update;
disconnect select;
unlock tables;
drop table t1;
set low_priority_updates=default;
|