~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) ENGINE=MyISAM;

insert into t1 (id,id2) values (1,1),(1,2),(1,3);

LOCK TABLE t1 WRITE;

select dummy1,count(distinct id) from t1 group by dummy1;

dummy1 count(distinct id)

NULL 1

update t1 set id=-1 where id=1;

LOCK TABLE t1 READ;

update t1 set id=1 where id=1;

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

create table t2 SELECT * from t1;

ERROR HY000: Table 't2' was not locked with LOCK TABLES

create temporary table t2 SELECT * from t1;

drop table if exists t2;

unlock tables;

create table t2 SELECT * from t1;

LOCK TABLE t1 WRITE,t2 write;

insert into t2 SELECT * from t1;

update t1 set id=1 where id=-1;

drop table t1,t2;

CREATE TABLE t1 (

index1 smallint(6) default NULL,

nr smallint(6) default NULL,

KEY index1(index1)

) ENGINE=MyISAM;

CREATE TABLE t2 (

nr smallint(6) default NULL,

name varchar(20) default NULL

) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,'item1');

INSERT INTO t2 VALUES (2,'item2');

lock tables t1 write, t2 read;

insert into t1 select 1,nr from t2 where name='item1';

insert into t1 select 2,nr from t2 where name='item2';

unlock tables;

check table t1;

Table Op Msg_type Msg_text

test.t1 check status OK

lock tables t1 write;

check table t2;

Table Op Msg_type Msg_text

test.t2 check Error Table 't2' was not locked with LOCK TABLES

test.t2 check status Operation failed

insert into t1 select index1,nr from t1;

ERROR HY000: Table 't1' was not locked with LOCK TABLES

unlock tables;

lock tables t1 write, t1 as t1_alias read;

insert into t1 select index1,nr from t1 as t1_alias;

drop table t1,t2;

ERROR HY000: Table 't2' was not locked with LOCK TABLES

unlock tables;

drop table t1,t2;

create table t1 (c1 int);

create table t2 (c1 int);

create table t3 (c1 int);

lock tables t1 write, t2 write, t3 write;

drop table t2, t3, t1;

create table t1 (c1 int);

create table t2 (c1 int);

create table t3 (c1 int);

lock tables t1 write, t2 write, t3 write, t1 as t4 read;

alter table t2 add column c2 int;

drop table t1, t2, t3;

create table t1 ( a int(11) not null auto_increment, primary key(a));

create table t2 ( a int(11) not null auto_increment, primary key(a));

lock tables t1 write, t2 read;

delete from t1 using t1,t2 where t1.a=t2.a;

delete t1 from t1,t2 where t1.a=t2.a;

delete from t2 using t1,t2 where t1.a=t2.a;

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

delete t2 from t1,t2 where t1.a=t2.a;

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

drop table t1,t2;

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

unlock tables;

drop table t2,t1;

End of 4.1 tests.

drop table if exists t1;

create table t1 (a int);

lock table t1 write;

flush tables with read lock;

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

unlock tables;

drop table t1;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (i INT);

LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;

UNLOCK TABLES;

LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;

UNLOCK TABLES;

LOCK TABLES mysql.time_zone READ, mysql.proc READ;

UNLOCK TABLES;

LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;

UNLOCK TABLES;

LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

100

LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;

101

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

102

LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;

103

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

104

DROP TABLE t1;

105

106

Bug#5719 impossible to lock VIEW

107

108

Just covering existing behaviour with tests.

109

Consistency has not been found here.

110

111

drop view if exists v_bug5719;

112

drop table if exists t1, t2, t3;

113

create table t1 (a int);

114

create temporary table t2 (a int);

115

create table t3 (a int);

116

create view v_bug5719 as select 1;

117

lock table v_bug5719 write;

118

select * from t1;

119

ERROR HY000: Table 't1' was not locked with LOCK TABLES

120

121

Allowed to select from a temporary talbe under LOCK TABLES

122

123

select * from t2;

124

125

select * from t3;

126

ERROR HY000: Table 't3' was not locked with LOCK TABLES

127

select * from v_bug5719;

128

129

130

drop view v_bug5719;

131

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

132

133

sic: did not left LOCK TABLES mode automatically

134

135

select * from t1;

136

ERROR HY000: Table 't1' was not locked with LOCK TABLES

137

unlock tables;

138

create or replace view v_bug5719 as select * from t1;

139

lock tables v_bug5719 write;

140

select * from v_bug5719;

141

142

143

Allowed to use an underlying table under LOCK TABLES <view>

144

145

select * from t1;

146

147

148

Allowed to select from a temporary table under LOCK TABLES

149

150

select * from t2;

151

152

select * from t3;

153

ERROR HY000: Table 't3' was not locked with LOCK TABLES

154

Dropping of implicitly locked table is disallowed.

155

drop table t1;

156

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

157

unlock tables;

158

Now let us also lock table explicitly and drop it.

159

lock tables t1 write, v_bug5719 write;

160

drop table t1;

161

162

sic: left LOCK TABLES mode

163

164

select * from t3;

165

166

select * from v_bug5719;

167

ERROR HY000: View 'test.v_bug5719' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

168

unlock tables;

169

drop view v_bug5719;

170

171

When limitation to use temporary tables in views is removed, please

172

add a test that shows what happens under LOCK TABLES when a view

173

references a temporary table, is locked, and the underlying table

174

is dropped.

175

176

create view v_bug5719 as select * from t2;

177

ERROR HY000: View's SELECT refers to a temporary table 't2'

178

179

Cleanup.

180

181

drop table t2, t3;

182

183

# Bug#39843 DELETE requires write access to table in subquery in where clause

184

185

DROP TABLE IF EXISTS t1,t2;

186

CREATE TABLE t1 (

187

table1_rowid SMALLINT NOT NULL

188

);

189

CREATE TABLE t2 (

190

table2_rowid SMALLINT NOT NULL

191

);

192

INSERT INTO t1 VALUES (1);

193

INSERT INTO t2 VALUES (1);

194

LOCK TABLES t1 WRITE, t2 READ;

195

# Sub-select should not try to aquire a write lock.

196

DELETE FROM t1

197

WHERE EXISTS

198

(

199

SELECT 'x'

200

FROM t2

201

WHERE t1.table1_rowid = t2.table2_rowid

202

) ;

203

# While implementing the patch we didn't break old behavior;

204

# The following sub-select should still requires a write lock:

205

SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);

206

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

207

UNLOCK TABLES;

208

DROP TABLE t1,t2;

209

End of 5.1 tests.

210

211

# Ensure that FLUSH TABLES doesn't substitute a base locked table

212

# with a temporary one.

213

214

drop table if exists t1, t2;

215

create table t1 (a int);

216

create table t2 (a int);

217

lock table t1 write, t2 write;

218

create temporary table t1 (a int);

219

flush table t1;

220

drop temporary table t1;

221

select * from t1;

222

223

unlock tables;

224

drop table t1, t2;

225

226

# Ensure that REPAIR .. USE_FRM works under LOCK TABLES.

227

228

drop table if exists t1, t2;

229

create table t1 (a int);

230

create table t2 (a int);

231

lock table t1 write, t2 write;

232

repair table t1 use_frm;

233

Table Op Msg_type Msg_text

234

test.t1 repair status OK

235

repair table t1 use_frm;

236

Table Op Msg_type Msg_text

237

test.t1 repair status OK

238

select * from t1;

239

240

select * from t2;

241

242

repair table t2 use_frm;

243

Table Op Msg_type Msg_text

244

test.t2 repair status OK

245

repair table t2 use_frm;

246

Table Op Msg_type Msg_text

247

test.t2 repair status OK

248

select * from t1;

249

250

unlock tables;

251

drop table t1, t2;

252

253

# Ensure that mi_copy_status is called for two instances

254

# of the same table when it is reopened after a flush.

255

256

drop table if exists t1;

257

drop view if exists v1;

258

create table t1 (c1 int);

259

create view v1 as select * from t1;

260

lock tables t1 write, v1 write;

261

flush table t1;

262

insert into t1 values (33);

263

flush table t1;

264

select * from t1;

265

266

267

unlock tables;

268

drop table t1;

269

drop view v1;

270

271

# WL#4284: Transactional DDL locking

272

273

drop table if exists t1;

274

create table t1 (a int);

275

set autocommit= 0;

276

insert into t1 values (1);

277

lock table t1 write;

278

# Disconnect

279

# Ensure that metadata locks will be released if there is an open

280

# transaction (autocommit=off) in conjunction with lock tables.

281

drop table t1;

282

# Same problem but now for BEGIN

283

drop table if exists t1;

284

create table t1 (a int);

285

begin;

286

insert into t1 values (1);

287

# Disconnect

288

# Ensure that metadata locks held by the transaction are released.

289

drop table t1;

290

291

# Coverage for situations when we try to execute DDL on tables

292

# which are locked by LOCK TABLES only implicitly.

293

294

drop tables if exists t1, t2;

295

drop view if exists v1;

296

drop function if exists f1;

297

create table t1 (i int);

298

create table t2 (j int);

299

300

# Try to perform DDL on table which is locked through view.

301

create view v1 as select * from t2;

302

lock tables t1 write, v1 write;

303

flush table t2;

304

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

305

drop table t2;

306

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

307

alter table t2 add column k int;

308

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

309

create trigger t2_bi before insert on t2 for each row set @a:=1;

310

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

311

# Repair produces error as part of its result set.

312

repair table t2;

313

Table Op Msg_type Msg_text

314

test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated

315

test.t2 repair status Operation failed

316

unlock tables;

317

drop view v1;

318

319

# Now, try DDL on table which is locked through routine.

320

create function f1 () returns int

321

begin

322

insert into t2 values (1);

323

return 0;

324

end|

325

create view v1 as select f1() from t1;

326

lock tables v1 read;

327

flush table t2;

328

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

329

drop table t2;

330

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

331

alter table t2 add column k int;

332

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

333

create trigger t2_bi before insert on t2 for each row set @a:=1;

334

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

335

# Repair produces error as part of its result set.

336

repair table t2;

337

Table Op Msg_type Msg_text

338

test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated

339

test.t2 repair status Operation failed

340

unlock tables;

341

drop view v1;

342

drop function f1;

343

344

# Finally, try DDL on table which is locked thanks to trigger.

345

create trigger t1_ai after insert on t1 for each row insert into t2 values (1);

346

lock tables t1 write;

347

flush table t2;

348

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

349

drop table t2;

350

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

351

alter table t2 add column k int;

352

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

353

create trigger t2_bi before insert on t2 for each row set @a:=1;

354

ERROR HY000: Table 't2' was locked with a READ lock and can't be updated

355

# Repair produces error as part of its result set.

356

repair table t2;

357

Table Op Msg_type Msg_text

358

test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated

359

test.t2 repair status Operation failed

360

unlock tables;

361

drop trigger t1_ai;

362

drop tables t1, t2;

363

364

# Bug#45035 " Altering table under LOCK TABLES results in

365

# "Error 1213 Deadlock found..."

366

367

# When reopening tables under LOCK TABLES after ALTER TABLE,

368

# 6.0 used to be taking thr_lock locks one by one, and

369

# that would lead to a lock conflict.

370

# Check that taking all locks at once works.

371

372

drop table if exists t1;

373

create table t1 (i int);

374

lock tables t1 write, t1 as a read, t1 as b read;

375

alter table t1 add column j int;

376

unlock tables;

377

drop table t1;

378

create temporary table t1 (i int);

379

380

# This is just for test coverage purposes,

381

# when this is allowed, remove the --error.

382

383

lock tables t1 write, t1 as a read, t1 as b read;

384

ERROR HY000: Can't reopen table: 't1'

385

alter table t1 add column j int;

386

unlock tables;

387

drop table t1;

388

# Moved case for partitioned tables to partition.test

389

390

# Bug #43272 HANDLER SQL command does not work under LOCK TABLES

391

392

DROP TABLE IF EXISTS t1;

393

CREATE TABLE t1 (a INT);

394

LOCK TABLE t1 WRITE;

395

# HANDLER commands are not allowed in LOCK TABLES mode

396

HANDLER t1 OPEN;

397

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

398

HANDLER t1 READ FIRST;

399

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

400

HANDLER t1 CLOSE;

401

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

402

UNLOCK TABLES;

403

DROP TABLE t1;

404

405

# Bug#45066 FLUSH TABLES WITH READ LOCK deadlocks against

406

# LOCK TABLE

407

408

DROP TABLE IF EXISTS t1;

409

CREATE TABLE t1(a INT);

410

LOCK TABLE t1 READ;

411

FLUSH TABLES;

412

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

413

LOCK TABLE t1 WRITE;

414

FLUSH TABLES;

415

416

# If you allow the next combination, you reintroduce bug Bug#45066

417

418

LOCK TABLE t1 READ;

419

FLUSH TABLES WITH READ LOCK;

420

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

421

LOCK TABLE t1 WRITE;

422

FLUSH TABLES WITH READ LOCK;

423

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

424

UNLOCK TABLES;

425

DROP TABLE t1;

426

427

# Simplified test for bug #48538 "Assertion in thr_lock() on LOAD DATA

428

# CONCURRENT INFILE".

429

430

DROP TABLE IF EXISTS t1;

431

CREATE TABLE t1 (f1 INT, f2 INT) ENGINE = MEMORY;

432

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW

433

UPDATE LOW_PRIORITY t1 SET f2 = 7;

434

# Statement below should fail with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG

435

# error instead of failing on assertion in table-level locking subsystem.

436

INSERT INTO t1(f1) VALUES(0);

437

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

438

DROP TABLE t1;

439

440

# Bug#43685 Lock table affects other non-related tables

441

442

DROP TABLE IF EXISTS t1, t2;

443

CREATE TABLE t1 (id INT);

444

CREATE TABLE t2 (id INT);

445

# Connection default

446

LOCK TABLE t1 WRITE;

447

ANALYZE TABLE t1;

448

Table Op Msg_type Msg_text

449

test.t1 analyze status Table is already up to date

450

# Connection con2

451

LOCK TABLE t2 WRITE;

452

# This used to hang until the first connection

453

# unlocked t1.

454

FLUSH TABLE t2;

455

UNLOCK TABLES;

456

# Connection default

457

UNLOCK TABLES;

458

DROP TABLE t1, t2;

459

460

# Bug#13586314 - RUNTIME - HIBISCUS: ISSUE DEPRECATION

461

# WARNING FOR LOW_PRIORITY MODIFIER

462

463

DROP TABLE IF EXISTS t1;

464

CREATE TABLE t1 (a INT);

465

# Below statement should return a deprecation warning

466

LOCK TABLES t1 LOW_PRIORITY WRITE;

467

Warnings:

468

Warning 1287 'LOW_PRIORITY WRITE' is deprecated and will be removed in a future release. Please use WRITE instead

469

UNLOCK TABLES;

470

DROP TABLE t1;

471

# End of Bug#13586314

472

473

# Additional test coverage for WL#5534 - Online ALTER phase 1.

474

475

DROP TABLE IF EXISTS t1, m1;

476

CREATE TABLE t1(a INT);

477

CREATE TABLE m1(a INT) engine=merge union(t1);

478

LOCK TABLES m1 WRITE;

479

ALTER TABLE m1 COMMENT 'test';

480

# This triggered an assert in MDL_ticket::downgrade_lock

481

FLUSH TABLE m1;

482

UNLOCK TABLES;

483

DROP TABLE m1, t1;

484

485

# End of 6.0 tests.

486

Older »