~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

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

100

INNER JOIN view_stations AS stations

101

ON table_source.id = stations.icao

102

LEFT JOIN table_target AS old

103

USING (mexs_id);

104

105

--error 1142

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');

108

109

SELECT stations.mexs_id AS mexs_id, datetime AS messzeit

110

FROM table_source

111

INNER JOIN view_stations AS stations

112

ON table_source.id = stations.icao

113

LEFT JOIN view_target2 AS old

114

USING (mexs_id);

115

116

REPLACE INTO view_target2

117

SELECT stations.mexs_id AS mexs_id, datetime AS messzeit

118

FROM table_source

119

INNER JOIN view_stations AS stations

120

ON table_source.id = stations.icao

121

LEFT JOIN view_target2 AS old

122

USING (mexs_id);

123

124

REPLACE INTO view_target3

125

SELECT stations.mexs_id AS mexs_id, datetime AS messzeit

126

FROM table_source

127

INNER JOIN view_stations AS stations

128

ON table_source.id = stations.icao

129

LEFT JOIN view_target3 AS old

130

USING (mexs_id);

131

132

connection root;

133

disconnect user20989;

134

135

SELECT * FROM table_target;

136

SELECT * FROM view_target2;

137

SELECT * FROM view_target3;

138

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;

149

150

disconnect root;

151

152

connection default;

153

154

DROP DATABASE meow;

155

156

157

# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates

158

159

--echo connection: default

160

set low_priority_updates=1;

161

--disable_warnings

162

drop table if exists t1;

163

--enable_warnings

164

create table t1 (a int, b int, unique key t1$a (a));

165

lock table t1 read;

166

connect (update,localhost,root,,);

167

connection update;

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;

173

connection default;

174

# we must wait till the insert opens and locks the table

175

let $wait_condition=

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

181

select * from t1;

182

connection default;

183

--echo connection: default

184

select * from t1;

185

connection default;

186

disconnect update;

187

disconnect select;

188

drop table t1;

189

set low_priority_updates=default;

Older »