summaryrefslogtreecommitdiff
path: root/mysql-test/r/insert_notembedded.result
blob: ac69cb6597227e848a32c9a44aa475575896f73a (plain)
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
drop table if exists t1;
create table t1 (n int);
create view  v1 as select * from t1;
insert delayed into v1 values (1);
ERROR HY000: 'test.v1' is not BASE TABLE
drop table t1;
drop view  v1;
CREATE DATABASE meow;
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;
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 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target'
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 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2'
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);
ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
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;
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);
REPLACE INTO    table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2'
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);
mexs_id	messzeit
87654321	2006-07-12 07:50:00
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);
SELECT * FROM table_target;
mexs_id	messzeit
87654321	2006-07-12 07:50:00
SELECT * FROM view_target2;
mexs_id	messzeit
12X45Y78	2006-07-12 07:50:00
87654321	2006-07-12 07:50:00
SELECT * FROM view_target3;
mexs_id	messzeit
87654321	2006-07-12 07:50:00
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;
DROP DATABASE meow;
connection: default
set low_priority_updates=1;
drop table if exists t1;
create table t1 (a int, b int, unique key t1$a (a));
lock table t1 read;
connection: update
set low_priority_updates=1;
show variables like 'low_priority_updates';
Variable_name	Value
low_priority_updates	ON
insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;;
connection: select
select * from t1;
a	b
connection: default
select * from t1;
a	b
drop table t1;
set low_priority_updates=default;