Edit File: insert_notembedded.result
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 unlock tables; drop table t1; set low_priority_updates=default;
Back to File Manager