~ubuntu-branches/ubuntu/trusty/drizzle/trusty

eval create $temp table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;

select id, code, name from t1 order by id;

update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;

select id, code, name from t1 order by id;

drop table t1;

# A bit bigger test

# The 'replace_column' statements are needed because the cardinality calculated

# by innodb is not always the same between runs

eval create $temp table t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

) engine=$engine_type;

INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);

update t1 set parent_id=parent_id+100;

select * from t1 where parent_id=102;

update t1 set id=id+1000;

-- error ER_DUP_ENTRY,1022

100

update t1 set id=1024 where id=1009;

101

select * from t1;

102

update ignore t1 set id=id+1; # This will change all rows

103

select * from t1;

104

update ignore t1 set id=1023 where id=1010;

105

select * from t1 where parent_id=102;

106

--replace_column 9 #

107

explain select level from t1 where level=1;

108

--replace_column 9 #

109

explain select level,id from t1 where level=1;

110

--replace_column 9 #

111

explain select level,id,parent_id from t1 where level=1;

112

select level,id from t1 where level=1;

113

select level,id,parent_id from t1 where level=1;

114

eval alter table t1 engine=$engine_type;

115

--replace_column 7 #

116

#show keys from t1;

117

drop table t1;

118

119

120

# Test replace

121

122

123

eval create $temp table t1 (

124

gesuchnr int DEFAULT '0' NOT NULL,

125

benutzer_id int DEFAULT '0' NOT NULL,

126

PRIMARY KEY (gesuchnr,benutzer_id)

127

) engine=$engine_type;

128

129

replace into t1 (gesuchnr,benutzer_id) values (2,1);

130

replace into t1 (gesuchnr,benutzer_id) values (1,1);

131

replace into t1 (gesuchnr,benutzer_id) values (1,1);

132

select * from t1;

133

drop table t1;

134

135

136

# test delete using hidden_primary_key

137

138

139

eval create $temp table t1 (a int) engine=$engine_type;

140

insert into t1 values (1), (2);

141

eval alter table t1 ENGINE=$engine_type;

142

delete from t1 where a = 1;

143

select * from t1;

144

check table t1;

145

drop table t1;

146

147

eval create $temp table t1 (a int,b varchar(20)) engine=$engine_type;

148

insert into t1 values (1,""), (2,"testing");

149

delete from t1 where a = 1;

150

select * from t1;

151

create index skr on t1 (a);

152

insert into t1 values (3,""), (4,"testing");

153

analyze table t1;

154

--replace_column 7 #

155

#show keys from t1;

156

drop table t1;

157

158

159

# Test of reading on secondary key with may be null

160

161

eval create $temp table t1 (a int,b varchar(20),key(a)) engine=$engine_type;

162

insert into t1 values (1,""), (2,"testing");

163

select * from t1 where a = 1;

164

drop table t1;

165

166

if ($test_transactions)

167

{

168

169

# Test rollback

170

171

172

eval create $temp table t1 (n int not null primary key) engine=$engine_type;

173

set autocommit=0;

174

insert into t1 values (4);

175

rollback;

176

select n, "after rollback" from t1;

177

insert into t1 values (4);

178

commit;

179

select n, "after commit" from t1;

180

commit;

181

insert into t1 values (5);

182

-- error ER_DUP_ENTRY

183

insert into t1 values (4);

184

commit;

185

select n, "after commit" from t1;

186

set autocommit=1;

187

insert into t1 values (6);

188

-- error ER_DUP_ENTRY

189

insert into t1 values (4);

190

select n from t1;

191

set autocommit=0;

192

193

# savepoints

194

195

begin;

196

savepoint `my_savepoint`;

197

insert into t1 values (7);

198

savepoint `savept2`;

199

insert into t1 values (3);

200

select n from t1;

201

savepoint savept3;

202

rollback to savepoint savept2;

203

--error 1305

204

rollback to savepoint savept3;

205

rollback to savepoint savept2;

206

release savepoint `my_savepoint`;

207

select n from t1;

208

-- error 1305

209

rollback to savepoint `my_savepoint`;

210

--error 1305

211

rollback to savepoint savept2;

212

insert into t1 values (8);

213

savepoint sv;

214

commit;

215

savepoint sv;

216

set autocommit=1;

217

# nop

218

rollback;

219

drop table t1;

220

221

222

# Test for commit and FLUSH TABLES WITH READ LOCK

223

224

225

eval create $temp table t1 (n int not null primary key) engine=$engine_type;

226

start transaction;

227

insert into t1 values (4);

228

flush tables with read lock;

229

230

# Current code can't handle a read lock in middle of transaction

231

#--error 1223;

232

commit;

233

unlock tables;

234

commit;

235

select * from t1;

236

drop table t1;

237

238

239

# Testing transactions

240

241

242

eval create $temp table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=$engine_type;

243

begin;

244

insert into t1 values(1,'hamdouni');

245

select id as afterbegin_id,nom as afterbegin_nom from t1;

246

rollback;

247

select id as afterrollback_id,nom as afterrollback_nom from t1;

248

set autocommit=0;

249

insert into t1 values(2,'mysql');

250

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

251

rollback;

252

select id as afterrollback_id,nom as afterrollback_nom from t1;

253

set autocommit=1;

254

drop table t1;

255

256

257

# Simple not autocommit test

258

259

260

eval create $temp table t1 (id char(8) not null primary key, val int not null) engine=$engine_type;

261

insert into t1 values ('pippo', 12);

262

-- error ER_DUP_ENTRY

263

insert into t1 values ('pippo', 12); # Gives error

264

delete from t1;

265

delete from t1 where id = 'pippo';

266

select * from t1;

267

268

insert into t1 values ('pippo', 12);

269

set autocommit=0;

270

delete from t1;

271

rollback;

272

select * from t1;

273

delete from t1;

274

commit;

275

select * from t1;

276

drop table t1;

277

278

279

# Test of active transactions

280

281

282

eval create $temp table t1 (a integer) engine=$engine_type;

283

start transaction;

284

rename table t1 to t2;

285

eval create $temp table t1 (b integer) engine=$engine_type;

286

insert into t1 values (1);

287

rollback;

288

drop table t1;

289

rename table t2 to t1;

290

drop table t1;

291

set autocommit=1;

292

293

294

# The following simple tests failed at some point

295

296

297

eval create $temp table t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=$engine_type;

298

INSERT INTO t1 VALUES (1, 'Jochen');

299

select * from t1;

300

drop table t1;

301

302

eval create $temp table t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type;

303

set autocommit=0;

304

INSERT INTO t1 SET _userid='marc@anyware.co.uk';

305

COMMIT;

306

SELECT * FROM t1;

307

SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';

308

drop table t1;

309

set autocommit=1;

310

311

}

312

# End of transactional tests

313

314

315

# Test when reading on part of unique key

316

317

eval create $temp table t1 (

318

user_id int DEFAULT '0' NOT NULL,

319

name varchar(100),

320

phone varchar(100),

321

ref_email varchar(100) DEFAULT '' NOT NULL,

322

detail varchar(200),

323

PRIMARY KEY (user_id,ref_email)

324

)engine=$engine_type;

325

326

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');

327

select * from t1 where user_id=10292;

328

INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');

329

select * from t1 where user_id=10292;

330

select * from t1 where user_id>=10292;

331

select * from t1 where user_id>10292;

332

select * from t1 where user_id<10292;

333

drop table t1;

334

335

336

# Test that keys are created in right order

337

338

339

eval create $temp table t1 (a int not null, b int not null,c int not null,

340

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = $engine_type;

341

--replace_column 7 #

342

#show index from t1;

343

drop table t1;

344

345

346

# Test of ALTER TABLE and innodb tables

347

348

349

eval create $temp table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type;

350

eval alter table t1 engine=$engine_type;

351

insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');

352

select * from t1;

353

update t1 set col2='7' where col1='4';

354

select * from t1;

355

alter table t1 add co3 int not null;

356

select * from t1;

357

update t1 set col2='9' where col1='2';

358

select * from t1;

359

drop table t1;

360

361

362

# INSERT INTO innodb tables

363

364

365

eval create $temp table t1 (a int not null , b int, primary key (a)) engine = $engine_type;

366

eval create $temp table t2 (a int not null , b int, primary key (a)) engine = $other_engine_type;

367

insert into t1 VALUES (1,3) , (2,3), (3,3);

368

select * from t1;

369

insert into t2 select * from t1;

370

select * from t2;

371

delete from t1 where b = 3;

372

select * from t1;

373

insert into t1 select * from t2;

374

select * from t1;

375

select * from t2;

376

drop table t1,t2;

377

378

379

# Search on unique key

380

381

382

eval create $temp table t1 (

383

id int NOT NULL auto_increment,

384

ggid varchar(32) DEFAULT '' NOT NULL,

385

email varchar(64) DEFAULT '' NOT NULL,

386

passwd varchar(32) DEFAULT '' NOT NULL,

387

PRIMARY KEY (id),

388

UNIQUE ggid (ggid)

389

) ENGINE=$engine_type;

390

391

insert into t1 (ggid,passwd) values ('test1','xxx');

392

insert into t1 (ggid,passwd) values ('test2','yyy');

393

-- error ER_DUP_ENTRY

394

insert into t1 (ggid,passwd) values ('test2','this will fail');

395

-- error ER_DUP_ENTRY

396

insert into t1 (ggid,id) values ('this will fail',1);

397

398

select * from t1 where ggid='test1';

399

select * from t1 where passwd='xxx';

400

select * from t1 where id=2;

401

402

replace into t1 (ggid,id) values ('this will work',1);

403

replace into t1 (ggid,passwd) values ('test2','this will work');

404

-- error ER_DUP_ENTRY

405

update t1 set id=100,ggid='test2' where id=1;

406

select * from t1;

407

select * from t1 where id=1;

408

select * from t1 where id=999;

409

drop table t1;

410

411

412

# ORDER BY on not primary key

413

414

415

eval create $temp table t1 (

416

user_name varchar(12),

417

password text,

418

subscribed char(1),

419

user_id int DEFAULT '0' NOT NULL,

420

quota bigint,

421

weight double,

422

access_date date,

423

approved datetime,

424

dummy_primary_key int NOT NULL auto_increment,

425

PRIMARY KEY (dummy_primary_key)

426

) ENGINE=$engine_type;

427

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);

428

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);

429

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);

430

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);

431

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);

432

select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;

433

drop table t1;

434

435

436

# Testing of tables without primary keys

437

438

439

eval create $temp table t1 (

440

id int NOT NULL auto_increment,

441

parent_id int DEFAULT '0' NOT NULL,

442

level int DEFAULT '0' NOT NULL,

443

KEY (id),

444

KEY parent_id (parent_id),

445

KEY level (level)

446

) engine=$engine_type;

447

448

INSERT INTO t1 values (179,5,2);

449

update t1 set parent_id=parent_id+100;

450

select * from t1 where parent_id=102;

451

update t1 set id=id+1000;

452

update t1 set id=1024 where id=1009;

453

select * from t1;

454

update ignore t1 set id=id+1; # This will change all rows

455

select * from t1;

456

update ignore t1 set id=1023 where id=1010;

457

select * from t1 where parent_id=102;

458

--replace_column 9 #

459

explain select level from t1 where level=1;

460

select level,id from t1 where level=1;

461

select level,id,parent_id from t1 where level=1;

462

select level,id from t1 where level=1 order by id;

463

delete from t1 where level=1;

464

select * from t1;

465

drop table t1;

466

467

468

# Test of index only reads

469

470

eval create $temp table t1 (

471

sca_code char(6) NOT NULL,

472

cat_code char(6) NOT NULL,

473

sca_desc varchar(50),

474

lan_code char(2) NOT NULL,

475

sca_pic varchar(100),

476

sca_sdesc varchar(50),

477

sca_sch_desc varchar(16),

478

PRIMARY KEY (sca_code, cat_code, lan_code),

479

INDEX sca_pic (sca_pic)

480

) engine = $engine_type ;

481

482

INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');

483

select count(*) from t1 where sca_code = 'PD';

484

select count(*) from t1 where sca_code <= 'PD';

485

select count(*) from t1 where sca_pic is null;

486

alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);

487

select count(*) from t1 where sca_code='PD' and sca_pic is null;

488

select count(*) from t1 where cat_code='E';

489

490

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);

491

select count(*) from t1 where sca_code='PD' and sca_pic is null;

492

select count(*) from t1 where sca_pic >= 'n';

493

select sca_pic from t1 where sca_pic is null;

494

update t1 set sca_pic="test" where sca_pic is null;

495

delete from t1 where sca_code='pd';

496

drop table t1;

497

498

499

# Test of opening table twice and timestamps

500

501

--echo $temp

502

if(!$using_temp)

503

{

504

set @a:=now();

505

eval create $temp table t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type;

506

insert into t1 (a) values(1),(2),(3);

507

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

508

select a from t1 natural join t1 as t2 where b >= @a order by a;

509

update t1 set a=5 where a=1;

510

select a from t1;

511

drop table t1;

512

}

513

514

# Test with variable length primary key

515

516

eval create $temp table t1 (a varchar(100) not null, primary key(a), b int not null) engine=$engine_type;

517

insert into t1 values("hello",1),("world",2);

518

select * from t1 order by b desc;

519

eval alter table t1 ENGINE=$engine_type;

520

--replace_column 7 #

521

#show keys from t1;

522

drop table t1;

523

524

525

# Test of create index with NULL columns

526

527

eval create $temp table t1 (i int, j int ) ENGINE=$engine_type;

528

insert into t1 values (1,2);

529

select * from t1 where i=1 and j=2;

530

create index ax1 on t1 (i,j);

531

select * from t1 where i=1 and j=2;

532

drop table t1;

533

534

535

# Test min-max optimization

536

537

538

eval create $temp table t1 (

539

a int NOT NULL,

540

b int NOT NULL,

541

UNIQUE (a, b)

542

) ENGINE = $engine_type;

543

544

INSERT INTO t1 VALUES (1, 1);

545

SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;

546

drop table t1;

547

548

549

# Test INSERT DELAYED

550

551

552

eval create $temp table t1 (a int NOT NULL) engine=$engine_type;

553

# Can't test this in 3.23

554

# INSERT DELAYED INTO t1 VALUES (1);

555

INSERT INTO t1 VALUES (1);

556

SELECT * FROM t1;

557

DROP TABLE t1;

558

559

560

561

# Crash when using many tables (Test case by Jeremy D Zawodny)

562

563

564

eval create $temp table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = $engine_type;

565

insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

566

--replace_column 9 #

567

explain select * from t1 where a > 0 and a < 50;

568

drop table t1;

569

570

571

# Test prefix key

572

573

eval create $temp table t1 (a char(20), unique (a(5))) engine=$engine_type;

574

drop table t1;

575

eval create $temp table t1 (a char(20), index (a(5))) engine=$engine_type;

576

show create table t1;

577

drop table t1;

578

579

580

# Test using temporary table and auto_increment

581

582

583

eval create temporary table t1 (a int not null auto_increment, primary key(a)) engine=$engine_type;

584

insert into t1 values (NULL),(NULL),(NULL);

585

delete from t1 where a=3;

586

insert into t1 values (NULL);

587

select * from t1;

588

alter table t1 add b int;

589

select * from t1;

590

drop table t1;

591

592

#Slashdot bug

593

eval create $temp table t1

594

(

595

id int auto_increment primary key,

596

name varchar(32) not null,

597

value text not null,

598

uid int not null,

599

unique key(name,uid)

600

) engine=$engine_type;

601

insert into t1 values (1,'one','one value',101),

602

(2,'two','two value',102),(3,'three','three value',103);

603

replace into t1 (value,name,uid) values ('other value','two',102);

604

delete from t1 where uid=102;

605

replace into t1 (value,name,uid) values ('other value','two',102);

606

replace into t1 (value,name,uid) values ('other value','two',102);

607

select * from t1;

608

drop table t1;

609

610

611

# Test DROP DATABASE

612

613

# ML: Test logics

614

# Check that the creation of a table with engine = $engine_type does

615

# in a certain database (already containing some tables using other

616

# storage engines) not prevent the dropping of this database.

617

618

create database mysqltest;

619

eval create $temp table mysqltest.t1 (a int not null) engine= $engine_type;

620

insert into mysqltest.t1 values(1);

621

eval create $temp table mysqltest.t2 (a int not null) engine= $other_engine_type;

622

insert into mysqltest.t2 values(1);

623

eval create $temp table mysqltest.t3 (a int not null) engine= $other_engine_type1;

624

insert into mysqltest.t3 values(1);

625

commit;

626

drop database mysqltest;

627

# Don't check error message

628

--error 1049

629

show tables from mysqltest;

630

631

632

# Test truncate table with and without auto_commit

633

634

635

set autocommit=0;

636

eval create $temp table t1 (a int not null) engine= $engine_type;

637

insert into t1 values(1),(2);

638

truncate table t1;

639

commit;

640

truncate table t1;

641

truncate table t1;

642

select * from t1;

643

insert into t1 values(1),(2);

644

delete from t1;

645

select * from t1;

646

commit;

647

drop table t1;

648

set autocommit=1;

649

650

eval create $temp table t1 (a int not null) engine= $engine_type;

651

insert into t1 values(1),(2);

652

truncate table t1;

653

insert into t1 values(1),(2);

654

select * from t1;

655

truncate table t1;

656

insert into t1 values(1),(2);

657

delete from t1;

658

select * from t1;

659

drop table t1;

660

661

662

# Test of how ORDER BY works when doing it on the whole table

663

664

665

eval create $temp table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=$engine_type;

666

insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);

667

--replace_column 9 #

668

explain select * from t1 order by a;

669

--replace_column 9 #

670

explain select * from t1 order by b;

671

--replace_column 9 #

672

explain select * from t1 order by c;

673

--replace_column 9 #

674

explain select a from t1 order by a;

675

--replace_column 9 #

676

explain select b from t1 order by b;

677

--replace_column 9 #

678

explain select a,b from t1 order by b;

679

--replace_column 9 #

680

explain select a,b from t1;

681

--replace_column 9 #

682

explain select a,b,c from t1;

683

drop table t1;

684

685

686

# Check describe

687

688

689

eval create $temp table t1 (t int not null default 1, key (t)) engine=$engine_type;

690

#desc t1;

691

drop table t1;

692

693

694

# A simple test with some isolation levels

695

# TODO: Make this into a test using replication to really test how

696

# this works.

697

698

699

eval create $temp table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

700

701

BEGIN;

702

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

703

SELECT @@tx_isolation,@@global.tx_isolation;

704

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');

705

select id, code, name from t1 order by id;

706

COMMIT;

707

708

BEGIN;

709

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

710

insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');

711

select id, code, name from t1 order by id;

712

COMMIT;

713

714

BEGIN;

715

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

716

insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');

717

select id, code, name from t1 order by id;

718

COMMIT;

719

DROP TABLE t1;

720

721

722

# Testing of IFNULL

723

724

if(!$using_temp)

725

{

726

eval create $temp table t1 (a int, b int) engine=$engine_type;

727

insert into t1 values(20,null);

728

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

729

t2.b=t3.a;

730

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

731

t2.b=t3.a order by 1;

732

insert into t1 values(10,null);

733

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

734

t2.b=t3.a order by 1;

735

drop table t1;

736

}

737

738

if ($test_foreign_keys)

739

{

740

741

# Test of multi-table-delete with foreign key constraints

742

743

744

eval create $temp table t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

745

eval create $temp table t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=$engine_type;

746

insert into t1 set id=1;

747

insert into t2 set id=1, t1_id=1;

748

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

749

select * from t1;

750

select * from t2;

751

drop table t2,t1;

752

eval create $temp table t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

753

eval create $temp table t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type;

754

INSERT INTO t1 VALUES(1);

755

INSERT INTO t2 VALUES(1, 1);

756

SELECT * from t1;

757

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

758

SELECT * from t1;

759

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

760

SELECT * from t1;

761

DROP TABLE t1,t2;

762

}

763

764

if ($test_transactions)

765

{

766

767

# Test of range_optimizer

768

769

770

set autocommit=0;

771

772

eval create $temp table t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

773

774

eval create $temp table t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

775

776

eval create $temp table t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=$engine_type;

777

778

INSERT INTO t3 VALUES("my-test-1", "my-test-2");

779

COMMIT;

780

781

INSERT INTO t1 VALUES("this-key", "will disappear");

782

INSERT INTO t2 VALUES("this-key", "will also disappear");

783

DELETE FROM t3 WHERE id1="my-test-1";

784

785

SELECT * FROM t1;

786

SELECT * FROM t2;

787

SELECT * FROM t3;

788

ROLLBACK;

789

790

SELECT * FROM t1;

791

SELECT * FROM t2;

792

SELECT * FROM t3;

793

SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;

794

COMMIT;

795

set autocommit=1;

796

DROP TABLE t1,t2,t3;

797

}

798

799

800

# Check update with conflicting key

801

802

803

eval create $temp table t1 (a int not null primary key, b int not null, unique (b)) engine=$engine_type;

804

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

805

# We need the a < 1000 test here to quard against the halloween problems

806

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

807

SELECT * from t1;

808

drop table t1;

809

810

811

# Test that MySQL priorities clustered indexes

812

813

eval create $temp table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=$engine_type;

814

eval create $temp table t2 (a int not null auto_increment primary key, b int) ENGINE = $other_engine_type;

815

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

816

insert into t2 (a) select b from t1;

817

insert into t1 (b) select b from t2;

818

insert into t2 (a) select b from t1;

819

insert into t1 (a) select b from t2;

820

insert into t2 (a) select b from t1;

821

insert into t1 (a) select b from t2;

822

insert into t2 (a) select b from t1;

823

insert into t1 (a) select b from t2;

824

insert into t2 (a) select b from t1;

825

insert into t1 (a) select b from t2;

826

insert into t2 (a) select b from t1;

827

insert into t1 (a) select b from t2;

828

insert into t2 (a) select b from t1;

829

insert into t1 (a) select b from t2;

830

insert into t2 (a) select b from t1;

831

insert into t1 (a) select b from t2;

832

insert into t2 (a) select b from t1;

833

insert into t1 (a) select b from t2;

834

select count(*) from t1;

835

--replace_column 9 #

836

explain select * from t1 where c between 1 and 2500;

837

update t1 set c=a;

838

--replace_column 9 #

839

explain select * from t1 where c between 1 and 2500;

840

drop table t1,t2;

841

842

843

# Test of UPDATE ... ORDER BY

844

845

846

eval create $temp table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=$engine_type;

847

848

insert into t1 (id) values (null),(null),(null),(null),(null);

849

update t1 set fk=69 where fk is null order by id limit 1;

850

SELECT * from t1;

851

drop table t1;

852

853

eval create $temp table t1 (a int not null, b int not null, key (a)) engine=$engine_type;

854

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

855

SET @tmp=0;

856

update t1 set b=(@tmp:=@tmp+1) order by a;

857

update t1 set b=99 where a=1 order by b asc limit 1;

858

update t1 set b=100 where a=1 order by b desc limit 2;

859

update t1 set a=a+10+b where a=1 order by b;

860

select * from t1 order by a,b;

861

drop table t1;

862

863

864

# test autoincrement with TRUNCATE

865

866

867

SET AUTOCOMMIT=1;

868

eval create $temp table t1 (a integer auto_increment primary key) engine=$engine_type;

869

insert into t1 (a) values (NULL),(NULL);

870

truncate table t1;

871

insert into t1 (a) values (NULL),(NULL);

872

SELECT * from t1;

873

drop table t1;

874

875

876

if ($test_foreign_keys)

877

{

878

879

# Test dictionary handling with spaceand quoting

880

881

882

eval create $temp table t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=$engine_type;

883

eval create $temp table t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=$engine_type;

884

#show create table t2;

885

drop table t2,t1;

886

887

888

# Test of multi updated and foreign keys

889

890

891

eval create $temp table `t1` (`id` int not null ,primary key ( `id` )) engine = $engine_type;

892

insert into `t1`values ( 1 ) ;

893

eval create $temp table `t2` (`id` int not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

894

insert into `t2`values ( 1 ) ;

895

eval create $temp table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

896

insert into `t3`values ( 1 ) ;

897

--error 1451

898

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

899

--error 1451

900

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

901

--error 1054

902

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

903

drop table t3,t2,t1;

904

905

906

# test for recursion depth limit

907

908

eval create $temp table t1(

909

id int primary key,

910

pid int,

911

index(pid),

912

foreign key(pid) references t1(id) on delete cascade) engine=$engine_type;

913

insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),

914

(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);

915

-- error 1451

916

delete from t1 where id=0;

917

delete from t1 where id=15;

918

delete from t1 where id=0;

919

920

drop table t1;

921

}

922

# End of FOREIGN KEY tests

923

924

925

# Test timestamps

926

927

928

eval create $temp table t1 (col1 int)ENGINE=$engine_type;

929

eval create $temp table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

930

(stamp))ENGINE=$engine_type;

931

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

932

# Note that timestamp 3 is wrong

933

--error 1685 # Bad timestamp

934

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

935

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

936

'20020204120000' GROUP BY col1;

937

drop table t1,t2;

938

939

940

# Test by Francois MASUREL

941

942

943

if(!$using_temp)

944

{

945

eval create $temp table t1 (

946

`id` int NOT NULL auto_increment,

947

`id_object` int default '0',

948

`id_version` int NOT NULL default '1',

949

`label` varchar(100) NOT NULL default '',

950

`description` text,

951

PRIMARY KEY (`id`),

952

KEY `id_object` (`id_object`),

953

KEY `id_version` (`id_version`)

954

) ENGINE=$engine_type;

955

956

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

957

958

eval create $temp table t2 (

959

`id` int NOT NULL auto_increment,

960

`id_version` int NOT NULL default '1',

961

PRIMARY KEY (`id`),

962

KEY `id_version` (`id_version`)

963

) ENGINE=$engine_type;

964

965

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

966

967

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

968

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

969

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

970

drop table t1,t2;

971

}

972

973

# Live checksum feature available + enabled

974

eval create $temp table t1 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

975

# Live checksum feature available + disabled

976

eval create $temp table t2 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

977

978

# Live checksum feature not available + enabled

979

eval create $temp table t3 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

980

# Live checksum feature not available + disabled

981

eval create $temp table t4 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

982

983

# Live checksum feature probably available + enabled

984

eval create $temp table t5 (a int, b varchar(200), c text not null) engine=$engine_type;

985

# Live checksum feature probably available + disabled

986

eval create $temp table t6 (a int, b varchar(200), c text not null) engine=$engine_type;

987

988

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

989

insert t2 select * from t1;

990

insert t3 select * from t1;

991

insert t4 select * from t1;

992

insert t5 select * from t1;

993

insert t6 select * from t1;

994

checksum table t1, t2, t3, t4, t5, t6, t7;

995

checksum table t1, t2, t3, t4, t5, t6, t7;

996

checksum table t1, t2, t3, t4, t5, t6, t7;

997

# #show table status;

998

drop table t1,t2,t3, t4, t5, t6;

999

1000

1001

# Test problem with refering to different fields in same table in UNION

1002

# (Bug#2552: UNION returns NULL instead of expected value (innoDB only tables))

1003

1004

if(!$using_temp)

1005

{

1006

eval create $temp table t1 (id int, name char(10) not null, name2 char(10) not null) engine=$engine_type;

1007

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

1008

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

1009

drop table t1;

1010

}

1011

1012

# Bug#2160: Extra error message for create $temp table LIKE with InnoDB

1013

1014

eval create $temp table t1 (a int) engine=$engine_type;

1015

--error 1005

1016

create table t2 like t1;

1017

create table t2 like t1 engine=innodb;

1018

show create table t2;

1019

drop table t1,t2;

1020

1021

if ($test_foreign_keys)

1022

{

1023

1024

# Test of automaticly created foreign keys

1025

1026

1027

eval create $temp table t1 (id int not null, id2 int not null, unique (id,id2)) engine=$engine_type;

1028

eval create $temp table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1029

show create table t1;

1030

show create table t2;

1031

create index id on t2 (id);

1032

show create table t2;

1033

create index id2 on t2 (id);

1034

show create table t2;

1035

drop index id2 on t2;

1036

--error 1025,1025

1037

drop index id on t2;

1038

show create table t2;

1039

drop table t2;

1040

1041

eval create $temp table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type;

1042

show create table t2;

1043

create unique index id on t2 (id,id2);

1044

show create table t2;

1045

drop table t2;

1046

1047

# Check foreign key columns created in different order than key columns

1048

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1049

show create table t2;

1050

drop table t2;

1051

1052

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type;

1053

show create table t2;

1054

drop table t2;

1055

1056

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1057

show create table t2;

1058

drop table t2;

1059

1060

eval create $temp table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type;

1061

show create table t2;

1062

drop table t2;

1063

1064

eval create $temp table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type;

1065

show create table t2;

1066

alter table t2 add index id_test (id), add index id_test2 (id,id2);

1067

show create table t2;

1068

drop table t2;

1069

1070

# Test error handling

1071

1072

# Clean up filename -- embedded server reports whole path without .frm,

1073

# regular server reports relative path with .frm (argh!)

1074

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1075

--error 1005

1076

eval create $temp table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1077

1078

# bug#3749

1079

1080

eval create $temp table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1081

show create table t2;

1082

drop table t2;

1083

eval create $temp table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1084

show create table t2;

1085

drop table t2, t1;

1086

}

1087

# End of FOREIGN KEY tests

1088

1089

1090

1091

# Let us test binlog_cache_use and binlog_cache_disk_use status vars.

1092

# Actually this test has nothing to do with innodb per se, it just requires

1093

# transactional table.

1094

1095

flush status;

1096

show status like "binlog_cache_use";

1097

show status like "binlog_cache_disk_use";

1098

1099

eval create $temp table t1 (a int) engine=$engine_type;

1100

1101

# Now we are going to create transaction which is long enough so its

1102

# transaction binlog will be flushed to disk...

1103

let $1=2000;

1104

disable_query_log;

1105

begin;

1106

while ($1)

1107

{

1108

eval insert into t1 values( $1 );

1109

dec $1;

1110

}

1111

commit;

1112

enable_query_log;

1113

show status like "binlog_cache_use";

1114

show status like "binlog_cache_disk_use";

1115

1116

# Transaction which should not be flushed to disk and so should not

1117

# increase binlog_cache_disk_use.

1118

begin;

1119

delete from t1;

1120

commit;

1121

show status like "binlog_cache_use";

1122

show status like "binlog_cache_disk_use";

1123

drop table t1;

1124

1125

1126

# Bug #6126: Duplicate columns in keys gives misleading error message

1127

1128

--error 1060

1129

eval create $temp table t1 (c char(10), index (c,c)) engine=$engine_type;

1130

--error 1060

1131

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1132

--error 1060

1133

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1134

--error 1060

1135

eval create $temp table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1136

eval create $temp table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1137

--error 1060

1138

alter table t1 add key (c1,c1);

1139

--error 1060

1140

alter table t1 add key (c2,c1,c1);

1141

--error 1060

1142

alter table t1 add key (c1,c2,c1);

1143

--error 1060

1144

alter table t1 add key (c1,c1,c2);

1145

drop table t1;

1146

1147

1148

# Bug #4082: integer truncation

1149

1150

1151

eval create $temp table t1(a int, b int) engine=$engine_type;

1152

insert into t1 values ('1111', '3333');

1153

select distinct concat(a, b) from t1;

1154

drop table t1;

1155

1156

if ($fulltext_query_unsupported)

1157

{

1158

1159

# BUG#7709 test case - Boolean fulltext query against unsupported

1160

# engines does not fail

1161

1162

1163

eval create $temp table t1 ( a char(10) ) ENGINE=$engine_type;

1164

--error 1214

1165

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1166

DROP TABLE t1;

1167

}

1168

1169

if ($test_foreign_keys)

1170

{

1171

1172

# check null values #1

1173

1174

1175

--disable_warnings

1176

eval create $temp table t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1177

INSERT INTO t1 VALUES (1),(2),(3);

1178

eval create $temp table t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1179

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1180

--enable_warnings

1181

INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);

1182

SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;

1183

DROP TABLE t2;

1184

DROP TABLE t1;

1185

}

1186

1187

1188

# Bug#11816 - Truncate table doesn't work with temporary innodb tables

1189

# This is not an innodb bug, but we test it using innodb.

1190

1191

eval create temporary table t1 (a int) engine=$engine_type;

1192

insert into t1 values (4711);

1193

truncate t1;

1194

insert into t1 values (42);

1195

select * from t1;

1196

drop table t1;

1197

# Show that it works with permanent tables too.

1198

eval create $temp table t1 (a int) engine=$engine_type;

1199

insert into t1 values (4711);

1200

truncate t1;

1201

insert into t1 values (42);

1202

select * from t1;

1203

drop table t1;

1204

1205

1206

# Bug #13025 Server crash during filesort

1207

1208

1209

eval create $temp table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=$engine_type;

1210

insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);

1211

select * from t1 order by a,b,c,d;

1212

explain select * from t1 order by a,b,c,d;

1213

drop table t1;

1214

1215

1216

# BUG#11039,#13218 Wrong key length in min()

1217

1218

1219

eval create $temp table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1220

insert into t1 values ('8', '6'), ('4', '7');

1221

select min(a) from t1;

1222

select min(b) from t1 where a='8';

1223

drop table t1;

1224

1225

# End of 4.1 tests

1226

1227

1228

# range optimizer problem

1229

1230

1231

eval create $temp table t1 (x bigint not null primary key) engine=$engine_type;

1232

# The below is out of range for a BIGINT (signed)

1233

--error 1264

1234

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1235

select * from t1;

1236

select count(*) from t1 where x>0;

1237

select count(*) from t1 where x=0;

1238

select count(*) from t1 where x<0;

1239

select count(*) from t1 where x < -16;

1240

select count(*) from t1 where x = -16;

1241

explain select count(*) from t1 where x > -16;

1242

select count(*) from t1 where x > -16;

1243

select * from t1 where x > -16;

1244

select count(*) from t1 where x = 18446744073709551601;

1245

drop table t1;

1246

1247

# Please do not remove the following skipped InnoDB specific tests.

1248

# They make the synchronization with innodb.test easier and give

1249

# an idea what to test on other storage engines.

1250

if (0)

1251

{

1252

1253

# Test for testable InnoDB status variables. This test

1254

# uses previous ones(pages_created, rows_deleted, ...).

1255

show status like "Innodb_buffer_pool_pages_total";

1256

show status like "Innodb_page_size";

1257

show status like "Innodb_rows_deleted";

1258

show status like "Innodb_rows_inserted";

1259

show status like "Innodb_rows_updated";

1260

1261

# Test for row locks InnoDB status variables.

1262

show status like "Innodb_row_lock_waits";

1263

show status like "Innodb_row_lock_current_waits";

1264

show status like "Innodb_row_lock_time";

1265

show status like "Innodb_row_lock_time_max";

1266

show status like "Innodb_row_lock_time_avg";

1267

1268

# Test for innodb_sync_spin_loops variable

1269

show variables like "innodb_sync_spin_loops";

1270

set global innodb_sync_spin_loops=1000;

1271

show variables like "innodb_sync_spin_loops";

1272

set global innodb_sync_spin_loops=0;

1273

show variables like "innodb_sync_spin_loops";

1274

set global innodb_sync_spin_loops=20;

1275

show variables like "innodb_sync_spin_loops";

1276

1277

# Test for innodb_thread_concurrency variable

1278

show variables like "innodb_thread_concurrency";

1279

set global innodb_thread_concurrency=1001;

1280

show variables like "innodb_thread_concurrency";

1281

set global innodb_thread_concurrency=0;

1282

show variables like "innodb_thread_concurrency";

1283

set global innodb_thread_concurrency=16;

1284

show variables like "innodb_thread_concurrency";

1285

1286

# Test for innodb_concurrency_tickets variable

1287

show variables like "innodb_concurrency_tickets";

1288

set global innodb_concurrency_tickets=1000;

1289

show variables like "innodb_concurrency_tickets";

1290

set global innodb_concurrency_tickets=0;

1291

show variables like "innodb_concurrency_tickets";

1292

set global innodb_concurrency_tickets=500;

1293

show variables like "innodb_concurrency_tickets";

1294

1295

# Test for innodb_thread_sleep_delay variable

1296

show variables like "innodb_thread_sleep_delay";

1297

set global innodb_thread_sleep_delay=100000;

1298

show variables like "innodb_thread_sleep_delay";

1299

set global innodb_thread_sleep_delay=0;

1300

show variables like "innodb_thread_sleep_delay";

1301

set global innodb_thread_sleep_delay=10000;

1302

show variables like "innodb_thread_sleep_delay";

1303

1304

}

1305

1306

1307

1308

# Test varchar

1309

1310

1311

let $default=`select @@storage_engine`;

1312

eval set storage_engine=$engine_type;

1313

source include/varchar.inc;

1314

1315

1316

# Some errors/warnings on create

1317

1318

1319

# Clean up filename -- embedded server reports whole path without .frm,

1320

# regular server reports relative path with .frm (argh!)

1321

# @TODO The below fails because it assumes latin1

1322

# as the charset. Possibly re-enable a similar test

1323

#--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t1.frm t1

1324

#create $temp table t1 (v varchar(65530), key(v));

1325

#drop table t1;

1326

#create $temp table t1 (v varchar(65536));

1327

#show create table t1;

1328

#drop table t1;

1329

#create $temp table t1 (v varchar(65530) character set utf8);

1330

#show create table t1;

1331

#drop table t1;

1332

1333

#eval set storage_engine=$default;

1334

1335

# InnoDB specific varchar tests

1336

#eval create $temp table t1 (v varchar(16384)) engine=$engine_type;

1337

#drop table t1;

1338

1339

1340

# BUG#11039 Wrong key length in min()

1341

1342

1343

eval create $temp table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1344

insert into t1 values ('8', '6'), ('4', '7');

1345

select min(a) from t1;

1346

select min(b) from t1 where a='8';

1347

drop table t1;

1348

1349

1350

# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error

1351

1352

1353

eval create $temp table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1354

insert into t1 (b) values (1);

1355

replace into t1 (b) values (2), (1), (3);

1356

select * from t1;

1357

truncate table t1;

1358

insert into t1 (b) values (1);

1359

replace into t1 (b) values (2);

1360

replace into t1 (b) values (1);

1361

replace into t1 (b) values (3);

1362

select * from t1;

1363

drop table t1;

1364

1365

eval create $temp table t1 (rowid int not null auto_increment, val int not null,primary

1366

key (rowid), unique(val)) engine=$engine_type;

1367

replace into t1 (val) values ('1'),('2');

1368

replace into t1 (val) values ('1'),('2');

1369

--error ER_DUP_ENTRY

1370

insert into t1 (val) values ('1'),('2');

1371

select * from t1;

1372

drop table t1;

1373

1374

if ($no_autoinc_update)

1375

{

1376

1377

# Test that update does not change internal auto-increment value

1378

1379

1380

eval create $temp table t1 (a int not null auto_increment primary key, val int) engine=$engine_type;

1381

insert into t1 (val) values (1);

1382

update t1 set a=2 where a=1;

1383

# We should get the following error because InnoDB does not update the counter

1384

--error ER_DUP_ENTRY

1385

insert into t1 (val) values (1);

1386

select * from t1;

1387

drop table t1;

1388

}

1389

1390

1391

1392

# Bug#10465: DECIMAL, crash on DELETE (InnoDB only)

1393

1394

1395

--disable_warnings

1396

eval create $temp table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=$engine_type;

1397

--enable_warnings

1398

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

1399

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

1400

SELECT GRADE FROM t1 WHERE GRADE= 151;

1401

DROP TABLE t1;

1402

1403

if ($test_foreign_keys)

1404

{

1405

1406

# Test that the slow TRUNCATE implementation resets autoincrement columns

1407

# (bug #11946)

1408

1409

1410

eval create $temp table t1 (

1411

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1412

) ENGINE=$engine_type;

1413

1414

eval create $temp table t2 (

1415

id INTEGER NOT NULL,

1416

FOREIGN KEY (id) REFERENCES t1 (id)

1417

) ENGINE=$engine_type;

1418

1419

INSERT INTO t1 (id) VALUES (NULL);

1420

SELECT * FROM t1;

1421

TRUNCATE t1;

1422

INSERT INTO t1 (id) VALUES (NULL);

1423

SELECT * FROM t1;

1424

1425

# continued from above; test that doing a slow TRUNCATE on a table with 0

1426

# rows resets autoincrement columns

1427

DELETE FROM t1;

1428

TRUNCATE t1;

1429

INSERT INTO t1 (id) VALUES (NULL);

1430

SELECT * FROM t1;

1431

DROP TABLE t2, t1;

1432

1433

# Test that foreign keys in temporary tables are not accepted (bug #12084)

1434

eval create $temp table t1

1435

(

1436

id INT PRIMARY KEY

1437

) ENGINE=$engine_type;

1438

1439

--error 1005,1005

1440

eval CREATE TEMPORARY TABLE t2

1441

(

1442

id INT NOT NULL PRIMARY KEY,

1443

b INT,

1444

FOREIGN KEY (b) REFERENCES test.t1(id)

1445

) ENGINE=$engine_type;

1446

DROP TABLE t1;

1447

}

1448

# End of FOREIGN KEY test

1449

1450

# Please do not remove the following skipped InnoDB specific tests.

1451

# They make the synchronization with innodb.test easier and give

1452

# an idea what to test on other storage engines.

1453

if (0)

1454

{

1455

1456

1457

# Test that index column max sizes are honored (bug #13315)

1458

1459

1460

# prefix index

1461

eval create $temp table t1 (col1 varchar(2000), index (col1(767)))

1462

character set = latin1 engine = $engine_type;

1463

1464

# normal indexes

1465

eval create $temp table t2 (col1 char(255), index (col1))

1466

character set = latin1 engine = $engine_type;

1467

eval create $temp table t3 (col1 binary(255), index (col1))

1468

character set = latin1 engine = $engine_type;

1469

eval create $temp table t4 (col1 varchar(767), index (col1))

1470

character set = latin1 engine = $engine_type;

1471

eval create $temp table t5 (col1 varchar(767) primary key)

1472

character set = latin1 engine = $engine_type;

1473

eval create $temp table t6 (col1 varbinary(767) primary key)

1474

character set = latin1 engine = $engine_type;

1475

eval create $temp table t7 (col1 text, index(col1(767)))

1476

character set = latin1 engine = $engine_type;

1477

eval create $temp table t8 (col1 blob, index(col1(767)))

1478

character set = latin1 engine = $engine_type;

1479

1480

1481

# multi-column indexes are allowed to be longer

1482

eval create $temp table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))

1483

character set = latin1 engine = $engine_type;

1484

1485

show create table t9;

1486

1487

drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;

1488

1489

# these should have their index length trimmed

1490

eval create $temp table t1 (col1 varchar(768), index(col1))

1491

character set = latin1 engine = $engine_type;

1492

eval create $temp table t2 (col1 varbinary(768), index(col1))

1493

character set = latin1 engine = $engine_type;

1494

eval create $temp table t3 (col1 text, index(col1(768)))

1495

character set = latin1 engine = $engine_type;

1496

eval create $temp table t4 (col1 blob, index(col1(768)))

1497

character set = latin1 engine = $engine_type;

1498

1499

show create table t1;

1500

1501

drop table t1, t2, t3, t4;

1502

1503

}

1504

# End of skipped test

1505

1506

# Please do not remove the following skipped InnoDB specific tests.

1507

# They make the synchronization with innodb.test easier and give

1508

# an idea what to test on other storage engines.

1509

if (0)

1510

{

1511

1512

# these should be refused

1513

--error 1071

1514

eval create $temp table t1 (col1 varchar(768) primary key)

1515

character set = latin1 engine = $engine_type;

1516

--error 1071

1517

eval create $temp table t2 (col1 varbinary(768) primary key)

1518

character set = latin1 engine = $engine_type;

1519

--error 1071

1520

eval create $temp table t3 (col1 text, primary key(col1(768)))

1521

character set = latin1 engine = $engine_type;

1522

--error 1071

1523

eval create $temp table t4 (col1 blob, primary key(col1(768)))

1524

character set = latin1 engine = $engine_type;

1525

1526

}

1527

1528

if ($test_foreign_keys)

1529

{

1530

1531

# Test improved foreign key error messages (bug #3443)

1532

1533

1534

eval create $temp table t1

1535

(

1536

id INT PRIMARY KEY

1537

) ENGINE=$engine_type;

1538

1539

eval create $temp table t2

1540

(

1541

v INT,

1542

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1543

) ENGINE=$engine_type;

1544

1545

--error 1452

1546

INSERT INTO t2 VALUES(2);

1547

1548

INSERT INTO t1 VALUES(1);

1549

INSERT INTO t2 VALUES(1);

1550

1551

--error 1451

1552

DELETE FROM t1 WHERE id = 1;

1553

1554

--error 1217

1555

DROP TABLE t1;

1556

1557

SET FOREIGN_KEY_CHECKS=0;

1558

DROP TABLE t1;

1559

SET FOREIGN_KEY_CHECKS=1;

1560

1561

--error 1452

1562

INSERT INTO t2 VALUES(3);

1563

1564

DROP TABLE t2;

1565

}

1566

# End of FOREIGN tests

1567

1568

if ($test_transactions)

1569

{

1570

1571

# Test that checksum table uses a consistent read Bug #12669

1572

1573

connect (a,localhost,root,,);

1574

connect (b,localhost,root,,);

1575

connection a;

1576

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1577

insert into t1 values (1),(2);

1578

set autocommit=0;

1579

checksum table t1;

1580

connection b;

1581

insert into t1 values(3);

1582

connection a;

1583

1584

# Here checksum should not see insert

1585

1586

checksum table t1;

1587

connection a;

1588

commit;

1589

checksum table t1;

1590

commit;

1591

drop table t1;

1592

1593

# autocommit = 1

1594

1595

connection a;

1596

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1597

insert into t1 values (1),(2);

1598

set autocommit=1;

1599

checksum table t1;

1600

connection b;

1601

set autocommit=1;

1602

insert into t1 values(3);

1603

connection a;

1604

1605

# Here checksum sees insert

1606

1607

checksum table t1;

1608

drop table t1;

1609

1610

connection default;

1611

disconnect a;

1612

disconnect b;

1613

}

1614

1615

# tests for bugs #9802 and #13778

1616

1617

if ($test_foreign_keys)

1618

{

1619

# test that FKs between invalid types are not accepted

1620

1621

set foreign_key_checks=0;

1622

eval create $temp table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type;

1623

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1624

-- error 1005

1625

eval create $temp table t1(a char(10) primary key, b varchar(20)) engine = $engine_type;

1626

set foreign_key_checks=1;

1627

drop table t2;

1628

1629

# test that FKs between different charsets are not accepted in CREATE even

1630

# when f_k_c is 0

1631

1632

set foreign_key_checks=0;

1633

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1634

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1635

-- error 1005

1636

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1637

set foreign_key_checks=1;

1638

drop table t1;

1639

1640

# test that invalid datatype conversions with ALTER are not allowed

1641

1642

set foreign_key_checks=0;

1643

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type;

1644

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type;

1645

-- error 1025,1025

1646

alter table t1 modify column a int;

1647

set foreign_key_checks=1;

1648

drop table t2,t1;

1649

1650

# test that charset conversions with ALTER are allowed when f_k_c is 0

1651

1652

set foreign_key_checks=0;

1653

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1654

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1655

alter table t1 convert to character set utf8;

1656

set foreign_key_checks=1;

1657

drop table t2,t1;

1658

1659

# test that RENAME does not allow invalid charsets when f_k_c is 0

1660

1661

set foreign_key_checks=0;

1662

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1663

eval create $temp table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1664

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1665

-- error 1025

1666

rename table t3 to t1;

1667

set foreign_key_checks=1;

1668

drop table t2,t3;

1669

1670

# test that foreign key errors are reported correctly (Bug #15550)

1671

1672

eval create $temp table t1(a int primary key) row_format=redundant engine=$engine_type;

1673

eval create $temp table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=$engine_type;

1674

eval create $temp table t3(a int primary key) row_format=compact engine=$engine_type;

1675

eval create $temp table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=$engine_type;

1676

1677

insert into t1 values(1);

1678

insert into t3 values(1);

1679

-- error 1452

1680

insert into t2 values(2);

1681

-- error 1452

1682

insert into t4 values(2);

1683

insert into t2 values(1);

1684

insert into t4 values(1);

1685

-- error 1451

1686

update t1 set a=2;

1687

-- error 1452

1688

update t2 set a=2;

1689

-- error 1451

1690

update t3 set a=2;

1691

-- error 1452

1692

update t4 set a=2;

1693

-- error 1451

1694

truncate t1;

1695

-- error 1451

1696

truncate t3;

1697

truncate t2;

1698

truncate t4;

1699

truncate t1;

1700

truncate t3;

1701

1702

drop table t4,t3,t2,t1;

1703

}

1704

# End of FOREIGN KEY tests

1705

1706

1707

# Please do not remove the following skipped InnoDB specific tests.

1708

# They make the synchronization with innodb.test easier and give

1709

# an idea what to test on other storage engines.

1710

if (0)

1711

{

1712

1713

1714

# Test that we can create a large (>1K) key

1715

1716

eval create $temp table t1 (a varchar(255) character set utf8,

1717

b varchar(255) character set utf8,

1718

c varchar(255) character set utf8,

1719

d varchar(255) character set utf8,

1720

key (a,b,c,d)) engine=$engine_type;

1721

drop table t1;

1722

--error ER_TOO_LONG_KEY

1723

eval create $temp table t1 (a varchar(255) character set utf8,

1724

b varchar(255) character set utf8,

1725

c varchar(255) character set utf8,

1726

d varchar(255) character set utf8,

1727

e varchar(255) character set utf8,

1728

key (a,b,c,d,e)) engine=$engine_type;

1729

1730

1731

# test the padding of BINARY types and collations (Bug #14189)

1732

1733

eval create $temp table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type;

1734

eval create $temp table t2 (s1 binary(2),primary key (s1)) engine=$engine_type;

1735

eval create $temp table t3 (s1 varchar(2),primary key (s1)) engine=$engine_type;

1736

eval create $temp table t4 (s1 char(2),primary key (s1)) engine=$engine_type;

1737

1738

insert into t1 values (0x41),(0x4120),(0x4100);

1739

-- error ER_DUP_ENTRY

1740

insert into t2 values (0x41),(0x4120),(0x4100);

1741

insert into t2 values (0x41),(0x4120);

1742

-- error ER_DUP_ENTRY

1743

insert into t3 values (0x41),(0x4120),(0x4100);

1744

insert into t3 values (0x41),(0x4100);

1745

-- error ER_DUP_ENTRY

1746

insert into t4 values (0x41),(0x4120),(0x4100);

1747

insert into t4 values (0x41),(0x4100);

1748

select hex(s1) from t1;

1749

select hex(s1) from t2;

1750

select hex(s1) from t3;

1751

select hex(s1) from t4;

1752

drop table t1,t2,t3,t4;

1753

}

1754

1755

if (test_foreign_keys)

1756

{

1757

eval create $temp table t1 (a int primary key,s1 varbinary(3) not null unique) engine=$engine_type;

1758

eval create $temp table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1759

1760

insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);

1761

-- error 1452

1762

insert into t2 values(0x42);

1763

insert into t2 values(0x41);

1764

select hex(s1) from t2;

1765

update t1 set s1=0x123456 where a=2;

1766

select hex(s1) from t2;

1767

-- error 1451

1768

update t1 set s1=0x12 where a=1;

1769

-- error 1451

1770

update t1 set s1=0x12345678 where a=1;

1771

-- error 1451

1772

update t1 set s1=0x123457 where a=1;

1773

update t1 set s1=0x1220 where a=1;

1774

select hex(s1) from t2;

1775

update t1 set s1=0x1200 where a=1;

1776

select hex(s1) from t2;

1777

update t1 set s1=0x4200 where a=1;

1778

select hex(s1) from t2;

1779

-- error 1451

1780

delete from t1 where a=1;

1781

delete from t1 where a=2;

1782

update t2 set s1=0x4120;

1783

-- error 1451

1784

delete from t1;

1785

delete from t1 where a!=3;

1786

select a,hex(s1) from t1;

1787

select hex(s1) from t2;

1788

1789

drop table t2,t1;

1790

1791

eval create $temp table t1 (a int primary key,s1 varchar(2) not null unique) engine=$engine_type;

1792

eval create $temp table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1793

1794

insert into t1 values(1,0x4100),(2,0x41);

1795

insert into t2 values(0x41);

1796

select hex(s1) from t2;

1797

update t1 set s1=0x1234 where a=1;

1798

select hex(s1) from t2;

1799

update t1 set s1=0x12 where a=2;

1800

select hex(s1) from t2;

1801

delete from t1 where a=1;

1802

-- error 1451

1803

delete from t1 where a=2;

1804

select a,hex(s1) from t1;

1805

select hex(s1) from t2;

1806

1807

drop table t2,t1;

1808

}

1809

# End FOREIGN KEY tests

1810

1811

if ($test_foreign_keys)

1812

{

1813

# Ensure that <tablename>_ibfk_0 is not mistreated as a

1814

# generated foreign key identifier. (Bug #16387)

1815

1816

eval create $temp table t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type;

1817

eval create $temp table t2(a INT) ENGINE=$engine_type;

1818

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);

1819

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1820

ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);

1821

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1822

SHOW create $temp table t2;

1823

DROP TABLE t2,t1;

1824

}

1825

1826

if ($test_foreign_keys)

1827

{

1828

1829

# Test that cascading updates leading to duplicate keys give the correct

1830

# error message (bug #9680)

1831

1832

1833

eval create $temp table t1 (

1834

field1 varchar(8) NOT NULL DEFAULT '',

1835

field2 varchar(8) NOT NULL DEFAULT '',

1836

PRIMARY KEY (field1, field2)

1837

) ENGINE=$engine_type;

1838

1839

eval create $temp table t2 (

1840

field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,

1841

FOREIGN KEY (field1) REFERENCES t1 (field1)

1842

ON DELETE CASCADE ON UPDATE CASCADE

1843

) ENGINE=$engine_type;

1844

1845

INSERT INTO t1 VALUES ('old', 'somevalu');

1846

INSERT INTO t1 VALUES ('other', 'anyvalue');

1847

1848

INSERT INTO t2 VALUES ('old');

1849

INSERT INTO t2 VALUES ('other');

1850

1851

--error ER_FOREIGN_DUPLICATE_KEY

1852

UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';

1853

1854

DROP TABLE t2;

1855

DROP TABLE t1;

1856

1857

1858

# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE

1859

1860

eval create $temp table t1 (

1861

c1 bigint not null,

1862

c2 bigint not null,

1863

primary key (c1),

1864

unique key (c2)

1865

) engine=$engine_type;

1866

1867

eval create $temp table t2 (

1868

c1 bigint not null,

1869

primary key (c1)

1870

) engine=$engine_type;

1871

1872

alter table t1 add constraint c2_fk foreign key (c2)

1873

references t2(c1) on delete cascade;

1874

show create table t1;

1875

1876

alter table t1 drop foreign key c2_fk;

1877

show create table t1;

1878

1879

drop table t1, t2;

1880

}

1881

# End FOREIGN KEY test

1882

1883

1884

# Bug #14360: problem with intervals

1885

1886

1887

eval create $temp table t1(a date) engine=$engine_type;

1888

eval create $temp table t2(a date, key(a)) engine=$engine_type;

1889

insert into t1 values('2005-10-01');

1890

insert into t2 values('2005-10-01');

1891

select * from t1, t2

1892

where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;

1893

drop table t1, t2;

1894

1895

eval create $temp table t1 (id int not null, f_id int not null, f int not null,

1896

primary key(f_id, id)) engine=$engine_type;

1897

eval create $temp table t2 (id int not null,s_id int not null,s varchar(200),

1898

primary key(id)) engine=$engine_type;

1899

INSERT INTO t1 VALUES (8, 1, 3);

1900

INSERT INTO t1 VALUES (1, 2, 1);

1901

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

1902

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

1903

commit;

1904

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1905

where mm.id is null lock in share mode;

1906

drop table t1,t2;

1907

1908

1909

# Test case where X-locks on unused rows should be released in a

1910

# update (because READ COMMITTED isolation level)

1911

1912

if(!$using_temp)

1913

{

1914

connect (a,localhost,root,,);

1915

connect (b,localhost,root,,);

1916

connection a;

1917

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1918

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

1919

commit;

1920

set autocommit = 0;

1921

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1922

update t1 set b = 5 where b = 1;

1923

connection b;

1924

set autocommit = 0;

1925

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1926

1927

# X-lock to record (7,3) should be released in a update

1928

1929

select * from t1 where a = 7 and b = 3 for update;

1930

connection a;

1931

commit;

1932

connection b;

1933

commit;

1934

drop table t1;

1935

connection default;

1936

disconnect a;

1937

disconnect b;

1938

}

1939

if ($test_transactions)

1940

{

1941

1942

# Test case where no locks should be released (because we are not

1943

# using READ COMMITTED isolation level)

1944

1945

1946

connect (a,localhost,root,,);

1947

connect (b,localhost,root,,);

1948

connection a;

1949

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1950

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);

1951

commit;

1952

set autocommit = 0;

1953

select * from t1 lock in share mode;

1954

update t1 set b = 5 where b = 1;

1955

connection b;

1956

set autocommit = 0;

1957

1958

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

1959

1960

--error 1205

1961

select * from t1 where a = 2 and b = 2 for update;

1962

1963

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

1964

1965

--error 1205

1966

connection a;

1967

commit;

1968

connection b;

1969

commit;

1970

connection default;

1971

disconnect a;

1972

disconnect b;

1973

drop table t1;

1974

1975

1976

# Consistent read should be used in following selects

1977

1978

# 1) INSERT INTO ... SELECT

1979

# 2) UPDATE ... = ( SELECT ...)

1980

# 3) CREATE ... SELECT

1981

1982

connect (a,localhost,root,,);

1983

connect (b,localhost,root,,);

1984

connection a;

1985

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1986

insert into t1 values (1,2),(5,3),(4,2);

1987

eval create $temp table t2(d int not null, e int, primary key(d)) engine=$engine_type;

1988

insert into t2 values (8,6),(12,1),(3,1);

1989

commit;

1990

set autocommit = 0;

1991

select * from t2 for update;

1992

connection b;

1993

set autocommit = 0;

1994

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1995

insert into t1 select * from t2;

1996

update t1 set b = (select e from t2 where a = d);

1997

eval create $temp table t3(d int not null, e int, primary key(d)) engine=$engine_type

1998

select * from t2;

1999

commit;

2000

connection a;

2001

commit;

2002

connection default;

2003

disconnect a;

2004

disconnect b;

2005

drop table t1, t2, t3;

2006

2007

2008

# Consistent read should not be used if

2009

2010

# (a) isolation level is serializable OR

2011

# (b) select ... lock in share mode OR

2012

# (c) select ... for update

2013

2014

# in following queries:

2015

2016

# 1) INSERT INTO ... SELECT

2017

# 2) UPDATE ... = ( SELECT ...)

2018

# 3) CREATE ... SELECT

2019

2020

connect (a,localhost,root,,);

2021

eval SET SESSION STORAGE_ENGINE = $engine_type;

2022

connect (b,localhost,root,,);

2023

eval SET SESSION STORAGE_ENGINE = $engine_type;

2024

connect (c,localhost,root,,);

2025

eval SET SESSION STORAGE_ENGINE = $engine_type;

2026

connect (d,localhost,root,,);

2027

eval SET SESSION STORAGE_ENGINE = $engine_type;

2028

connect (e,localhost,root,,);

2029

eval SET SESSION STORAGE_ENGINE = $engine_type;

2030

connect (f,localhost,root,,);

2031

eval SET SESSION STORAGE_ENGINE = $engine_type;

2032

connect (g,localhost,root,,);

2033

eval SET SESSION STORAGE_ENGINE = $engine_type;

2034

connect (h,localhost,root,,);

2035

eval SET SESSION STORAGE_ENGINE = $engine_type;

2036

connect (i,localhost,root,,);

2037

eval SET SESSION STORAGE_ENGINE = $engine_type;

2038

connect (j,localhost,root,,);

2039

eval SET SESSION STORAGE_ENGINE = $engine_type;

2040

connection a;

2041

create $temp table t1(a int not null, b int, primary key(a));

2042

insert into t1 values (1,2),(5,3),(4,2);

2043

create $temp table t2(a int not null, b int, primary key(a));

2044

insert into t2 values (8,6),(12,1),(3,1);

2045

create $temp table t3(d int not null, b int, primary key(d));

2046

insert into t3 values (8,6),(12,1),(3,1);

2047

create $temp table t5(a int not null, b int, primary key(a));

2048

insert into t5 values (1,2),(5,3),(4,2);

2049

create $temp table t6(d int not null, e int, primary key(d));

2050

insert into t6 values (8,6),(12,1),(3,1);

2051

create $temp table t8(a int not null, b int, primary key(a));

2052

insert into t8 values (1,2),(5,3),(4,2);

2053

create $temp table t9(d int not null, e int, primary key(d));

2054

insert into t9 values (8,6),(12,1),(3,1);

2055

commit;

2056

set autocommit = 0;

2057

select * from t2 for update;

2058

connection b;

2059

set autocommit = 0;

2060

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2061

--send

2062

insert into t1 select * from t2;

2063

connection c;

2064

set autocommit = 0;

2065

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2066

--send

2067

update t3 set b = (select b from t2 where a = d);

2068

connection d;

2069

set autocommit = 0;

2070

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2071

--send

2072

create $temp table t4(a int not null, b int, primary key(a)) select * from t2;

2073

connection e;

2074

set autocommit = 0;

2075

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2076

--send

2077

insert into t5 (select * from t2 lock in share mode);

2078

connection f;

2079

set autocommit = 0;

2080

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2081

--send

2082

update t6 set e = (select b from t2 where a = d lock in share mode);

2083

connection g;

2084

set autocommit = 0;

2085

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2086

--send

2087

create $temp table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;

2088

connection h;

2089

set autocommit = 0;

2090

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2091

--send

2092

insert into t8 (select * from t2 for update);

2093

connection i;

2094

set autocommit = 0;

2095

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2096

--send

2097

update t9 set e = (select b from t2 where a = d for update);

2098

connection j;

2099

set autocommit = 0;

2100

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2101

--send

2102

create $temp table t10(a int not null, b int, primary key(a)) select * from t2 for update;

2103

2104

connection b;

2105

--error 1205

2106

reap;

2107

2108

connection c;

2109

--error 1205

2110

reap;

2111

2112

connection d;

2113

--error 1205

2114

reap;

2115

2116

connection e;

2117

--error 1205

2118

reap;

2119

2120

connection f;

2121

--error 1205

2122

reap;

2123

2124

connection g;

2125

--error 1205

2126

reap;

2127

2128

connection h;

2129

--error 1205

2130

reap;

2131

2132

connection i;

2133

--error 1205

2134

reap;

2135

2136

connection j;

2137

--error 1205

2138

reap;

2139

2140

connection a;

2141

commit;

2142

2143

connection default;

2144

disconnect a;

2145

disconnect b;

2146

disconnect c;

2147

disconnect d;

2148

disconnect e;

2149

disconnect f;

2150

disconnect g;

2151

disconnect h;

2152

disconnect i;

2153

disconnect j;

2154

drop table t1, t2, t3, t5, t6, t8, t9;

2155

}

2156

# End transactional tests

2157

2158

if (test_foreign_keys)

2159

{

2160

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

2161

--error 1005

2162

eval create $temp table t1 (DB_ROW_ID int) engine=$engine_type;

2163

2164

2165

# Bug #17152: Wrong result with BINARY comparison on aliased column

2166

2167

2168

eval create $temp table t1 (

2169

a BIGINT(20) NOT NULL,

2170

PRIMARY KEY (a)

2171

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2172

2173

eval create $temp table t2 (

2174

a BIGINT(20) NOT NULL,

2175

b VARCHAR(128) NOT NULL,

2176

c TEXT NOT NULL,

2177

PRIMARY KEY (a,b),

2178

KEY idx_t2_b_c (b,c(200)),

2179

CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)

2180

ON DELETE CASCADE

2181

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2182

2183

INSERT INTO t1 VALUES (1);

2184

INSERT INTO t2 VALUES (1, 'bar', 'vbar');

2185

INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');

2186

INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');

2187

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

2188

2189

SELECT * FROM t2 WHERE b = 'customer_over';

2190

SELECT * FROM t2 WHERE BINARY b = 'customer_over';

2191

SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';

2192

/* Bang: Empty result set, above was expected: */

2193

SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

2194

SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

2195

2196

drop table t2, t1;

2197

}

2198

2199

if ($no_spatial_key)

2200

{

2201

2202

# Bug #15680 (SPATIAL key in innodb)

2203

2204

--error ER_TABLE_CANT_HANDLE_SPKEYS

2205

eval create $temp table t1 (g geometry not null, spatial gk(g)) engine=$engine_type;

2206

}

2207

2208

2209

# Test optimize on table with open transaction

2210

2211

2212

eval create $temp table t1 ( a int ) ENGINE=$engine_type;

2213

BEGIN;

2214

INSERT INTO t1 VALUES (1);

2215

eval ALTER TABLE t1 ENGINE=$engine_type;

2216

DROP TABLE t1;

2217

2218

#######################################################################

2219

# #

2220

# This is derivate of t/innodb.test and has to be maintained by #

2221

# MySQL guys only. #

2222

# #

2223

# Please synchronize this file from time to time with t/innodb.test. #

2224

# Please, DO NOT create a toplevel testcase innodb-mix2.test, because #

2225

# innodb.test does already these tests. #

2226

# #

2227

#######################################################################

Older »