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

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;

check table t1;

drop table t1;

100

101

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

102

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

103

delete from t1 where a = 1;

104

select * from t1;

105

create index skr on t1 (a);

106

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

107

analyze table t1;

108

--replace_column 7 #

109

show keys from t1;

110

drop table t1;

111

112

113

# Test of reading on secondary key with may be null

114

115

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

116

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

117

select * from t1 where a = 1;

118

drop table t1;

119

120

121

# Test rollback

122

123

124

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

125

set autocommit=0;

126

insert into t1 values (4);

127

rollback;

128

select n, "after rollback" from t1;

129

insert into t1 values (4);

130

commit;

131

select n, "after commit" from t1;

132

commit;

133

insert into t1 values (5);

134

insert into t1 values (4);

135

commit;

136

select n, "after commit" from t1;

137

set autocommit=1;

138

insert into t1 values (6);

139

insert into t1 values (4);

140

select n from t1;

141

set autocommit=0;

142

143

# savepoints

144

145

begin;

146

savepoint `my_savepoint`;

147

insert into t1 values (7);

148

savepoint `savept2`;

149

insert into t1 values (3);

150

select n from t1;

151

savepoint savept3;

152

rollback to savepoint savept2;

153

--error 1305

154

rollback to savepoint savept3;

155

rollback to savepoint savept2;

156

release savepoint `my_savepoint`;

157

select n from t1;

158

rollback to savepoint `my_savepoint`;

159

--error 1305

160

rollback to savepoint savept2;

161

insert into t1 values (8);

162

savepoint sv;

163

commit;

164

savepoint sv;

165

set autocommit=1;

166

# nop

167

rollback;

168

drop table t1;

169

170

171

# Test for commit and FLUSH TABLES WITH READ LOCK

172

173

174

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

175

start transaction;

176

insert into t1 values (4);

177

flush tables with read lock;

178

179

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

180

#--error 1223;

181

commit;

182

unlock tables;

183

commit;

184

select * from t1;

185

drop table t1;

186

187

188

# Testing transactions

189

190

191

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

192

begin;

193

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

194

select id as afterbegin_id,nom as afterbegin_nom from t1;

195

rollback;

196

select id as afterrollback_id,nom as afterrollback_nom from t1;

197

set autocommit=0;

198

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

199

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

200

rollback;

201

select id as afterrollback_id,nom as afterrollback_nom from t1;

202

set autocommit=1;

203

drop table t1;

204

205

206

# Simple not autocommit test

207

208

209

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

210

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

211

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

212

delete from t1;

213

delete from t1 where id = 'pippo';

214

select * from t1;

215

216

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

217

set autocommit=0;

218

delete from t1;

219

rollback;

220

select * from t1;

221

delete from t1;

222

commit;

223

select * from t1;

224

drop table t1;

225

226

227

# Test of active transactions

228

229

230

create table t1 (a integer) engine=innodb;

231

start transaction;

232

rename table t1 to t2;

233

create table t1 (b integer) engine=innodb;

234

insert into t1 values (1);

235

rollback;

236

drop table t1;

237

rename table t2 to t1;

238

drop table t1;

239

set autocommit=1;

240

241

242

# The following simple tests failed at some point

243

244

245

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

246

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

247

select * from t1;

248

drop table t1;

249

250

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

251

set autocommit=0;

252

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

253

COMMIT;

254

SELECT * FROM t1;

255

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

256

drop table t1;

257

set autocommit=1;

258

259

260

# Test when reading on part of unique key

261

262

CREATE TABLE t1 (

263

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

264

name varchar(100),

265

phone varchar(100),

266

ref_email varchar(100) DEFAULT '' NOT NULL,

267

detail varchar(200),

268

PRIMARY KEY (user_id,ref_email)

269

)engine=innodb;

270

271

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

272

select * from t1 where user_id=10292;

273

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

274

select * from t1 where user_id=10292;

275

select * from t1 where user_id>=10292;

276

select * from t1 where user_id>10292;

277

select * from t1 where user_id<10292;

278

drop table t1;

279

280

281

# Test that keys are created in right order

282

283

284

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

285

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

286

--replace_column 7 #

287

show index from t1;

288

drop table t1;

289

290

291

# Test of ALTER TABLE and innodb tables

292

293

294

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

295

alter table t1 engine=innodb;

296

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

297

select * from t1;

298

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

299

select * from t1;

300

alter table t1 add co3 int not null;

301

select * from t1;

302

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

303

select * from t1;

304

drop table t1;

305

306

307

# INSERT INTO innodb tables

308

309

310

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

311

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

312

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

313

select * from t1;

314

insert into t2 select * from t1;

315

select * from t2;

316

delete from t1 where b = 3;

317

select * from t1;

318

insert into t1 select * from t2;

319

select * from t1;

320

select * from t2;

321

drop table t1,t2;

322

323

324

# ORDER BY on not primary key

325

326

327

CREATE TABLE t1 (

328

user_name varchar(12),

329

password text,

330

subscribed char(1),

331

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

332

quota bigint(20),

333

weight double,

334

access_date date,

335

access_time time,

336

approved datetime,

337

dummy_primary_key int(11) NOT NULL auto_increment,

338

PRIMARY KEY (dummy_primary_key)

339

) ENGINE=innodb;

340

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

341

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

342

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

343

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

344

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

345

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

346

drop table t1;

347

348

349

# Testing of tables without primary keys

350

351

352

CREATE TABLE t1 (

353

id int(11) NOT NULL auto_increment,

354

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

355

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

356

KEY (id),

357

KEY parent_id (parent_id),

358

KEY level (level)

359

) engine=innodb;

360

361

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

362

update t1 set parent_id=parent_id+100;

363

select * from t1 where parent_id=102;

364

update t1 set id=id+1000;

365

update t1 set id=1024 where id=1009;

366

select * from t1;

367

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

368

select * from t1;

369

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

370

select * from t1 where parent_id=102;

371

--replace_column 9 #

372

explain select level from t1 where level=1;

373

select level,id from t1 where level=1;

374

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

375

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

376

delete from t1 where level=1;

377

select * from t1;

378

drop table t1;

379

380

381

# Test of index only reads

382

383

CREATE TABLE t1 (

384

sca_code char(6) NOT NULL,

385

cat_code char(6) NOT NULL,

386

sca_desc varchar(50),

387

lan_code char(2) NOT NULL,

388

sca_pic varchar(100),

389

sca_sdesc varchar(50),

390

sca_sch_desc varchar(16),

391

PRIMARY KEY (sca_code, cat_code, lan_code),

392

INDEX sca_pic (sca_pic)

393

) engine = innodb ;

394

395

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

396

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

397

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

398

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

399

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

400

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

401

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

402

403

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

404

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

405

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

406

select sca_pic from t1 where sca_pic is null;

407

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

408

delete from t1 where sca_code='pd';

409

drop table t1;

410

411

412

# Test of opening table twice and timestamps

413

414

set @a:=now();

415

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

416

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

417

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

418

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

419

update t1 set a=5 where a=1;

420

select a from t1;

421

drop table t1;

422

423

424

# Test with variable length primary key

425

426

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

427

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

428

select * from t1 order by b desc;

429

optimize table t1;

430

--replace_column 7 #

431

show keys from t1;

432

drop table t1;

433

434

435

# Test of create index with NULL columns

436

437

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

438

insert into t1 values (1,2);

439

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

440

create index ax1 on t1 (i,j);

441

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

442

drop table t1;

443

444

445

# Test min-max optimization

446

447

448

CREATE TABLE t1 (

449

a int3 unsigned NOT NULL,

450

b int1 unsigned NOT NULL,

451

UNIQUE (a, b)

452

) ENGINE = innodb;

453

454

INSERT INTO t1 VALUES (1, 1);

455

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

456

drop table t1;

457

458

459

# Test INSERT DELAYED

460

461

462

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

463

# Can't test this in 3.23

464

# INSERT DELAYED INTO t1 VALUES (1);

465

INSERT INTO t1 VALUES (1);

466

SELECT * FROM t1;

467

DROP TABLE t1;

468

469

470

471

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

472

473

474

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;

475

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

476

--replace_column 9 #

477

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

478

drop table t1;

479

480

481

# Test lock tables

482

483

484

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;

485

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

486

LOCK TABLES t1 WRITE;

487

--error ER_DUP_ENTRY

488

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

489

select id from t1;

490

select id from t1;

491

UNLOCK TABLES;

492

DROP TABLE t1;

493

494

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;

495

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

496

LOCK TABLES t1 WRITE;

497

begin;

498

--error ER_DUP_ENTRY

499

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

500

select id from t1;

501

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

502

commit;

503

select id,id3 from t1;

504

UNLOCK TABLES;

505

DROP TABLE t1;

506

507

508

# Test prefix key

509

510

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

511

drop table t1;

512

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

513

show create table t1;

514

drop table t1;

515

516

517

# Test using temporary table and auto_increment

518

519

520

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

521

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

522

delete from t1 where a=3;

523

insert into t1 values (NULL);

524

select * from t1;

525

alter table t1 add b int;

526

select * from t1;

527

drop table t1;

528

529

#Slashdot bug

530

create table t1

531

(

532

id int auto_increment primary key,

533

name varchar(32) not null,

534

value text not null,

535

uid int not null,

536

unique key(name,uid)

537

) engine=innodb;

538

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

539

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

540

set insert_id=5;

541

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

542

delete from t1 where uid=102;

543

set insert_id=5;

544

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

545

set insert_id=6;

546

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

547

select * from t1;

548

drop table t1;

549

550

551

# Test DROP DATABASE

552

553

554

create database mysqltest;

555

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

556

insert into mysqltest.t1 values(1);

557

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

558

insert into mysqltest.t2 values(1);

559

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

560

insert into mysqltest.t3 values(1);

561

commit;

562

drop database mysqltest;

563

# Don't check error message

564

--error 1049

565

show tables from mysqltest;

566

567

568

# Test truncate table with and without auto_commit

569

570

571

set autocommit=0;

572

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

573

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

574

truncate table t1;

575

commit;

576

truncate table t1;

577

truncate table t1;

578

select * from t1;

579

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

580

delete from t1;

581

select * from t1;

582

commit;

583

drop table t1;

584

set autocommit=1;

585

586

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

587

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

588

truncate table t1;

589

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

590

select * from t1;

591

truncate table t1;

592

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

593

delete from t1;

594

select * from t1;

595

drop table t1;

596

597

598

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

599

600

601

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

602

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

603

--replace_column 9 #

604

explain select * from t1 order by a;

605

--replace_column 9 #

606

explain select * from t1 order by b;

607

--replace_column 9 #

608

explain select * from t1 order by c;

609

--replace_column 9 #

610

explain select a from t1 order by a;

611

--replace_column 9 #

612

explain select b from t1 order by b;

613

--replace_column 9 #

614

explain select a,b from t1 order by b;

615

--replace_column 9 #

616

explain select a,b from t1;

617

--replace_column 9 #

618

explain select a,b,c from t1;

619

drop table t1;

620

621

622

# Check describe

623

624

625

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

626

desc t1;

627

drop table t1;

628

629

630

# Test of multi-table-delete

631

632

633

CREATE TABLE t1 (

634

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

635

cname char(15) NOT NULL default '',

636

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

637

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

638

last_mod_date timestamp NOT NULL,

639

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

640

last_app_date timestamp NOT NULL,

641

last_app_id smallint(6) default '-1',

642

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

643

assigned_scps int(11) default '0',

644

status tinyint(4) default '0'

645

) ENGINE=InnoDB;

646

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

647

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

648

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

649

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

650

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

651

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

652

CREATE TABLE t2 (

653

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

654

cname char(15) NOT NULL default '',

655

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

656

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

657

last_mod_date timestamp NOT NULL,

658

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

659

last_app_date timestamp NOT NULL,

660

last_app_id smallint(6) default '-1',

661

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

662

assigned_scps int(11) default '0',

663

status tinyint(4) default '0'

664

) ENGINE=InnoDB;

665

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

666

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

667

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

668

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

669

select * from t1;

670

select * from t2;

671

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

672

select * from t1;

673

select * from t2;

674

select * from t2;

675

drop table t1,t2;

676

677

678

# A simple test with some isolation levels

679

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

680

# this works.

681

682

683

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;

684

685

BEGIN;

686

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

687

SELECT @@tx_isolation,@@global.tx_isolation;

688

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

689

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

690

COMMIT;

691

692

BEGIN;

693

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

694

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

695

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

696

COMMIT;

697

698

SET binlog_format='MIXED';

699

BEGIN;

700

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

701

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

702

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

703

COMMIT;

704

DROP TABLE t1;

705

706

707

# Test of multi-table-update

708

709

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

710

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

711

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

712

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

713

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

714

select * from t1;

715

select * from t2;

716

drop table t1,t2;

717

718

719

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

720

721

722

# prepare

723

--disable_warnings

724

drop table if exists t1, t2;

725

--enable_warnings

726

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

727

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

728

create trigger trg_del_t2 after delete on t2 for each row

729

insert into t1 values (1);

730

insert into t1 values (1);

731

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

732

733

734

# exec cases A, B - see multi_update.test

735

736

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

737

738

--error ER_DUP_ENTRY

739

delete t2 from t2;

740

741

# check

742

743

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

744

745

# cleanup bug#29136

746

747

drop table t1, t2;

748

749

750

751

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

752

753

754

# prepare

755

--disable_warnings

756

drop table if exists t1, t2;

757

--enable_warnings

758

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

759

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

760

create trigger trg_del_t2 after delete on t2 for each row

761

insert into t1 values (1);

762

insert into t1 values (1);

763

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

764

765

766

# exec cases A, B - see multi_update.test

767

768

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

769

770

--error ER_DUP_ENTRY

771

delete t2 from t2;

772

773

# check

774

775

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

776

777

# cleanup bug#29136

778

779

drop table t1, t2;

780

781

782

783

# Testing of IFNULL

784

785

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

786

insert into t1 values(20,null);

787

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

788

t2.b=t3.a;

789

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

790

t2.b=t3.a order by 1;

791

insert into t1 values(10,null);

792

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

793

t2.b=t3.a order by 1;

794

drop table t1;

795

796

797

# Test of read_through not existing const_table

798

799

800

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

801

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

802

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

803

drop table t1,t2;

804

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

805

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

806

insert into t1 values (10, 20);

807

insert into t2 values (10, 20);

808

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

809

drop table t1,t2;

810

811

812

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

813

814

815

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

816

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;

817

insert into t1 set id=1;

818

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

819

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

820

select * from t1;

821

select * from t2;

822

drop table t2,t1;

823

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

824

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

825

INSERT INTO t1 VALUES(1);

826

INSERT INTO t2 VALUES(1, 1);

827

SELECT * from t1;

828

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

829

SELECT * from t1;

830

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

831

SELECT * from t1;

832

DROP TABLE t1,t2;

833

834

835

# Test of range_optimizer

836

837

838

set autocommit=0;

839

840

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

841

842

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

843

844

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

845

846

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

847

COMMIT;

848

849

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

850

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

851

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

852

853

SELECT * FROM t1;

854

SELECT * FROM t2;

855

SELECT * FROM t3;

856

ROLLBACK;

857

858

SELECT * FROM t1;

859

SELECT * FROM t2;

860

SELECT * FROM t3;

861

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

862

COMMIT;

863

set autocommit=1;

864

DROP TABLE t1,t2,t3;

865

866

867

# Check update with conflicting key

868

869

870

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

871

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

872

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

873

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

874

SELECT * from t1;

875

drop table t1;

876

877

878

# Test multi update with different join methods

879

880

881

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

882

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

883

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

884

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

885

886

# Full join, without key

887

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

888

select * from t1;

889

890

# unique key

891

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

892

select * from t1;

893

894

# ref key

895

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

896

select * from t1;

897

898

# Range key (in t1)

899

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;

900

select * from t1;

901

select * from t2;

902

903

drop table t1,t2;

904

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

905

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

906

SET AUTOCOMMIT=0;

907

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

908

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

909

ROLLBACK;

910

SELECT * FROM t1;

911

drop table t1,t2;

912

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

913

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

914

select distinct parent,child from t1 order by parent;

915

drop table t1;

916

917

918

# Test that MySQL priorities clustered indexes

919

920

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

921

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

922

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

923

insert into t2 (a) select b from t1;

924

insert into t1 (b) select b from t2;

925

insert into t2 (a) select b from t1;

926

insert into t1 (a) select b from t2;

927

insert into t2 (a) select b from t1;

928

insert into t1 (a) select b from t2;

929

insert into t2 (a) select b from t1;

930

insert into t1 (a) select b from t2;

931

insert into t2 (a) select b from t1;

932

insert into t1 (a) select b from t2;

933

select count(*) from t1;

934

--replace_column 9 #

935

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

936

update t1 set c=a;

937

--replace_column 9 #

938

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

939

drop table t1,t2;

940

941

942

# Test of UPDATE ... ORDER BY

943

944

945

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

946

947

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

948

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

949

SELECT * from t1;

950

drop table t1;

951

952

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

953

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

954

SET @tmp=0;

955

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

956

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

957

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

958

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

959

select * from t1 order by a,b;

960

drop table t1;

961

962

963

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

964

965

966

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

967

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

968

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

969

970

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

971

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

972

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

973

update t1 set a=c, b=c;

974

975

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;

976

insert into t2 select * from t1;

977

978

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

979

drop table t1,t2;

980

981

982

# test autoincrement with TRUNCATE

983

984

985

SET AUTOCOMMIT=1;

986

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

987

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

988

truncate table t1;

989

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

990

SELECT * from t1;

991

drop table t1;

992

993

994

# Test dictionary handling with spaceand quoting

995

996

997

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

998

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;

999

#show create table t2;

1000

drop table t2,t1;

1001

1002

1003

# Test of multi updated and foreign keys

1004

1005

1006

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

1007

insert into `t1`values ( 1 ) ;

1008

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;

1009

insert into `t2`values ( 1 ) ;

1010

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;

1011

insert into `t3`values ( 1 ) ;

1012

--error 1451

1013

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

1014

--error 1451

1015

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;

1016

--error 1054

1017

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

1018

drop table t3,t2,t1;

1019

1020

1021

# test for recursion depth limit

1022

1023

create table t1(

1024

id int primary key,

1025

pid int,

1026

index(pid),

1027

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

1028

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

1029

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

1030

delete from t1 where id=0;

1031

delete from t1 where id=15;

1032

delete from t1 where id=0;

1033

1034

drop table t1;

1035

1036

1037

# Test timestamps

1038

1039

1040

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

1041

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

1042

(stamp))ENGINE=InnoDB;

1043

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

1044

# Note that timestamp 3 is wrong

1045

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

1046

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

1047

'20020204120000' GROUP BY col1;

1048

drop table t1,t2;

1049

1050

1051

# Test by Francois MASUREL

1052

1053

1054

CREATE TABLE t1 (

1055

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

1056

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

1057

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

1058

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

1059

`description` text,

1060

PRIMARY KEY (`id`),

1061

KEY `id_object` (`id_object`),

1062

KEY `id_version` (`id_version`)

1063

) ENGINE=InnoDB;

1064

1065

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

1066

1067

CREATE TABLE t2 (

1068

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

1069

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

1070

PRIMARY KEY (`id`),

1071

KEY `id_version` (`id_version`)

1072

) ENGINE=InnoDB;

1073

1074

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

1075

1076

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

1077

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

1078

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

1079

drop table t1,t2;

1080

1081

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

1082

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

1083

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

1084

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

1085

insert t2 select * from t1;

1086

insert t3 select * from t1;

1087

checksum table t1, t2, t3, t4 quick;

1088

checksum table t1, t2, t3, t4;

1089

checksum table t1, t2, t3, t4 extended;

1090

#show table status;

1091

drop table t1,t2,t3;

1092

1093

1094

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

1095

# (Bug #2552)

1096

1097

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

1098

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

1099

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

1100

drop table t1;

1101

1102

1103

# Bug2160

1104

1105

create table t1 (a int) engine=innodb;

1106

create table t2 like t1;

1107

drop table t1,t2;

1108

1109

1110

# Test of automaticly created foreign keys

1111

1112

1113

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

1114

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

1115

show create table t1;

1116

show create table t2;

1117

create index id on t2 (id);

1118

show create table t2;

1119

create index id2 on t2 (id);

1120

show create table t2;

1121

drop index id2 on t2;

1122

--error ER_DROP_INDEX_FK

1123

drop index id on t2;

1124

show create table t2;

1125

drop table t2;

1126

1127

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;

1128

show create table t2;

1129

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

1130

show create table t2;

1131

drop table t2;

1132

1133

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

1134

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;

1135

show create table t2;

1136

drop table t2;

1137

1138

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;

1139

show create table t2;

1140

drop table t2;

1141

1142

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;

1143

show create table t2;

1144

drop table t2;

1145

1146

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;

1147

show create table t2;

1148

drop table t2;

1149

1150

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;

1151

show create table t2;

1152

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

1153

show create table t2;

1154

drop table t2;

1155

1156

# Test error handling

1157

1158

# Embedded server doesn't chdir to data directory

1159

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

1160

--error ER_WRONG_FK_DEF

1161

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;

1162

1163

# bug#3749

1164

1165

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

1166

show create table t2;

1167

drop table t2;

1168

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;

1169

show create table t2;

1170

drop table t2, t1;

1171

1172

1173

1174

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

1175

1176

--error 1060

1177

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

1178

--error 1060

1179

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

1180

--error 1060

1181

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

1182

--error 1060

1183

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

1184

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

1185

--error 1060

1186

alter table t1 add key (c1,c1);

1187

--error 1060

1188

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

1189

--error 1060

1190

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

1191

--error 1060

1192

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

1193

drop table t1;

1194

1195

1196

# Bug #4082: integer truncation

1197

1198

1199

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

1200

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

1201

select distinct concat(a, b) from t1;

1202

drop table t1;

1203

1204

1205

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

1206

# engines does not fail

1207

1208

1209

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

1210

--error 1214

1211

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

1212

DROP TABLE t1;

1213

1214

1215

# check null values #1

1216

1217

1218

--disable_warnings

1219

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

1220

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

1221

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

1222

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

1223

--enable_warnings

1224

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

1225

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;

1226

DROP TABLE t2;

1227

DROP TABLE t1;

1228

1229

1230

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

1231

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

1232

1233

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

1234

insert into t1 values (4711);

1235

truncate t1;

1236

insert into t1 values (42);

1237

select * from t1;

1238

drop table t1;

1239

# Show that it works with permanent tables too.

1240

create table t1 (a int) engine=innodb;

1241

insert into t1 values (4711);

1242

truncate t1;

1243

insert into t1 values (42);

1244

select * from t1;

1245

drop table t1;

1246

1247

1248

# Bug #13025 Server crash during filesort

1249

1250

1251

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;

1252

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

1253

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

1254

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

1255

drop table t1;

1256

1257

1258

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

1259

1260

1261

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

1262

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

1263

select min(a) from t1;

1264

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

1265

drop table t1;

1266

1267

# End of 4.1 tests

1268

1269

1270

# range optimizer problem

1271

1272

1273

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

1274

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

1275

select * from t1;

1276

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

1277

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

1278

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

1279

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

1280

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

1281

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

1282

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

1283

select * from t1 where x > -16;

1284

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

1285

drop table t1;

1286

1287

1288

# Test for testable InnoDB status variables. This test

1289

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

1290

--replace_result 512 511

1291

show status like "Innodb_buffer_pool_pages_total";

1292

show status like "Innodb_page_size";

1293

show status like "Innodb_rows_deleted";

1294

show status like "Innodb_rows_inserted";

1295

show status like "Innodb_rows_updated";

1296

1297

# Test for row locks InnoDB status variables.

1298

show status like "Innodb_row_lock_waits";

1299

show status like "Innodb_row_lock_current_waits";

1300

show status like "Innodb_row_lock_time";

1301

show status like "Innodb_row_lock_time_max";

1302

show status like "Innodb_row_lock_time_avg";

1303

1304

# Test for innodb_sync_spin_loops variable

1305

show variables like "innodb_sync_spin_loops";

1306

set global innodb_sync_spin_loops=1000;

1307

show variables like "innodb_sync_spin_loops";

1308

set global innodb_sync_spin_loops=0;

1309

show variables like "innodb_sync_spin_loops";

1310

set global innodb_sync_spin_loops=20;

1311

show variables like "innodb_sync_spin_loops";

1312

1313

# Test for innodb_thread_concurrency variable

1314

show variables like "innodb_thread_concurrency";

1315

set global innodb_thread_concurrency=1001;

1316

show variables like "innodb_thread_concurrency";

1317

set global innodb_thread_concurrency=0;

1318

show variables like "innodb_thread_concurrency";

1319

set global innodb_thread_concurrency=16;

1320

show variables like "innodb_thread_concurrency";

1321

1322

# Test for innodb_concurrency_tickets variable

1323

show variables like "innodb_concurrency_tickets";

1324

set global innodb_concurrency_tickets=1000;

1325

show variables like "innodb_concurrency_tickets";

1326

set global innodb_concurrency_tickets=0;

1327

show variables like "innodb_concurrency_tickets";

1328

set global innodb_concurrency_tickets=500;

1329

show variables like "innodb_concurrency_tickets";

1330

1331

# Test for innodb_thread_sleep_delay variable

1332

show variables like "innodb_thread_sleep_delay";

1333

set global innodb_thread_sleep_delay=100000;

1334

show variables like "innodb_thread_sleep_delay";

1335

set global innodb_thread_sleep_delay=0;

1336

show variables like "innodb_thread_sleep_delay";

1337

set global innodb_thread_sleep_delay=10000;

1338

show variables like "innodb_thread_sleep_delay";

1339

1340

1341

# Test varchar

1342

1343

1344

let $default=`select @@storage_engine`;

1345

set storage_engine=INNODB;

1346

source include/varchar.inc;

1347

1348

1349

# Some errors/warnings on create

1350

1351

1352

# Embedded server doesn't chdir to data directory

1353

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

1354

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

1355

drop table t1;

1356

create table t1 (v varchar(65536));

1357

show create table t1;

1358

drop table t1;

1359

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

1360

show create table t1;

1361

drop table t1;

1362

1363

eval set storage_engine=$default;

1364

1365

# InnoDB specific varchar tests

1366

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

1367

drop table t1;

1368

1369

1370

# BUG#11039 Wrong key length in min()

1371

1372

1373

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

1374

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

1375

select min(a) from t1;

1376

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

1377

drop table t1;

1378

1379

1380

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

1381

1382

1383

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

1384

insert into t1 (b) values (1);

1385

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

1386

select * from t1;

1387

truncate table t1;

1388

insert into t1 (b) values (1);

1389

replace into t1 (b) values (2);

1390

replace into t1 (b) values (1);

1391

replace into t1 (b) values (3);

1392

select * from t1;

1393

drop table t1;

1394

1395

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

1396

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

1397

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

1398

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

1399

--error ER_DUP_ENTRY

1400

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

1401

select * from t1;

1402

drop table t1;

1403

1404

1405

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

1406

1407

1408

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

1409

insert into t1 (val) values (1);

1410

update t1 set a=2 where a=1;

1411

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

1412

--error ER_DUP_ENTRY

1413

insert into t1 (val) values (1);

1414

select * from t1;

1415

drop table t1;

1416

1417

# Bug #10465

1418

1419

1420

--disable_warnings

1421

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

1422

--enable_warnings

1423

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

1424

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

1425

SELECT GRADE FROM t1 WHERE GRADE= 151;

1426

DROP TABLE t1;

1427

1428

1429

# Bug #12340 multitable delete deletes only one record

1430

1431

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

1432

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

1433

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

1434

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

1435

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

1436

select * from t1;

1437

drop table t1,t2;

1438

1439

1440

# Test that the slow TRUNCATE implementation resets autoincrement columns

1441

# (bug #11946)

1442

1443

1444

CREATE TABLE t1 (

1445

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1446

) ENGINE=InnoDB;

1447

1448

CREATE TABLE t2 (

1449

id INTEGER NOT NULL,

1450

FOREIGN KEY (id) REFERENCES t1 (id)

1451

) ENGINE=InnoDB;

1452

1453

INSERT INTO t1 (id) VALUES (NULL);

1454

SELECT * FROM t1;

1455

TRUNCATE t1;

1456

INSERT INTO t1 (id) VALUES (NULL);

1457

SELECT * FROM t1;

1458

1459

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

1460

# rows resets autoincrement columns

1461

DELETE FROM t1;

1462

TRUNCATE t1;

1463

INSERT INTO t1 (id) VALUES (NULL);

1464

SELECT * FROM t1;

1465

DROP TABLE t2, t1;

1466

1467

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

1468

CREATE TABLE t1

1469

(

1470

id INT PRIMARY KEY

1471

) ENGINE=InnoDB;

1472

1473

--error 1005,1005

1474

CREATE TEMPORARY TABLE t2

1475

(

1476

id INT NOT NULL PRIMARY KEY,

1477

b INT,

1478

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

1479

) ENGINE=InnoDB;

1480

DROP TABLE t1;

1481

1482

1483

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

1484

1485

1486

# prefix index

1487

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

1488

character set = latin1 engine = innodb;

1489

1490

# normal indexes

1491

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

1492

character set = latin1 engine = innodb;

1493

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

1494

character set = latin1 engine = innodb;

1495

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

1496

character set = latin1 engine = innodb;

1497

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

1498

character set = latin1 engine = innodb;

1499

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

1500

character set = latin1 engine = innodb;

1501

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

1502

character set = latin1 engine = innodb;

1503

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

1504

character set = latin1 engine = innodb;

1505

1506

# multi-column indexes are allowed to be longer

1507

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

1508

character set = latin1 engine = innodb;

1509

1510

show create table t9;

1511

1512

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

1513

1514

# these should have their index length trimmed

1515

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

1516

character set = latin1 engine = innodb;

1517

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

1518

character set = latin1 engine = innodb;

1519

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

1520

character set = latin1 engine = innodb;

1521

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

1522

character set = latin1 engine = innodb;

1523

1524

show create table t1;

1525

1526

drop table t1, t2, t3, t4;

1527

1528

# these should be refused

1529

--error 1071

1530

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

1531

character set = latin1 engine = innodb;

1532

--error 1071

1533

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

1534

character set = latin1 engine = innodb;

1535

--error 1071

1536

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

1537

character set = latin1 engine = innodb;

1538

--error 1071

1539

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

1540

character set = latin1 engine = innodb;

1541

1542

1543

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

1544

1545

1546

CREATE TABLE t1

1547

(

1548

id INT PRIMARY KEY

1549

) ENGINE=InnoDB;

1550

1551

CREATE TABLE t2

1552

(

1553

v INT,

1554

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

1555

) ENGINE=InnoDB;

1556

1557

--error 1452

1558

INSERT INTO t2 VALUES(2);

1559

1560

INSERT INTO t1 VALUES(1);

1561

INSERT INTO t2 VALUES(1);

1562

1563

--error 1451

1564

DELETE FROM t1 WHERE id = 1;

1565

1566

--error 1217

1567

DROP TABLE t1;

1568

1569

SET FOREIGN_KEY_CHECKS=0;

1570

DROP TABLE t1;

1571

SET FOREIGN_KEY_CHECKS=1;

1572

1573

--error 1452

1574

INSERT INTO t2 VALUES(3);

1575

1576

DROP TABLE t2;

1577

1578

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

1579

1580

connect (a,localhost,root,,);

1581

connect (b,localhost,root,,);

1582

connection a;

1583

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

1584

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

1585

set autocommit=0;

1586

checksum table t1;

1587

connection b;

1588

insert into t1 values(3);

1589

connection a;

1590

1591

# Here checksum should not see insert

1592

1593

checksum table t1;

1594

connection a;

1595

commit;

1596

checksum table t1;

1597

commit;

1598

drop table t1;

1599

1600

# autocommit = 1

1601

1602

connection a;

1603

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

1604

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

1605

set autocommit=1;

1606

checksum table t1;

1607

connection b;

1608

set autocommit=1;

1609

insert into t1 values(3);

1610

connection a;

1611

1612

# Here checksum sees insert

1613

1614

checksum table t1;

1615

drop table t1;

1616

1617

connection default;

1618

disconnect a;

1619

disconnect b;

1620

1621

# tests for bugs #9802 and #13778

1622

1623

# test that FKs between invalid types are not accepted

1624

1625

set foreign_key_checks=0;

1626

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

1627

# Embedded server doesn't chdir to data directory

1628

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

1629

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

1630

set foreign_key_checks=1;

1631

drop table t2;

1632

1633

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

1634

# when f_k_c is 0

1635

1636

set foreign_key_checks=0;

1637

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

1638

# Embedded server doesn't chdir to data directory

1639

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

1640

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

1641

set foreign_key_checks=1;

1642

drop table t1;

1643

1644

# test that invalid datatype conversions with ALTER are not allowed

1645

1646

set foreign_key_checks=0;

1647

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

1648

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

1649

alter table t1 modify column a int;

1650

set foreign_key_checks=1;

1651

drop table t2,t1;

1652

1653

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

1654

1655

set foreign_key_checks=0;

1656

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

1657

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

1658

alter table t1 convert to character set utf8;

1659

set foreign_key_checks=1;

1660

drop table t2,t1;

1661

1662

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

1663

1664

set foreign_key_checks=0;

1665

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

1666

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

1667

# Embedded server doesn't chdir to data directory

1668

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

1669

rename table t3 to t1;

1670

set foreign_key_checks=1;

1671

drop table t2,t3;

1672

1673

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

1674

1675

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

1676

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

1677

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

1678

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

1679

1680

insert into t1 values(1);

1681

insert into t3 values(1);

1682

insert into t2 values(2);

1683

insert into t4 values(2);

1684

insert into t2 values(1);

1685

insert into t4 values(1);

1686

update t1 set a=2;

1687

update t2 set a=2;

1688

update t3 set a=2;

1689

update t4 set a=2;

1690

truncate t1;

1691

truncate t3;

1692

truncate t2;

1693

truncate t4;

1694

truncate t1;

1695

truncate t3;

1696

1697

drop table t4,t3,t2,t1;

1698

1699

1700

1701

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

1702

1703

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

1704

b varchar(255) character set utf8,

1705

c varchar(255) character set utf8,

1706

d varchar(255) character set utf8,

1707

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

1708

drop table t1;

1709

--error ER_TOO_LONG_KEY

1710

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

1711

b varchar(255) character set utf8,

1712

c varchar(255) character set utf8,

1713

d varchar(255) character set utf8,

1714

e varchar(255) character set utf8,

1715

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

1716

1717

1718

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

1719

1720

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

1721

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

1722

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

1723

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

1724

1725

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

1726

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

1727

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

1728

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

1729

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

1730

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

1731

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

1732

select hex(s1) from t1;

1733

select hex(s1) from t2;

1734

select hex(s1) from t3;

1735

select hex(s1) from t4;

1736

drop table t1,t2,t3,t4;

1737

1738

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

1739

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

1740

1741

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

1742

insert into t2 values(0x42);

1743

insert into t2 values(0x41);

1744

select hex(s1) from t2;

1745

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

1746

select hex(s1) from t2;

1747

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

1748

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

1749

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

1750

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

1751

select hex(s1) from t2;

1752

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

1753

select hex(s1) from t2;

1754

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

1755

select hex(s1) from t2;

1756

delete from t1 where a=1;

1757

delete from t1 where a=2;

1758

update t2 set s1=0x4120;

1759

delete from t1;

1760

delete from t1 where a!=3;

1761

select a,hex(s1) from t1;

1762

select hex(s1) from t2;

1763

1764

drop table t2,t1;

1765

1766

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

1767

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

1768

1769

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

1770

insert into t2 values(0x41);

1771

select hex(s1) from t2;

1772

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

1773

select hex(s1) from t2;

1774

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

1775

select hex(s1) from t2;

1776

delete from t1 where a=1;

1777

delete from t1 where a=2;

1778

select a,hex(s1) from t1;

1779

select hex(s1) from t2;

1780

1781

drop table t2,t1;

1782

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

1783

# generated foreign key identifier. (Bug #16387)

1784

1785

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

1786

CREATE TABLE t2(a INT) ENGINE=InnoDB;

1787

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

1788

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1789

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

1790

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1791

SHOW CREATE TABLE t2;

1792

DROP TABLE t2,t1;

1793

1794

1795

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

1796

1797

1798

connect (a,localhost,root,,);

1799

connect (b,localhost,root,,);

1800

connection a;

1801

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

1802

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

1803

commit;

1804

connection b;

1805

set autocommit = 0;

1806

update t1 set b = 5 where a = 2;

1807

connection a;

1808

delimiter |;

1809

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

1810

delimiter ;|

1811

set autocommit = 0;

1812

connection a;

1813

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

1814

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

1815

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

1816

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

1817

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

1818

connection b;

1819

commit;

1820

connection a;

1821

commit;

1822

drop trigger t1t;

1823

drop table t1;

1824

disconnect a;

1825

disconnect b;

1826

1827

# Another trigger test

1828

1829

connect (a,localhost,root,,);

1830

connect (b,localhost,root,,);

1831

connection a;

1832

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

1833

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

1834

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

1835

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

1836

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

1837

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

1838

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

1839

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

1840

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

1841

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

1842

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

1843

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

1844

1845

delimiter |;

1846

create trigger t1t before insert on t1 for each row begin

1847

INSERT INTO t2 SET a = NEW.a;

1848

end |

1849

1850

create trigger t2t before insert on t2 for each row begin

1851

DELETE FROM t3 WHERE a = NEW.a;

1852

end |

1853

1854

create trigger t3t before delete on t3 for each row begin

1855

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

1856

end |

1857

1858

create trigger t4t before update on t4 for each row begin

1859

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

1860

end |

1861

delimiter ;|

1862

commit;

1863

set autocommit = 0;

1864

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

1865

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

1866

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

1867

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

1868

insert into t5(a) values(20);

1869

connection b;

1870

set autocommit = 0;

1871

insert into t1(a) values(7);

1872

insert into t2(a) values(8);

1873

delete from t2 where a = 3;

1874

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

1875

commit;

1876

drop trigger t1t;

1877

drop trigger t2t;

1878

drop trigger t3t;

1879

drop trigger t4t;

1880

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

1881

connection default;

1882

disconnect a;

1883

disconnect b;

1884

1885

1886

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

1887

# error message (bug #9680)

1888

1889

1890

CREATE TABLE t1 (

1891

field1 varchar(8) NOT NULL DEFAULT '',

1892

field2 varchar(8) NOT NULL DEFAULT '',

1893

PRIMARY KEY (field1, field2)

1894

) ENGINE=InnoDB;

1895

1896

CREATE TABLE t2 (

1897

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

1898

FOREIGN KEY (field1) REFERENCES t1 (field1)

1899

ON DELETE CASCADE ON UPDATE CASCADE

1900

) ENGINE=InnoDB;

1901

1902

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

1903

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

1904

1905

INSERT INTO t2 VALUES ('old');

1906

INSERT INTO t2 VALUES ('other');

1907

1908

--error ER_FOREIGN_DUPLICATE_KEY

1909

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

1910

1911

DROP TABLE t2;

1912

DROP TABLE t1;

1913

1914

1915

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

1916

1917

create table t1 (

1918

c1 bigint not null,

1919

c2 bigint not null,

1920

primary key (c1),

1921

unique key (c2)

1922

) engine=innodb;

1923

1924

create table t2 (

1925

c1 bigint not null,

1926

primary key (c1)

1927

) engine=innodb;

1928

1929

alter table t1 add constraint c2_fk foreign key (c2)

1930

references t2(c1) on delete cascade;

1931

show create table t1;

1932

1933

alter table t1 drop foreign key c2_fk;

1934

show create table t1;

1935

1936

drop table t1, t2;

1937

1938

1939

# Bug #14360: problem with intervals

1940

1941

1942

create table t1(a date) engine=innodb;

1943

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

1944

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

1945

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

1946

select * from t1, t2

1947

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

1948

drop table t1, t2;

1949

1950

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

1951

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

1952

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

1953

primary key(id)) engine=innodb;

1954

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

1955

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

1956

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

1957

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

1958

commit;

1959

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

1960

WHERE mm.id IS NULL;

1961

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

1962

where mm.id is null lock in share mode;

1963

drop table t1,t2;

1964

1965

1966

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

1967

# update (because READ COMMITTED isolation level)

1968

1969

1970

connect (a,localhost,root,,);

1971

connect (b,localhost,root,,);

1972

connection a;

1973

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

1974

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

1975

commit;

1976

SET binlog_format='MIXED';

1977

set autocommit = 0;

1978

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1979

update t1 set b = 5 where b = 1;

1980

connection b;

1981

SET binlog_format='MIXED';

1982

set autocommit = 0;

1983

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1984

1985

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

1986

1987

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

1988

connection a;

1989

commit;

1990

connection b;

1991

commit;

1992

drop table t1;

1993

connection default;

1994

disconnect a;

1995

disconnect b;

1996

1997

1998

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

1999

# using READ COMMITTED isolation level)

2000

2001

2002

connect (a,localhost,root,,);

2003

connect (b,localhost,root,,);

2004

connection a;

2005

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

2006

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

2007

commit;

2008

set autocommit = 0;

2009

select * from t1 lock in share mode;

2010

update t1 set b = 5 where b = 1;

2011

connection b;

2012

set autocommit = 0;

2013

2014

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

2015

2016

--error 1205

2017

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

2018

2019

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

2020

2021

--error 1205

2022

connection a;

2023

commit;

2024

connection b;

2025

commit;

2026

connection default;

2027

disconnect a;

2028

disconnect b;

2029

drop table t1;

2030

2031

2032

# Consistent read should be used in following selects

2033

2034

# 1) INSERT INTO ... SELECT

2035

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

2036

# 3) CREATE ... SELECT

2037

2038

connect (a,localhost,root,,);

2039

connect (b,localhost,root,,);

2040

connection a;

2041

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

2042

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

2043

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

2044

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

2045

commit;

2046

set autocommit = 0;

2047

select * from t2 for update;

2048

connection b;

2049

SET binlog_format='MIXED';

2050

set autocommit = 0;

2051

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2052

insert into t1 select * from t2;

2053

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

2054

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

2055

select * from t2;

2056

commit;

2057

connection a;

2058

commit;

2059

connection default;

2060

disconnect a;

2061

disconnect b;

2062

drop table t1, t2, t3;

2063

2064

2065

# Consistent read should not be used if

2066

2067

# (a) isolation level is serializable OR

2068

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

2069

# (c) select ... for update

2070

2071

# in following queries:

2072

2073

# 1) INSERT INTO ... SELECT

2074

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

2075

# 3) CREATE ... SELECT

2076

2077

connect (a,localhost,root,,);

2078

connect (b,localhost,root,,);

2079

connect (c,localhost,root,,);

2080

connect (d,localhost,root,,);

2081

connect (e,localhost,root,,);

2082

connect (f,localhost,root,,);

2083

connect (g,localhost,root,,);

2084

connect (h,localhost,root,,);

2085

connect (i,localhost,root,,);

2086

connect (j,localhost,root,,);

2087

connection a;

2088

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

2089

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

2090

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

2091

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

2092

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

2093

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

2094

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

2095

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

2096

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

2097

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

2098

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

2099

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

2100

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

2101

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

2102

commit;

2103

set autocommit = 0;

2104

select * from t2 for update;

2105

connection b;

2106

SET binlog_format='MIXED';

2107

set autocommit = 0;

2108

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2109

--send

2110

insert into t1 select * from t2;

2111

connection c;

2112

SET binlog_format='MIXED';

2113

set autocommit = 0;

2114

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2115

--send

2116

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

2117

connection d;

2118

SET binlog_format='MIXED';

2119

set autocommit = 0;

2120

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2121

--send

2122

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

2123

connection e;

2124

SET binlog_format='MIXED';

2125

set autocommit = 0;

2126

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2127

--send

2128

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

2129

connection f;

2130

SET binlog_format='MIXED';

2131

set autocommit = 0;

2132

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2133

--send

2134

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

2135

connection g;

2136

SET binlog_format='MIXED';

2137

set autocommit = 0;

2138

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2139

--send

2140

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

2141

connection h;

2142

SET binlog_format='MIXED';

2143

set autocommit = 0;

2144

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2145

--send

2146

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

2147

connection i;

2148

SET binlog_format='MIXED';

2149

set autocommit = 0;

2150

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2151

--send

2152

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

2153

connection j;

2154

SET binlog_format='MIXED';

2155

set autocommit = 0;

2156

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2157

--send

2158

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

2159

2160

connection b;

2161

--error 1205

2162

reap;

2163

2164

connection c;

2165

--error 1205

2166

reap;

2167

2168

connection d;

2169

--error 1205

2170

reap;

2171

2172

connection e;

2173

--error 1205

2174

reap;

2175

2176

connection f;

2177

--error 1205

2178

reap;

2179

2180

connection g;

2181

--error 1205

2182

reap;

2183

2184

connection h;

2185

--error 1205

2186

reap;

2187

2188

connection i;

2189

--error 1205

2190

reap;

2191

2192

connection j;

2193

--error 1205

2194

reap;

2195

2196

connection a;

2197

commit;

2198

2199

connection default;

2200

disconnect a;

2201

disconnect b;

2202

disconnect c;

2203

disconnect d;

2204

disconnect e;

2205

disconnect f;

2206

disconnect g;

2207

disconnect h;

2208

disconnect i;

2209

disconnect j;

2210

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

2211

2212

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

2213

--error 1005

2214

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

2215

2216

2217

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

2218

2219

2220

CREATE TABLE t1 (

2221

a BIGINT(20) NOT NULL,

2222

PRIMARY KEY (a)

2223

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2224

2225

CREATE TABLE t2 (

2226

a BIGINT(20) NOT NULL,

2227

b VARCHAR(128) NOT NULL,

2228

c TEXT NOT NULL,

2229

PRIMARY KEY (a,b),

2230

KEY idx_t2_b_c (b,c(200)),

2231

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

2232

ON DELETE CASCADE

2233

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2234

2235

INSERT INTO t1 VALUES (1);

2236

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

2237

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

2238

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

2239

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

2240

2241

SELECT * FROM t2 WHERE b = 'customer_over';

2242

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

2243

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

2244

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

2245

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

2246

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

2247

2248

drop table t2, t1;

2249

2250

2251

# Test optimize on table with open transaction

2252

2253

2254

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2255

BEGIN;

2256

INSERT INTO t1 VALUES (1);

2257

OPTIMIZE TABLE t1;

2258

DROP TABLE t1;

2259

2260

2261

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

2262

2263

2264

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

2265

2266

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

2267

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

2268

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

2269

2270

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

2271

DELETE CASCADE ON UPDATE CASCADE;

2272

2273

SHOW CREATE TABLE t2;

2274

DROP TABLE t2, t1;

2275

2276

2277

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

2278

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

2279

2280

2281

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

2282

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

2283

INSERT INTO t1 VALUES (1);

2284

INSERT INTO t2 VALUES (1);

2285

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

2286

# mysqltest first does replace_regex, then replace_result

2287

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

2288

# Embedded server doesn't chdir to data directory

2289

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

2290

--error 1025

2291

ALTER TABLE t2 MODIFY a INT NOT NULL;

2292

DELETE FROM t1;

2293

DROP TABLE t2,t1;

2294

2295

2296

# Bug #26835: table corruption after delete+insert

2297

2298

2299

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

2300

ENGINE=InnoDB;

2301

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

2302

DELETE FROM t1;

2303

INSERT INTO t1 VALUES ('DDD');

2304

SELECT * FROM t1;

2305

DROP TABLE t1;

2306

2307

2308

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

2309

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

2310

2311

2312

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

2313

AUTO_INCREMENT=42;

2314

2315

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

2316

SELECT * FROM t1;

2317

2318

SHOW CREATE TABLE t1;

2319

2320

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

2321

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

2322

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

2323

SHOW CREATE TABLE t1;

2324

2325

DROP TABLE t1,t2;

2326

2327

2328

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

2329

2330

set innodb_strict_mode=on;

2331

--error 1118

2332

CREATE TABLE t1 (

2333

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

2334

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

2335

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

2336

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

2337

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

2338

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

2339

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

2340

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

2341

) ENGINE = InnoDB;

2342

2343

2344

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

2345

2346

DROP TABLE IF EXISTS t1;

2347

CREATE TABLE t1(

2348

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

2349

) ENGINE=InnoDB;

2350

INSERT INTO t1 VALUES(-10);

2351

SELECT * FROM t1;

2352

2353

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

2354

# for the test to be useful.

2355

2356

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

2357

INSERT INTO t1 VALUES(NULL);

2358

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

2359

SELECT * FROM t1;

2360

DROP TABLE t1;

2361

2362

2363

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

2364

# query_cache ON

2365

2366

CONNECT (c1,localhost,root,,);

2367

CONNECT (c2,localhost,root,,);

2368

CONNECTION c1;

2369

SET binlog_format='MIXED';

2370

SET TX_ISOLATION='read-committed';

2371

SET AUTOCOMMIT=0;

2372

DROP TABLE IF EXISTS t1, t2;

2373

CREATE TABLE t1 ( a int ) ENGINE=InnoDB;

2374

CREATE TABLE t2 LIKE t1;

2375

SELECT * FROM t2;

2376

CONNECTION c2;

2377

SET binlog_format='MIXED';

2378

SET TX_ISOLATION='read-committed';

2379

SET AUTOCOMMIT=0;

2380

INSERT INTO t1 VALUES (1);

2381

COMMIT;

2382

CONNECTION c1;

2383

SELECT * FROM t1 WHERE a=1;

2384

DISCONNECT c1;

2385

DISCONNECT c2;

2386

CONNECT (c1,localhost,root,,);

2387

CONNECT (c2,localhost,root,,);

2388

CONNECTION c1;

2389

SET binlog_format='MIXED';

2390

SET TX_ISOLATION='read-committed';

2391

SET AUTOCOMMIT=0;

2392

SELECT * FROM t2;

2393

CONNECTION c2;

2394

SET binlog_format='MIXED';

2395

SET TX_ISOLATION='read-committed';

2396

SET AUTOCOMMIT=0;

2397

INSERT INTO t1 VALUES (2);

2398

COMMIT;

2399

CONNECTION c1;

2400

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

2401

SELECT * FROM t1 WHERE a=2;

2402

SELECT * FROM t1 WHERE a=2;

2403

DROP TABLE t1;

2404

DROP TABLE t2;

2405

DISCONNECT c1;

2406

DISCONNECT c2;

2407

CONNECTION default;

2408

2409

2410

# Bug #29157 UPDATE, changed rows incorrect

2411

2412

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

2413

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

2414

--enable_info

2415

update t1 set j = 2;

2416

--disable_info

2417

drop table t1;

2418

2419

2420

# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or

2421

# I_S

2422

2423

create table t1 (id int) comment='this is a comment' engine=innodb;

2424

select table_comment, data_free > 0 as data_free_is_set

2425

from information_schema.tables

2426

where table_schema='test' and table_name = 't1';

2427

drop table t1;

2428

2429

2430

# Bug 34920 test

2431

2432

CONNECTION default;

2433

CREATE TABLE t1 (

2434

c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

2435

c2 VARCHAR(128) NOT NULL,

2436

PRIMARY KEY(c1)

2437

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;

2438

2439

CREATE TABLE t2 (

2440

c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

2441

c2 INT(10) UNSIGNED DEFAULT NULL,

2442

PRIMARY KEY(c1)

2443

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;

2444

2445

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';

2446

ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);

2447

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';

2448

DROP TABLE t2;

2449

DROP TABLE t1;

2450

# End 34920 test

2451

2452

# Bug #29507 TRUNCATE shows to many rows effected

2453

2454

CONNECTION default;

2455

CREATE TABLE t1 (c1 int default NULL,

2456

c2 int default NULL

2457

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2458

2459

--enable_info

2460

TRUNCATE TABLE t1;

2461

2462

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

2463

TRUNCATE TABLE t1;

2464

2465

--disable_info

2466

DROP TABLE t1;

2467

2468

# Bug#35537 Innodb doesn't increment handler_update and handler_delete.

2469

2470

2471

CONNECT (c1,localhost,root,,);

2472

2473

DROP TABLE IF EXISTS bug35537;

2474

CREATE TABLE bug35537 (

2475

c1 int

2476

) ENGINE=InnoDB;

2477

2478

INSERT INTO bug35537 VALUES (1);

2479

2480

2481

SHOW SESSION STATUS LIKE 'Handler_update%';

2482

SHOW SESSION STATUS LIKE 'Handler_delete%';

2483

2484

UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;

2485

DELETE FROM bug35537 WHERE c1 = 2;

2486

2487

SHOW SESSION STATUS LIKE 'Handler_update%';

2488

SHOW SESSION STATUS LIKE 'Handler_delete%';

2489

2490

DROP TABLE bug35537;

2491

2492

DISCONNECT c1;

2493

CONNECTION default;

2494

2495

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

2496

# #

2497

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

2498

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

2499

# #

2500

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

2501

# #

2502

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

2503

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

2504

# (otherwise your changes may be erased). #

2505

# #

2506

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

Older »