~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits');

SET @innodb_row_lock_time_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time');

SET @innodb_row_lock_time_max_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max');

SET @innodb_row_lock_time_avg_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg');

-- enable_query_log

--disable_warnings

drop table if exists t1,t2,t3,t4;

drop database if exists mysqltest;

--enable_warnings

# Small basic test with ignore

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;

100

101

# Test replace

102

103

104

CREATE TABLE t1 (

105

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

106

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

107

PRIMARY KEY (gesuchnr,benutzer_id)

108

) engine=innodb;

109

110

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

111

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

112

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

113

select * from t1;

114

drop table t1;

115

116

117

# test delete using hidden_primary_key

118

119

120

create table t1 (a int) engine=innodb;

121

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

122

optimize table t1;

123

delete from t1 where a = 1;

124

select * from t1;

125

check table t1;

126

drop table t1;

127

128

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

129

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

130

delete from t1 where a = 1;

131

select * from t1;

132

create index skr on t1 (a);

133

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

134

analyze table t1;

135

--replace_column 7 #

136

show keys from t1;

137

drop table t1;

138

139

140

# Test of reading on secondary key with may be null

141

142

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

143

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

144

select * from t1 where a = 1;

145

drop table t1;

146

147

148

# Test rollback

149

150

151

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

152

set autocommit=0;

153

insert into t1 values (4);

154

rollback;

155

select n, "after rollback" from t1;

156

insert into t1 values (4);

157

commit;

158

select n, "after commit" from t1;

159

commit;

160

insert into t1 values (5);

161

-- error ER_DUP_ENTRY

162

insert into t1 values (4);

163

commit;

164

select n, "after commit" from t1;

165

set autocommit=1;

166

insert into t1 values (6);

167

-- error ER_DUP_ENTRY

168

insert into t1 values (4);

169

select n from t1;

170

set autocommit=0;

171

172

# savepoints

173

174

begin;

175

savepoint `my_savepoint`;

176

insert into t1 values (7);

177

savepoint `savept2`;

178

insert into t1 values (3);

179

select n from t1;

180

savepoint savept3;

181

rollback to savepoint savept2;

182

--error 1305

183

rollback to savepoint savept3;

184

rollback to savepoint savept2;

185

release savepoint `my_savepoint`;

186

select n from t1;

187

-- error 1305

188

rollback to savepoint `my_savepoint`;

189

--error 1305

190

rollback to savepoint savept2;

191

insert into t1 values (8);

192

savepoint sv;

193

commit;

194

savepoint sv;

195

set autocommit=1;

196

# nop

197

rollback;

198

drop table t1;

199

200

201

# Test for commit and FLUSH TABLES WITH READ LOCK

202

203

204

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

205

start transaction;

206

insert into t1 values (4);

207

flush tables with read lock;

208

209

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

210

#--error 1223;

211

commit;

212

unlock tables;

213

commit;

214

select * from t1;

215

drop table t1;

216

217

218

# Testing transactions

219

220

221

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

222

begin;

223

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

224

select id as afterbegin_id,nom as afterbegin_nom from t1;

225

rollback;

226

select id as afterrollback_id,nom as afterrollback_nom from t1;

227

set autocommit=0;

228

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

229

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

230

rollback;

231

select id as afterrollback_id,nom as afterrollback_nom from t1;

232

set autocommit=1;

233

drop table t1;

234

235

236

# Simple not autocommit test

237

238

239

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

240

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

241

-- error ER_DUP_ENTRY

242

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

243

delete from t1;

244

delete from t1 where id = 'pippo';

245

select * from t1;

246

247

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

248

set autocommit=0;

249

delete from t1;

250

rollback;

251

select * from t1;

252

delete from t1;

253

commit;

254

select * from t1;

255

drop table t1;

256

257

258

# Test of active transactions

259

260

261

create table t1 (a integer) engine=innodb;

262

start transaction;

263

rename table t1 to t2;

264

create table t1 (b integer) engine=innodb;

265

insert into t1 values (1);

266

rollback;

267

drop table t1;

268

rename table t2 to t1;

269

drop table t1;

270

set autocommit=1;

271

272

273

# The following simple tests failed at some point

274

275

276

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

277

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

278

select * from t1;

279

drop table t1;

280

281

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

282

set autocommit=0;

283

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

284

COMMIT;

285

SELECT * FROM t1;

286

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

287

drop table t1;

288

set autocommit=1;

289

290

291

# Test when reading on part of unique key

292

293

CREATE TABLE t1 (

294

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

295

name varchar(100),

296

phone varchar(100),

297

ref_email varchar(100) DEFAULT '' NOT NULL,

298

detail varchar(200),

299

PRIMARY KEY (user_id,ref_email)

300

)engine=innodb;

301

302

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

303

select * from t1 where user_id=10292;

304

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

305

select * from t1 where user_id=10292;

306

select * from t1 where user_id>=10292;

307

select * from t1 where user_id>10292;

308

select * from t1 where user_id<10292;

309

drop table t1;

310

311

312

# Test that keys are created in right order

313

314

315

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

316

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

317

--replace_column 7 #

318

show index from t1;

319

drop table t1;

320

321

322

# Test of ALTER TABLE and innodb tables

323

324

325

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

326

alter table t1 engine=innodb;

327

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

328

select * from t1;

329

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

330

select * from t1;

331

alter table t1 add co3 int not null;

332

select * from t1;

333

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

334

select * from t1;

335

drop table t1;

336

337

338

# INSERT INTO innodb tables

339

340

341

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

342

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

343

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

344

select * from t1;

345

insert into t2 select * from t1;

346

select * from t2;

347

delete from t1 where b = 3;

348

select * from t1;

349

insert into t1 select * from t2;

350

select * from t1;

351

select * from t2;

352

drop table t1,t2;

353

354

355

# ORDER BY on not primary key

356

357

358

CREATE TABLE t1 (

359

user_name varchar(12),

360

password text,

361

subscribed char(1),

362

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

363

quota bigint(20),

364

weight double,

365

access_date date,

366

access_time time,

367

approved datetime,

368

dummy_primary_key int(11) NOT NULL auto_increment,

369

PRIMARY KEY (dummy_primary_key)

370

) ENGINE=innodb;

371

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

372

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

373

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

374

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

375

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

376

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

377

drop table t1;

378

379

380

# Testing of tables without primary keys

381

382

383

CREATE TABLE t1 (

384

id int(11) NOT NULL auto_increment,

385

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

386

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

387

KEY (id),

388

KEY parent_id (parent_id),

389

KEY level (level)

390

) engine=innodb;

391

392

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

393

update t1 set parent_id=parent_id+100;

394

select * from t1 where parent_id=102;

395

update t1 set id=id+1000;

396

update t1 set id=1024 where id=1009;

397

select * from t1;

398

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

399

select * from t1;

400

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

401

select * from t1 where parent_id=102;

402

--replace_column 9 #

403

explain select level from t1 where level=1;

404

select level,id from t1 where level=1;

405

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

406

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

407

delete from t1 where level=1;

408

select * from t1;

409

drop table t1;

410

411

412

# Test of index only reads

413

414

CREATE TABLE t1 (

415

sca_code char(6) NOT NULL,

416

cat_code char(6) NOT NULL,

417

sca_desc varchar(50),

418

lan_code char(2) NOT NULL,

419

sca_pic varchar(100),

420

sca_sdesc varchar(50),

421

sca_sch_desc varchar(16),

422

PRIMARY KEY (sca_code, cat_code, lan_code),

423

INDEX sca_pic (sca_pic)

424

) engine = innodb ;

425

426

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

427

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

428

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

429

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

430

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

431

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

432

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

433

434

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

435

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

436

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

437

select sca_pic from t1 where sca_pic is null;

438

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

439

delete from t1 where sca_code='pd';

440

drop table t1;

441

442

443

# Test of opening table twice and timestamps

444

445

set @a:=now();

446

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

447

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

448

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

449

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

450

update t1 set a=5 where a=1;

451

select a from t1;

452

drop table t1;

453

454

455

# Test with variable length primary key

456

457

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

458

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

459

select * from t1 order by b desc;

460

optimize table t1;

461

--replace_column 7 #

462

show keys from t1;

463

drop table t1;

464

465

466

# Test of create index with NULL columns

467

468

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

469

insert into t1 values (1,2);

470

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

471

create index ax1 on t1 (i,j);

472

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

473

drop table t1;

474

475

476

# Test min-max optimization

477

478

479

CREATE TABLE t1 (

480

a int3 unsigned NOT NULL,

481

b int1 unsigned NOT NULL,

482

UNIQUE (a, b)

483

) ENGINE = innodb;

484

485

INSERT INTO t1 VALUES (1, 1);

486

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

487

drop table t1;

488

489

490

# Test INSERT DELAYED

491

492

493

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

494

# Can't test this in 3.23

495

# INSERT DELAYED INTO t1 VALUES (1);

496

INSERT INTO t1 VALUES (1);

497

SELECT * FROM t1;

498

DROP TABLE t1;

499

500

501

502

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

503

504

505

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;

506

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

507

--replace_column 9 #

508

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

509

drop table t1;

510

511

512

# Test lock tables

513

514

515

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;

516

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

517

LOCK TABLES t1 WRITE;

518

--error ER_DUP_ENTRY

519

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

520

select id from t1;

521

select id from t1;

522

UNLOCK TABLES;

523

DROP TABLE t1;

524

525

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;

526

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

527

LOCK TABLES t1 WRITE;

528

begin;

529

--error ER_DUP_ENTRY

530

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

531

select id from t1;

532

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

533

commit;

534

select id,id3 from t1;

535

UNLOCK TABLES;

536

DROP TABLE t1;

537

538

539

# Test prefix key

540

541

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

542

drop table t1;

543

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

544

show create table t1;

545

drop table t1;

546

547

548

# Test using temporary table and auto_increment

549

550

551

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

552

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

553

delete from t1 where a=3;

554

insert into t1 values (NULL);

555

select * from t1;

556

alter table t1 add b int;

557

select * from t1;

558

drop table t1;

559

560

#Slashdot bug

561

create table t1

562

(

563

id int auto_increment primary key,

564

name varchar(32) not null,

565

value text not null,

566

uid int not null,

567

unique key(name,uid)

568

) engine=innodb;

569

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

570

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

571

set insert_id=5;

572

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

573

delete from t1 where uid=102;

574

set insert_id=5;

575

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

576

set insert_id=6;

577

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

578

select * from t1;

579

drop table t1;

580

581

582

# Test DROP DATABASE

583

584

585

create database mysqltest;

586

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

587

insert into mysqltest.t1 values(1);

588

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

589

insert into mysqltest.t2 values(1);

590

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

591

insert into mysqltest.t3 values(1);

592

commit;

593

drop database mysqltest;

594

# Don't check error message

595

--error 1049

596

show tables from mysqltest;

597

598

599

# Test truncate table with and without auto_commit

600

601

602

set autocommit=0;

603

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

604

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

605

truncate table t1;

606

commit;

607

truncate table t1;

608

truncate table t1;

609

select * from t1;

610

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

611

delete from t1;

612

select * from t1;

613

commit;

614

drop table t1;

615

set autocommit=1;

616

617

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

618

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

619

truncate table t1;

620

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

621

select * from t1;

622

truncate table t1;

623

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

624

delete from t1;

625

select * from t1;

626

drop table t1;

627

628

629

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

630

631

632

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

633

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

634

--replace_column 9 #

635

explain select * from t1 order by a;

636

--replace_column 9 #

637

explain select * from t1 order by b;

638

--replace_column 9 #

639

explain select * from t1 order by c;

640

--replace_column 9 #

641

explain select a from t1 order by a;

642

--replace_column 9 #

643

explain select b from t1 order by b;

644

--replace_column 9 #

645

explain select a,b from t1 order by b;

646

--replace_column 9 #

647

explain select a,b from t1;

648

--replace_column 9 #

649

explain select a,b,c from t1;

650

drop table t1;

651

652

653

# Check describe

654

655

656

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

657

desc t1;

658

drop table t1;

659

660

661

# Test of multi-table-delete

662

663

664

CREATE TABLE t1 (

665

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

666

cname char(15) NOT NULL default '',

667

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

668

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

669

last_mod_date timestamp NOT NULL,

670

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

671

last_app_date timestamp NOT NULL,

672

last_app_id smallint(6) default '-1',

673

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

674

assigned_scps int(11) default '0',

675

status tinyint(4) default '0'

676

) ENGINE=InnoDB;

677

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

678

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

679

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

680

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

681

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

682

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

683

CREATE TABLE t2 (

684

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

685

cname char(15) NOT NULL default '',

686

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

687

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

688

last_mod_date timestamp NOT NULL,

689

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

690

last_app_date timestamp NOT NULL,

691

last_app_id smallint(6) default '-1',

692

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

693

assigned_scps int(11) default '0',

694

status tinyint(4) default '0'

695

) ENGINE=InnoDB;

696

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

697

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

698

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

699

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

700

select * from t1;

701

select * from t2;

702

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

703

select * from t1;

704

select * from t2;

705

select * from t2;

706

drop table t1,t2;

707

708

709

# A simple test with some isolation levels

710

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

711

# this works.

712

713

714

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;

715

716

BEGIN;

717

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

718

SELECT @@tx_isolation,@@global.tx_isolation;

719

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

720

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

721

COMMIT;

722

723

BEGIN;

724

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

725

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

726

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

727

COMMIT;

728

729

SET binlog_format='MIXED';

730

BEGIN;

731

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

732

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

733

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

734

COMMIT;

735

DROP TABLE t1;

736

737

738

# Test of multi-table-update

739

740

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

741

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

742

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

743

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

744

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

745

select * from t1;

746

select * from t2;

747

drop table t1,t2;

748

749

750

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

751

752

753

# prepare

754

--disable_warnings

755

drop table if exists t1, t2;

756

--enable_warnings

757

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

758

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

759

create trigger trg_del_t2 after delete on t2 for each row

760

insert into t1 values (1);

761

insert into t1 values (1);

762

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

763

764

765

# exec cases A, B - see multi_update.test

766

767

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

768

769

--error ER_DUP_ENTRY

770

delete t2 from t2;

771

772

# check

773

774

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

775

776

# cleanup bug#29136

777

778

drop table t1, t2;

779

780

781

782

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

783

784

785

# prepare

786

--disable_warnings

787

drop table if exists t1, t2;

788

--enable_warnings

789

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

790

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

791

create trigger trg_del_t2 after delete on t2 for each row

792

insert into t1 values (1);

793

insert into t1 values (1);

794

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

795

796

797

# exec cases A, B - see multi_update.test

798

799

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

800

801

--error ER_DUP_ENTRY

802

delete t2 from t2;

803

804

# check

805

806

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

807

808

# cleanup bug#29136

809

810

drop table t1, t2;

811

812

813

814

# Testing of IFNULL

815

816

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

817

insert into t1 values(20,null);

818

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

819

t2.b=t3.a;

820

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

821

t2.b=t3.a order by 1;

822

insert into t1 values(10,null);

823

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

824

t2.b=t3.a order by 1;

825

drop table t1;

826

827

828

# Test of read_through not existing const_table

829

830

831

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

832

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

833

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

834

drop table t1,t2;

835

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

836

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

837

insert into t1 values (10, 20);

838

insert into t2 values (10, 20);

839

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

840

drop table t1,t2;

841

842

843

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

844

845

846

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

847

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;

848

insert into t1 set id=1;

849

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

850

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

851

select * from t1;

852

select * from t2;

853

drop table t2,t1;

854

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

855

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

856

INSERT INTO t1 VALUES(1);

857

INSERT INTO t2 VALUES(1, 1);

858

SELECT * from t1;

859

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

860

SELECT * from t1;

861

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

862

SELECT * from t1;

863

DROP TABLE t1,t2;

864

865

866

# Test of range_optimizer

867

868

869

set autocommit=0;

870

871

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

872

873

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

874

875

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

876

877

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

878

COMMIT;

879

880

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

881

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

882

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

883

884

SELECT * FROM t1;

885

SELECT * FROM t2;

886

SELECT * FROM t3;

887

ROLLBACK;

888

889

SELECT * FROM t1;

890

SELECT * FROM t2;

891

SELECT * FROM t3;

892

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

893

COMMIT;

894

set autocommit=1;

895

DROP TABLE t1,t2,t3;

896

897

898

# Check update with conflicting key

899

900

901

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

902

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

903

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

904

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

905

SELECT * from t1;

906

drop table t1;

907

908

909

# Test multi update with different join methods

910

911

912

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

913

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

914

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

915

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

916

917

# Full join, without key

918

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

919

select * from t1;

920

921

# unique key

922

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

923

select * from t1;

924

925

# ref key

926

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

927

select * from t1;

928

929

# Range key (in t1)

930

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;

931

select * from t1;

932

select * from t2;

933

934

drop table t1,t2;

935

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

936

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

937

SET AUTOCOMMIT=0;

938

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

939

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

940

ROLLBACK;

941

SELECT * FROM t1;

942

drop table t1,t2;

943

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

944

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

945

select distinct parent,child from t1 order by parent;

946

drop table t1;

947

948

949

# Test that MySQL priorities clustered indexes

950

951

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

952

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

953

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

954

insert into t2 (a) select b from t1;

955

insert into t1 (b) select b from t2;

956

insert into t2 (a) select b from t1;

957

insert into t1 (a) select b from t2;

958

insert into t2 (a) select b from t1;

959

insert into t1 (a) select b from t2;

960

insert into t2 (a) select b from t1;

961

insert into t1 (a) select b from t2;

962

insert into t2 (a) select b from t1;

963

insert into t1 (a) select b from t2;

964

select count(*) from t1;

965

--replace_column 9 #

966

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

967

update t1 set c=a;

968

--replace_column 9 #

969

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

970

drop table t1,t2;

971

972

973

# Test of UPDATE ... ORDER BY

974

975

976

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

977

978

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

979

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

980

SELECT * from t1;

981

drop table t1;

982

983

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

984

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

985

SET @tmp=0;

986

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

987

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

988

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

989

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

990

select * from t1 order by a,b;

991

drop table t1;

992

993

994

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

995

996

997

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

998

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

999

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

1000

1001

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

1002

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

1003

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

1004

update t1 set a=c, b=c;

1005

1006

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;

1007

insert into t2 select * from t1;

1008

1009

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

1010

drop table t1,t2;

1011

1012

1013

# test autoincrement with TRUNCATE

1014

1015

1016

SET AUTOCOMMIT=1;

1017

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

1018

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

1019

truncate table t1;

1020

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

1021

SELECT * from t1;

1022

drop table t1;

1023

1024

1025

# Test dictionary handling with spaceand quoting

1026

1027

1028

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

1029

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;

1030

#show create table t2;

1031

drop table t2,t1;

1032

1033

1034

# Test of multi updated and foreign keys

1035

1036

1037

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

1038

insert into `t1`values ( 1 ) ;

1039

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;

1040

insert into `t2`values ( 1 ) ;

1041

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;

1042

insert into `t3`values ( 1 ) ;

1043

--error 1451

1044

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

1045

--error 1451

1046

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;

1047

--error 1054

1048

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

1049

drop table t3,t2,t1;

1050

1051

1052

# test for recursion depth limit

1053

1054

create table t1(

1055

id int primary key,

1056

pid int,

1057

index(pid),

1058

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

1059

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

1060

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

1061

-- error 1451

1062

delete from t1 where id=0;

1063

delete from t1 where id=15;

1064

delete from t1 where id=0;

1065

1066

drop table t1;

1067

1068

1069

# Test timestamps

1070

1071

1072

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

1073

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

1074

(stamp))ENGINE=InnoDB;

1075

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

1076

# Note that timestamp 3 is wrong

1077

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

1078

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

1079

'20020204120000' GROUP BY col1;

1080

drop table t1,t2;

1081

1082

1083

# Test by Francois MASUREL

1084

1085

1086

CREATE TABLE t1 (

1087

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

1088

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

1089

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

1090

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

1091

`description` text,

1092

PRIMARY KEY (`id`),

1093

KEY `id_object` (`id_object`),

1094

KEY `id_version` (`id_version`)

1095

) ENGINE=InnoDB;

1096

1097

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

1098

1099

CREATE TABLE t2 (

1100

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

1101

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

1102

PRIMARY KEY (`id`),

1103

KEY `id_version` (`id_version`)

1104

) ENGINE=InnoDB;

1105

1106

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

1107

1108

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

1109

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

1110

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

1111

drop table t1,t2;

1112

1113

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

1114

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

1115

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

1116

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

1117

insert t2 select * from t1;

1118

insert t3 select * from t1;

1119

checksum table t1, t2, t3, t4 quick;

1120

checksum table t1, t2, t3, t4;

1121

checksum table t1, t2, t3, t4 extended;

1122

#show table status;

1123

drop table t1,t2,t3;

1124

1125

1126

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

1127

# (Bug #2552)

1128

1129

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

1130

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

1131

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

1132

drop table t1;

1133

1134

1135

# Bug2160

1136

1137

create table t1 (a int) engine=innodb;

1138

create table t2 like t1;

1139

drop table t1,t2;

1140

1141

1142

# Test of automaticly created foreign keys

1143

1144

1145

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

1146

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

1147

show create table t1;

1148

show create table t2;

1149

create index id on t2 (id);

1150

show create table t2;

1151

create index id2 on t2 (id);

1152

show create table t2;

1153

drop index id2 on t2;

1154

--error 1025,1025

1155

drop index id on t2;

1156

show create table t2;

1157

drop table t2;

1158

1159

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;

1160

show create table t2;

1161

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

1162

show create table t2;

1163

drop table t2;

1164

1165

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

1166

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;

1167

show create table t2;

1168

drop table t2;

1169

1170

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;

1171

show create table t2;

1172

drop table t2;

1173

1174

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;

1175

show create table t2;

1176

drop table t2;

1177

1178

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;

1179

show create table t2;

1180

drop table t2;

1181

1182

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;

1183

show create table t2;

1184

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

1185

show create table t2;

1186

drop table t2;

1187

1188

# Test error handling

1189

1190

# Embedded server doesn't chdir to data directory

1191

--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''

1192

--error ER_WRONG_FK_DEF

1193

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;

1194

1195

# bug#3749

1196

1197

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

1198

show create table t2;

1199

drop table t2;

1200

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;

1201

show create table t2;

1202

drop table t2, t1;

1203

1204

1205

1206

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

1207

1208

--error 1060

1209

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

1210

--error 1060

1211

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

1212

--error 1060

1213

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

1214

--error 1060

1215

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

1216

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

1217

--error 1060

1218

alter table t1 add key (c1,c1);

1219

--error 1060

1220

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

1221

--error 1060

1222

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

1223

--error 1060

1224

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

1225

drop table t1;

1226

1227

1228

# Bug #4082: integer truncation

1229

1230

1231

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

1232

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

1233

select distinct concat(a, b) from t1;

1234

drop table t1;

1235

1236

1237

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

1238

# engines does not fail

1239

1240

1241

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

1242

--error 1214

1243

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

1244

DROP TABLE t1;

1245

1246

1247

# check null values #1

1248

1249

1250

--disable_warnings

1251

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

1252

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

1253

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

1254

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

1255

--enable_warnings

1256

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

1257

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;

1258

DROP TABLE t2;

1259

DROP TABLE t1;

1260

1261

1262

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

1263

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

1264

1265

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

1266

insert into t1 values (4711);

1267

truncate t1;

1268

insert into t1 values (42);

1269

select * from t1;

1270

drop table t1;

1271

# Show that it works with permanent tables too.

1272

create table t1 (a int) engine=innodb;

1273

insert into t1 values (4711);

1274

truncate t1;

1275

insert into t1 values (42);

1276

select * from t1;

1277

drop table t1;

1278

1279

1280

# Bug #13025 Server crash during filesort

1281

1282

1283

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;

1284

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

1285

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

1286

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

1287

drop table t1;

1288

1289

1290

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

1291

1292

1293

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

1294

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

1295

select min(a) from t1;

1296

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

1297

drop table t1;

1298

1299

# End of 4.1 tests

1300

1301

1302

# range optimizer problem

1303

1304

1305

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

1306

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

1307

select * from t1;

1308

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

1309

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

1310

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

1311

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

1312

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

1313

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

1314

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

1315

select * from t1 where x > -16;

1316

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

1317

drop table t1;

1318

1319

1320

# Test for testable InnoDB status variables. This test

1321

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

1322

SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';

1323

SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';

1324

SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted';

1325

SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';

1326

SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';

1327

1328

# Test for row locks InnoDB status variables.

1329

SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';

1330

SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';

1331

SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';

1332

SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';

1333

SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';

1334

1335

# Test for innodb_sync_spin_loops variable

1336

show variables like "innodb_sync_spin_loops";

1337

set global innodb_sync_spin_loops=1000;

1338

show variables like "innodb_sync_spin_loops";

1339

set global innodb_sync_spin_loops=0;

1340

show variables like "innodb_sync_spin_loops";

1341

set global innodb_sync_spin_loops=20;

1342

show variables like "innodb_sync_spin_loops";

1343

1344

# Test for innodb_thread_concurrency variable

1345

SET @old_innodb_thread_concurrency= @@global.innodb_thread_concurrency;

1346

show variables like "innodb_thread_concurrency";

1347

set global innodb_thread_concurrency=1001;

1348

show variables like "innodb_thread_concurrency";

1349

set global innodb_thread_concurrency=0;

1350

show variables like "innodb_thread_concurrency";

1351

set global innodb_thread_concurrency=16;

1352

show variables like "innodb_thread_concurrency";

1353

SET @@global.innodb_thread_concurrency= @old_innodb_thread_concurrency;

1354

1355

# Test for innodb_concurrency_tickets variable

1356

show variables like "innodb_concurrency_tickets";

1357

set global innodb_concurrency_tickets=1000;

1358

show variables like "innodb_concurrency_tickets";

1359

set global innodb_concurrency_tickets=0;

1360

show variables like "innodb_concurrency_tickets";

1361

set global innodb_concurrency_tickets=500;

1362

show variables like "innodb_concurrency_tickets";

1363

1364

# Test for innodb_thread_sleep_delay variable

1365

show variables like "innodb_thread_sleep_delay";

1366

set global innodb_thread_sleep_delay=100000;

1367

show variables like "innodb_thread_sleep_delay";

1368

set global innodb_thread_sleep_delay=0;

1369

show variables like "innodb_thread_sleep_delay";

1370

set global innodb_thread_sleep_delay=10000;

1371

show variables like "innodb_thread_sleep_delay";

1372

1373

1374

# Test varchar

1375

1376

1377

let $default=`select @@storage_engine`;

1378

set storage_engine=INNODB;

1379

source include/varchar.inc;

1380

1381

1382

# Some errors/warnings on create

1383

1384

1385

# Embedded server doesn't chdir to data directory

1386

--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''

1387

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

1388

drop table t1;

1389

create table t1 (v varchar(65536));

1390

show create table t1;

1391

drop table t1;

1392

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

1393

show create table t1;

1394

drop table t1;

1395

1396

eval set storage_engine=$default;

1397

1398

# InnoDB specific varchar tests

1399

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

1400

drop table t1;

1401

1402

1403

# BUG#11039 Wrong key length in min()

1404

1405

1406

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

1407

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

1408

select min(a) from t1;

1409

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

1410

drop table t1;

1411

1412

1413

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

1414

1415

1416

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

1417

insert into t1 (b) values (1);

1418

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

1419

select * from t1;

1420

truncate table t1;

1421

insert into t1 (b) values (1);

1422

replace into t1 (b) values (2);

1423

replace into t1 (b) values (1);

1424

replace into t1 (b) values (3);

1425

select * from t1;

1426

drop table t1;

1427

1428

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

1429

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

1430

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

1431

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

1432

--error ER_DUP_ENTRY

1433

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

1434

select * from t1;

1435

drop table t1;

1436

1437

1438

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

1439

1440

1441

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

1442

insert into t1 (val) values (1);

1443

update t1 set a=2 where a=1;

1444

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

1445

--error ER_DUP_ENTRY

1446

insert into t1 (val) values (1);

1447

select * from t1;

1448

drop table t1;

1449

1450

# Bug #10465

1451

1452

1453

--disable_warnings

1454

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

1455

--enable_warnings

1456

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

1457

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

1458

SELECT GRADE FROM t1 WHERE GRADE= 151;

1459

DROP TABLE t1;

1460

1461

1462

# Bug #12340 multitable delete deletes only one record

1463

1464

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

1465

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

1466

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

1467

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

1468

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

1469

select * from t1;

1470

drop table t1,t2;

1471

1472

1473

# Test that the slow TRUNCATE implementation resets autoincrement columns

1474

# (bug #11946)

1475

1476

1477

CREATE TABLE t1 (

1478

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1479

) ENGINE=InnoDB;

1480

1481

CREATE TABLE t2 (

1482

id INTEGER NOT NULL,

1483

FOREIGN KEY (id) REFERENCES t1 (id)

1484

) ENGINE=InnoDB;

1485

1486

INSERT INTO t1 (id) VALUES (NULL);

1487

SELECT * FROM t1;

1488

TRUNCATE t1;

1489

INSERT INTO t1 (id) VALUES (NULL);

1490

SELECT * FROM t1;

1491

1492

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

1493

# rows resets autoincrement columns

1494

DELETE FROM t1;

1495

TRUNCATE t1;

1496

INSERT INTO t1 (id) VALUES (NULL);

1497

SELECT * FROM t1;

1498

DROP TABLE t2, t1;

1499

1500

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

1501

CREATE TABLE t1

1502

(

1503

id INT PRIMARY KEY

1504

) ENGINE=InnoDB;

1505

1506

--error 1005,1005

1507

CREATE TEMPORARY TABLE t2

1508

(

1509

id INT NOT NULL PRIMARY KEY,

1510

b INT,

1511

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

1512

) ENGINE=InnoDB;

1513

DROP TABLE t1;

1514

1515

1516

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

1517

1518

1519

# prefix index

1520

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

1521

character set = latin1 engine = innodb;

1522

1523

# normal indexes

1524

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

1525

character set = latin1 engine = innodb;

1526

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

1527

character set = latin1 engine = innodb;

1528

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

1529

character set = latin1 engine = innodb;

1530

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

1531

character set = latin1 engine = innodb;

1532

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

1533

character set = latin1 engine = innodb;

1534

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

1535

character set = latin1 engine = innodb;

1536

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

1537

character set = latin1 engine = innodb;

1538

1539

# multi-column indexes are allowed to be longer

1540

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

1541

character set = latin1 engine = innodb;

1542

1543

show create table t9;

1544

1545

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

1546

1547

# these should have their index length trimmed

1548

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

1549

character set = latin1 engine = innodb;

1550

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

1551

character set = latin1 engine = innodb;

1552

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

1553

character set = latin1 engine = innodb;

1554

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

1555

character set = latin1 engine = innodb;

1556

1557

show create table t1;

1558

1559

drop table t1, t2, t3, t4;

1560

1561

# these should be refused

1562

--error 1071

1563

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

1564

character set = latin1 engine = innodb;

1565

--error 1071

1566

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

1567

character set = latin1 engine = innodb;

1568

--error 1071

1569

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

1570

character set = latin1 engine = innodb;

1571

--error 1071

1572

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

1573

character set = latin1 engine = innodb;

1574

1575

1576

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

1577

1578

1579

CREATE TABLE t1

1580

(

1581

id INT PRIMARY KEY

1582

) ENGINE=InnoDB;

1583

1584

CREATE TABLE t2

1585

(

1586

v INT,

1587

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

1588

) ENGINE=InnoDB;

1589

1590

--error 1452

1591

INSERT INTO t2 VALUES(2);

1592

1593

INSERT INTO t1 VALUES(1);

1594

INSERT INTO t2 VALUES(1);

1595

1596

--error 1451

1597

DELETE FROM t1 WHERE id = 1;

1598

1599

--error 1217

1600

DROP TABLE t1;

1601

1602

SET FOREIGN_KEY_CHECKS=0;

1603

DROP TABLE t1;

1604

SET FOREIGN_KEY_CHECKS=1;

1605

1606

--error 1452

1607

INSERT INTO t2 VALUES(3);

1608

1609

DROP TABLE t2;

1610

1611

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

1612

1613

connect (a,localhost,root,,);

1614

connect (b,localhost,root,,);

1615

connection a;

1616

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

1617

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

1618

set autocommit=0;

1619

checksum table t1;

1620

connection b;

1621

insert into t1 values(3);

1622

connection a;

1623

1624

# Here checksum should not see insert

1625

1626

checksum table t1;

1627

connection a;

1628

commit;

1629

checksum table t1;

1630

commit;

1631

drop table t1;

1632

1633

# autocommit = 1

1634

1635

connection a;

1636

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

1637

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

1638

set autocommit=1;

1639

checksum table t1;

1640

connection b;

1641

set autocommit=1;

1642

insert into t1 values(3);

1643

connection a;

1644

1645

# Here checksum sees insert

1646

1647

checksum table t1;

1648

drop table t1;

1649

1650

connection default;

1651

disconnect a;

1652

disconnect b;

1653

1654

# tests for bugs #9802 and #13778

1655

1656

# test that FKs between invalid types are not accepted

1657

1658

set foreign_key_checks=0;

1659

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

1660

# Embedded server doesn't chdir to data directory

1661

--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''

1662

-- error 1005

1663

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

1664

set foreign_key_checks=1;

1665

drop table t2;

1666

1667

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

1668

# when f_k_c is 0

1669

1670

set foreign_key_checks=0;

1671

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

1672

# Embedded server doesn't chdir to data directory

1673

--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''

1674

-- error 1005

1675

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

1676

set foreign_key_checks=1;

1677

drop table t1;

1678

1679

# test that invalid datatype conversions with ALTER are not allowed

1680

1681

set foreign_key_checks=0;

1682

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

1683

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

1684

-- error 1025,1025

1685

alter table t1 modify column a int;

1686

set foreign_key_checks=1;

1687

drop table t2,t1;

1688

1689

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

1690

1691

set foreign_key_checks=0;

1692

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

1693

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

1694

alter table t1 convert to character set utf8;

1695

set foreign_key_checks=1;

1696

drop table t2,t1;

1697

1698

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

1699

1700

set foreign_key_checks=0;

1701

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

1702

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

1703

# Embedded server doesn't chdir to data directory

1704

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

1705

-- error 1025

1706

rename table t3 to t1;

1707

set foreign_key_checks=1;

1708

drop table t2,t3;

1709

1710

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

1711

1712

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

1713

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

1714

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

1715

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

1716

1717

insert into t1 values(1);

1718

insert into t3 values(1);

1719

-- error 1452

1720

insert into t2 values(2);

1721

-- error 1452

1722

insert into t4 values(2);

1723

insert into t2 values(1);

1724

insert into t4 values(1);

1725

-- error 1451

1726

update t1 set a=2;

1727

-- error 1452

1728

update t2 set a=2;

1729

-- error 1451

1730

update t3 set a=2;

1731

-- error 1452

1732

update t4 set a=2;

1733

-- error 1451

1734

truncate t1;

1735

-- error 1451

1736

truncate t3;

1737

truncate t2;

1738

truncate t4;

1739

truncate t1;

1740

truncate t3;

1741

1742

drop table t4,t3,t2,t1;

1743

1744

1745

1746

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

1747

1748

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

1749

b varchar(255) character set utf8,

1750

c varchar(255) character set utf8,

1751

d varchar(255) character set utf8,

1752

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

1753

drop table t1;

1754

--error ER_TOO_LONG_KEY

1755

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

1756

b varchar(255) character set utf8,

1757

c varchar(255) character set utf8,

1758

d varchar(255) character set utf8,

1759

e varchar(255) character set utf8,

1760

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

1761

1762

1763

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

1764

1765

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

1766

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

1767

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

1768

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

1769

1770

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

1771

-- error ER_DUP_ENTRY

1772

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

1773

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

1774

-- error ER_DUP_ENTRY

1775

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

1776

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

1777

-- error ER_DUP_ENTRY

1778

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

1779

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

1780

select hex(s1) from t1;

1781

select hex(s1) from t2;

1782

select hex(s1) from t3;

1783

select hex(s1) from t4;

1784

drop table t1,t2,t3,t4;

1785

1786

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

1787

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

1788

1789

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

1790

-- error 1452

1791

insert into t2 values(0x42);

1792

insert into t2 values(0x41);

1793

select hex(s1) from t2;

1794

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

1795

select hex(s1) from t2;

1796

-- error 1451

1797

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

1798

-- error 1451

1799

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

1800

-- error 1451

1801

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

1802

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

1803

select hex(s1) from t2;

1804

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

1805

select hex(s1) from t2;

1806

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

1807

select hex(s1) from t2;

1808

-- error 1451

1809

delete from t1 where a=1;

1810

delete from t1 where a=2;

1811

update t2 set s1=0x4120;

1812

-- error 1451

1813

delete from t1;

1814

delete from t1 where a!=3;

1815

select a,hex(s1) from t1;

1816

select hex(s1) from t2;

1817

1818

drop table t2,t1;

1819

1820

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

1821

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

1822

1823

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

1824

insert into t2 values(0x41);

1825

select hex(s1) from t2;

1826

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

1827

select hex(s1) from t2;

1828

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

1829

select hex(s1) from t2;

1830

delete from t1 where a=1;

1831

-- error 1451

1832

delete from t1 where a=2;

1833

select a,hex(s1) from t1;

1834

select hex(s1) from t2;

1835

1836

drop table t2,t1;

1837

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

1838

# generated foreign key identifier. (Bug #16387)

1839

1840

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

1841

CREATE TABLE t2(a INT) ENGINE=InnoDB;

1842

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

1843

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1844

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

1845

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1846

SHOW CREATE TABLE t2;

1847

DROP TABLE t2,t1;

1848

1849

1850

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

1851

1852

1853

connect (a,localhost,root,,);

1854

connect (b,localhost,root,,);

1855

connection a;

1856

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

1857

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

1858

commit;

1859

connection b;

1860

set autocommit = 0;

1861

update t1 set b = 5 where a = 2;

1862

connection a;

1863

delimiter |;

1864

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

1865

delimiter ;|

1866

set autocommit = 0;

1867

connection a;

1868

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

1869

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

1870

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

1871

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

1872

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

1873

connection b;

1874

commit;

1875

connection a;

1876

commit;

1877

drop trigger t1t;

1878

drop table t1;

1879

disconnect a;

1880

disconnect b;

1881

1882

# Another trigger test

1883

1884

connect (a,localhost,root,,);

1885

connect (b,localhost,root,,);

1886

connection a;

1887

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

1888

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

1889

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

1890

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

1891

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

1892

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

1893

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

1894

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

1895

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

1896

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

1897

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

1898

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

1899

1900

delimiter |;

1901

create trigger t1t before insert on t1 for each row begin

1902

INSERT INTO t2 SET a = NEW.a;

1903

end |

1904

1905

create trigger t2t before insert on t2 for each row begin

1906

DELETE FROM t3 WHERE a = NEW.a;

1907

end |

1908

1909

create trigger t3t before delete on t3 for each row begin

1910

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

1911

end |

1912

1913

create trigger t4t before update on t4 for each row begin

1914

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

1915

end |

1916

delimiter ;|

1917

commit;

1918

set autocommit = 0;

1919

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

1920

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

1921

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

1922

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

1923

insert into t5(a) values(20);

1924

connection b;

1925

set autocommit = 0;

1926

insert into t1(a) values(7);

1927

insert into t2(a) values(8);

1928

delete from t2 where a = 3;

1929

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

1930

commit;

1931

drop trigger t1t;

1932

drop trigger t2t;

1933

drop trigger t3t;

1934

drop trigger t4t;

1935

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

1936

connection default;

1937

disconnect a;

1938

disconnect b;

1939

1940

1941

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

1942

# error message (bug #9680)

1943

1944

1945

CREATE TABLE t1 (

1946

field1 varchar(8) NOT NULL DEFAULT '',

1947

field2 varchar(8) NOT NULL DEFAULT '',

1948

PRIMARY KEY (field1, field2)

1949

) ENGINE=InnoDB;

1950

1951

CREATE TABLE t2 (

1952

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

1953

FOREIGN KEY (field1) REFERENCES t1 (field1)

1954

ON DELETE CASCADE ON UPDATE CASCADE

1955

) ENGINE=InnoDB;

1956

1957

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

1958

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

1959

1960

INSERT INTO t2 VALUES ('old');

1961

INSERT INTO t2 VALUES ('other');

1962

1963

--error ER_FOREIGN_DUPLICATE_KEY

1964

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

1965

1966

DROP TABLE t2;

1967

DROP TABLE t1;

1968

1969

1970

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

1971

1972

create table t1 (

1973

c1 bigint not null,

1974

c2 bigint not null,

1975

primary key (c1),

1976

unique key (c2)

1977

) engine=innodb;

1978

1979

create table t2 (

1980

c1 bigint not null,

1981

primary key (c1)

1982

) engine=innodb;

1983

1984

alter table t1 add constraint c2_fk foreign key (c2)

1985

references t2(c1) on delete cascade;

1986

show create table t1;

1987

1988

alter table t1 drop foreign key c2_fk;

1989

show create table t1;

1990

1991

drop table t1, t2;

1992

1993

1994

# Bug #14360: problem with intervals

1995

1996

1997

create table t1(a date) engine=innodb;

1998

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

1999

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

2000

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

2001

select * from t1, t2

2002

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

2003

drop table t1, t2;

2004

2005

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

2006

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

2007

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

2008

primary key(id)) engine=innodb;

2009

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

2010

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

2011

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

2012

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

2013

commit;

2014

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

2015

WHERE mm.id IS NULL;

2016

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

2017

where mm.id is null lock in share mode;

2018

drop table t1,t2;

2019

2020

2021

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

2022

# update (because READ COMMITTED isolation level)

2023

2024

2025

connect (a,localhost,root,,);

2026

connect (b,localhost,root,,);

2027

connection a;

2028

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

2029

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

2030

commit;

2031

SET binlog_format='MIXED';

2032

set autocommit = 0;

2033

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2034

update t1 set b = 5 where b = 1;

2035

connection b;

2036

SET binlog_format='MIXED';

2037

set autocommit = 0;

2038

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2039

2040

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

2041

2042

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

2043

connection a;

2044

commit;

2045

connection b;

2046

commit;

2047

drop table t1;

2048

connection default;

2049

disconnect a;

2050

disconnect b;

2051

2052

2053

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

2054

# using READ COMMITTED isolation level)

2055

2056

2057

connect (a,localhost,root,,);

2058

connect (b,localhost,root,,);

2059

connection a;

2060

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

2061

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

2062

commit;

2063

set autocommit = 0;

2064

select * from t1 lock in share mode;

2065

update t1 set b = 5 where b = 1;

2066

connection b;

2067

set autocommit = 0;

2068

2069

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

2070

2071

--error 1205

2072

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

2073

2074

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

2075

2076

--error 1205

2077

connection a;

2078

commit;

2079

connection b;

2080

commit;

2081

connection default;

2082

disconnect a;

2083

disconnect b;

2084

drop table t1;

2085

2086

2087

# Consistent read should be used in following selects

2088

2089

# 1) INSERT INTO ... SELECT

2090

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

2091

# 3) CREATE ... SELECT

2092

2093

connect (a,localhost,root,,);

2094

connect (b,localhost,root,,);

2095

connection a;

2096

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

2097

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

2098

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

2099

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

2100

commit;

2101

set autocommit = 0;

2102

select * from t2 for update;

2103

connection b;

2104

SET binlog_format='MIXED';

2105

set autocommit = 0;

2106

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2107

insert into t1 select * from t2;

2108

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

2109

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

2110

select * from t2;

2111

commit;

2112

connection a;

2113

commit;

2114

connection default;

2115

disconnect a;

2116

disconnect b;

2117

drop table t1, t2, t3;

2118

2119

2120

# Consistent read should not be used if

2121

2122

# (a) isolation level is serializable OR

2123

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

2124

# (c) select ... for update

2125

2126

# in following queries:

2127

2128

# 1) INSERT INTO ... SELECT

2129

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

2130

# 3) CREATE ... SELECT

2131

2132

connect (a,localhost,root,,);

2133

connect (b,localhost,root,,);

2134

connect (c,localhost,root,,);

2135

connect (d,localhost,root,,);

2136

connect (e,localhost,root,,);

2137

connect (f,localhost,root,,);

2138

connect (g,localhost,root,,);

2139

connect (h,localhost,root,,);

2140

connect (i,localhost,root,,);

2141

connect (j,localhost,root,,);

2142

connection a;

2143

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

2144

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

2145

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

2146

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

2147

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

2148

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

2149

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

2150

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

2151

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

2152

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

2153

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

2154

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

2155

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

2156

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

2157

commit;

2158

set autocommit = 0;

2159

select * from t2 for update;

2160

connection b;

2161

SET binlog_format='MIXED';

2162

set autocommit = 0;

2163

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2164

--send

2165

insert into t1 select * from t2;

2166

connection c;

2167

SET binlog_format='MIXED';

2168

set autocommit = 0;

2169

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2170

--send

2171

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

2172

connection d;

2173

SET binlog_format='MIXED';

2174

set autocommit = 0;

2175

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2176

--send

2177

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

2178

connection e;

2179

SET binlog_format='MIXED';

2180

set autocommit = 0;

2181

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2182

--send

2183

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

2184

connection f;

2185

SET binlog_format='MIXED';

2186

set autocommit = 0;

2187

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2188

--send

2189

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

2190

connection g;

2191

SET binlog_format='MIXED';

2192

set autocommit = 0;

2193

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2194

--send

2195

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

2196

connection h;

2197

SET binlog_format='MIXED';

2198

set autocommit = 0;

2199

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2200

--send

2201

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

2202

connection i;

2203

SET binlog_format='MIXED';

2204

set autocommit = 0;

2205

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2206

--send

2207

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

2208

connection j;

2209

SET binlog_format='MIXED';

2210

set autocommit = 0;

2211

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2212

--send

2213

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

2214

2215

connection b;

2216

--error 1205

2217

reap;

2218

2219

connection c;

2220

--error 1205

2221

reap;

2222

2223

connection d;

2224

--error 1205

2225

reap;

2226

2227

connection e;

2228

--error 1205

2229

reap;

2230

2231

connection f;

2232

--error 1205

2233

reap;

2234

2235

connection g;

2236

--error 1205

2237

reap;

2238

2239

connection h;

2240

--error 1205

2241

reap;

2242

2243

connection i;

2244

--error 1205

2245

reap;

2246

2247

connection j;

2248

--error 1205

2249

reap;

2250

2251

connection a;

2252

commit;

2253

2254

connection default;

2255

disconnect a;

2256

disconnect b;

2257

disconnect c;

2258

disconnect d;

2259

disconnect e;

2260

disconnect f;

2261

disconnect g;

2262

disconnect h;

2263

disconnect i;

2264

disconnect j;

2265

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

2266

2267

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

2268

--error 1005

2269

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

2270

2271

2272

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

2273

2274

2275

CREATE TABLE t1 (

2276

a BIGINT(20) NOT NULL,

2277

PRIMARY KEY (a)

2278

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2279

2280

CREATE TABLE t2 (

2281

a BIGINT(20) NOT NULL,

2282

b VARCHAR(128) NOT NULL,

2283

c TEXT NOT NULL,

2284

PRIMARY KEY (a,b),

2285

KEY idx_t2_b_c (b,c(200)),

2286

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

2287

ON DELETE CASCADE

2288

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2289

2290

INSERT INTO t1 VALUES (1);

2291

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

2292

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

2293

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

2294

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

2295

2296

SELECT * FROM t2 WHERE b = 'customer_over';

2297

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

2298

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

2299

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

2300

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

2301

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

2302

2303

drop table t2, t1;

2304

2305

2306

# Test optimize on table with open transaction

2307

2308

2309

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2310

BEGIN;

2311

INSERT INTO t1 VALUES (1);

2312

OPTIMIZE TABLE t1;

2313

DROP TABLE t1;

2314

2315

2316

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

2317

2318

2319

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

2320

2321

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

2322

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

2323

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

2324

2325

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

2326

DELETE CASCADE ON UPDATE CASCADE;

2327

2328

SHOW CREATE TABLE t2;

2329

DROP TABLE t2, t1;

2330

2331

2332

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

2333

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

2334

2335

2336

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

2337

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

2338

INSERT INTO t1 VALUES (1);

2339

INSERT INTO t2 VALUES (1);

2340

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

2341

# mysqltest first does replace_regex, then replace_result

2342

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

2343

# Embedded server doesn't chdir to data directory

2344

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

2345

--error 1025

2346

ALTER TABLE t2 MODIFY a INT NOT NULL;

2347

DELETE FROM t1;

2348

DROP TABLE t2,t1;

2349

2350

2351

# Bug #26835: table corruption after delete+insert

2352

2353

2354

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

2355

ENGINE=InnoDB;

2356

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

2357

DELETE FROM t1;

2358

INSERT INTO t1 VALUES ('DDD');

2359

SELECT * FROM t1;

2360

DROP TABLE t1;

2361

2362

2363

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

2364

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

2365

2366

2367

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

2368

AUTO_INCREMENT=42;

2369

2370

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

2371

SELECT * FROM t1;

2372

2373

SHOW CREATE TABLE t1;

2374

2375

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

2376

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

2377

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

2378

SHOW CREATE TABLE t1;

2379

2380

DROP TABLE t1,t2;

2381

2382

2383

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

2384

2385

--error 1118

2386

CREATE TABLE t1 (

2387

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

2388

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

2389

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

2390

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

2391

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

2392

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

2393

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

2394

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

2395

) ENGINE = InnoDB;

2396

2397

2398

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

2399

2400

DROP TABLE IF EXISTS t1;

2401

CREATE TABLE t1(

2402

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

2403

) ENGINE=InnoDB;

2404

INSERT INTO t1 VALUES(-10);

2405

SELECT * FROM t1;

2406

2407

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

2408

# for the test to be useful.

2409

2410

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

2411

INSERT INTO t1 VALUES(NULL);

2412

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

2413

SELECT * FROM t1;

2414

DROP TABLE t1;

2415

2416

2417

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

2418

# query_cache ON

2419

2420

CONNECT (c1,localhost,root,,);

2421

CONNECT (c2,localhost,root,,);

2422

CONNECTION c1;

2423

SET binlog_format='MIXED';

2424

SET TX_ISOLATION='read-committed';

2425

SET AUTOCOMMIT=0;

2426

DROP TABLE IF EXISTS t1, t2;

2427

CREATE TABLE t1 ( a int ) ENGINE=InnoDB;

2428

CREATE TABLE t2 LIKE t1;

2429

SELECT * FROM t2;

2430

CONNECTION c2;

2431

SET binlog_format='MIXED';

2432

SET TX_ISOLATION='read-committed';

2433

SET AUTOCOMMIT=0;

2434

INSERT INTO t1 VALUES (1);

2435

COMMIT;

2436

CONNECTION c1;

2437

SELECT * FROM t1 WHERE a=1;

2438

DISCONNECT c1;

2439

DISCONNECT c2;

2440

CONNECT (c1,localhost,root,,);

2441

CONNECT (c2,localhost,root,,);

2442

CONNECTION c1;

2443

SET binlog_format='MIXED';

2444

SET TX_ISOLATION='read-committed';

2445

SET AUTOCOMMIT=0;

2446

SELECT * FROM t2;

2447

CONNECTION c2;

2448

SET binlog_format='MIXED';

2449

SET TX_ISOLATION='read-committed';

2450

SET AUTOCOMMIT=0;

2451

INSERT INTO t1 VALUES (2);

2452

COMMIT;

2453

CONNECTION c1;

2454

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

2455

SELECT * FROM t1 WHERE a=2;

2456

SELECT * FROM t1 WHERE a=2;

2457

DROP TABLE t1;

2458

DROP TABLE t2;

2459

DISCONNECT c1;

2460

DISCONNECT c2;

2461

CONNECTION default;

2462

2463

2464

# Bug #29157 UPDATE, changed rows incorrect

2465

2466

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

2467

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

2468

--enable_info

2469

update t1 set j = 2;

2470

--disable_info

2471

drop table t1;

2472

2473

2474

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

2475

# I_S

2476

2477

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

2478

select table_comment, data_free > 0 as data_free_is_set

2479

from information_schema.tables

2480

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

2481

drop table t1;

2482

2483

2484

# Bug 34920 test

2485

2486

CONNECTION default;

2487

CREATE TABLE t1 (

2488

c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

2489

c2 VARCHAR(128) NOT NULL,

2490

PRIMARY KEY(c1)

2491

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

2492

2493

CREATE TABLE t2 (

2494

c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

2495

c2 INT(10) UNSIGNED DEFAULT NULL,

2496

PRIMARY KEY(c1)

2497

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

2498

2499

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

2500

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

2501

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

2502

DROP TABLE t2;

2503

DROP TABLE t1;

2504

# End 34920 test

2505

2506

# Bug #29507 TRUNCATE shows to many rows effected

2507

2508

CONNECTION default;

2509

CREATE TABLE t1 (c1 int default NULL,

2510

c2 int default NULL

2511

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2512

2513

--enable_info

2514

TRUNCATE TABLE t1;

2515

2516

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

2517

TRUNCATE TABLE t1;

2518

2519

--disable_info

2520

DROP TABLE t1;

2521

2522

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

2523

2524

-- disable_query_log

2525

-- disable_result_log

2526

2527

CONNECT (c1,localhost,root,,);

2528

2529

DROP TABLE IF EXISTS bug35537;

2530

CREATE TABLE bug35537 (

2531

c1 int

2532

) ENGINE=InnoDB;

2533

2534

INSERT INTO bug35537 VALUES (1);

2535

2536

-- enable_result_log

2537

2538

SHOW SESSION STATUS LIKE 'Handler_update%';

2539

SHOW SESSION STATUS LIKE 'Handler_delete%';

2540

2541

UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;

2542

DELETE FROM bug35537 WHERE c1 = 2;

2543

2544

SHOW SESSION STATUS LIKE 'Handler_update%';

2545

SHOW SESSION STATUS LIKE 'Handler_delete%';

2546

2547

DROP TABLE bug35537;

2548

2549

DISCONNECT c1;

2550

CONNECTION default;

2551

2552

SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;

2553

2554

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

2555

# #

2556

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

2557

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

2558

# #

2559

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

2560

# #

2561

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

2562

# your commit message #

2563

# To: innodb_dev_ww@oracle.com #

2564

# Cc: dev-innodb@mysql.com #

2565

# (otherwise your changes may be erased). #

2566

# #

2567

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

Older »