~drizzle-trunk/drizzle/development

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;

eval create table t1 (id int not null auto_increment, code tinyint 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;

# 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,

eval CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

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, ER_DUP_KEY

-- 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

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;

114

optimize table t1;

115

--replace_column 7 #

116

#show keys from t1;

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,

123

eval CREATE TABLE t1 (

124

gesuchnr int(11) DEFAULT '0' NOT NULL,

125

benutzer_id int(11) DEFAULT '0' NOT NULL,

126

PRIMARY KEY (gesuchnr,benutzer_id)

127

) engine=$engine_type;

128

136

# test delete using hidden_primary_key

137

138

139

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

139

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

140

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

141

eval alter table t1 ENGINE=$engine_type;

141

optimize table t1;

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;

147

eval create 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;

152

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

153

analyze table t1;

154

--replace_column 7 #

155

#show keys from t1;

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;

161

eval create 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;

169

# Test rollback

170

171

172

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

172

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

173

set autocommit=0;

174

insert into t1 values (4);

175

rollback;

179

select n, "after commit" from t1;

180

commit;

181

insert into t1 values (5);

182

--error ER_DUP_ENTRY

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

188

-- error ER_DUP_ENTRY

189

insert into t1 values (4);

190

select n from t1;

191

set autocommit=0;

200

select n from t1;

201

savepoint savept3;

202

rollback to savepoint savept2;

203

--error ER_SP_DOES_NOT_EXIST

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 ER_SP_DOES_NOT_EXIST

208

-- error 1305

209

rollback to savepoint `my_savepoint`;

210

--error ER_SP_DOES_NOT_EXIST

210

--error 1305

211

rollback to savepoint savept2;

212

insert into t1 values (8);

213

savepoint sv;

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;

225

eval create 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;

239

# Testing transactions

240

241

242

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

242

eval create 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;

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;

260

eval CREATE 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

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

279

# Test of active transactions

280

281

282

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

282

eval create 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;

285

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

286

insert into t1 values (1);

287

rollback;

288

drop table t1;

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;

297

eval CREATE 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;

302

eval CREATE 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;

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,

317

eval CREATE TABLE t1 (

318

user_id int(10) DEFAULT '0' NOT NULL,

319

name varchar(100),

320

phone varchar(100),

321

ref_email varchar(100) DEFAULT '' NOT NULL,

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,

339

eval CREATE 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;

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;

349

eval create 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 DEFAULT 42 not null;

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;

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;

365

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

366

eval create 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;

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,

382

eval CREATE TABLE t1 (

383

id int(11) NOT NULL auto_increment,

384

ggid varchar(32) binary DEFAULT '' NOT NULL,

385

email varchar(64) DEFAULT '' NOT NULL,

386

passwd varchar(32) DEFAULT '' NOT NULL,

386

passwd varchar(32) binary 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

393

-- error ER_DUP_ENTRY

394

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

395

--error ER_DUP_ENTRY

395

-- error ER_DUP_ENTRY

396

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

397

398

select * from t1 where ggid='test1';

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

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;

412

# ORDER BY on not primary key

413

414

415

eval create $temp table t1 (

415

eval CREATE 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,

419

user_id int(11) DEFAULT '0' NOT NULL,

420

quota bigint(20),

421

weight double,

422

access_date date,

423

access_time time,

423

424

approved datetime,

424

dummy_primary_key int NOT NULL auto_increment,

425

dummy_primary_key int(11) NOT NULL auto_increment,

425

426

PRIMARY KEY (dummy_primary_key)

426

427

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

428

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

429

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

430

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

431

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

432

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

433

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

433

434

drop table t1;

434

435

436

437

# 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,

440

eval CREATE TABLE t1 (

441

id int(11) NOT NULL auto_increment,

442

parent_id int(11) DEFAULT '0' NOT NULL,

443

level tinyint(4) DEFAULT '0' NOT NULL,

443

444

KEY (id),

444

445

KEY parent_id (parent_id),

445

446

KEY level (level)

467

468

469

# Test of index only reads

469

470

eval create $temp table t1 (

471

eval CREATE TABLE t1 (

471

472

sca_code char(6) NOT NULL,

472

473

cat_code char(6) NOT NULL,

473

474

sca_desc varchar(50),

498

499

500

# Test of opening table twice and timestamps

500

501

--echo $temp

502

if(!$using_temp)

503

{

504

502

set @a:=now();

505

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

503

eval CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type;

506

504

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

507

505

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

508

506

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

509

507

update t1 set a=5 where a=1;

510

508

select a from t1;

511

509

drop table t1;

512

}

510

513

511

514

512

# Test with variable length primary key

515

513

516

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

514

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

517

515

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

518

516

select * from t1 order by b desc;

519

eval alter table t1 ENGINE=$engine_type;

517

optimize table t1;

520

518

--replace_column 7 #

521

#show keys from t1;

519

show keys from t1;

522

520

drop table t1;

523

521

524

522

525

523

# Test of create index with NULL columns

526

524

527

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

525

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

528

526

insert into t1 values (1,2);

529

527

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

530

528

create index ax1 on t1 (i,j);

535

533

# Test min-max optimization

536

534

537

535

538

eval create $temp table t1 (

539

a int NOT NULL,

540

b int NOT NULL,

536

eval CREATE TABLE t1 (

537

a int3 NOT NULL,

538

b int1 NOT NULL,

541

539

UNIQUE (a, b)

542

540

) ENGINE = $engine_type;

543

541

549

547

# Test INSERT DELAYED

550

548

551

549

552

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

550

eval CREATE TABLE t1 (a int NOT NULL) engine=$engine_type;

553

551

# Can't test this in 3.23

554

552

# INSERT DELAYED INTO t1 VALUES (1);

555

553

INSERT INTO t1 VALUES (1);

561

559

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

562

560

563

561

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;

562

eval create 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

563

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

564

--replace_column 9 #

567

565

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

568

566

drop table t1;

569

567

570

568

569

# Test lock tables

570

571

572

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

573

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

574

LOCK TABLES t1 WRITE;

575

--error ER_DUP_ENTRY

576

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

577

select id from t1;

578

select id from t1;

579

UNLOCK TABLES;

580

DROP TABLE t1;

581

582

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

583

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

584

LOCK TABLES t1 WRITE;

585

begin;

586

--error ER_DUP_ENTRY

587

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

588

select id from t1;

589

insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');

590

commit;

591

select id,id3 from t1;

592

UNLOCK TABLES;

593

DROP TABLE t1;

594

595

571

596

# Test prefix key

572

597

573

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

598

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

574

599

drop table t1;

575

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

600

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

576

601

show create table t1;

577

602

drop table t1;

578

603

590

615

drop table t1;

591

616

592

617

#Slashdot bug

593

eval create $temp table t1

618

eval create table t1

594

619

(

595

620

id int auto_increment primary key,

596

621

name varchar(32) not null,

600

625

) engine=$engine_type;

601

626

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

602

627

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

628

set insert_id=5;

603

629

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

604

630

delete from t1 where uid=102;

631

set insert_id=5;

605

632

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

633

set insert_id=6;

606

634

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

607

635

select * from t1;

608

636

drop table t1;

616

644

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

617

645

618

646

create database mysqltest;

619

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

647

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

620

648

insert into mysqltest.t1 values(1);

621

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

649

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

622

650

insert into mysqltest.t2 values(1);

623

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

651

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

624

652

insert into mysqltest.t3 values(1);

625

653

commit;

626

654

drop database mysqltest;

627

655

# Don't check error message

628

--error ER_BAD_DB_ERROR

656

--error 1049

629

657

show tables from mysqltest;

630

658

631

659

633

661

634

662

635

663

set autocommit=0;

636

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

664

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

637

665

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

638

commit;

639

666

truncate table t1;

640

667

commit;

641

668

truncate table t1;

648

675

drop table t1;

649

676

set autocommit=1;

650

677

651

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

678

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

652

679

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

653

680

truncate table t1;

654

681

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

663

690

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

664

691

665

692

666

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;

693

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

667

694

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

668

695

--replace_column 9 #

669

696

explain select * from t1 order by a;

687

714

# Check describe

688

715

689

716

690

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

691

#desc t1;

717

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

718

desc t1;

692

719

drop table t1;

693

720

694

721

722

# Test of multi-table-delete

723

724

725

eval CREATE TABLE t1 (

726

number bigint(20) NOT NULL default '0',

727

cname char(15) NOT NULL default '',

728

carrier_id smallint(6) NOT NULL default '0',

729

privacy tinyint(4) NOT NULL default '0',

730

last_mod_date timestamp NOT NULL,

731

last_mod_id smallint(6) NOT NULL default '0',

732

last_app_date timestamp NOT NULL,

733

last_app_id smallint(6) default '-1',

734

version smallint(6) NOT NULL default '0',

735

assigned_scps int(11) default '0',

736

status tinyint(4) default '0'

737

) ENGINE=$engine_type;

738

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

739

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

740

INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);

741

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

742

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

743

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

744

eval CREATE TABLE t2 (

745

number bigint(20) NOT NULL default '0',

746

cname char(15) NOT NULL default '',

747

carrier_id smallint(6) NOT NULL default '0',

748

privacy tinyint(4) NOT NULL default '0',

749

last_mod_date timestamp NOT NULL,

750

last_mod_id smallint(6) NOT NULL default '0',

751

last_app_date timestamp NOT NULL,

752

last_app_id smallint(6) default '-1',

753

version smallint(6) NOT NULL default '0',

754

assigned_scps int(11) default '0',

755

status tinyint(4) default '0'

756

) ENGINE=$engine_type;

757

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

758

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

759

INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);

760

INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);

761

select * from t1;

762

select * from t2;

763

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

764

select * from t1;

765

select * from t2;

766

select * from t2;

767

drop table t1,t2;

768

769

695

770

# A simple test with some isolation levels

696

771

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

697

772

# this works.

698

773

699

774

700

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;

775

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

701

776

702

777

BEGIN;

703

778

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

720

795

DROP TABLE t1;

721

796

722

797

798

# Test of multi-table-update

799

800

eval create table t1 (n int(10), d int(10)) engine=$engine_type;

801

eval create table t2 (n int(10), d int(10)) engine=$engine_type;

802

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

803

insert into t2 values(1,10),(2,20);

804

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

805

select * from t1;

806

select * from t2;

807

drop table t1,t2;

808

809

723

810

# Testing of IFNULL

724

811

725

if(!$using_temp)

726

{

727

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

812

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

728

813

insert into t1 values(20,null);

729

814

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

730

815

t2.b=t3.a;

734

819

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

735

820

t2.b=t3.a order by 1;

736

821

drop table t1;

737

}

822

823

824

# Test of read_through not existing const_table

825

826

827

eval create table t1 (a varchar(10) not null) engine = $other_engine_type;

828

eval create table t2 (b varchar(10) not null unique) engine=$engine_type;

829

select t1.a from t1,t2 where t1.a=t2.b;

830

drop table t1,t2;

831

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

832

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

833

insert into t1 values (10, 20);

834

insert into t2 values (10, 20);

835

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

836

drop table t1,t2;

738

837

739

838

if ($test_foreign_keys)

740

839

{

742

841

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

743

842

744

843

745

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

746

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;

844

eval CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

845

eval CREATE 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;

747

846

insert into t1 set id=1;

748

847

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

749

848

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

750

849

select * from t1;

751

850

select * from t2;

752

851

drop table t2,t1;

753

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

754

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

852

eval CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

853

eval CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type;

755

854

INSERT INTO t1 VALUES(1);

756

855

INSERT INTO t2 VALUES(1, 1);

757

856

SELECT * from t1;

770

869

771

870

set autocommit=0;

772

871

773

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

774

775

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

776

777

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

872

eval CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

873

874

eval CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

875

876

eval CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=$engine_type;

778

877

779

878

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

780

879

COMMIT;

801

900

# Check update with conflicting key

802

901

803

902

804

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

903

eval CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=$engine_type;

805

904

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

806

905

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

807

906

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

809

908

drop table t1;

810

909

811

910

911

# Test multi update with different join methods

912

913

914

eval CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

915

eval CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

916

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

917

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

918

919

# Full join, without key

920

update t1,t2 set t1.a=t1.a+100;

921

select * from t1;

922

923

# unique key

924

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

925

select * from t1;

926

927

# ref key

928

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

929

select * from t1;

930

931

# Range key (in t1)

932

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

933

select * from t1;

934

select * from t2;

935

936

drop table t1,t2;

937

eval CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

938

eval CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

939

SET AUTOCOMMIT=0;

940

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

941

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

942

ROLLBACK;

943

SELECT * FROM t1;

944

drop table t1,t2;

945

eval create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type;

946

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

947

select distinct parent,child from t1 order by parent;

948

drop table t1;

949

950

812

951

# Test that MySQL priorities clustered indexes

813

952

814

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

815

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

953

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

954

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

816

955

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

817

956

insert into t2 (a) select b from t1;

818

957

insert into t1 (b) select b from t2;

844

983

# Test of UPDATE ... ORDER BY

845

984

846

985

847

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

986

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

848

987

849

988

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

850

989

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

851

990

SELECT * from t1;

852

991

drop table t1;

853

992

854

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

993

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

855

994

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

856

995

SET @tmp=0;

857

996

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

862

1001

drop table t1;

863

1002

864

1003

1004

# Test of multi-table-updates (bug #1980).

1005

1006

1007

eval create table t1 ( c char(8) not null ) engine=$engine_type;

1008

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1009

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1010

1011

alter table t1 add b char(8) not null;

1012

alter table t1 add a char(8) not null;

1013

alter table t1 add primary key (a,b,c);

1014

update t1 set a=c, b=c;

1015

1016

eval create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type;

1017

insert into t2 select * from t1;

1018

1019

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1020

drop table t1,t2;

1021

1022

865

1023

# test autoincrement with TRUNCATE

866

1024

867

1025

868

1026

SET AUTOCOMMIT=1;

869

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

1027

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

870

1028

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

871

1029

truncate table t1;

872

1030

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

880

1038

# Test dictionary handling with spaceand quoting

881

1039

882

1040

883

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

884

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;

1041

eval CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=$engine_type;

1042

eval CREATE 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;

885

1043

#show create table t2;

886

1044

drop table t2,t1;

887

1045

889

1047

# Test of multi updated and foreign keys

890

1048

891

1049

892

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

1050

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

893

1051

insert into `t1`values ( 1 ) ;

894

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;

1052

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

895

1053

insert into `t2`values ( 1 ) ;

896

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;

1054

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

897

1055

insert into `t3`values ( 1 ) ;

898

--error ER_ROW_IS_REFERENCED_2

1056

--error 1451

899

1057

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

900

--error ER_ROW_IS_REFERENCED_2

1058

--error 1451

901

1059

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;

902

--error ER_BAD_FIELD_ERROR

1060

--error 1054

903

1061

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

904

1062

drop table t3,t2,t1;

905

1063

906

1064

907

1065

# test for recursion depth limit

908

1066

909

eval create $temp table t1(

1067

eval create table t1(

910

1068

id int primary key,

911

1069

pid int,

912

1070

index(pid),

913

1071

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

914

1072

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

915

1073

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

916

--error ER_ROW_IS_REFERENCED_2

1074

-- error 1451

917

1075

delete from t1 where id=0;

918

1076

delete from t1 where id=15;

919

1077

delete from t1 where id=0;

926

1084

# Test timestamps

927

1085

928

1086

929

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

930

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

1087

eval CREATE TABLE t1 (col1 int(1))ENGINE=$engine_type;

1088

eval CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

931

1089

(stamp))ENGINE=$engine_type;

932

1090

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

933

insert into t2 values (1, 20020204130000);

934

insert into t2 values (2, 20020204130000);

935

--error ER_INVALID_TIMESTAMP_VALUE # Bad timestamp

936

insert into t2 values (4, 20020204310000);

937

insert into t2 values (5, 20020204230000);

938

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1;

1091

# Note that timestamp 3 is wrong

1092

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

1093

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

1094

'20020204120000' GROUP BY col1;

939

1095

drop table t1,t2;

940

1096

941

1097

942

1098

# Test by Francois MASUREL

943

1099

944

1100

945

if(!$using_temp)

946

{

947

eval create $temp table t1 (

948

`id` int NOT NULL auto_increment,

949

`id_object` int default '0',

950

`id_version` int NOT NULL default '1',

1101

eval CREATE TABLE t1 (

1102

`id` int(10) NOT NULL auto_increment,

1103

`id_object` int(10) default '0',

1104

`id_version` int(10) NOT NULL default '1',

951

1105

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

952

1106

`description` text,

953

1107

PRIMARY KEY (`id`),

957

1111

958

1112

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

959

1113

960

eval create $temp table t2 (

961

`id` int NOT NULL auto_increment,

962

`id_version` int NOT NULL default '1',

1114

eval CREATE TABLE t2 (

1115

`id` int(10) NOT NULL auto_increment,

1116

`id_version` int(10) NOT NULL default '1',

963

1117

PRIMARY KEY (`id`),

964

1118

KEY `id_version` (`id_version`)

965

1119

) ENGINE=$engine_type;

970

1124

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

971

1125

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

972

1126

drop table t1,t2;

973

}

974

1127

975

1128

# Live checksum feature available + enabled

976

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

1129

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

977

1130

# Live checksum feature available + disabled

978

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

1131

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

979

1132

980

1133

# Live checksum feature not available + enabled

981

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

1134

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

982

1135

# Live checksum feature not available + disabled

983

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

1136

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

984

1137

985

1138

# Live checksum feature probably available + enabled

986

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

1139

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

987

1140

# Live checksum feature probably available + disabled

988

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

1141

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

989

1142

990

1143

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

991

1144

insert t2 select * from t1;

993

1146

insert t4 select * from t1;

994

1147

insert t5 select * from t1;

995

1148

insert t6 select * from t1;

1149

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

1150

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

1151

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

996

1152

# #show table status;

997

1153

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

998

1154

1000

1156

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

1001

1157

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

1002

1158

1003

if(!$using_temp)

1004

{

1005

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

1159

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

1006

1160

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

1007

1161

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

1008

1162

drop table t1;

1009

}

1010

1011

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

1012

1013

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

1014

--error ER_CANT_CREATE_TABLE

1163

1164

1165

# Bug#2160: Extra error message for CREATE TABLE LIKE with InnoDB

1166

1167

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

1015

1168

create table t2 like t1;

1016

create table t2 like t1 engine=innodb;

1017

1169

show create table t2;

1018

1170

drop table t1,t2;

1019

1171

1023

1175

# Test of automaticly created foreign keys

1024

1176

1025

1177

1026

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

1027

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

1178

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

1179

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

1028

1180

show create table t1;

1029

1181

show create table t2;

1030

1182

create index id on t2 (id);

1032

1184

create index id2 on t2 (id);

1033

1185

show create table t2;

1034

1186

drop index id2 on t2;

1035

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1187

--error 1025,1025

1036

1188

drop index id on t2;

1037

1189

show create table t2;

1038

1190

drop table t2;

1039

1191

1040

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;

1192

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

1041

1193

show create table t2;

1042

1194

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

1043

1195

show create table t2;

1044

1196

drop table t2;

1045

1197

1046

1198

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

1047

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;

1048

show create table t2;

1049

drop table t2;

1050

1051

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;

1052

show create table t2;

1053

drop table t2;

1054

1055

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;

1056

show create table t2;

1057

drop table t2;

1058

1059

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;

1060

show create table t2;

1061

drop table t2;

1062

1063

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;

1199

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

1200

show create table t2;

1201

drop table t2;

1202

1203

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

1204

show create table t2;

1205

drop table t2;

1206

1207

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

1208

show create table t2;

1209

drop table t2;

1210

1211

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

1212

show create table t2;

1213

drop table t2;

1214

1215

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

1064

1216

show create table t2;

1065

1217

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

1066

1218

show create table t2;

1071

1223

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

1072

1224

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

1073

1225

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

1074

--error ER_CANT_CREATE_TABLE

1075

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;

1226

--error 1005

1227

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

1076

1228

1077

1229

# bug#3749

1078

1230

1079

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;

1231

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

1080

1232

show create table t2;

1081

1233

drop table t2;

1082

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;

1234

eval create 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;

1083

1235

show create table t2;

1084

1236

drop table t2, t1;

1085

1237

}

1095

1247

show status like "binlog_cache_use";

1096

1248

show status like "binlog_cache_disk_use";

1097

1249

1098

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

1250

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

1099

1251

1100

1252

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

1101

1253

# transaction binlog will be flushed to disk...

1124

1276

1125

1277

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

1126

1278

1127

--error ER_DUP_FIELDNAME

1128

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

1129

--error ER_DUP_FIELDNAME

1130

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

1131

--error ER_DUP_FIELDNAME

1132

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

1133

--error ER_DUP_FIELDNAME

1134

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

1135

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

1136

--error ER_DUP_FIELDNAME

1279

--error 1060

1280

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

1281

--error 1060

1282

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

1283

--error 1060

1284

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

1285

--error 1060

1286

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

1287

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

1288

--error 1060

1137

1289

alter table t1 add key (c1,c1);

1138

--error ER_DUP_FIELDNAME

1290

--error 1060

1139

1291

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

1140

--error ER_DUP_FIELDNAME

1292

--error 1060

1141

1293

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

1142

--error ER_DUP_FIELDNAME

1294

--error 1060

1143

1295

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

1144

1296

drop table t1;

1145

1297

1147

1299

# Bug #4082: integer truncation

1148

1300

1149

1301

1150

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

1302

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

1151

1303

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

1152

1304

select distinct concat(a, b) from t1;

1153

1305

drop table t1;

1159

1311

# engines does not fail

1160

1312

1161

1313

1162

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

1163

--error ER_TABLE_CANT_HANDLE_FT

1314

eval CREATE TABLE t1 ( a char(10) ) ENGINE=$engine_type;

1315

--error 1214

1164

1316

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

1165

1317

DROP TABLE t1;

1166

1318

}

1172

1324

1173

1325

1174

1326

--disable_warnings

1175

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

1327

eval CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1176

1328

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

1177

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),

1329

eval CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1178

1330

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;

1179

1331

--enable_warnings

1180

1332

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

1194

1346

select * from t1;

1195

1347

drop table t1;

1196

1348

# Show that it works with permanent tables too.

1197

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

1349

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

1198

1350

insert into t1 values (4711);

1199

1351

truncate t1;

1200

1352

insert into t1 values (42);

1205

1357

# Bug #13025 Server crash during filesort

1206

1358

1207

1359

1208

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;

1360

eval create 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;

1209

1361

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

1210

1362

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

1211

1363

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

1215

1367

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

1216

1368

1217

1369

1218

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

1370

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

1219

1371

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

1220

1372

select min(a) from t1;

1221

1373

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

1227

1379

# range optimizer problem

1228

1380

1229

1381

1230

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

1231

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

1232

--error ER_WARN_DATA_OUT_OF_RANGE

1382

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

1233

1383

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

1234

1384

select * from t1;

1235

1385

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

1317

1467

1318

1468

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

1319

1469

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

1320

# @TODO The below fails because it assumes latin1

1321

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

1322

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

1323

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

1324

#drop table t1;

1325

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

1326

#show create table t1;

1327

#drop table t1;

1328

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

1329

#show create table t1;

1330

#drop table t1;

1331

1332

#eval set storage_engine=$default;

1333

1470

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

1471

create table t1 (v varchar(65530), key(v));

1472

drop table t1;

1473

create table t1 (v varchar(65536));

1474

show create table t1;

1475

drop table t1;

1476

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

1477

show create table t1;

1478

drop table t1;

1479

1480

eval set storage_engine=$default;

1481

1334

1482

# InnoDB specific varchar tests

1335

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

1336

#drop table t1;

1483

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

1484

drop table t1;

1337

1485

1338

1486

1339

1487

# BUG#11039 Wrong key length in min()

1340

1488

1341

1489

1342

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

1490

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

1343

1491

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

1344

1492

select min(a) from t1;

1345

1493

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

1349

1497

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

1350

1498

1351

1499

1352

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;

1500

eval CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1353

1501

insert into t1 (b) values (1);

1354

1502

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

1355

1503

select * from t1;

1361

1509

select * from t1;

1362

1510

drop table t1;

1363

1511

1364

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

1512

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

1365

1513

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

1366

1514

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

1367

1515

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

1376

1524

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

1377

1525

1378

1526

1379

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

1527

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

1380

1528

insert into t1 (val) values (1);

1381

1529

update t1 set a=2 where a=1;

1382

1530

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

1392

1540

1393

1541

1394

1542

--disable_warnings

1395

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

1543

eval CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=$engine_type;

1396

1544

--enable_warnings

1397

1545

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

1398

1546

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

1399

1547

SELECT GRADE FROM t1 WHERE GRADE= 151;

1400

1548

DROP TABLE t1;

1401

1549

1550

1551

# Bug #12340 multitable delete deletes only one record

1552

1553

eval create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type;

1554

eval create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type;

1555

insert into t2 values ('aa','cc');

1556

insert into t1 values ('aa','bb'),('aa','cc');

1557

delete t1 from t1,t2 where f1=f3 and f4='cc';

1558

select * from t1;

1559

drop table t1,t2;

1560

1402

1561

if ($test_foreign_keys)

1403

1562

{

1404

1563

1406

1565

# (bug #11946)

1407

1566

1408

1567

1409

eval create $temp table t1 (

1568

eval CREATE TABLE t1 (

1410

1569

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1411

1570

) ENGINE=$engine_type;

1412

1571

1413

eval create $temp table t2 (

1572

eval CREATE TABLE t2 (

1414

1573

id INTEGER NOT NULL,

1415

1574

FOREIGN KEY (id) REFERENCES t1 (id)

1416

1575

) ENGINE=$engine_type;

1430

1589

DROP TABLE t2, t1;

1431

1590

1432

1591

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

1433

eval create $temp table t1

1592

eval CREATE TABLE t1

1434

1593

(

1435

1594

id INT PRIMARY KEY

1436

1595

) ENGINE=$engine_type;

1437

1596

1438

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1597

--error 1005,1005

1439

1598

eval CREATE TEMPORARY TABLE t2

1440

1599

(

1441

1600

id INT NOT NULL PRIMARY KEY,

1457

1616

1458

1617

1459

1618

# prefix index

1460

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

1619

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

1461

1620

character set = latin1 engine = $engine_type;

1462

1621

1463

1622

# normal indexes

1464

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

1465

character set = latin1 engine = $engine_type;

1466

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

1467

character set = latin1 engine = $engine_type;

1468

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

1469

character set = latin1 engine = $engine_type;

1470

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

1471

character set = latin1 engine = $engine_type;

1472

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

1473

character set = latin1 engine = $engine_type;

1474

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

1475

character set = latin1 engine = $engine_type;

1476

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

1623

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

1624

character set = latin1 engine = $engine_type;

1625

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

1626

character set = latin1 engine = $engine_type;

1627

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

1628

character set = latin1 engine = $engine_type;

1629

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

1630

character set = latin1 engine = $engine_type;

1631

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

1632

character set = latin1 engine = $engine_type;

1633

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

1634

character set = latin1 engine = $engine_type;

1635

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

1477

1636

character set = latin1 engine = $engine_type;

1478

1637

1479

1638

1480

1639

# multi-column indexes are allowed to be longer

1481

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

1640

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

1482

1641

character set = latin1 engine = $engine_type;

1483

1642

1484

1643

show create table t9;

1486

1645

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

1487

1646

1488

1647

# these should have their index length trimmed

1489

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

1490

character set = latin1 engine = $engine_type;

1491

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

1492

character set = latin1 engine = $engine_type;

1493

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

1494

character set = latin1 engine = $engine_type;

1495

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

1648

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

1649

character set = latin1 engine = $engine_type;

1650

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

1651

character set = latin1 engine = $engine_type;

1652

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

1653

character set = latin1 engine = $engine_type;

1654

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

1496

1655

character set = latin1 engine = $engine_type;

1497

1656

1498

1657

show create table t1;

1509

1668

{

1510

1669

1511

1670

# these should be refused

1512

--error ER_TOO_LONG_KEY

1513

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

1514

character set = latin1 engine = $engine_type;

1515

--error ER_TOO_LONG_KEY

1516

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

1517

character set = latin1 engine = $engine_type;

1518

--error ER_TOO_LONG_KEY

1519

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

1520

character set = latin1 engine = $engine_type;

1521

--error ER_TOO_LONG_KEY

1522

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

1671

--error 1071

1672

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

1673

character set = latin1 engine = $engine_type;

1674

--error 1071

1675

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

1676

character set = latin1 engine = $engine_type;

1677

--error 1071

1678

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

1679

character set = latin1 engine = $engine_type;

1680

--error 1071

1681

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

1523

1682

character set = latin1 engine = $engine_type;

1524

1683

1525

1684

}

1530

1689

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

1531

1690

1532

1691

1533

eval create $temp table t1

1692

eval CREATE TABLE t1

1534

1693

(

1535

1694

id INT PRIMARY KEY

1536

1695

) ENGINE=$engine_type;

1537

1696

1538

eval create $temp table t2

1697

eval CREATE TABLE t2

1539

1698

(

1540

1699

v INT,

1541

1700

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

1542

1701

) ENGINE=$engine_type;

1543

1702

1544

--error ER_NO_REFERENCED_ROW_2

1703

--error 1452

1545

1704

INSERT INTO t2 VALUES(2);

1546

1705

1547

1706

INSERT INTO t1 VALUES(1);

1548

1707

INSERT INTO t2 VALUES(1);

1549

1708

1550

--error ER_ROW_IS_REFERENCED_2

1709

--error 1451

1551

1710

DELETE FROM t1 WHERE id = 1;

1552

1711

1553

--error ER_ROW_IS_REFERENCED

1712

--error 1217

1554

1713

DROP TABLE t1;

1555

1714

1556

1715

SET FOREIGN_KEY_CHECKS=0;

1557

1716

DROP TABLE t1;

1558

1717

SET FOREIGN_KEY_CHECKS=1;

1559

1718

1560

--error ER_NO_REFERENCED_ROW_2

1719

--error 1452

1561

1720

INSERT INTO t2 VALUES(3);

1562

1721

1563

1722

DROP TABLE t2;

1564

1723

}

1565

1724

# End of FOREIGN tests

1566

1725

1726

if ($test_transactions)

1727

{

1728

1729

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

1730

1731

connect (a,localhost,root,,);

1732

connect (b,localhost,root,,);

1733

connection a;

1734

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

1735

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

1736

set autocommit=0;

1737

checksum table t1;

1738

connection b;

1739

insert into t1 values(3);

1740

connection a;

1741

1742

# Here checksum should not see insert

1743

1744

checksum table t1;

1745

connection a;

1746

commit;

1747

checksum table t1;

1748

commit;

1749

drop table t1;

1750

1751

# autocommit = 1

1752

1753

connection a;

1754

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

1755

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

1756

set autocommit=1;

1757

checksum table t1;

1758

connection b;

1759

set autocommit=1;

1760

insert into t1 values(3);

1761

connection a;

1762

1763

# Here checksum sees insert

1764

1765

checksum table t1;

1766

drop table t1;

1767

1768

connection default;

1769

disconnect a;

1770

disconnect b;

1771

}

1772

1567

1773

# tests for bugs #9802 and #13778

1568

1774

1569

1775

if ($test_foreign_keys)

1571

1777

# test that FKs between invalid types are not accepted

1572

1778

1573

1779

set foreign_key_checks=0;

1574

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

1780

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

1575

1781

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

1576

--error ER_CANT_CREATE_TABLE

1577

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

1782

-- error 1005

1783

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

1578

1784

set foreign_key_checks=1;

1579

1785

drop table t2;

1580

1786

1582

1788

# when f_k_c is 0

1583

1789

1584

1790

set foreign_key_checks=0;

1585

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

1791

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

1586

1792

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

1587

--error ER_CANT_CREATE_TABLE

1588

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

1793

-- error 1005

1794

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

1589

1795

set foreign_key_checks=1;

1590

1796

drop table t1;

1591

1797

1592

1798

# test that invalid datatype conversions with ALTER are not allowed

1593

1799

1594

1800

set foreign_key_checks=0;

1595

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

1596

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

1597

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1801

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

1802

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

1803

-- error 1025,1025

1598

1804

alter table t1 modify column a int;

1599

1805

set foreign_key_checks=1;

1600

1806

drop table t2,t1;

1602

1808

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

1603

1809

1604

1810

set foreign_key_checks=0;

1605

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

1606

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

1811

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

1812

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

1607

1813

alter table t1 convert to character set utf8;

1608

1814

set foreign_key_checks=1;

1609

1815

drop table t2,t1;

1611

1817

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

1612

1818

1613

1819

set foreign_key_checks=0;

1614

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

1615

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

1820

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

1821

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

1616

1822

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

1617

--error ER_ERROR_ON_RENAME

1823

-- error 1025

1618

1824

rename table t3 to t1;

1619

1825

set foreign_key_checks=1;

1620

1826

drop table t2,t3;

1621

1827

1622

1828

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

1623

1829

1624

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

1625

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

1626

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

1627

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

1830

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

1831

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

1832

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

1833

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

1628

1834

1629

1835

insert into t1 values(1);

1630

1836

insert into t3 values(1);

1631

--error ER_NO_REFERENCED_ROW_2

1837

-- error 1452

1632

1838

insert into t2 values(2);

1633

--error ER_NO_REFERENCED_ROW_2

1839

-- error 1452

1634

1840

insert into t4 values(2);

1635

1841

insert into t2 values(1);

1636

1842

insert into t4 values(1);

1637

--error ER_ROW_IS_REFERENCED_2

1843

-- error 1451

1638

1844

update t1 set a=2;

1639

--error ER_NO_REFERENCED_ROW_2

1845

-- error 1452

1640

1846

update t2 set a=2;

1641

--error ER_ROW_IS_REFERENCED_2

1847

-- error 1451

1642

1848

update t3 set a=2;

1643

--error ER_NO_REFERENCED_ROW_2

1849

-- error 1452

1644

1850

update t4 set a=2;

1645

--error ER_ROW_IS_REFERENCED_2

1851

-- error 1451

1646

1852

truncate t1;

1647

--error ER_ROW_IS_REFERENCED_2

1853

-- error 1451

1648

1854

truncate t3;

1649

1855

truncate t2;

1650

1856

truncate t4;

1665

1871

1666

1872

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

1667

1873

1668

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

1874

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

1669

1875

b varchar(255) character set utf8,

1670

1876

c varchar(255) character set utf8,

1671

1877

d varchar(255) character set utf8,

1672

1878

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

1673

1879

drop table t1;

1674

1880

--error ER_TOO_LONG_KEY

1675

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

1881

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

1676

1882

b varchar(255) character set utf8,

1677

1883

c varchar(255) character set utf8,

1678

1884

d varchar(255) character set utf8,

1682

1888

1683

1889

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

1684

1890

1685

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

1686

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

1687

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

1688

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

1891

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

1892

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

1893

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

1894

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

1689

1895

1690

1896

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

1691

--error ER_DUP_ENTRY

1897

-- error ER_DUP_ENTRY

1692

1898

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

1693

1899

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

1694

--error ER_DUP_ENTRY

1900

-- error ER_DUP_ENTRY

1695

1901

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

1696

1902

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

1697

--error ER_DUP_ENTRY

1903

-- error ER_DUP_ENTRY

1698

1904

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

1699

1905

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

1700

1906

select hex(s1) from t1;

1706

1912

1707

1913

if (test_foreign_keys)

1708

1914

{

1709

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

1710

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

1915

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

1916

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

1711

1917

1712

1918

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

1713

--error ER_NO_REFERENCED_ROW_2

1919

-- error 1452

1714

1920

insert into t2 values(0x42);

1715

1921

insert into t2 values(0x41);

1716

1922

select hex(s1) from t2;

1717

1923

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

1718

1924

select hex(s1) from t2;

1719

--error ER_ROW_IS_REFERENCED_2

1925

-- error 1451

1720

1926

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

1721

--error ER_ROW_IS_REFERENCED_2

1927

-- error 1451

1722

1928

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

1723

--error ER_ROW_IS_REFERENCED_2

1929

-- error 1451

1724

1930

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

1725

1931

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

1726

1932

select hex(s1) from t2;

1728

1934

select hex(s1) from t2;

1729

1935

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

1730

1936

select hex(s1) from t2;

1731

--error ER_ROW_IS_REFERENCED_2

1937

-- error 1451

1732

1938

delete from t1 where a=1;

1733

1939

delete from t1 where a=2;

1734

1940

update t2 set s1=0x4120;

1735

--error ER_ROW_IS_REFERENCED_2

1941

-- error 1451

1736

1942

delete from t1;

1737

1943

delete from t1 where a!=3;

1738

1944

select a,hex(s1) from t1;

1740

1946

1741

1947

drop table t2,t1;

1742

1948

1743

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

1744

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

1949

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

1950

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

1745

1951

1746

1952

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

1747

1953

insert into t2 values(0x41);

1751

1957

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

1752

1958

select hex(s1) from t2;

1753

1959

delete from t1 where a=1;

1754

--error ER_ROW_IS_REFERENCED_2

1960

-- error 1451

1755

1961

delete from t1 where a=2;

1756

1962

select a,hex(s1) from t1;

1757

1963

select hex(s1) from t2;

1765

1971

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

1766

1972

# generated foreign key identifier. (Bug #16387)

1767

1973

1768

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

1769

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

1974

eval CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type;

1975

eval CREATE TABLE t2(a INT) ENGINE=$engine_type;

1770

1976

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

1771

1977

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1772

1978

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

1773

1979

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1774

SHOW create $temp table t2;

1980

SHOW CREATE TABLE t2;

1775

1981

DROP TABLE t2,t1;

1776

1982

}

1777

1983

1984

1985

# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing

1986

1987

1988

connect (a,localhost,root,,);

1989

connect (b,localhost,root,,);

1990

connection a;

1991

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

1992

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

1993

commit;

1994

connection b;

1995

set autocommit = 0;

1996

update t1 set b = 5 where a = 2;

1997

connection a;

1998

delimiter |;

1999

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

2000

delimiter ;|

2001

set autocommit = 0;

2002

connection a;

2003

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

2004

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

2005

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

2006

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

2007

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

2008

connection b;

2009

commit;

2010

connection a;

2011

commit;

2012

drop trigger t1t;

2013

drop table t1;

2014

disconnect a;

2015

disconnect b;

2016

2017

# Another trigger test

2018

2019

connect (a,localhost,root,,);

2020

connect (b,localhost,root,,);

2021

connection a;

2022

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

2023

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

2024

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

2025

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

2026

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

2027

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

2028

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

2029

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

2030

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

2031

insert into t3(a) values (5),(7),(8);

2032

insert into t4(a) values (5),(7),(8);

2033

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2034

2035

delimiter |;

2036

create trigger t1t before insert on t1 for each row begin

2037

INSERT INTO t2 SET a = NEW.a;

2038

end |

2039

2040

create trigger t2t before insert on t2 for each row begin

2041

DELETE FROM t3 WHERE a = NEW.a;

2042

end |

2043

2044

create trigger t3t before delete on t3 for each row begin

2045

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2046

end |

2047

2048

create trigger t4t before update on t4 for each row begin

2049

UPDATE t5 SET b = b + 1 where a = NEW.a;

2050

end |

2051

delimiter ;|

2052

commit;

2053

set autocommit = 0;

2054

update t1 set b = b + 5 where a = 1;

2055

update t2 set b = b + 5 where a = 1;

2056

update t3 set b = b + 5 where a = 1;

2057

update t4 set b = b + 5 where a = 1;

2058

insert into t5(a) values(20);

2059

connection b;

2060

set autocommit = 0;

2061

insert into t1(a) values(7);

2062

insert into t2(a) values(8);

2063

delete from t2 where a = 3;

2064

update t4 set b = b + 1 where a = 3;

2065

commit;

2066

drop trigger t1t;

2067

drop trigger t2t;

2068

drop trigger t3t;

2069

drop trigger t4t;

2070

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

2071

connection default;

2072

disconnect a;

2073

disconnect b;

2074

1778

2075

if ($test_foreign_keys)

1779

2076

{

1780

2077

1782

2079

# error message (bug #9680)

1783

2080

1784

2081

1785

eval create $temp table t1 (

2082

eval CREATE TABLE t1 (

1786

2083

field1 varchar(8) NOT NULL DEFAULT '',

1787

2084

field2 varchar(8) NOT NULL DEFAULT '',

1788

2085

PRIMARY KEY (field1, field2)

1789

2086

) ENGINE=$engine_type;

1790

2087

1791

eval create $temp table t2 (

2088

eval CREATE TABLE t2 (

1792

2089

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

1793

2090

FOREIGN KEY (field1) REFERENCES t1 (field1)

1794

2091

ON DELETE CASCADE ON UPDATE CASCADE

1809

2106

1810

2107

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

1811

2108

1812

eval create $temp table t1 (

2109

eval create table t1 (

1813

2110

c1 bigint not null,

1814

2111

c2 bigint not null,

1815

2112

primary key (c1),

1816

2113

unique key (c2)

1817

2114

) engine=$engine_type;

1818

2115

1819

eval create $temp table t2 (

2116

eval create table t2 (

1820

2117

c1 bigint not null,

1821

2118

primary key (c1)

1822

2119

) engine=$engine_type;

1836

2133

# Bug #14360: problem with intervals

1837

2134

1838

2135

1839

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

1840

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

2136

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

2137

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

1841

2138

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

1842

2139

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

1843

2140

select * from t1, t2

1844

2141

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

1845

2142

drop table t1, t2;

1846

2143

1847

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

2144

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

1848

2145

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

1849

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

2146

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

1850

2147

primary key(id)) engine=$engine_type;

1851

2148

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

1852

2149

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

1853

2150

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

1854

2151

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

1855

2152

commit;

2153

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2154

WHERE mm.id IS NULL;

1856

2155

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

1857

2156

where mm.id is null lock in share mode;

1858

2157

drop table t1,t2;

1861

2160

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

1862

2161

# update (because READ COMMITTED isolation level)

1863

2162

1864

if(!$using_temp)

1865

{

2163

1866

2164

connect (a,localhost,root,,);

1867

2165

connect (b,localhost,root,,);

1868

2166

connection a;

1869

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

2167

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

1870

2168

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

1871

2169

commit;

1872

2170

set autocommit = 0;

1887

2185

connection default;

1888

2186

disconnect a;

1889

2187

disconnect b;

1890

}

2188

1891

2189

if ($test_transactions)

1892

2190

{

1893

2191

1898

2196

connect (a,localhost,root,,);

1899

2197

connect (b,localhost,root,,);

1900

2198

connection a;

1901

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

2199

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

1902

2200

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

1903

2201

commit;

1904

2202

set autocommit = 0;

1909

2207

1910

2208

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

1911

2209

1912

--error ER_XXXLOCK_WAIT_TIMEOUTTTT

2210

--error 1205

1913

2211

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

1914

2212

1915

2213

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

1916

2214

1917

--error ER_LOCK_WAIT_TIMEOUT

2215

--error 1205

1918

2216

connection a;

1919

2217

commit;

1920

2218

connection b;

1934

2232

connect (a,localhost,root,,);

1935

2233

connect (b,localhost,root,,);

1936

2234

connection a;

1937

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

2235

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

1938

2236

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

1939

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

2237

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

1940

2238

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

1941

2239

commit;

1942

2240

set autocommit = 0;

1946

2244

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1947

2245

insert into t1 select * from t2;

1948

2246

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

1949

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

2247

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

1950

2248

select * from t2;

1951

2249

commit;

1952

2250

connection a;

1990

2288

connect (j,localhost,root,,);

1991

2289

eval SET SESSION STORAGE_ENGINE = $engine_type;

1992

2290

connection a;

1993

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

2291

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

1994

2292

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

1995

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

2293

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

1996

2294

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

1997

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

2295

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

1998

2296

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

1999

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

2297

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

2000

2298

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

2001

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

2299

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

2002

2300

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

2003

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

2301

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

2004

2302

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

2005

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

2303

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

2006

2304

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

2007

2305

commit;

2008

2306

set autocommit = 0;

2021

2319

set autocommit = 0;

2022

2320

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2023

2321

--send

2024

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

2322

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

2025

2323

connection e;

2026

2324

set autocommit = 0;

2027

2325

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2036

2334

set autocommit = 0;

2037

2335

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2038

2336

--send

2039

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

2337

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

2040

2338

connection h;

2041

2339

set autocommit = 0;

2042

2340

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2051

2349

set autocommit = 0;

2052

2350

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2053

2351

--send

2054

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

2352

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

2055

2353

2056

2354

connection b;

2057

--error ER_LOCK_WAIT_TIMEOUT

2355

--error 1205

2058

2356

reap;

2059

2357

2060

2358

connection c;

2061

--error ER_LOCK_WAIT_TIMEOUT

2359

--error 1205

2062

2360

reap;

2063

2361

2064

2362

connection d;

2065

--error ER_LOCK_WAIT_TIMEOUT

2363

--error 1205

2066

2364

reap;

2067

2365

2068

2366

connection e;

2069

--error ER_LOCK_WAIT_TIMEOUT

2367

--error 1205

2070

2368

reap;

2071

2369

2072

2370

connection f;

2073

--error ER_LOCK_WAIT_TIMEOUT

2371

--error 1205

2074

2372

reap;

2075

2373

2076

2374

connection g;

2077

--error ER_LOCK_WAIT_TIMEOUT

2375

--error 1205

2078

2376

reap;

2079

2377

2080

2378

connection h;

2081

--error ER_LOCK_WAIT_TIMEOUT

2379

--error 1205

2082

2380

reap;

2083

2381

2084

2382

connection i;

2085

--error ER_LOCK_WAIT_TIMEOUT

2383

--error 1205

2086

2384

reap;

2087

2385

2088

2386

connection j;

2089

--error ER_LOCK_WAIT_TIMEOUT

2387

--error 1205

2090

2388

reap;

2091

2389

2092

2390

connection a;

2110

2408

if (test_foreign_keys)

2111

2409

{

2112

2410

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

2113

--error ER_CANT_CREATE_TABLE

2114

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

2411

--error 1005

2412

eval CREATE TABLE t1 (DB_ROW_ID int) engine=$engine_type;

2115

2413

2116

2414

2117

2415

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

2118

2416

2119

2417

2120

eval create $temp table t1 (

2418

eval CREATE TABLE t1 (

2121

2419

a BIGINT(20) NOT NULL,

2122

2420

PRIMARY KEY (a)

2123

2421

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2124

2422

2125

eval create $temp table t2 (

2423

eval CREATE TABLE t2 (

2126

2424

a BIGINT(20) NOT NULL,

2127

2425

b VARCHAR(128) NOT NULL,

2128

2426

c TEXT NOT NULL,

2154

2452

# Bug #15680 (SPATIAL key in innodb)

2155

2453

2156

2454

--error ER_TABLE_CANT_HANDLE_SPKEYS

2157

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

2455

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

2158

2456

}

2159

2457

2160

2458

2161

2459

# Test optimize on table with open transaction

2162

2460

2163

2461

2164

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

2462

eval CREATE TABLE t1 ( a int ) ENGINE=$engine_type;

2165

2463

BEGIN;

2166

2464

INSERT INTO t1 VALUES (1);

2167

COMMIT;

2168

eval ALTER TABLE t1 ENGINE=$engine_type;

2465

OPTIMIZE TABLE t1;

2169

2466

DROP TABLE t1;

2170

2467

2171

2468

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

Older »