~drizzle-trunk/drizzle/development

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

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

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)

) engine=innodb;

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

update t1 set id=1024 where id=1009;

select * from t1;

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

select * from t1;

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

select * from t1 where parent_id=102;

--replace_column 9 #

explain select level from t1 where level=1;

--replace_column 9 #

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

--replace_column 9 #

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

select level,id from t1 where level=1;

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

optimize table t1;

--replace_column 7 #

show keys from t1;

drop table t1;

# Test replace

CREATE TABLE t1 (

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

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

PRIMARY KEY (gesuchnr,benutzer_id)

) engine=innodb;

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

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

select * from t1;

drop table t1;

# test delete using hidden_primary_key

create table t1 (a int) engine=innodb;

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

optimize table t1;

delete from t1 where a = 1;

select * from t1;

100

check table t1;

101

drop table t1;

102

103

create table t1 (a int,b varchar(20)) engine=innodb;

104

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

105

delete from t1 where a = 1;

106

select * from t1;

107

create index skr on t1 (a);

108

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

109

analyze table t1;

110

--replace_column 7 #

111

show keys from t1;

112

drop table t1;

113

114

115

# Test of reading on secondary key with may be null

116

117

create table t1 (a int,b varchar(20),key(a)) engine=innodb;

118

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

119

select * from t1 where a = 1;

120

drop table t1;

121

122

123

# Test rollback

124

125

126

create table t1 (n int not null primary key) engine=innodb;

127

set autocommit=0;

128

insert into t1 values (4);

129

rollback;

130

select n, "after rollback" from t1;

131

insert into t1 values (4);

132

commit;

133

select n, "after commit" from t1;

134

commit;

135

insert into t1 values (5);

136

-- error ER_DUP_ENTRY

137

insert into t1 values (4);

138

commit;

139

select n, "after commit" from t1;

140

set autocommit=1;

141

insert into t1 values (6);

142

-- error ER_DUP_ENTRY

143

insert into t1 values (4);

144

select n from t1;

145

set autocommit=0;

146

147

# savepoints

148

149

begin;

150

savepoint `my_savepoint`;

151

insert into t1 values (7);

152

savepoint `savept2`;

153

insert into t1 values (3);

154

select n from t1;

155

savepoint savept3;

156

rollback to savepoint savept2;

157

--error 1305

158

rollback to savepoint savept3;

159

rollback to savepoint savept2;

160

release savepoint `my_savepoint`;

161

select n from t1;

162

-- error 1305

163

rollback to savepoint `my_savepoint`;

164

--error 1305

165

rollback to savepoint savept2;

166

insert into t1 values (8);

167

savepoint sv;

168

commit;

169

savepoint sv;

170

set autocommit=1;

171

# nop

172

rollback;

173

drop table t1;

174

175

176

# Test for commit and FLUSH TABLES WITH READ LOCK

177

178

179

create table t1 (n int not null primary key) engine=innodb;

180

start transaction;

181

insert into t1 values (4);

182

flush tables with read lock;

183

184

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

185

#--error 1223;

186

commit;

187

unlock tables;

188

commit;

189

select * from t1;

190

drop table t1;

191

192

193

# Testing transactions

194

195

196

create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;

197

begin;

198

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

199

select id as afterbegin_id,nom as afterbegin_nom from t1;

200

rollback;

201

select id as afterrollback_id,nom as afterrollback_nom from t1;

202

set autocommit=0;

203

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

204

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

205

rollback;

206

select id as afterrollback_id,nom as afterrollback_nom from t1;

207

set autocommit=1;

208

drop table t1;

209

210

211

# Simple not autocommit test

212

213

214

CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;

215

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

216

-- error ER_DUP_ENTRY

217

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

218

delete from t1;

219

delete from t1 where id = 'pippo';

220

select * from t1;

221

222

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

223

set autocommit=0;

224

delete from t1;

225

rollback;

226

select * from t1;

227

delete from t1;

228

commit;

229

select * from t1;

230

drop table t1;

231

232

233

# Test of active transactions

234

235

236

create table t1 (a integer) engine=innodb;

237

start transaction;

238

rename table t1 to t2;

239

create table t1 (b integer) engine=innodb;

240

insert into t1 values (1);

241

rollback;

242

drop table t1;

243

rename table t2 to t1;

244

drop table t1;

245

set autocommit=1;

246

247

248

# The following simple tests failed at some point

249

250

251

CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;

252

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

253

select * from t1;

254

drop table t1;

255

256

CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;

257

set autocommit=0;

258

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

259

COMMIT;

260

SELECT * FROM t1;

261

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

262

drop table t1;

263

set autocommit=1;

264

265

266

# Test when reading on part of unique key

267

268

CREATE TABLE t1 (

269

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

270

name varchar(100),

271

phone varchar(100),

272

ref_email varchar(100) DEFAULT '' NOT NULL,

273

detail varchar(200),

274

PRIMARY KEY (user_id,ref_email)

275

)engine=innodb;

276

277

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

278

select * from t1 where user_id=10292;

279

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

280

select * from t1 where user_id=10292;

281

select * from t1 where user_id>=10292;

282

select * from t1 where user_id>10292;

283

select * from t1 where user_id<10292;

284

drop table t1;

285

286

287

# Test that keys are created in right order

288

289

290

CREATE TABLE t1 (a int not null, b int not null,c int not null,

291

key(a),primary key(a,b), unique(c),key(a),unique(b));

292

--replace_column 7 #

293

show index from t1;

294

drop table t1;

295

296

297

# Test of ALTER TABLE and innodb tables

298

299

300

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));

301

alter table t1 engine=innodb;

302

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

303

select * from t1;

304

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

305

select * from t1;

306

alter table t1 add co3 int not null;

307

select * from t1;

308

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

309

select * from t1;

310

drop table t1;

311

312

313

# INSERT INTO innodb tables

314

315

316

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

317

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

318

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

319

select * from t1;

320

insert into t2 select * from t1;

321

select * from t2;

322

delete from t1 where b = 3;

323

select * from t1;

324

insert into t1 select * from t2;

325

select * from t1;

326

select * from t2;

327

drop table t1,t2;

328

329

330

# ORDER BY on not primary key

331

332

333

CREATE TABLE t1 (

334

user_name varchar(12),

335

password text,

336

subscribed char(1),

337

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

338

quota bigint(20),

339

weight double,

340

access_date date,

341

access_time time,

342

approved datetime,

343

dummy_primary_key int(11) NOT NULL auto_increment,

344

PRIMARY KEY (dummy_primary_key)

345

) ENGINE=innodb;

346

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

347

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

348

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

349

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

350

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

351

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

352

drop table t1;

353

354

355

# Testing of tables without primary keys

356

357

358

CREATE TABLE t1 (

359

id int(11) NOT NULL auto_increment,

360

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

361

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

362

KEY (id),

363

KEY parent_id (parent_id),

364

KEY level (level)

365

) engine=innodb;

366

367

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

368

update t1 set parent_id=parent_id+100;

369

select * from t1 where parent_id=102;

370

update t1 set id=id+1000;

371

update t1 set id=1024 where id=1009;

372

select * from t1;

373

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

374

select * from t1;

375

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

376

select * from t1 where parent_id=102;

377

--replace_column 9 #

378

explain select level from t1 where level=1;

379

select level,id from t1 where level=1;

380

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

381

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

382

delete from t1 where level=1;

383

select * from t1;

384

drop table t1;

385

386

387

# Test of index only reads

388

389

CREATE TABLE t1 (

390

sca_code char(6) NOT NULL,

391

cat_code char(6) NOT NULL,

392

sca_desc varchar(50),

393

lan_code char(2) NOT NULL,

394

sca_pic varchar(100),

395

sca_sdesc varchar(50),

396

sca_sch_desc varchar(16),

397

PRIMARY KEY (sca_code, cat_code, lan_code),

398

INDEX sca_pic (sca_pic)

399

) engine = innodb ;

400

401

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

402

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

403

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

404

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

405

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

406

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

407

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

408

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

409

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

410

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

411

select sca_pic from t1 where sca_pic is null;

412

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

413

delete from t1 where sca_code='pd';

414

drop table t1;

415

416

417

# Test of opening table twice and timestamps

418

419

set @a:=now();

420

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

421

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

422

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

423

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

424

update t1 set a=5 where a=1;

425

select a from t1;

426

drop table t1;

427

428

429

# Test with variable length primary key

430

431

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

432

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

433

select * from t1 order by b desc;

434

optimize table t1;

435

--replace_column 7 #

436

show keys from t1;

437

drop table t1;

438

439

440

# Test of create index with NULL columns

441

442

create table t1 (i int, j int ) ENGINE=innodb;

443

insert into t1 values (1,2);

444

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

445

create index ax1 on t1 (i,j);

446

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

447

drop table t1;

448

449

450

# Test min-max optimization

451

452

453

CREATE TABLE t1 (

454

a int3 unsigned NOT NULL,

455

b int1 unsigned NOT NULL,

456

UNIQUE (a, b)

457

) ENGINE = innodb;

458

459

INSERT INTO t1 VALUES (1, 1);

460

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

461

drop table t1;

462

463

464

# Test INSERT DELAYED

465

466

467

CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;

468

# Can't test this in 3.23

469

# INSERT DELAYED INTO t1 VALUES (1);

470

INSERT INTO t1 VALUES (1);

471

SELECT * FROM t1;

472

DROP TABLE t1;

473

474

475

476

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

477

478

479

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 = innodb;

480

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

481

--replace_column 9 #

482

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

483

drop table t1;

484

485

486

# Test lock tables

487

488

489

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=innodb;

490

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

491

LOCK TABLES t1 WRITE;

492

--error ER_DUP_ENTRY

493

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

494

select id from t1;

495

select id from t1;

496

UNLOCK TABLES;

497

DROP TABLE t1;

498

499

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=innodb;

500

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

501

LOCK TABLES t1 WRITE;

502

begin;

503

--error ER_DUP_ENTRY

504

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

505

select id from t1;

506

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

507

commit;

508

select id,id3 from t1;

509

UNLOCK TABLES;

510

DROP TABLE t1;

511

512

513

# Test prefix key

514

515

create table t1 (a char(20), unique (a(5))) engine=innodb;

516

drop table t1;

517

create table t1 (a char(20), index (a(5))) engine=innodb;

518

show create table t1;

519

drop table t1;

520

521

522

# Test using temporary table and auto_increment

523

524

525

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

526

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

527

delete from t1 where a=3;

528

insert into t1 values (NULL);

529

select * from t1;

530

alter table t1 add b int;

531

select * from t1;

532

drop table t1;

533

534

#Slashdot bug

535

create table t1

536

(

537

id int auto_increment primary key,

538

name varchar(32) not null,

539

value text not null,

540

uid int not null,

541

unique key(name,uid)

542

) engine=innodb;

543

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

544

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

545

set insert_id=5;

546

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

547

delete from t1 where uid=102;

548

set insert_id=5;

549

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

550

set insert_id=6;

551

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

552

select * from t1;

553

drop table t1;

554

555

556

# Test DROP DATABASE

557

558

559

create database mysqltest;

560

create table mysqltest.t1 (a int not null) engine= innodb;

561

insert into mysqltest.t1 values(1);

562

create table mysqltest.t2 (a int not null) engine= myisam;

563

insert into mysqltest.t2 values(1);

564

create table mysqltest.t3 (a int not null) engine= heap;

565

insert into mysqltest.t3 values(1);

566

commit;

567

drop database mysqltest;

568

# Don't check error message

569

--error 1049

570

show tables from mysqltest;

571

572

573

# Test truncate table with and without auto_commit

574

575

576

set autocommit=0;

577

create table t1 (a int not null) engine= innodb;

578

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

579

truncate table t1;

580

commit;

581

truncate table t1;

582

truncate table t1;

583

select * from t1;

584

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

585

delete from t1;

586

select * from t1;

587

commit;

588

drop table t1;

589

set autocommit=1;

590

591

create table t1 (a int not null) engine= innodb;

592

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

593

truncate table t1;

594

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

595

select * from t1;

596

truncate table t1;

597

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

598

delete from t1;

599

select * from t1;

600

drop table t1;

601

602

603

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

604

605

606

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

607

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

608

--replace_column 9 #

609

explain select * from t1 order by a;

610

--replace_column 9 #

611

explain select * from t1 order by b;

612

--replace_column 9 #

613

explain select * from t1 order by c;

614

--replace_column 9 #

615

explain select a from t1 order by a;

616

--replace_column 9 #

617

explain select b from t1 order by b;

618

--replace_column 9 #

619

explain select a,b from t1 order by b;

620

--replace_column 9 #

621

explain select a,b from t1;

622

--replace_column 9 #

623

explain select a,b,c from t1;

624

drop table t1;

625

626

627

# Check describe

628

629

630

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

631

desc t1;

632

drop table t1;

633

634

635

# Test of multi-table-delete

636

637

638

CREATE TABLE t1 (

639

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

640

cname char(15) NOT NULL default '',

641

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

642

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

643

last_mod_date timestamp NOT NULL,

644

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

645

last_app_date timestamp NOT NULL,

646

last_app_id smallint(6) default '-1',

647

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

648

assigned_scps int(11) default '0',

649

status tinyint(4) default '0'

650

) ENGINE=InnoDB;

651

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

652

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

653

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

654

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

655

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

656

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

657

CREATE TABLE t2 (

658

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

659

cname char(15) NOT NULL default '',

660

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

661

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

662

last_mod_date timestamp NOT NULL,

663

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

664

last_app_date timestamp NOT NULL,

665

last_app_id smallint(6) default '-1',

666

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

667

assigned_scps int(11) default '0',

668

status tinyint(4) default '0'

669

) ENGINE=InnoDB;

670

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

671

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

672

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

673

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

674

select * from t1;

675

select * from t2;

676

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

677

select * from t1;

678

select * from t2;

679

select * from t2;

680

drop table t1,t2;

681

682

683

# A simple test with some isolation levels

684

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

685

# this works.

686

687

688

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

689

690

BEGIN;

691

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

692

SELECT @@tx_isolation,@@global.tx_isolation;

693

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

694

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

695

COMMIT;

696

697

BEGIN;

698

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

699

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

700

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

701

COMMIT;

702

703

BEGIN;

704

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

705

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

706

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

707

COMMIT;

708

DROP TABLE t1;

709

710

711

# Test of multi-table-update

712

713

create table t1 (n int(10), d int(10)) engine=innodb;

714

create table t2 (n int(10), d int(10)) engine=innodb;

715

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

716

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

717

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

718

select * from t1;

719

select * from t2;

720

drop table t1,t2;

721

722

723

# Bug #29136 erred multi-delete on trans table does not rollback

724

725

726

# prepare

727

--disable_warnings

728

drop table if exists t1, t2;

729

--enable_warnings

730

CREATE TABLE t1 (a int, PRIMARY KEY (a));

731

CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;

732

create trigger trg_del_t2 after delete on t2 for each row

733

insert into t1 values (1);

734

insert into t1 values (1);

735

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

736

737

738

# exec cases A, B - see multi_update.test

739

740

# A. send_error() w/o send_eof() branch

741

742

--error ER_DUP_ENTRY

743

delete t2 from t2;

744

745

# check

746

747

select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;

748

749

# cleanup bug#29136

750

751

drop table t1, t2;

752

753

754

755

# Testing of IFNULL

756

757

create table t1 (a int, b int) engine=innodb;

758

insert into t1 values(20,null);

759

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

760

t2.b=t3.a;

761

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

762

t2.b=t3.a order by 1;

763

insert into t1 values(10,null);

764

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

765

t2.b=t3.a order by 1;

766

drop table t1;

767

768

769

# Test of read_through not existing const_table

770

771

772

create table t1 (a varchar(10) not null) engine=myisam;

773

create table t2 (b varchar(10) not null unique) engine=innodb;

774

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

775

drop table t1,t2;

776

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

777

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

778

insert into t1 values (10, 20);

779

insert into t2 values (10, 20);

780

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

781

drop table t1,t2;

782

783

784

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

785

786

787

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

788

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=INNODB;

789

insert into t1 set id=1;

790

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

791

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

792

select * from t1;

793

select * from t2;

794

drop table t2,t1;

795

CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

796

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

797

INSERT INTO t1 VALUES(1);

798

INSERT INTO t2 VALUES(1, 1);

799

SELECT * from t1;

800

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

801

SELECT * from t1;

802

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

803

SELECT * from t1;

804

DROP TABLE t1,t2;

805

806

807

# Test of range_optimizer

808

809

810

set autocommit=0;

811

812

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

813

814

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

815

816

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

817

818

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

819

COMMIT;

820

821

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

822

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

823

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

824

825

SELECT * FROM t1;

826

SELECT * FROM t2;

827

SELECT * FROM t3;

828

ROLLBACK;

829

830

SELECT * FROM t1;

831

SELECT * FROM t2;

832

SELECT * FROM t3;

833

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

834

COMMIT;

835

set autocommit=1;

836

DROP TABLE t1,t2,t3;

837

838

839

# Check update with conflicting key

840

841

842

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

843

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

844

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

845

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

846

SELECT * from t1;

847

drop table t1;

848

849

850

# Test multi update with different join methods

851

852

853

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

854

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

855

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

856

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

857

858

# Full join, without key

859

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

860

select * from t1;

861

862

# unique key

863

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

864

select * from t1;

865

866

# ref key

867

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

868

select * from t1;

869

870

# Range key (in t1)

871

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;

872

select * from t1;

873

select * from t2;

874

875

drop table t1,t2;

876

CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

877

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;

878

SET AUTOCOMMIT=0;

879

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

880

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

881

ROLLBACK;

882

SELECT * FROM t1;

883

drop table t1,t2;

884

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

885

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

886

select distinct parent,child from t1 order by parent;

887

drop table t1;

888

889

890

# Test that MySQL priorities clustered indexes

891

892

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

893

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

894

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

895

insert into t2 (a) select b from t1;

896

insert into t1 (b) select b from t2;

897

insert into t2 (a) select b from t1;

898

insert into t1 (a) select b from t2;

899

insert into t2 (a) select b from t1;

900

insert into t1 (a) select b from t2;

901

insert into t2 (a) select b from t1;

902

insert into t1 (a) select b from t2;

903

insert into t2 (a) select b from t1;

904

insert into t1 (a) select b from t2;

905

select count(*) from t1;

906

--replace_column 9 #

907

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

908

update t1 set c=a;

909

--replace_column 9 #

910

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

911

drop table t1,t2;

912

913

914

# Test of UPDATE ... ORDER BY

915

916

917

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

918

919

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

920

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

921

SELECT * from t1;

922

drop table t1;

923

924

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

925

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

926

SET @tmp=0;

927

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

928

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

929

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

930

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

931

select * from t1 order by a,b;

932

drop table t1;

933

934

935

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

936

937

938

create table t1 ( c char(8) not null ) engine=innodb;

939

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

940

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

941

942

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

943

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

944

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

945

update t1 set a=c, b=c;

946

947

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

948

insert into t2 select * from t1;

949

950

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

951

drop table t1,t2;

952

953

954

# test autoincrement with TRUNCATE

955

956

957

SET AUTOCOMMIT=1;

958

create table t1 (a integer auto_increment primary key) engine=innodb;

959

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

960

truncate table t1;

961

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

962

SELECT * from t1;

963

drop table t1;

964

965

966

# Test dictionary handling with spaceand quoting

967

968

969

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

970

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=INNODB;

971

#show create table t2;

972

drop table t2,t1;

973

974

975

# Test of multi updated and foreign keys

976

977

978

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

979

insert into `t1`values ( 1 ) ;

980

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 = innodb;

981

insert into `t2`values ( 1 ) ;

982

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

983

insert into `t3`values ( 1 ) ;

984

--error 1451

985

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

986

--error 1451

987

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;

988

--error 1054

989

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

990

drop table t3,t2,t1;

991

992

993

# test for recursion depth limit

994

995

create table t1(

996

id int primary key,

997

pid int,

998

index(pid),

999

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

1000

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

1001

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

1002

-- error 1451

1003

delete from t1 where id=0;

1004

delete from t1 where id=15;

1005

delete from t1 where id=0;

1006

1007

drop table t1;

1008

1009

1010

# Test timestamps

1011

1012

1013

CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;

1014

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

1015

(stamp))ENGINE=InnoDB;

1016

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

1017

# Note that timestamp 3 is wrong

1018

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

1019

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

1020

'20020204120000' GROUP BY col1;

1021

drop table t1,t2;

1022

1023

1024

# Test by Francois MASUREL

1025

1026

1027

CREATE TABLE t1 (

1028

`id` int(10) unsigned NOT NULL auto_increment,

1029

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

1030

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

1031

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

1032

`description` text,

1033

PRIMARY KEY (`id`),

1034

KEY `id_object` (`id_object`),

1035

KEY `id_version` (`id_version`)

1036

) ENGINE=InnoDB;

1037

1038

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

1039

1040

CREATE TABLE t2 (

1041

`id` int(10) unsigned NOT NULL auto_increment,

1042

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

1043

PRIMARY KEY (`id`),

1044

KEY `id_version` (`id_version`)

1045

) ENGINE=InnoDB;

1046

1047

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

1048

1049

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

1050

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

1051

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

1052

drop table t1,t2;

1053

1054

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

1055

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

1056

create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;

1057

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

1058

insert t2 select * from t1;

1059

insert t3 select * from t1;

1060

checksum table t1, t2, t3, t4 quick;

1061

checksum table t1, t2, t3, t4;

1062

checksum table t1, t2, t3, t4 extended;

1063

#show table status;

1064

drop table t1,t2,t3;

1065

1066

1067

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

1068

# (Bug #2552)

1069

1070

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

1071

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

1072

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

1073

drop table t1;

1074

1075

1076

# Bug2160

1077

1078

create table t1 (a int) engine=innodb;

1079

create table t2 like t1;

1080

drop table t1,t2;

1081

1082

1083

# Test of automaticly created foreign keys

1084

1085

1086

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

1087

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

1088

show create table t1;

1089

show create table t2;

1090

create index id on t2 (id);

1091

show create table t2;

1092

create index id2 on t2 (id);

1093

show create table t2;

1094

drop index id2 on t2;

1095

--error 1025,1025

1096

drop index id on t2;

1097

show create table t2;

1098

drop table t2;

1099

1100

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 = innodb;

1101

show create table t2;

1102

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

1103

show create table t2;

1104

drop table t2;

1105

1106

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

1107

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 = innodb;

1108

show create table t2;

1109

drop table t2;

1110

1111

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 = innodb;

1112

show create table t2;

1113

drop table t2;

1114

1115

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 = innodb;

1116

show create table t2;

1117

drop table t2;

1118

1119

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 = innodb;

1120

show create table t2;

1121

drop table t2;

1122

1123

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= innodb;

1124

show create table t2;

1125

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

1126

show create table t2;

1127

drop table t2;

1128

1129

# Test error handling

1130

1131

# Embedded server doesn't chdir to data directory

1132

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

1133

--error ER_WRONG_FK_DEF

1134

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 = innodb;

1135

1136

# bug#3749

1137

1138

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

1139

show create table t2;

1140

drop table t2;

1141

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=innodb;

1142

show create table t2;

1143

drop table t2, t1;

1144

1145

1146

1147

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

1148

1149

--error 1060

1150

create table t1 (c char(10), index (c,c)) engine=innodb;

1151

--error 1060

1152

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;

1153

--error 1060

1154

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;

1155

--error 1060

1156

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;

1157

create table t1 (c1 char(10), c2 char(10)) engine=innodb;

1158

--error 1060

1159

alter table t1 add key (c1,c1);

1160

--error 1060

1161

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

1162

--error 1060

1163

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

1164

--error 1060

1165

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

1166

drop table t1;

1167

1168

1169

# Bug #4082: integer truncation

1170

1171

1172

create table t1(a int(1) , b int(1)) engine=innodb;

1173

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

1174

select distinct concat(a, b) from t1;

1175

drop table t1;

1176

1177

1178

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

1179

# engines does not fail

1180

1181

1182

CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;

1183

--error 1214

1184

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

1185

DROP TABLE t1;

1186

1187

1188

# check null values #1

1189

1190

1191

--disable_warnings

1192

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

1193

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

1194

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

1195

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

1196

--enable_warnings

1197

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

1198

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;

1199

DROP TABLE t2;

1200

DROP TABLE t1;

1201

1202

1203

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

1204

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

1205

1206

create temporary table t1 (a int) engine=innodb;

1207

insert into t1 values (4711);

1208

truncate t1;

1209

insert into t1 values (42);

1210

select * from t1;

1211

drop table t1;

1212

# Show that it works with permanent tables too.

1213

create table t1 (a int) engine=innodb;

1214

insert into t1 values (4711);

1215

truncate t1;

1216

insert into t1 values (42);

1217

select * from t1;

1218

drop table t1;

1219

1220

1221

# Bug #13025 Server crash during filesort

1222

1223

1224

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=innodb;

1225

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

1226

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

1227

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

1228

drop table t1;

1229

1230

1231

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

1232

1233

1234

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;

1235

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

1236

select min(a) from t1;

1237

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

1238

drop table t1;

1239

1240

# End of 4.1 tests

1241

1242

1243

# range optimizer problem

1244

1245

1246

create table t1 (x bigint unsigned not null primary key) engine=innodb;

1247

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

1248

select * from t1;

1249

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

1250

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

1251

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

1252

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

1253

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

1254

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

1255

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

1256

select * from t1 where x > -16;

1257

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

1258

drop table t1;

1259

1260

1261

# Test for testable InnoDB status variables. This test

1262

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

1263

show status like "Innodb_buffer_pool_pages_total";

1264

show status like "Innodb_page_size";

1265

show status like "Innodb_rows_deleted";

1266

show status like "Innodb_rows_inserted";

1267

show status like "Innodb_rows_updated";

1268

1269

# Test for row locks InnoDB status variables.

1270

show status like "Innodb_row_lock_waits";

1271

show status like "Innodb_row_lock_current_waits";

1272

show status like "Innodb_row_lock_time";

1273

show status like "Innodb_row_lock_time_max";

1274

show status like "Innodb_row_lock_time_avg";

1275

1276

# Test for innodb_sync_spin_loops variable

1277

show variables like "innodb_sync_spin_loops";

1278

set global innodb_sync_spin_loops=1000;

1279

show variables like "innodb_sync_spin_loops";

1280

set global innodb_sync_spin_loops=0;

1281

show variables like "innodb_sync_spin_loops";

1282

set global innodb_sync_spin_loops=20;

1283

show variables like "innodb_sync_spin_loops";

1284

1285

# Test for innodb_thread_concurrency variable

1286

show variables like "innodb_thread_concurrency";

1287

set global innodb_thread_concurrency=1001;

1288

show variables like "innodb_thread_concurrency";

1289

set global innodb_thread_concurrency=0;

1290

show variables like "innodb_thread_concurrency";

1291

set global innodb_thread_concurrency=16;

1292

show variables like "innodb_thread_concurrency";

1293

1294

# Test for innodb_concurrency_tickets variable

1295

show variables like "innodb_concurrency_tickets";

1296

set global innodb_concurrency_tickets=1000;

1297

show variables like "innodb_concurrency_tickets";

1298

set global innodb_concurrency_tickets=0;

1299

show variables like "innodb_concurrency_tickets";

1300

set global innodb_concurrency_tickets=500;

1301

show variables like "innodb_concurrency_tickets";

1302

1303

# Test for innodb_thread_sleep_delay variable

1304

show variables like "innodb_thread_sleep_delay";

1305

set global innodb_thread_sleep_delay=100000;

1306

show variables like "innodb_thread_sleep_delay";

1307

set global innodb_thread_sleep_delay=0;

1308

show variables like "innodb_thread_sleep_delay";

1309

set global innodb_thread_sleep_delay=10000;

1310

show variables like "innodb_thread_sleep_delay";

1311

1312

1313

# Test varchar

1314

1315

1316

let $default=`select @@storage_engine`;

1317

set storage_engine=INNODB;

1318

source include/varchar.inc;

1319

1320

1321

# Some errors/warnings on create

1322

1323

1324

# Embedded server doesn't chdir to data directory

1325

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

1326

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

1327

drop table t1;

1328

create table t1 (v varchar(65536));

1329

show create table t1;

1330

drop table t1;

1331

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

1332

show create table t1;

1333

drop table t1;

1334

1335

eval set storage_engine=$default;

1336

1337

# InnoDB specific varchar tests

1338

create table t1 (v varchar(16384)) engine=innodb;

1339

drop table t1;

1340

1341

1342

# BUG#11039 Wrong key length in min()

1343

1344

1345

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;

1346

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

1347

select min(a) from t1;

1348

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

1349

drop table t1;

1350

1351

1352

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

1353

1354

1355

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

1356

insert into t1 (b) values (1);

1357

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

1358

select * from t1;

1359

truncate table t1;

1360

insert into t1 (b) values (1);

1361

replace into t1 (b) values (2);

1362

replace into t1 (b) values (1);

1363

replace into t1 (b) values (3);

1364

select * from t1;

1365

drop table t1;

1366

1367

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

1368

key (rowid), unique(val)) engine=innodb;

1369

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

1370

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

1371

--error ER_DUP_ENTRY

1372

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

1373

select * from t1;

1374

drop table t1;

1375

1376

1377

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

1378

1379

1380

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

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

# Bug #10465

1390

1391

1392

--disable_warnings

1393

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

1394

--enable_warnings

1395

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

1396

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

1397

SELECT GRADE FROM t1 WHERE GRADE= 151;

1398

DROP TABLE t1;

1399

1400

1401

# Bug #12340 multitable delete deletes only one record

1402

1403

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;

1404

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;

1405

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

1406

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

1407

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

1408

select * from t1;

1409

drop table t1,t2;

1410

1411

1412

# Test that the slow TRUNCATE implementation resets autoincrement columns

1413

# (bug #11946)

1414

1415

1416

CREATE TABLE t1 (

1417

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1418

) ENGINE=InnoDB;

1419

1420

CREATE TABLE t2 (

1421

id INTEGER NOT NULL,

1422

FOREIGN KEY (id) REFERENCES t1 (id)

1423

) ENGINE=InnoDB;

1424

1425

INSERT INTO t1 (id) VALUES (NULL);

1426

SELECT * FROM t1;

1427

TRUNCATE t1;

1428

INSERT INTO t1 (id) VALUES (NULL);

1429

SELECT * FROM t1;

1430

1431

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

1432

# rows resets autoincrement columns

1433

DELETE FROM t1;

1434

TRUNCATE t1;

1435

INSERT INTO t1 (id) VALUES (NULL);

1436

SELECT * FROM t1;

1437

DROP TABLE t2, t1;

1438

1439

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

1440

CREATE TABLE t1

1441

(

1442

id INT PRIMARY KEY

1443

) ENGINE=InnoDB;

1444

1445

--error 1005,1005

1446

CREATE TEMPORARY TABLE t2

1447

(

1448

id INT NOT NULL PRIMARY KEY,

1449

b INT,

1450

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

1451

) ENGINE=InnoDB;

1452

DROP TABLE t1;

1453

1454

1455

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

1456

1457

1458

# prefix index

1459

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

1460

character set = latin1 engine = innodb;

1461

1462

# normal indexes

1463

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

1464

character set = latin1 engine = innodb;

1465

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

1466

character set = latin1 engine = innodb;

1467

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

1468

character set = latin1 engine = innodb;

1469

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

1470

character set = latin1 engine = innodb;

1471

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

1472

character set = latin1 engine = innodb;

1473

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

1474

character set = latin1 engine = innodb;

1475

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

1476

character set = latin1 engine = innodb;

1477

1478

# multi-column indexes are allowed to be longer

1479

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

1480

character set = latin1 engine = innodb;

1481

1482

show create table t9;

1483

1484

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

1485

1486

# these should have their index length trimmed

1487

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

1488

character set = latin1 engine = innodb;

1489

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

1490

character set = latin1 engine = innodb;

1491

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

1492

character set = latin1 engine = innodb;

1493

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

1494

character set = latin1 engine = innodb;

1495

1496

show create table t1;

1497

1498

drop table t1, t2, t3, t4;

1499

1500

# these should be refused

1501

--error 1071

1502

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

1503

character set = latin1 engine = innodb;

1504

--error 1071

1505

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

1506

character set = latin1 engine = innodb;

1507

--error 1071

1508

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

1509

character set = latin1 engine = innodb;

1510

--error 1071

1511

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

1512

character set = latin1 engine = innodb;

1513

1514

1515

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

1516

1517

1518

CREATE TABLE t1

1519

(

1520

id INT PRIMARY KEY

1521

) ENGINE=InnoDB;

1522

1523

CREATE TABLE t2

1524

(

1525

v INT,

1526

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

1527

) ENGINE=InnoDB;

1528

1529

--error 1452

1530

INSERT INTO t2 VALUES(2);

1531

1532

INSERT INTO t1 VALUES(1);

1533

INSERT INTO t2 VALUES(1);

1534

1535

--error 1451

1536

DELETE FROM t1 WHERE id = 1;

1537

1538

--error 1217

1539

DROP TABLE t1;

1540

1541

SET FOREIGN_KEY_CHECKS=0;

1542

DROP TABLE t1;

1543

SET FOREIGN_KEY_CHECKS=1;

1544

1545

--error 1452

1546

INSERT INTO t2 VALUES(3);

1547

1548

DROP TABLE t2;

1549

1550

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

1551

1552

connect (a,localhost,root,,);

1553

connect (b,localhost,root,,);

1554

connection a;

1555

create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;

1556

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

1557

set autocommit=0;

1558

checksum table t1;

1559

connection b;

1560

insert into t1 values(3);

1561

connection a;

1562

1563

# Here checksum should not see insert

1564

1565

checksum table t1;

1566

connection a;

1567

commit;

1568

checksum table t1;

1569

commit;

1570

drop table t1;

1571

1572

# autocommit = 1

1573

1574

connection a;

1575

create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;

1576

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

1577

set autocommit=1;

1578

checksum table t1;

1579

connection b;

1580

set autocommit=1;

1581

insert into t1 values(3);

1582

connection a;

1583

1584

# Here checksum sees insert

1585

1586

checksum table t1;

1587

drop table t1;

1588

1589

connection default;

1590

disconnect a;

1591

disconnect b;

1592

1593

# tests for bugs #9802 and #13778

1594

1595

# test that FKs between invalid types are not accepted

1596

1597

set foreign_key_checks=0;

1598

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

1599

# Embedded server doesn't chdir to data directory

1600

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

1601

-- error 1005

1602

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

1603

set foreign_key_checks=1;

1604

drop table t2;

1605

1606

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

1607

# when f_k_c is 0

1608

1609

set foreign_key_checks=0;

1610

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

1611

# Embedded server doesn't chdir to data directory

1612

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

1613

-- error 1005

1614

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

1615

set foreign_key_checks=1;

1616

drop table t1;

1617

1618

# test that invalid datatype conversions with ALTER are not allowed

1619

1620

set foreign_key_checks=0;

1621

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

1622

create table t1(a varchar(10) primary key) engine = innodb;

1623

-- error 1025,1025

1624

alter table t1 modify column a int;

1625

set foreign_key_checks=1;

1626

drop table t2,t1;

1627

1628

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

1629

1630

set foreign_key_checks=0;

1631

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

1632

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

1633

alter table t1 convert to character set utf8;

1634

set foreign_key_checks=1;

1635

drop table t2,t1;

1636

1637

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

1638

1639

set foreign_key_checks=0;

1640

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

1641

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

1642

# Embedded server doesn't chdir to data directory

1643

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

1644

-- error 1025

1645

rename table t3 to t1;

1646

set foreign_key_checks=1;

1647

drop table t2,t3;

1648

1649

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

1650

1651

create table t1(a int primary key) row_format=redundant engine=innodb;

1652

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

1653

create table t3(a int primary key) row_format=compact engine=innodb;

1654

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

1655

1656

insert into t1 values(1);

1657

insert into t3 values(1);

1658

-- error 1452

1659

insert into t2 values(2);

1660

-- error 1452

1661

insert into t4 values(2);

1662

insert into t2 values(1);

1663

insert into t4 values(1);

1664

-- error 1451

1665

update t1 set a=2;

1666

-- error 1452

1667

update t2 set a=2;

1668

-- error 1451

1669

update t3 set a=2;

1670

-- error 1452

1671

update t4 set a=2;

1672

-- error 1451

1673

truncate t1;

1674

-- error 1451

1675

truncate t3;

1676

truncate t2;

1677

truncate t4;

1678

truncate t1;

1679

truncate t3;

1680

1681

drop table t4,t3,t2,t1;

1682

1683

1684

1685

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

1686

1687

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

1688

b varchar(255) character set utf8,

1689

c varchar(255) character set utf8,

1690

d varchar(255) character set utf8,

1691

key (a,b,c,d)) engine=innodb;

1692

drop table t1;

1693

--error ER_TOO_LONG_KEY

1694

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

1695

b varchar(255) character set utf8,

1696

c varchar(255) character set utf8,

1697

d varchar(255) character set utf8,

1698

e varchar(255) character set utf8,

1699

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

1700

1701

1702

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

1703

1704

create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;

1705

create table t2 (s1 binary(2),primary key (s1)) engine=innodb;

1706

create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;

1707

create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;

1708

1709

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

1710

-- error ER_DUP_ENTRY

1711

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

1712

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

1713

-- error ER_DUP_ENTRY

1714

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

1715

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

1716

-- error ER_DUP_ENTRY

1717

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

1718

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

1719

select hex(s1) from t1;

1720

select hex(s1) from t2;

1721

select hex(s1) from t3;

1722

select hex(s1) from t4;

1723

drop table t1,t2,t3,t4;

1724

1725

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

1726

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

1727

1728

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

1729

-- error 1452

1730

insert into t2 values(0x42);

1731

insert into t2 values(0x41);

1732

select hex(s1) from t2;

1733

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

1734

select hex(s1) from t2;

1735

-- error 1451

1736

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

1737

-- error 1451

1738

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

1739

-- error 1451

1740

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

1741

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

1742

select hex(s1) from t2;

1743

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

1744

select hex(s1) from t2;

1745

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

1746

select hex(s1) from t2;

1747

-- error 1451

1748

delete from t1 where a=1;

1749

delete from t1 where a=2;

1750

update t2 set s1=0x4120;

1751

-- error 1451

1752

delete from t1;

1753

delete from t1 where a!=3;

1754

select a,hex(s1) from t1;

1755

select hex(s1) from t2;

1756

1757

drop table t2,t1;

1758

1759

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

1760

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

1761

1762

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

1763

insert into t2 values(0x41);

1764

select hex(s1) from t2;

1765

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

1766

select hex(s1) from t2;

1767

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

1768

select hex(s1) from t2;

1769

delete from t1 where a=1;

1770

-- error 1451

1771

delete from t1 where a=2;

1772

select a,hex(s1) from t1;

1773

select hex(s1) from t2;

1774

1775

drop table t2,t1;

1776

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

1777

# generated foreign key identifier. (Bug #16387)

1778

1779

CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;

1780

CREATE TABLE t2(a INT) ENGINE=InnoDB;

1781

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

1782

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1783

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

1784

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1785

SHOW CREATE TABLE t2;

1786

DROP TABLE t2,t1;

1787

1788

1789

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

1790

1791

1792

connect (a,localhost,root,,);

1793

connect (b,localhost,root,,);

1794

connection a;

1795

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

1796

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

1797

commit;

1798

connection b;

1799

set autocommit = 0;

1800

update t1 set b = 5 where a = 2;

1801

connection a;

1802

delimiter |;

1803

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

1804

delimiter ;|

1805

set autocommit = 0;

1806

connection a;

1807

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

1808

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

1809

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

1810

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

1811

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

1812

connection b;

1813

commit;

1814

connection a;

1815

commit;

1816

drop trigger t1t;

1817

drop table t1;

1818

disconnect a;

1819

disconnect b;

1820

1821

# Another trigger test

1822

1823

connect (a,localhost,root,,);

1824

connect (b,localhost,root,,);

1825

connection a;

1826

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

1827

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

1828

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

1829

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

1830

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

1831

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

1832

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

1833

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

1834

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

1835

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

1836

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

1837

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

1838

1839

delimiter |;

1840

create trigger t1t before insert on t1 for each row begin

1841

INSERT INTO t2 SET a = NEW.a;

1842

end |

1843

1844

create trigger t2t before insert on t2 for each row begin

1845

DELETE FROM t3 WHERE a = NEW.a;

1846

end |

1847

1848

create trigger t3t before delete on t3 for each row begin

1849

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

1850

end |

1851

1852

create trigger t4t before update on t4 for each row begin

1853

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

1854

end |

1855

delimiter ;|

1856

commit;

1857

set autocommit = 0;

1858

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

1859

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

1860

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

1861

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

1862

insert into t5(a) values(20);

1863

connection b;

1864

set autocommit = 0;

1865

insert into t1(a) values(7);

1866

insert into t2(a) values(8);

1867

delete from t2 where a = 3;

1868

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

1869

commit;

1870

drop trigger t1t;

1871

drop trigger t2t;

1872

drop trigger t3t;

1873

drop trigger t4t;

1874

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

1875

connection default;

1876

disconnect a;

1877

disconnect b;

1878

1879

1880

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

1881

# error message (bug #9680)

1882

1883

1884

CREATE TABLE t1 (

1885

field1 varchar(8) NOT NULL DEFAULT '',

1886

field2 varchar(8) NOT NULL DEFAULT '',

1887

PRIMARY KEY (field1, field2)

1888

) ENGINE=InnoDB;

1889

1890

CREATE TABLE t2 (

1891

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

1892

FOREIGN KEY (field1) REFERENCES t1 (field1)

1893

ON DELETE CASCADE ON UPDATE CASCADE

1894

) ENGINE=InnoDB;

1895

1896

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

1897

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

1898

1899

INSERT INTO t2 VALUES ('old');

1900

INSERT INTO t2 VALUES ('other');

1901

1902

--error ER_FOREIGN_DUPLICATE_KEY

1903

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

1904

1905

DROP TABLE t2;

1906

DROP TABLE t1;

1907

1908

1909

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

1910

1911

create table t1 (

1912

c1 bigint not null,

1913

c2 bigint not null,

1914

primary key (c1),

1915

unique key (c2)

1916

) engine=innodb;

1917

1918

create table t2 (

1919

c1 bigint not null,

1920

primary key (c1)

1921

) engine=innodb;

1922

1923

alter table t1 add constraint c2_fk foreign key (c2)

1924

references t2(c1) on delete cascade;

1925

show create table t1;

1926

1927

alter table t1 drop foreign key c2_fk;

1928

show create table t1;

1929

1930

drop table t1, t2;

1931

1932

1933

# Bug #14360: problem with intervals

1934

1935

1936

create table t1(a date) engine=innodb;

1937

create table t2(a date, key(a)) engine=innodb;

1938

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

1939

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

1940

select * from t1, t2

1941

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

1942

drop table t1, t2;

1943

1944

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

1945

primary key(f_id, id)) engine=innodb;

1946

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

1947

primary key(id)) engine=innodb;

1948

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

1949

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

1950

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

1951

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

1952

commit;

1953

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

1954

WHERE mm.id IS NULL;

1955

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

1956

where mm.id is null lock in share mode;

1957

drop table t1,t2;

1958

1959

1960

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

1961

# update (because READ COMMITTED isolation level)

1962

1963

1964

connect (a,localhost,root,,);

1965

connect (b,localhost,root,,);

1966

connection a;

1967

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

1968

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

1969

commit;

1970

set autocommit = 0;

1971

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1972

update t1 set b = 5 where b = 1;

1973

connection b;

1974

set autocommit = 0;

1975

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1976

1977

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

1978

1979

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

1980

connection a;

1981

commit;

1982

connection b;

1983

commit;

1984

drop table t1;

1985

connection default;

1986

disconnect a;

1987

disconnect b;

1988

1989

1990

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

1991

# using READ COMMITTED isolation level)

1992

1993

1994

connect (a,localhost,root,,);

1995

connect (b,localhost,root,,);

1996

connection a;

1997

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

1998

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

1999

commit;

2000

set autocommit = 0;

2001

select * from t1 lock in share mode;

2002

update t1 set b = 5 where b = 1;

2003

connection b;

2004

set autocommit = 0;

2005

2006

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

2007

2008

--error 1205

2009

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

2010

2011

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

2012

2013

--error 1205

2014

connection a;

2015

commit;

2016

connection b;

2017

commit;

2018

connection default;

2019

disconnect a;

2020

disconnect b;

2021

drop table t1;

2022

2023

2024

# Consistent read should be used in following selects

2025

2026

# 1) INSERT INTO ... SELECT

2027

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

2028

# 3) CREATE ... SELECT

2029

2030

connect (a,localhost,root,,);

2031

connect (b,localhost,root,,);

2032

connection a;

2033

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

2034

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

2035

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

2036

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

2037

commit;

2038

set autocommit = 0;

2039

select * from t2 for update;

2040

connection b;

2041

set autocommit = 0;

2042

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2043

insert into t1 select * from t2;

2044

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

2045

create table t3(d int not null, e int, primary key(d)) engine=innodb

2046

select * from t2;

2047

commit;

2048

connection a;

2049

commit;

2050

connection default;

2051

disconnect a;

2052

disconnect b;

2053

drop table t1, t2, t3;

2054

2055

2056

# Consistent read should not be used if

2057

2058

# (a) isolation level is serializable OR

2059

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

2060

# (c) select ... for update

2061

2062

# in following queries:

2063

2064

# 1) INSERT INTO ... SELECT

2065

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

2066

# 3) CREATE ... SELECT

2067

2068

connect (a,localhost,root,,);

2069

connect (b,localhost,root,,);

2070

connect (c,localhost,root,,);

2071

connect (d,localhost,root,,);

2072

connect (e,localhost,root,,);

2073

connect (f,localhost,root,,);

2074

connect (g,localhost,root,,);

2075

connect (h,localhost,root,,);

2076

connect (i,localhost,root,,);

2077

connect (j,localhost,root,,);

2078

connection a;

2079

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

2080

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

2081

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

2082

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

2083

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

2084

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

2085

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

2086

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

2087

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

2088

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

2089

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

2090

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

2091

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

2092

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

2093

commit;

2094

set autocommit = 0;

2095

select * from t2 for update;

2096

connection b;

2097

set autocommit = 0;

2098

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2099

--send

2100

insert into t1 select * from t2;

2101

connection c;

2102

set autocommit = 0;

2103

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2104

--send

2105

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

2106

connection d;

2107

set autocommit = 0;

2108

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2109

--send

2110

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

2111

connection e;

2112

set autocommit = 0;

2113

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2114

--send

2115

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

2116

connection f;

2117

set autocommit = 0;

2118

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2119

--send

2120

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

2121

connection g;

2122

set autocommit = 0;

2123

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2124

--send

2125

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

2126

connection h;

2127

set autocommit = 0;

2128

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2129

--send

2130

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

2131

connection i;

2132

set autocommit = 0;

2133

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2134

--send

2135

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

2136

connection j;

2137

set autocommit = 0;

2138

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2139

--send

2140

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

2141

2142

connection b;

2143

--error 1205

2144

reap;

2145

2146

connection c;

2147

--error 1205

2148

reap;

2149

2150

connection d;

2151

--error 1205

2152

reap;

2153

2154

connection e;

2155

--error 1205

2156

reap;

2157

2158

connection f;

2159

--error 1205

2160

reap;

2161

2162

connection g;

2163

--error 1205

2164

reap;

2165

2166

connection h;

2167

--error 1205

2168

reap;

2169

2170

connection i;

2171

--error 1205

2172

reap;

2173

2174

connection j;

2175

--error 1205

2176

reap;

2177

2178

connection a;

2179

commit;

2180

2181

connection default;

2182

disconnect a;

2183

disconnect b;

2184

disconnect c;

2185

disconnect d;

2186

disconnect e;

2187

disconnect f;

2188

disconnect g;

2189

disconnect h;

2190

disconnect i;

2191

disconnect j;

2192

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

2193

2194

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

2195

--error 1005

2196

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

2197

2198

2199

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

2200

2201

2202

CREATE TABLE t1 (

2203

a BIGINT(20) NOT NULL,

2204

PRIMARY KEY (a)

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2206

2207

CREATE TABLE t2 (

2208

a BIGINT(20) NOT NULL,

2209

b VARCHAR(128) NOT NULL,

2210

c TEXT NOT NULL,

2211

PRIMARY KEY (a,b),

2212

KEY idx_t2_b_c (b,c(200)),

2213

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

2214

ON DELETE CASCADE

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2216

2217

INSERT INTO t1 VALUES (1);

2218

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

2219

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

2220

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

2221

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

2222

2223

SELECT * FROM t2 WHERE b = 'customer_over';

2224

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

2225

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

2226

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

2227

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

2228

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

2229

2230

drop table t2, t1;

2231

2232

2233

# Test optimize on table with open transaction

2234

2235

2236

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2237

BEGIN;

2238

INSERT INTO t1 VALUES (1);

2239

OPTIMIZE TABLE t1;

2240

DROP TABLE t1;

2241

2242

2243

# Bug #24741 (existing cascade clauses disappear when adding foreign keys)

2244

2245

2246

CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;

2247

2248

CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,

2249

CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)

2250

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

2251

2252

ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON

2253

DELETE CASCADE ON UPDATE CASCADE;

2254

2255

SHOW CREATE TABLE t2;

2256

DROP TABLE t2, t1;

2257

2258

2259

# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns

2260

# for which there is a foreign key constraint ON ... SET NULL.

2261

2262

2263

CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;

2264

CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;

2265

INSERT INTO t1 VALUES (1);

2266

INSERT INTO t2 VALUES (1);

2267

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;

2268

# mysqltest first does replace_regex, then replace_result

2269

--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/

2270

# Embedded server doesn't chdir to data directory

2271

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

2272

--error 1025

2273

ALTER TABLE t2 MODIFY a INT NOT NULL;

2274

DELETE FROM t1;

2275

DROP TABLE t2,t1;

2276

2277

2278

# Bug #26835: table corruption after delete+insert

2279

2280

2281

CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)

2282

ENGINE=InnoDB;

2283

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

2284

DELETE FROM t1;

2285

INSERT INTO t1 VALUES ('DDD');

2286

SELECT * FROM t1;

2287

DROP TABLE t1;

2288

2289

2290

# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)

2291

# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))

2292

2293

2294

CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB

2295

AUTO_INCREMENT=42;

2296

2297

INSERT INTO t1 VALUES (0),(347),(0);

2298

SELECT * FROM t1;

2299

2300

SHOW CREATE TABLE t1;

2301

2302

CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;

2303

INSERT INTO t2 VALUES(42),(347),(348);

2304

ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);

2305

SHOW CREATE TABLE t1;

2306

2307

DROP TABLE t1,t2;

2308

2309

2310

# Bug #21101 (Prints wrong error message if max row size is too large)

2311

2312

--error 1118

2313

CREATE TABLE t1 (

2314

c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),

2315

c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),

2316

c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),

2317

c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),

2318

c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),

2319

c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),

2320

c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),

2321

c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)

2322

) ENGINE = InnoDB;

2323

2324

2325

# Bug #31860 InnoDB assumes AUTOINC values can only be positive.

2326

2327

DROP TABLE IF EXISTS t1;

2328

CREATE TABLE t1(

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

2330

) ENGINE=InnoDB;

2331

INSERT INTO t1 VALUES(-10);

2332

SELECT * FROM t1;

2333

2334

# NOTE: The server really needs to be restarted at this point

2335

# for the test to be useful.

2336

2337

# Without the fix InnoDB would trip over an assertion here.

2338

INSERT INTO t1 VALUES(NULL);

2339

# The next value should be 1 and not -9 or a -ve number

2340

SELECT * FROM t1;

2341

DROP TABLE t1;

2342

2343

2344

# Bug #21409 Incorrect result returned when in READ-COMMITTED with

2345

# query_cache ON

2346

2347

CONNECT (c1,localhost,root,,);

2348

CONNECT (c2,localhost,root,,);

2349

CONNECTION c1;

2350

SET TX_ISOLATION='read-committed';

2351

SET AUTOCOMMIT=0;

2352

DROP TABLE IF EXISTS t1, t2;

2353

CREATE TABLE t1 ( a int ) ENGINE=InnoDB;

2354

CREATE TABLE t2 LIKE t1;

2355

SELECT * FROM t2;

2356

CONNECTION c2;

2357

SET TX_ISOLATION='read-committed';

2358

SET AUTOCOMMIT=0;

2359

INSERT INTO t1 VALUES (1);

2360

COMMIT;

2361

CONNECTION c1;

2362

SELECT * FROM t1 WHERE a=1;

2363

DISCONNECT c1;

2364

DISCONNECT c2;

2365

CONNECT (c1,localhost,root,,);

2366

CONNECT (c2,localhost,root,,);

2367

CONNECTION c1;

2368

SET TX_ISOLATION='read-committed';

2369

SET AUTOCOMMIT=0;

2370

SELECT * FROM t2;

2371

CONNECTION c2;

2372

SET TX_ISOLATION='read-committed';

2373

SET AUTOCOMMIT=0;

2374

INSERT INTO t1 VALUES (2);

2375

COMMIT;

2376

CONNECTION c1;

2377

# The result set below should be the same for both selects

2378

SELECT * FROM t1 WHERE a=2;

2379

SELECT * FROM t1 WHERE a=2;

2380

DROP TABLE t1;

2381

DROP TABLE t2;

2382

DISCONNECT c1;

2383

DISCONNECT c2;

2384

2385

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

2386

# #

2387

# Please, DO NOT TOUCH this file as well as the innodb.result file. #

2388

# These files are to be modified ONLY BY INNOBASE guys. #

2389

# #

2390

# Use innodb_mysql.[test|result] files instead. #

2391

# #

2392

# If nevertheless you need to make some changes here, please, forward #

2393

# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com #

2394

# (otherwise your changes may be erased). #

2395

# #

2396

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

Older »