~drizzle-trunk/drizzle/development

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=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;

id code name

1 1 Tim

2 1 Monty

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

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

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

id code name

2 1 Monty

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

8 1 Sinisa

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

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

id code name

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

8 1 Sinisa

12 1 Ralph

drop table t1;

CREATE TABLE t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

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

id parent_id level

8 102 2

9 102 2

15 102 2

update t1 set id=id+1000;

update t1 set id=1024 where id=1009;

Got one of the listed errors

select * from t1;

id parent_id level

1001 100 0

1002 101 1

1003 101 1

1004 101 1

1005 101 1

1006 101 1

1007 101 1

1008 102 2

1009 102 2

1015 102 2

1016 103 2

1017 103 2

1018 103 2

1019 103 2

1020 103 2

1021 104 2

1022 104 2

1024 104 2

1025 105 2

1026 105 2

1027 105 2

1028 105 2

1029 105 2

1030 105 2

1031 106 2

1032 106 2

1033 106 2

1034 106 2

1035 106 2

1036 107 2

1037 107 2

1038 107 2

1040 107 2

1157 100 0

1179 105 2

1183 104 2

1193 105 2

1202 107 2

1203 107 2

update ignore t1 set id=id+1;

select * from t1;

id parent_id level

1001 100 0

100

1002 101 1

101

1003 101 1

102

1004 101 1

103

1005 101 1

104

1006 101 1

105

1007 101 1

106

1008 102 2

107

1010 102 2

108

1015 102 2

109

1016 103 2

110

1017 103 2

111

1018 103 2

112

1019 103 2

113

1020 103 2

114

1021 104 2

115

1023 104 2

116

1024 104 2

117

1025 105 2

118

1026 105 2

119

1027 105 2

120

1028 105 2

121

1029 105 2

122

1030 105 2

123

1031 106 2

124

1032 106 2

125

1033 106 2

126

1034 106 2

127

1035 106 2

128

1036 107 2

129

1037 107 2

130

1039 107 2

131

1041 107 2

132

1158 100 0

133

1180 105 2

134

1184 104 2

135

1194 105 2

136

1202 107 2

137

1204 107 2

138

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

139

select * from t1 where parent_id=102;

140

id parent_id level

141

1008 102 2

142

1010 102 2

143

1015 102 2

144

explain select level from t1 where level=1;

145

id select_type table type possible_keys key key_len ref rows Extra

146

1 SIMPLE t1 ref level level 4 const # Using index

147

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

148

id select_type table type possible_keys key key_len ref rows Extra

149

1 SIMPLE t1 ref level level 4 const # Using index

150

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

151

id select_type table type possible_keys key key_len ref rows Extra

152

1 SIMPLE t1 ref level level 4 const #

153

select level,id from t1 where level=1;

154

level id

155

1 1002

156

1 1003

157

1 1004

158

1 1005

159

1 1006

160

1 1007

161

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

162

level id parent_id

163

1 1002 101

164

1 1003 101

165

1 1004 101

166

1 1005 101

167

1 1006 101

168

1 1007 101

169

alter table t1 ENGINE=innodb;

170

show keys from t1;

171

Table Unique Key_name Seq_in_index Column_name

172

t1 YES PRIMARY 1 id

173

t1 NO parent_id 1 parent_id

174

t1 NO level 1 level

175

drop table t1;

176

CREATE TABLE t1 (

177

gesuchnr int DEFAULT '0' NOT NULL,

178

benutzer_id int DEFAULT '0' NOT NULL,

179

PRIMARY KEY (gesuchnr,benutzer_id)

180

) engine=innodb;

181

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

182

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

183

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

184

select * from t1;

185

gesuchnr benutzer_id

186

1 1

187

2 1

188

drop table t1;

189

create table t1 (a int) engine=innodb;

190

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

191

alter table t1 engine=innodb;

192

delete from t1 where a = 1;

193

select * from t1;

194

195

196

check table t1;

197

Table Op Msg_type Msg_text

198

test.t1 check status OK

199

drop table t1;

200

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

201

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

202

delete from t1 where a = 1;

203

select * from t1;

204

a b

205

2 testing

206

create index skr on t1 (a);

207

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

208

analyze table t1;

209

Table Op Msg_type Msg_text

210

test.t1 analyze status OK

211

show keys from t1;

212

Table Unique Key_name Seq_in_index Column_name

213

t1 NO skr 1 a

214

drop table t1;

215

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

216

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

217

select * from t1 where a = 1;

218

a b

219

220

drop table t1;

221

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

222

set autocommit=0;

223

insert into t1 values (4);

224

rollback;

225

select n, "after rollback" from t1;

226

n after rollback

227

insert into t1 values (4);

228

commit;

229

select n, "after commit" from t1;

230

n after commit

231

4 after commit

232

commit;

233

insert into t1 values (5);

234

insert into t1 values (4);

235

ERROR 23000: Duplicate entry '4' for key 'PRIMARY'

236

commit;

237

select n, "after commit" from t1;

238

n after commit

239

4 after commit

240

5 after commit

241

set autocommit=1;

242

insert into t1 values (6);

243

insert into t1 values (4);

244

ERROR 23000: Duplicate entry '4' for key 'PRIMARY'

245

select n from t1;

246

247

248

249

250

set autocommit=0;

251

begin;

252

savepoint `my_savepoint`;

253

insert into t1 values (7);

254

savepoint `savept2`;

255

insert into t1 values (3);

256

select n from t1;

257

258

259

260

261

262

263

savepoint savept3;

264

rollback to savepoint savept2;

265

rollback to savepoint savept3;

266

ERROR 42000: SAVEPOINT savept3 does not exist

267

rollback to savepoint savept2;

268

release savepoint `my_savepoint`;

269

select n from t1;

270

271

272

273

274

275

rollback to savepoint `my_savepoint`;

276

ERROR 42000: SAVEPOINT my_savepoint does not exist

277

rollback to savepoint savept2;

278

insert into t1 values (8);

279

savepoint sv;

280

commit;

281

savepoint sv;

282

set autocommit=1;

283

rollback;

284

drop table t1;

285

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

286

start transaction;

287

insert into t1 values (4);

288

flush tables with read lock;

289

commit;

290

unlock tables;

291

commit;

292

select * from t1;

293

294

295

drop table t1;

296

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

297

begin;

298

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

299

select id as afterbegin_id,nom as afterbegin_nom from t1;

300

afterbegin_id afterbegin_nom

301

1 hamdouni

302

rollback;

303

select id as afterrollback_id,nom as afterrollback_nom from t1;

304

afterrollback_id afterrollback_nom

305

set autocommit=0;

306

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

307

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

308

afterautocommit0_id afterautocommit0_nom

309

2 mysql

310

rollback;

311

select id as afterrollback_id,nom as afterrollback_nom from t1;

312

afterrollback_id afterrollback_nom

313

set autocommit=1;

314

drop table t1;

315

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

316

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

317

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

318

ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'

319

delete from t1;

320

delete from t1 where id = 'pippo';

321

select * from t1;

322

id val

323

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

324

set autocommit=0;

325

delete from t1;

326

rollback;

327

select * from t1;

328

id val

329

pippo 12

330

delete from t1;

331

commit;

332

select * from t1;

333

id val

334

drop table t1;

335

create table t1 (a integer) engine=innodb;

336

start transaction;

337

rename table t1 to t2;

338

create table t1 (b integer) engine=innodb;

339

insert into t1 values (1);

340

rollback;

341

drop table t1;

342

rename table t2 to t1;

343

drop table t1;

344

set autocommit=1;

345

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

346

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

347

select * from t1;

348

ID NAME

349

1 Jochen

350

drop table t1;

351

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

352

set autocommit=0;

353

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

354

COMMIT;

355

SELECT * FROM t1;

356

_userid

357

marc@anyware.co.uk

358

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

359

_userid

360

marc@anyware.co.uk

361

drop table t1;

362

set autocommit=1;

363

CREATE TABLE t1 (

364

user_id int DEFAULT '0' NOT NULL,

365

name varchar(100),

366

phone varchar(100),

367

ref_email varchar(100) DEFAULT '' NOT NULL,

368

detail varchar(200),

369

PRIMARY KEY (user_id,ref_email)

370

)engine=innodb;

371

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

372

select * from t1 where user_id=10292;

373

user_id name phone ref_email detail

374

10292 sanjeev 29153373 sansh777@hotmail.com xxx

375

10292 shirish 2333604 shirish@yahoo.com ddsds

376

10292 sonali 323232 sonali@bolly.com filmstar

377

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

378

select * from t1 where user_id=10292;

379

user_id name phone ref_email detail

380

10292 sanjeev 29153373 sansh777@hotmail.com xxx

381

10292 shirish 2333604 shirish@yahoo.com ddsds

382

10292 sonali 323232 sonali@bolly.com filmstar

383

select * from t1 where user_id>=10292;

384

user_id name phone ref_email detail

385

10292 sanjeev 29153373 sansh777@hotmail.com xxx

386

10292 shirish 2333604 shirish@yahoo.com ddsds

387

10292 sonali 323232 sonali@bolly.com filmstar

388

10293 shirish 2333604 shirish@yahoo.com ddsds

389

select * from t1 where user_id>10292;

390

user_id name phone ref_email detail

391

10293 shirish 2333604 shirish@yahoo.com ddsds

392

select * from t1 where user_id<10292;

393

user_id name phone ref_email detail

394

10291 sanjeev 29153373 sansh777@hotmail.com xxx

395

drop table t1;

396

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

397

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

398

show index from t1;

399

Table Unique Key_name Seq_in_index Column_name

400

t1 YES PRIMARY 1 a

401

t1 YES PRIMARY 2 b

402

t1 YES c 1 c

403

t1 YES b 1 b

404

t1 NO a 1 a

405

t1 NO a_2 1 a

406

drop table t1;

407

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

408

alter table t1 engine=innodb;

409

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

410

select * from t1;

411

col1 col2

412

1 1

413

2 3

414

3 4

415

4 4

416

5 2

417

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

418

select * from t1;

419

col1 col2

420

1 1

421

2 3

422

3 4

423

4 7

424

5 2

425

alter table t1 add co3 int not null;

426

select * from t1;

427

col1 col2 co3

428

1 1 0

429

2 3 0

430

3 4 0

431

4 7 0

432

5 2 0

433

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

434

select * from t1;

435

col1 col2 co3

436

1 1 0

437

2 9 0

438

3 4 0

439

4 7 0

440

5 2 0

441

drop table t1;

442

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

443

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

444

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

445

select * from t1;

446

a b

447

1 3

448

2 3

449

3 3

450

insert into t2 select * from t1;

451

select * from t2;

452

a b

453

1 3

454

2 3

455

3 3

456

delete from t1 where b = 3;

457

select * from t1;

458

a b

459

insert into t1 select * from t2;

460

select * from t1;

461

a b

462

1 3

463

2 3

464

3 3

465

select * from t2;

466

a b

467

1 3

468

2 3

469

3 3

470

drop table t1,t2;

471

CREATE TABLE t1 (

472

user_name varchar(12),

473

password text,

474

subscribed char(1),

475

user_id int DEFAULT '0' NOT NULL,

476

quota bigint,

477

weight double,

478

access_date date,

479

approved datetime,

480

dummy_primary_key int NOT NULL auto_increment,

481

PRIMARY KEY (dummy_primary_key)

482

) ENGINE=innodb;

483

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

484

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

485

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

486

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

487

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

488

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

489

user_name password subscribed user_id quota weight access_date approved dummy_primary_key

490

user_0 somepassword N 0 0 0 2000-09-07 2000-09-07 23:06:59 1

491

user_1 somepassword Y 1 1 1 2000-09-07 2000-09-07 23:06:59 2

492

user_2 somepassword N 2 2 1.4142135623731 2000-09-07 2000-09-07 23:06:59 3

493

user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 2000-09-07 23:06:59 4

494

user_4 somepassword N 4 4 2 2000-09-07 2000-09-07 23:06:59 5

495

drop table t1;

496

CREATE TABLE t1 (

497

id int NOT NULL auto_increment,

498

parent_id int DEFAULT '0' NOT NULL,

499

level int DEFAULT '0' NOT NULL,

500

KEY (id),

501

KEY parent_id (parent_id),

502

KEY level (level)

503

) engine=innodb;

504

505

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

506

update t1 set parent_id=parent_id+100;

507

select * from t1 where parent_id=102;

508

id parent_id level

509

8 102 2

510

9 102 2

511

15 102 2

512

update t1 set id=id+1000;

513

update t1 set id=1024 where id=1009;

514

select * from t1;

515

id parent_id level

516

1001 100 0

517

1003 101 1

518

1004 101 1

519

1008 102 2

520

1024 102 2

521

1017 103 2

522

1022 104 2

523

1024 104 2

524

1028 105 2

525

1029 105 2

526

1030 105 2

527

1031 106 2

528

1032 106 2

529

1033 106 2

530

1203 107 2

531

1202 107 2

532

1020 103 2

533

1157 100 0

534

1193 105 2

535

1040 107 2

536

1002 101 1

537

1015 102 2

538

1006 101 1

539

1034 106 2

540

1035 106 2

541

1016 103 2

542

1007 101 1

543

1036 107 2

544

1018 103 2

545

1026 105 2

546

1027 105 2

547

1183 104 2

548

1038 107 2

549

1025 105 2

550

1037 107 2

551

1021 104 2

552

1019 103 2

553

1005 101 1

554

1179 105 2

555

update ignore t1 set id=id+1;

556

select * from t1;

557

id parent_id level

558

1002 100 0

559

1004 101 1

560

1005 101 1

561

1009 102 2

562

1025 102 2

563

1018 103 2

564

1023 104 2

565

1025 104 2

566

1029 105 2

567

1030 105 2

568

1031 105 2

569

1032 106 2

570

1033 106 2

571

1034 106 2

572

1204 107 2

573

1203 107 2

574

1021 103 2

575

1158 100 0

576

1194 105 2

577

1041 107 2

578

1003 101 1

579

1016 102 2

580

1007 101 1

581

1035 106 2

582

1036 106 2

583

1017 103 2

584

1008 101 1

585

1037 107 2

586

1019 103 2

587

1027 105 2

588

1028 105 2

589

1184 104 2

590

1039 107 2

591

1026 105 2

592

1038 107 2

593

1022 104 2

594

1020 103 2

595

1006 101 1

596

1180 105 2

597

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

598

select * from t1 where parent_id=102;

599

id parent_id level

600

1009 102 2

601

1025 102 2

602

1016 102 2

603

explain select level from t1 where level=1;

604

id select_type table type possible_keys key key_len ref rows Extra

605

1 SIMPLE t1 ref level level 4 const # Using index

606

select level,id from t1 where level=1;

607

level id

608

1 1004

609

1 1005

610

1 1003

611

1 1007

612

1 1008

613

1 1006

614

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

615

level id parent_id

616

1 1004 101

617

1 1005 101

618

1 1003 101

619

1 1007 101

620

1 1008 101

621

1 1006 101

622

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

623

level id

624

1 1003

625

1 1004

626

1 1005

627

1 1006

628

1 1007

629

1 1008

630

delete from t1 where level=1;

631

select * from t1;

632

id parent_id level

633

1002 100 0

634

1009 102 2

635

1025 102 2

636

1018 103 2

637

1023 104 2

638

1025 104 2

639

1029 105 2

640

1030 105 2

641

1031 105 2

642

1032 106 2

643

1033 106 2

644

1034 106 2

645

1204 107 2

646

1203 107 2

647

1021 103 2

648

1158 100 0

649

1194 105 2

650

1041 107 2

651

1016 102 2

652

1035 106 2

653

1036 106 2

654

1017 103 2

655

1037 107 2

656

1019 103 2

657

1027 105 2

658

1028 105 2

659

1184 104 2

660

1039 107 2

661

1026 105 2

662

1038 107 2

663

1022 104 2

664

1020 103 2

665

1180 105 2

666

drop table t1;

667

CREATE TABLE t1 (

668

sca_code char(6) NOT NULL,

669

cat_code char(6) NOT NULL,

670

sca_desc varchar(50),

671

lan_code char(2) NOT NULL,

672

sca_pic varchar(100),

673

sca_sdesc varchar(50),

674

sca_sch_desc varchar(16),

675

PRIMARY KEY (sca_code, cat_code, lan_code),

676

INDEX sca_pic (sca_pic)

677

) engine = innodb ;

678

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

679

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

680

count(*)

681

682

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

683

count(*)

684

685

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

686

count(*)

687

688

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

689

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

690

count(*)

691

692

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

693

count(*)

694

695

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

696

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

697

count(*)

698

699

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

700

count(*)

701

702

select sca_pic from t1 where sca_pic is null;

703

sca_pic

704

NULL

705

NULL

706

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

707

delete from t1 where sca_code='pd';

708

drop table t1;

709

set @a:=now();

710

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

711

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

712

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

713

714

715

716

717

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

718

719

720

721

722

update t1 set a=5 where a=1;

723

select a from t1;

724

725

726

727

728

drop table t1;

729

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

730

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

731

select * from t1 order by b desc;

732

a b

733

world 2

734

hello 1

735

alter table t1 engine=innodb;

736

show keys from t1;

737

Table Unique Key_name Seq_in_index Column_name

738

t1 YES PRIMARY 1 a

739

drop table t1;

740

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

741

insert into t1 values (1,2);

742

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

743

i j

744

1 2

745

create index ax1 on t1 (i,j);

746

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

747

i j

748

1 2

749

drop table t1;

750

CREATE TABLE t1 (

751

a int NOT NULL,

752

b int NOT NULL,

753

UNIQUE (a, b)

754

) ENGINE = innodb;

755

INSERT INTO t1 VALUES (1, 1);

756

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

757

MIN(B) MAX(b)

758

1 1

759

drop table t1;

760

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

761

INSERT INTO t1 VALUES (1);

762

SELECT * FROM t1;

763

764

765

DROP TABLE t1;

766

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;

767

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

768

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

769

id select_type table type possible_keys key key_len ref rows Extra

770

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where

771

drop table t1;

772

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

773

drop table t1;

774

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

775

show create table t1;

776

Table Create Table

777

t1 CREATE TABLE `t1` (

778

`a` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,

779

KEY `a` (`a`(5))

780

) ENGINE=InnoDB COLLATE = utf8_general_ci

781

drop table t1;

782

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

783

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

784

delete from t1 where a=3;

785

insert into t1 values (NULL);

786

select * from t1;

787

788

789

790

791

alter table t1 add b int;

792

select * from t1;

793

a b

794

1 NULL

795

2 NULL

796

4 NULL

797

drop table t1;

798

create table t1

799

(

800

id int auto_increment primary key,

801

name varchar(32) not null,

802

value text not null,

803

uid int not null,

804

unique key(name,uid)

805

) engine=innodb;

806

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

807

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

808

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

809

delete from t1 where uid=102;

810

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

811

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

812

select * from t1;

813

id name value uid

814

1 one one value 101

815

3 three three value 103

816

6 two other value 102

817

drop table t1;

818

create database mysqltest;

819

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

820

insert into mysqltest.t1 values(1);

821

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

822

insert into mysqltest.t2 values(1);

823

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

824

insert into mysqltest.t3 values(1);

825

commit;

826

drop database mysqltest;

827

show tables from mysqltest;

828

ERROR 42000: Unknown database 'mysqltest'

829

set autocommit=0;

830

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

831

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

832

truncate table t1;

833

commit;

834

truncate table t1;

835

truncate table t1;

836

select * from t1;

837

838

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

839

delete from t1;

840

select * from t1;

841

842

commit;

843

drop table t1;

844

set autocommit=1;

845

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

846

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

847

truncate table t1;

848

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

849

select * from t1;

850

851

852

853

truncate table t1;

854

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

855

delete from t1;

856

select * from t1;

857

858

drop table t1;

859

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

860

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

861

explain select * from t1 order by a;

862

id select_type table type possible_keys key key_len ref rows Extra

863

1 SIMPLE t1 index NULL PRIMARY 4 NULL #

864

explain select * from t1 order by b;

865

id select_type table type possible_keys key key_len ref rows Extra

866

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

867

explain select * from t1 order by c;

868

id select_type table type possible_keys key key_len ref rows Extra

869

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

870

explain select a from t1 order by a;

871

id select_type table type possible_keys key key_len ref rows Extra

872

1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index

873

explain select b from t1 order by b;

874

id select_type table type possible_keys key key_len ref rows Extra

875

1 SIMPLE t1 index NULL b 4 NULL # Using index

876

explain select a,b from t1 order by b;

877

id select_type table type possible_keys key key_len ref rows Extra

878

1 SIMPLE t1 index NULL b 4 NULL # Using index

879

explain select a,b from t1;

880

id select_type table type possible_keys key key_len ref rows Extra

881

1 SIMPLE t1 index NULL PRIMARY 4 NULL #

882

explain select a,b,c from t1;

883

id select_type table type possible_keys key key_len ref rows Extra

884

1 SIMPLE t1 ALL NULL NULL NULL NULL #

885

drop table t1;

886

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

887

desc t1;

888

Field Type Null Default Default_is_NULL On_Update

889

t INTEGER NO 1 NO

890

drop table t1;

891

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

892

BEGIN;

893

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

894

SELECT @@tx_isolation,@@global.tx_isolation;

895

@@tx_isolation @@global.tx_isolation

896

SERIALIZABLE REPEATABLE-READ

897

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

898

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

899

id code name

900

1 1 Tim

901

2 1 Monty

902

3 2 David

903

COMMIT;

904

BEGIN;

905

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

906

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

907

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

908

id code name

909

1 1 Tim

910

2 1 Monty

911

3 2 David

912

4 2 Erik

913

5 3 Sasha

914

COMMIT;

915

BEGIN;

916

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

917

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

918

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

919

id code name

920

1 1 Tim

921

2 1 Monty

922

3 2 David

923

4 2 Erik

924

5 3 Sasha

925

6 3 Jeremy

926

7 4 Matt

927

COMMIT;

928

DROP TABLE t1;

929

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

930

insert into t1 values(20,null);

931

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

932

t2.b=t3.a;

933

b ifnull(t2.b,"this is null")

934

NULL this is null

935

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

936

t2.b=t3.a order by 1;

937

b ifnull(t2.b,"this is null")

938

NULL this is null

939

insert into t1 values(10,null);

940

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

941

t2.b=t3.a order by 1;

942

b ifnull(t2.b,"this is null")

943

NULL this is null

944

NULL this is null

945

drop table t1;

946

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

947

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

948

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

949

950

drop table t1,t2;

951

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

952

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

953

insert into t1 values (10, 20);

954

insert into t2 values (10, 20);

955

drop table t1,t2;

956

set autocommit=0;

957

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

958

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

959

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

960

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

961

COMMIT;

962

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

963

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

964

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

965

SELECT * FROM t1;

966

id value

967

this-key will disappear

968

SELECT * FROM t2;

969

id value

970

this-key will also disappear

971

SELECT * FROM t3;

972

id1 id2

973

ROLLBACK;

974

SELECT * FROM t1;

975

id value

976

SELECT * FROM t2;

977

id value

978

SELECT * FROM t3;

979

id1 id2

980

my-test-1 my-test-2

981

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

982

id1 id2

983

my-test-1 my-test-2

984

COMMIT;

985

set autocommit=1;

986

DROP TABLE t1,t2,t3;

987

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

988

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

989

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

990

SELECT * from t1;

991

a b

992

1 1

993

4 4

994

5 5

995

6 6

996

7 7

997

8 8

998

9 9

999

102 2

1000

103 3

1001

drop table t1;

1002

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

1003

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

1004

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

1005

insert into t2 (a) select b from t1;

1006

insert into t1 (b) select b from t2;

1007

insert into t2 (a) select b from t1;

1008

insert into t1 (a) select b from t2;

1009

insert into t2 (a) select b from t1;

1010

insert into t1 (a) select b from t2;

1011

insert into t2 (a) select b from t1;

1012

insert into t1 (a) select b from t2;

1013

insert into t2 (a) select b from t1;

1014

insert into t1 (a) select b from t2;

1015

select count(*) from t1;

1016

count(*)

1017

623

1018

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

1019

id select_type table type possible_keys key key_len ref rows Extra

1020

1 SIMPLE t1 range c c 5 NULL # Using where

1021

update t1 set c=a;

1022

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

1023

id select_type table type possible_keys key key_len ref rows Extra

1024

1 SIMPLE t1 ALL c NULL NULL NULL # Using where

1025

drop table t1,t2;

1026

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

1027

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

1028

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

1029

SELECT * from t1;

1030

id fk

1031

1 69

1032

2 NULL

1033

3 NULL

1034

4 NULL

1035

5 NULL

1036

drop table t1;

1037

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

1038

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

1039

SET @tmp=0;

1040

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

1041

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

1042

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

1043

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

1044

select * from t1 order by a,b;

1045

a b

1046

2 4

1047

2 5

1048

2 6

1049

3 7

1050

3 8

1051

3 9

1052

3 10

1053

3 11

1054

3 12

1055

13 2

1056

111 100

1057

111 100

1058

drop table t1;

1059

SET AUTOCOMMIT=1;

1060

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

1061

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

1062

truncate table t1;

1063

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

1064

SELECT * from t1;

1065

1066

1067

1068

drop table t1;

1069

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

1070

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;

1071

drop table t2,t1;

1072

create table t1(

1073

id int primary key,

1074

pid int,

1075

index(pid),

1076

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

1077

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

1078

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

1079

delete from t1 where id=0;

1080

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t1` (`id`) ON DELETE CASCADE)

1081

delete from t1 where id=15;

1082

delete from t1 where id=0;

1083

drop table t1;

1084

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

1085

CREATE TABLE t2 (col1 int, stamp TIMESTAMP,INDEX stamp_idx (stamp)) ENGINE=InnoDB;

1086

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

1087

insert into t2 values (1, 20020204110000),(2, 20020204110001),(4,20020204110002 ),(5,20020204110003);

1088

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

1089

'20020204120000' GROUP BY col1;

1090

col1

1091

1092

1093

1094

1095

1096

drop table t1,t2;

1097

CREATE TABLE t1 (

1098

`id` int NOT NULL auto_increment,

1099

`id_object` int default '0',

1100

`id_version` int NOT NULL default '1',

1101

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

1102

`description` text,

1103

PRIMARY KEY (`id`),

1104

KEY `id_object` (`id_object`),

1105

KEY `id_version` (`id_version`)

1106

) ENGINE=InnoDB;

1107

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

1108

CREATE TABLE t2 (

1109

`id` int NOT NULL auto_increment,

1110

`id_version` int NOT NULL default '1',

1111

PRIMARY KEY (`id`),

1112

KEY `id_version` (`id_version`)

1113

) ENGINE=InnoDB;

1114

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

1115

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

1116

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

1117

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

1118

id label

1119

3382 Test

1120

102 Le Pekin (Test)

1121

1794 Test de resto

1122

1822 Test 3

1123

3524 Societe Test

1124

3525 Fournisseur Test

1125

drop table t1,t2;

1126

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

1127

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

1128

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

1129

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

1130

insert t2 select * from t1;

1131

insert t3 select * from t1;

1132

drop table t1,t2,t3;

1133

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

1134

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

1135

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

1136

trim(name2)

1137

fff

1138

sss

1139

ttt

1140

first

1141

second

1142

third

1143

1144

1145

1146

drop table t1;

1147

create table t1 (a int) engine=innodb;

1148

create table t2 like t1;

1149

drop table t1,t2;

1150

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

1151

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

1152

show create table t1;

1153

Table Create Table

1154

t1 CREATE TABLE `t1` (

1155

`id` INT NOT NULL,

1156

`id2` INT NOT NULL,

1157

UNIQUE KEY `id` (`id`,`id2`)

1158

) ENGINE=InnoDB COLLATE = utf8_general_ci

1159

show create table t2;

1160

Table Create Table

1161

t2 CREATE TABLE `t2` (

1162

`id` INT NOT NULL,

1163

KEY `t1_id_fk` (`id`),

1164

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1165

) ENGINE=InnoDB COLLATE = utf8_general_ci

1166

create index id on t2 (id);

1167

show create table t2;

1168

Table Create Table

1169

t2 CREATE TABLE `t2` (

1170

`id` INT NOT NULL,

1171

KEY `id` (`id`),

1172

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1173

) ENGINE=InnoDB COLLATE = utf8_general_ci

1174

create index id2 on t2 (id);

1175

show create table t2;

1176

Table Create Table

1177

t2 CREATE TABLE `t2` (

1178

`id` INT NOT NULL,

1179

KEY `id` (`id`),

1180

KEY `id2` (`id`),

1181

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1182

) ENGINE=InnoDB COLLATE = utf8_general_ci

1183

drop index id2 on t2;

1184

drop index id on t2;

1185

Got one of the listed errors

1186

show create table t2;

1187

Table Create Table

1188

t2 CREATE TABLE `t2` (

1189

`id` INT NOT NULL,

1190

KEY `id` (`id`),

1191

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1192

) ENGINE=InnoDB COLLATE = utf8_general_ci

1193

drop table t2;

1194

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

1195

show create table t2;

1196

Table Create Table

1197

t2 CREATE TABLE `t2` (

1198

`id` INT NOT NULL,

1199

`id2` INT NOT NULL,

1200

KEY `t1_id_fk` (`id`,`id2`),

1201

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)

1202

) ENGINE=InnoDB COLLATE = utf8_general_ci

1203

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

1204

show create table t2;

1205

Table Create Table

1206

t2 CREATE TABLE `t2` (

1207

`id` INT NOT NULL,

1208

`id2` INT NOT NULL,

1209

UNIQUE KEY `id` (`id`,`id2`),

1210

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)

1211

) ENGINE=InnoDB COLLATE = utf8_general_ci

1212

drop table t2;

1213

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

1214

show create table t2;

1215

Table Create Table

1216

t2 CREATE TABLE `t2` (

1217

`id` INT NOT NULL,

1218

`id2` INT NOT NULL,

1219

UNIQUE KEY `id` (`id`,`id2`),

1220

KEY `t1_id_fk` (`id2`,`id`),

1221

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)

1222

) ENGINE=InnoDB COLLATE = utf8_general_ci

1223

drop table t2;

1224

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

1225

show create table t2;

1226

Table Create Table

1227

t2 CREATE TABLE `t2` (

1228

`id` INT NOT NULL,

1229

`id2` INT NOT NULL,

1230

UNIQUE KEY `id` (`id`,`id2`),

1231

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1232

) ENGINE=InnoDB COLLATE = utf8_general_ci

1233

drop table t2;

1234

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

1235

show create table t2;

1236

Table Create Table

1237

t2 CREATE TABLE `t2` (

1238

`id` INT NOT NULL,

1239

`id2` INT NOT NULL,

1240

UNIQUE KEY `id` (`id`,`id2`),

1241

KEY `t1_id_fk` (`id2`,`id`),

1242

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)

1243

) ENGINE=InnoDB COLLATE = utf8_general_ci

1244

drop table t2;

1245

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

1246

show create table t2;

1247

Table Create Table

1248

t2 CREATE TABLE `t2` (

1249

`id` INT NOT NULL AUTO_INCREMENT,

1250

`id2` INT NOT NULL,

1251

PRIMARY KEY (`id`),

1252

KEY `id` (`id`,`id2`),

1253

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1254

) ENGINE=InnoDB COLLATE = utf8_general_ci

1255

drop table t2;

1256

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

1257

show create table t2;

1258

Table Create Table

1259

t2 CREATE TABLE `t2` (

1260

`id` INT NOT NULL AUTO_INCREMENT,

1261

`id2` INT NOT NULL,

1262

KEY `t1_id_fk` (`id`),

1263

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1264

) ENGINE=InnoDB COLLATE = utf8_general_ci

1265

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

1266

show create table t2;

1267

Table Create Table

1268

t2 CREATE TABLE `t2` (

1269

`id` INT NOT NULL AUTO_INCREMENT,

1270

`id2` INT NOT NULL,

1271

KEY `id_test` (`id`),

1272

KEY `id_test2` (`id`,`id2`),

1273

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1274

) ENGINE=InnoDB COLLATE = utf8_general_ci

1275

drop table t2;

1276

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

1277

ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match

1278

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

1279

show create table t2;

1280

Table Create Table

1281

t2 CREATE TABLE `t2` (

1282

`a` INT NOT NULL AUTO_INCREMENT,

1283

`b` INT DEFAULT NULL,

1284

PRIMARY KEY (`a`),

1285

UNIQUE KEY `b_2` (`b`),

1286

KEY `b` (`b`),

1287

CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)

1288

) ENGINE=InnoDB COLLATE = utf8_general_ci

1289

drop table t2;

1290

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;

1291

show create table t2;

1292

Table Create Table

1293

t2 CREATE TABLE `t2` (

1294

`a` INT NOT NULL AUTO_INCREMENT,

1295

`b` INT DEFAULT NULL,

1296

PRIMARY KEY (`a`),

1297

UNIQUE KEY `b` (`b`),

1298

CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),

1299

CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)

1300

) ENGINE=InnoDB COLLATE = utf8_general_ci

1301

drop table t2, t1;

1302

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

1303

ERROR 42S21: Duplicate column name 'c'

1304

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

1305

ERROR 42S21: Duplicate column name 'c1'

1306

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

1307

ERROR 42S21: Duplicate column name 'c1'

1308

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

1309

ERROR 42S21: Duplicate column name 'c1'

1310

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

1311

alter table t1 add key (c1,c1);

1312

ERROR 42S21: Duplicate column name 'c1'

1313

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

1314

ERROR 42S21: Duplicate column name 'c1'

1315

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

1316

ERROR 42S21: Duplicate column name 'c1'

1317

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

1318

ERROR 42S21: Duplicate column name 'c1'

1319

drop table t1;

1320

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

1321

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

1322

select distinct concat(a, b) from t1;

1323

concat(a, b)

1324

11113333

1325

drop table t1;

1326

CREATE TABLE t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB;

1327

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

1328

CREATE TABLE t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1329

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

1330

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

1331

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;

1332

a_id b_list

1333

1 1,2,3

1334

2 4,5

1335

3 NULL

1336

DROP TABLE t2;

1337

DROP TABLE t1;

1338

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

1339

insert into t1 values (4711);

1340

truncate t1;

1341

insert into t1 values (42);

1342

select * from t1;

1343

1344

1345

drop table t1;

1346

create table t1 (a int) engine=innodb;

1347

insert into t1 values (4711);

1348

truncate t1;

1349

insert into t1 values (42);

1350

select * from t1;

1351

1352

1353

drop table t1;

1354

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;

1355

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

1356

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

1357

a b c d e

1358

1 1 a 1 1

1359

2 2 b 2 2

1360

3 3 ab 3 3

1361

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

1362

id select_type table type possible_keys key key_len ref rows Extra

1363

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

1364

drop table t1;

1365

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

1366

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

1367

select min(a) from t1;

1368

min(a)

1369

1370

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

1371

min(b)

1372

1373

drop table t1;

1374

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

1375

insert into t1(x) values (0x0ffffffffffffff0),(0x0ffffffffffffff1);

1376

select * from t1;

1377

1378

1152921504606846960

1379

1152921504606846961

1380

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

1381

count(*)

1382

1383

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

1384

count(*)

1385

1386

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

1387

count(*)

1388

1389

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

1390

count(*)

1391

1392

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

1393

count(*)

1394

1395

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

1396

id select_type table type possible_keys key key_len ref rows Extra

1397

1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index

1398

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

1399

count(*)

1400

1401

select * from t1 where x > -16;

1402

1403

1152921504606846960

1404

1152921504606846961

1405

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

1406

count(*)

1407

1408

drop table t1;

1409

show status like "Innodb_buffer_pool_pages_total";

1410

Variable_name Value

1411

show status like "Innodb_page_size";

1412

Variable_name Value

1413

show status like "Innodb_rows_deleted";

1414

Variable_name Value

1415

show status like "Innodb_rows_inserted";

1416

Variable_name Value

1417

show status like "Innodb_rows_updated";

1418

Variable_name Value

1419

show status like "Innodb_row_lock_waits";

1420

Variable_name Value

1421

show status like "Innodb_row_lock_current_waits";

1422

Variable_name Value

1423

show status like "Innodb_row_lock_time";

1424

Variable_name Value

1425

show status like "Innodb_row_lock_time_max";

1426

Variable_name Value

1427

show status like "Innodb_row_lock_time_avg";

1428

Variable_name Value

1429

show variables like "innodb_sync_spin_loops";

1430

Variable_name Value

1431

innodb_sync_spin_loops 30

1432

set global innodb_sync_spin_loops=1000;

1433

show variables like "innodb_sync_spin_loops";

1434

Variable_name Value

1435

innodb_sync_spin_loops 1000

1436

set global innodb_sync_spin_loops=0;

1437

show variables like "innodb_sync_spin_loops";

1438

Variable_name Value

1439

innodb_sync_spin_loops 0

1440

set global innodb_sync_spin_loops=20;

1441

show variables like "innodb_sync_spin_loops";

1442

Variable_name Value

1443

innodb_sync_spin_loops 20

1444

show variables like "innodb_thread_concurrency";

1445

Variable_name Value

1446

innodb_thread_concurrency 0

1447

set global innodb_thread_concurrency=1001;

1448

Warnings:

1449

Error 1292 Truncated incorrect thread_concurrency value: '1001'

1450

show variables like "innodb_thread_concurrency";

1451

Variable_name Value

1452

innodb_thread_concurrency 1000

1453

set global innodb_thread_concurrency=0;

1454

show variables like "innodb_thread_concurrency";

1455

Variable_name Value

1456

innodb_thread_concurrency 0

1457

set global innodb_thread_concurrency=16;

1458

show variables like "innodb_thread_concurrency";

1459

Variable_name Value

1460

innodb_thread_concurrency 16

1461

show variables like "innodb_concurrency_tickets";

1462

Variable_name Value

1463

innodb_concurrency_tickets 500

1464

set global innodb_concurrency_tickets=1000;

1465

show variables like "innodb_concurrency_tickets";

1466

Variable_name Value

1467

innodb_concurrency_tickets 1000

1468

set global innodb_concurrency_tickets=0;

1469

Warnings:

1470

Error 1292 Truncated incorrect concurrency_tickets value: '0'

1471

show variables like "innodb_concurrency_tickets";

1472

Variable_name Value

1473

innodb_concurrency_tickets 1

1474

set global innodb_concurrency_tickets=500;

1475

show variables like "innodb_concurrency_tickets";

1476

Variable_name Value

1477

innodb_concurrency_tickets 500

1478

show variables like "innodb_thread_sleep_delay";

1479

Variable_name Value

1480

innodb_thread_sleep_delay 10000

1481

set global innodb_thread_sleep_delay=100000;

1482

show variables like "innodb_thread_sleep_delay";

1483

Variable_name Value

1484

innodb_thread_sleep_delay 100000

1485

set global innodb_thread_sleep_delay=0;

1486

show variables like "innodb_thread_sleep_delay";

1487

Variable_name Value

1488

innodb_thread_sleep_delay 0

1489

set global innodb_thread_sleep_delay=10000;

1490

show variables like "innodb_thread_sleep_delay";

1491

Variable_name Value

1492

innodb_thread_sleep_delay 10000

1493

set storage_engine=INNODB;

1494

drop table if exists t1,t2,t3;

1495

--- Testing varchar ---

1496

--- Testing varchar ---

1497

create table t1 (v varchar(10), c char(10), t text);

1498

insert into t1 values('+ ', '+ ', '+ ');

1499

set @a=repeat(' ',20);

1500

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1501

ERROR 22001: Data too long for column 'v' at row 1

1502

set @a=repeat(' ',10);

1503

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1504

ERROR 22001: Data too long for column 'v' at row 1

1505

set @a=repeat(' ',9);

1506

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1507

select concat('*',v,'*',c,'*',t,'*') from t1;

1508

concat('*',v,'*',c,'*',t,'*')

1509

*+ *+ *+ *

1510

*+ *+ *+ *

1511

show create table t1;

1512

Table Create Table

1513

t1 CREATE TABLE `t1` (

1514

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1515

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1516

`t` TEXT COLLATE utf8_general_ci

1517

) ENGINE=InnoDB COLLATE = utf8_general_ci

1518

create table t2 like t1;

1519

show create table t2;

1520

Table Create Table

1521

t2 CREATE TABLE `t2` (

1522

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1523

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1524

`t` TEXT COLLATE utf8_general_ci

1525

) ENGINE=InnoDB COLLATE = utf8_general_ci

1526

create table t3 select * from t1;

1527

show create table t3;

1528

Table Create Table

1529

t3 CREATE TABLE `t3` (

1530

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1531

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1532

`t` TEXT COLLATE utf8_general_ci

1533

) ENGINE=InnoDB COLLATE = utf8_general_ci

1534

alter table t1 modify c varchar(10);

1535

show create table t1;

1536

Table Create Table

1537

t1 CREATE TABLE `t1` (

1538

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1539

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1540

`t` TEXT COLLATE utf8_general_ci

1541

) ENGINE=InnoDB COLLATE = utf8_general_ci

1542

alter table t1 modify v char(10);

1543

show create table t1;

1544

Table Create Table

1545

t1 CREATE TABLE `t1` (

1546

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1547

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1548

`t` TEXT COLLATE utf8_general_ci

1549

) ENGINE=InnoDB COLLATE = utf8_general_ci

1550

alter table t1 modify t varchar(10);

1551

show create table t1;

1552

Table Create Table

1553

t1 CREATE TABLE `t1` (

1554

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1555

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1556

`t` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

1557

) ENGINE=InnoDB COLLATE = utf8_general_ci

1558

select concat('*',v,'*',c,'*',t,'*') from t1;

1559

concat('*',v,'*',c,'*',t,'*')

1560

*+ *+ *+ *

1561

*+ *+ *+ *

1562

drop table t1,t2,t3;

1563

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1564

show create table t1;

1565

Table Create Table

1566

t1 CREATE TABLE `t1` (

1567

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1568

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1569

`t` TEXT COLLATE utf8_general_ci,

1570

KEY `v` (`v`),

1571

KEY `c` (`c`),

1572

KEY `t` (`t`(10))

1573

) ENGINE=InnoDB COLLATE = utf8_general_ci

1574

select count(*) from t1;

1575

count(*)

1576

270

1577

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

1578

select count(*) from t1 where v='a';

1579

count(*)

1580

1581

select count(*) from t1 where c='a';

1582

count(*)

1583

1584

select count(*) from t1 where t='a';

1585

count(*)

1586

1587

select count(*) from t1 where v='a ';

1588

count(*)

1589

1590

select count(*) from t1 where c='a ';

1591

count(*)

1592

1593

select count(*) from t1 where t='a ';

1594

count(*)

1595

1596

select count(*) from t1 where v between 'a' and 'a ';

1597

count(*)

1598

1599

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1600

count(*)

1601

1602

select count(*) from t1 where v like 'a%';

1603

count(*)

1604

1605

select count(*) from t1 where c like 'a%';

1606

count(*)

1607

1608

select count(*) from t1 where t like 'a%';

1609

count(*)

1610

1611

select count(*) from t1 where v like 'a %';

1612

count(*)

1613

1614

explain select count(*) from t1 where v='a ';

1615

id select_type table type possible_keys key key_len ref rows Extra

1616

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1617

explain select count(*) from t1 where c='a ';

1618

id select_type table type possible_keys key key_len ref rows Extra

1619

1 SIMPLE t1 ref c c 43 const # Using where; Using index

1620

explain select count(*) from t1 where t='a ';

1621

id select_type table type possible_keys key key_len ref rows Extra

1622

1 SIMPLE t1 ref t t 43 const # Using where

1623

explain select count(*) from t1 where v like 'a%';

1624

id select_type table type possible_keys key key_len ref rows Extra

1625

1 SIMPLE t1 range v v 43 NULL # Using where; Using index

1626

explain select count(*) from t1 where v between 'a' and 'a ';

1627

id select_type table type possible_keys key key_len ref rows Extra

1628

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1629

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1630

id select_type table type possible_keys key key_len ref rows Extra

1631

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1632

alter table t1 add unique(v);

1633

ERROR 23000: Duplicate entry '{ ' for key 'v_2'

1634

alter table t1 add key(v);

1635

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';

1636

1637

*a*a*a*

1638

*a *a *a *

1639

*a *a *a *

1640

*a *a *a *

1641

*a *a *a *

1642

*a *a *a *

1643

*a *a *a *

1644

*a *a *a *

1645

*a *a *a *

1646

*a *a *a *

1647

explain select * from t1 where v='a';

1648

id select_type table type possible_keys key key_len ref rows Extra

1649

1 SIMPLE t1 ref v,v_2 # 43 const # Using where

1650

select v,count(*) from t1 group by v limit 10;

1651

v count(*)

1652

a 1

1653

a 10

1654

b 10

1655

c 10

1656

d 10

1657

e 10

1658

f 10

1659

g 10

1660

h 10

1661

i 10

1662

select v,count(t) from t1 group by v limit 10;

1663

v count(t)

1664

a 1

1665

a 10

1666

b 10

1667

c 10

1668

d 10

1669

e 10

1670

f 10

1671

g 10

1672

h 10

1673

i 10

1674

select v,count(c) from t1 group by v limit 10;

1675

v count(c)

1676

a 1

1677

a 10

1678

b 10

1679

c 10

1680

d 10

1681

e 10

1682

f 10

1683

g 10

1684

h 10

1685

i 10

1686

select sql_big_result v,count(t) from t1 group by v limit 10;

1687

v count(t)

1688

a 1

1689

a 10

1690

b 10

1691

c 10

1692

d 10

1693

e 10

1694

f 10

1695

g 10

1696

h 10

1697

i 10

1698

select sql_big_result v,count(c) from t1 group by v limit 10;

1699

v count(c)

1700

a 1

1701

a 10

1702

b 10

1703

c 10

1704

d 10

1705

e 10

1706

f 10

1707

g 10

1708

h 10

1709

i 10

1710

select c,count(*) from t1 group by c limit 10;

1711

c count(*)

1712

a 1

1713

a 10

1714

b 10

1715

c 10

1716

d 10

1717

e 10

1718

f 10

1719

g 10

1720

h 10

1721

i 10

1722

select c,count(t) from t1 group by c limit 10;

1723

c count(t)

1724

a 1

1725

a 10

1726

b 10

1727

c 10

1728

d 10

1729

e 10

1730

f 10

1731

g 10

1732

h 10

1733

i 10

1734

select sql_big_result c,count(t) from t1 group by c limit 10;

1735

c count(t)

1736

a 1

1737

a 10

1738

b 10

1739

c 10

1740

d 10

1741

e 10

1742

f 10

1743

g 10

1744

h 10

1745

i 10

1746

select t,count(*) from t1 group by t limit 10;

1747

t count(*)

1748

a 1

1749

a 10

1750

b 10

1751

c 10

1752

d 10

1753

e 10

1754

f 10

1755

g 10

1756

h 10

1757

i 10

1758

select t,count(t) from t1 group by t limit 10;

1759

t count(t)

1760

a 1

1761

a 10

1762

b 10

1763

c 10

1764

d 10

1765

e 10

1766

f 10

1767

g 10

1768

h 10

1769

i 10

1770

select sql_big_result t,count(t) from t1 group by t limit 10;

1771

t count(t)

1772

a 1

1773

a 10

1774

b 10

1775

c 10

1776

d 10

1777

e 10

1778

f 10

1779

g 10

1780

h 10

1781

i 10

1782

alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);

1783

Warnings:

1784

Warning 1071 Specified key was too long; max key length is 1023 bytes

1785

show create table t1;

1786

Table Create Table

1787

t1 CREATE TABLE `t1` (

1788

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1789

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1790

`t` TEXT COLLATE utf8_general_ci,

1791

KEY `c` (`c`),

1792

KEY `t` (`t`(10)),

1793

KEY `v` (`v`(255))

1794

) ENGINE=InnoDB COLLATE = utf8_general_ci

1795

select count(*) from t1 where v='a';

1796

count(*)

1797

1798

select count(*) from t1 where v='a ';

1799

count(*)

1800

1801

select count(*) from t1 where v between 'a' and 'a ';

1802

count(*)

1803

1804

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1805

count(*)

1806

1807

select count(*) from t1 where v like 'a%';

1808

count(*)

1809

1810

select count(*) from t1 where v like 'a %';

1811

count(*)

1812

1813

explain select count(*) from t1 where v='a ';

1814

id select_type table type possible_keys key key_len ref rows Extra

1815

1 SIMPLE t1 ref v v 1023 const # Using where

1816

explain select count(*) from t1 where v like 'a%';

1817

id select_type table type possible_keys key key_len ref rows Extra

1818

1 SIMPLE t1 range v v 1023 NULL # Using where

1819

explain select count(*) from t1 where v between 'a' and 'a ';

1820

id select_type table type possible_keys key key_len ref rows Extra

1821

1 SIMPLE t1 ref v v 1023 const # Using where

1822

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1823

id select_type table type possible_keys key key_len ref rows Extra

1824

1 SIMPLE t1 ref v v 1023 const # Using where

1825

explain select * from t1 where v='a';

1826

id select_type table type possible_keys key key_len ref rows Extra

1827

1 SIMPLE t1 ref v v 1023 const # Using where

1828

select v,count(*) from t1 group by v limit 10;

1829

v count(*)

1830

a 1

1831

a 10

1832

b 10

1833

c 10

1834

d 10

1835

e 10

1836

f 10

1837

g 10

1838

h 10

1839

i 10

1840

select v,count(t) from t1 group by v limit 10;

1841

v count(t)

1842

a 1

1843

a 10

1844

b 10

1845

c 10

1846

d 10

1847

e 10

1848

f 10

1849

g 10

1850

h 10

1851

i 10

1852

select sql_big_result v,count(t) from t1 group by v limit 10;

1853

v count(t)

1854

a 1

1855

a 10

1856

b 10

1857

c 10

1858

d 10

1859

e 10

1860

f 10

1861

g 10

1862

h 10

1863

i 10

1864

alter table t1 drop key v, add key v (v(30));

1865

show create table t1;

1866

Table Create Table

1867

t1 CREATE TABLE `t1` (

1868

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1869

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1870

`t` TEXT COLLATE utf8_general_ci,

1871

KEY `c` (`c`),

1872

KEY `t` (`t`(10)),

1873

KEY `v` (`v`(30))

1874

) ENGINE=InnoDB COLLATE = utf8_general_ci

1875

select count(*) from t1 where v='a';

1876

count(*)

1877

1878

select count(*) from t1 where v='a ';

1879

count(*)

1880

1881

select count(*) from t1 where v between 'a' and 'a ';

1882

count(*)

1883

1884

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1885

count(*)

1886

1887

select count(*) from t1 where v like 'a%';

1888

count(*)

1889

1890

select count(*) from t1 where v like 'a %';

1891

count(*)

1892

1893

explain select count(*) from t1 where v='a ';

1894

id select_type table type possible_keys key key_len ref rows Extra

1895

1 SIMPLE t1 ref v v 123 const # Using where

1896

explain select count(*) from t1 where v like 'a%';

1897

id select_type table type possible_keys key key_len ref rows Extra

1898

1 SIMPLE t1 range v v 123 NULL # Using where

1899

explain select count(*) from t1 where v between 'a' and 'a ';

1900

id select_type table type possible_keys key key_len ref rows Extra

1901

1 SIMPLE t1 ref v v 123 const # Using where

1902

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1903

id select_type table type possible_keys key key_len ref rows Extra

1904

1 SIMPLE t1 ref v v 123 const # Using where

1905

explain select * from t1 where v='a';

1906

id select_type table type possible_keys key key_len ref rows Extra

1907

1 SIMPLE t1 ref v v 123 const # Using where

1908

select v,count(*) from t1 group by v limit 10;

1909

v count(*)

1910

a 1

1911

a 10

1912

b 10

1913

c 10

1914

d 10

1915

e 10

1916

f 10

1917

g 10

1918

h 10

1919

i 10

1920

select v,count(t) from t1 group by v limit 10;

1921

v count(t)

1922

a 1

1923

a 10

1924

b 10

1925

c 10

1926

d 10

1927

e 10

1928

f 10

1929

g 10

1930

h 10

1931

i 10

1932

select sql_big_result v,count(t) from t1 group by v limit 10;

1933

v count(t)

1934

a 1

1935

a 10

1936

b 10

1937

c 10

1938

d 10

1939

e 10

1940

f 10

1941

g 10

1942

h 10

1943

i 10

1944

alter table t1 modify v varchar(600), drop key v, add key v (v);

1945

Warnings:

1946

Warning 1071 Specified key was too long; max key length is 1023 bytes

1947

show create table t1;

1948

Table Create Table

1949

t1 CREATE TABLE `t1` (

1950

`v` VARCHAR(600) COLLATE utf8_general_ci DEFAULT NULL,

1951

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1952

`t` TEXT COLLATE utf8_general_ci,

1953

KEY `c` (`c`),

1954

KEY `t` (`t`(10)),

1955

KEY `v` (`v`(255))

1956

) ENGINE=InnoDB COLLATE = utf8_general_ci

1957

select v,count(*) from t1 group by v limit 10;

1958

v count(*)

1959

a 1

1960

a 10

1961

b 10

1962

c 10

1963

d 10

1964

e 10

1965

f 10

1966

g 10

1967

h 10

1968

i 10

1969

select v,count(t) from t1 group by v limit 10;

1970

v count(t)

1971

a 1

1972

a 10

1973

b 10

1974

c 10

1975

d 10

1976

e 10

1977

f 10

1978

g 10

1979

h 10

1980

i 10

1981

select sql_big_result v,count(t) from t1 group by v limit 10;

1982

v count(t)

1983

a 1

1984

a 10

1985

b 10

1986

c 10

1987

d 10

1988

e 10

1989

f 10

1990

g 10

1991

h 10

1992

i 10

1993

drop table t1;

1994

create table t1 (a char(10), unique (a));

1995

insert into t1 values ('a ');

1996

insert into t1 values ('a ');

1997

ERROR 23000: Duplicate entry 'a ' for key 'a'

1998

alter table t1 modify a varchar(10);

1999

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

2000

ERROR 23000: Duplicate entry 'a ' for key 'a'

2001

insert into t1 values ('a ');

2002

ERROR 23000: Duplicate entry 'a ' for key 'a'

2003

insert into t1 values ('a ');

2004

ERROR 22001: Data too long for column 'a' at row 1

2005

insert into t1 values ('a ');

2006

ERROR 23000: Duplicate entry 'a ' for key 'a'

2007

update t1 set a='a ' where a like 'a%';

2008

select concat(a,'.') from t1;

2009

concat(a,'.')

2010

a .

2011

update t1 set a='abc ' where a like 'a ';

2012

select concat(a,'.') from t1;

2013

concat(a,'.')

2014

a .

2015

update t1 set a='a ' where a like 'a %';

2016

select concat(a,'.') from t1;

2017

concat(a,'.')

2018

a .

2019

update t1 set a='a ' where a like 'a ';

2020

select concat(a,'.') from t1;

2021

concat(a,'.')

2022

a .

2023

drop table t1;

2024

create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

2025

show create table t1;

2026

Table Create Table

2027

t1 CREATE TABLE `t1` (

2028

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2029

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2030

`t` TEXT COLLATE utf8_general_ci,

2031

KEY `v` (`v`(5)),

2032

KEY `c` (`c`(5)),

2033

KEY `t` (`t`(5))

2034

) ENGINE=InnoDB COLLATE = utf8_general_ci

2035

drop table t1;

2036

create table t1 (v char(10));

2037

show create table t1;

2038

Table Create Table

2039

t1 CREATE TABLE `t1` (

2040

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

2041

) ENGINE=InnoDB COLLATE = utf8_general_ci

2042

drop table t1;

2043

create table t1 (v varchar(10), c char(10));

2044

show create table t1;

2045

Table Create Table

2046

t1 CREATE TABLE `t1` (

2047

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2048

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

2049

) ENGINE=InnoDB COLLATE = utf8_general_ci

2050

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

2051

select concat('*',v,'*',c,'*') from t1;

2052

concat('*',v,'*',c,'*')

2053

*a*a*

2054

*a *a *

2055

drop table t1;

2056

create table t1(a int, b varchar(12), key ba(b, a));

2057

insert into t1 values (1, 'A'), (20, NULL);

2058

explain select * from t1 where a=20 and b is null;

2059

id select_type table type possible_keys key key_len ref rows Extra

2060

1 SIMPLE t1 ref ba ba 56 const,const 1 Using where; Using index

2061

select * from t1 where a=20 and b is null;

2062

a b

2063

20 NULL

2064

drop table t1;

2065

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

2066

Warnings:

2067

Warning 1071 Specified key was too long; max key length is 1023 bytes

2068

drop table t1;

2069

create table t1 (v varchar(16383));

2070

show create table t1;

2071

Table Create Table

2072

t1 CREATE TABLE `t1` (

2073

`v` VARCHAR(16383) COLLATE utf8_general_ci DEFAULT NULL

2074

) ENGINE=InnoDB COLLATE = utf8_general_ci

2075

drop table t1;

2076

create table t1 (v varchar(16383));

2077

show create table t1;

2078

Table Create Table

2079

t1 CREATE TABLE `t1` (

2080

`v` VARCHAR(16383) COLLATE utf8_general_ci DEFAULT NULL

2081

) ENGINE=InnoDB COLLATE = utf8_general_ci

2082

drop table t1;

2083

set storage_engine=PBXT;

2084

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

2085

drop table t1;

2086

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

2087

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

2088

select min(a) from t1;

2089

min(a)

2090

2091

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

2092

min(b)

2093

2094

drop table t1;

2095

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

2096

insert into t1 (b) values (1);

2097

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

2098

select * from t1;

2099

a b

2100

2 2

2101

3 1

2102

4 3

2103

truncate table t1;

2104

insert into t1 (b) values (1);

2105

replace into t1 (b) values (2);

2106

replace into t1 (b) values (1);

2107

replace into t1 (b) values (3);

2108

select * from t1;

2109

a b

2110

2 2

2111

3 1

2112

4 3

2113

drop table t1;

2114

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

2115

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

2116

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

2117

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

2118

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

2119

ERROR 23000: Duplicate entry '1' for key 'val'

2120

select * from t1;

2121

rowid val

2122

3 1

2123

4 2

2124

drop table t1;

2125

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

2126

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

2127

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

2128

GRADE

2129

252

2130

SELECT GRADE FROM t1 WHERE GRADE= 151;

2131

GRADE

2132

151

2133

DROP TABLE t1;

2134

CREATE TABLE t1 (

2135

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

2136

) ENGINE=InnoDB;

2137

CREATE TABLE t2 (

2138

id INTEGER NOT NULL,

2139

FOREIGN KEY (id) REFERENCES t1 (id)

2140

) ENGINE=InnoDB;

2141

INSERT INTO t1 (id) VALUES (NULL);

2142

SELECT * FROM t1;

2143

2144

2145

TRUNCATE t1;

2146

INSERT INTO t1 (id) VALUES (NULL);

2147

SELECT * FROM t1;

2148

2149

2150

DELETE FROM t1;

2151

TRUNCATE t1;

2152

INSERT INTO t1 (id) VALUES (NULL);

2153

SELECT * FROM t1;

2154

2155

2156

DROP TABLE t2, t1;

2157

CREATE TABLE t1

2158

(

2159

id INT PRIMARY KEY

2160

) ENGINE=InnoDB;

2161

CREATE TEMPORARY TABLE t2

2162

(

2163

id INT NOT NULL PRIMARY KEY,

2164

b INT,

2165

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

2166

) ENGINE=InnoDB;

2167

Got one of the listed errors

2168

DROP TABLE t1;

2169

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

2170

engine = innodb;

2171

Warnings:

2172

Warning 1071 Specified key was too long; max key length is 1023 bytes

2173

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

2174

engine = innodb;

2175

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

2176

engine = innodb;

2177

Warnings:

2178

Warning 1071 Specified key was too long; max key length is 1023 bytes

2179

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

2180

engine = innodb;

2181

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

2182

engine = innodb;

2183

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

2184

engine = innodb;

2185

Warnings:

2186

Warning 1071 Specified key was too long; max key length is 1023 bytes

2187

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

2188

engine = innodb;

2189

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

2190

engine = innodb;

2191

Warnings:

2192

Warning 1071 Specified key was too long; max key length is 1023 bytes

2193

Warning 1071 Specified key was too long; max key length is 1023 bytes

2194

show create table t9;

2195

Table Create Table

2196

t9 CREATE TABLE `t9` (

2197

`col1` VARCHAR(512) COLLATE utf8_general_ci DEFAULT NULL,

2198

`col2` VARCHAR(512) COLLATE utf8_general_ci DEFAULT NULL,

2199

KEY `col1` (`col1`(255),`col2`(255))

2200

) ENGINE=InnoDB COLLATE = utf8_general_ci

2201

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

2202

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

2203

engine = innodb;

2204

Warnings:

2205

Warning 1071 Specified key was too long; max key length is 1023 bytes

2206

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

2207

engine = innodb;

2208

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

2209

engine = innodb;

2210

Warnings:

2211

Warning 1071 Specified key was too long; max key length is 1023 bytes

2212

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

2213

engine = innodb;

2214

show create table t1;

2215

Table Create Table

2216

t1 CREATE TABLE `t1` (

2217

`col1` VARCHAR(768) COLLATE utf8_general_ci DEFAULT NULL,

2218

KEY `col1` (`col1`(255))

2219

) ENGINE=InnoDB COLLATE = utf8_general_ci

2220

drop table t1, t2, t3, t4;

2221

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

2222

engine = innodb;

2223

ERROR 42000: Specified key was too long; max key length is 1023 bytes

2224

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

2225

engine = innodb;

2226

ERROR 42000: Specified key was too long; max key length is 1023 bytes

2227

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

2228

engine = innodb;

2229

ERROR 42000: Specified key was too long; max key length is 1023 bytes

2230

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

2231

engine = innodb;

2232

ERROR 42000: Specified key was too long; max key length is 1023 bytes

2233

CREATE TABLE t1

2234

(

2235

id INT PRIMARY KEY

2236

) ENGINE=InnoDB;

2237

CREATE TABLE t2

2238

(

2239

v INT,

2240

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

2241

) ENGINE=InnoDB;

2242

INSERT INTO t2 VALUES(2);

2243

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))

2244

INSERT INTO t1 VALUES(1);

2245

INSERT INTO t2 VALUES(1);

2246

DELETE FROM t1 WHERE id = 1;

2247

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))

2248

DROP TABLE t1;

2249

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails

2250

SET FOREIGN_KEY_CHECKS=0;

2251

DROP TABLE t1;

2252

SET FOREIGN_KEY_CHECKS=1;

2253

INSERT INTO t2 VALUES(3);

2254

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))

2255

DROP TABLE t2;

2256

set foreign_key_checks=0;

2257

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

2258

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

2259

ERROR HY000: Can't create table 'test.t1' (errno: 150)

2260

set foreign_key_checks=1;

2261

drop table t2;

2262

set foreign_key_checks=0;

2263

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

2264

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

2265

alter table t1 modify column a int;

2266

Got one of the listed errors

2267

set foreign_key_checks=1;

2268

drop table t2,t1;

2269

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

2270

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

2271

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

2272

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

2273

insert into t1 values(1);

2274

insert into t3 values(1);

2275

insert into t2 values(2);

2276

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

2277

insert into t4 values(2);

2278

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))

2279

insert into t2 values(1);

2280

insert into t4 values(1);

2281

update t1 set a=2;

2282

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

2283

update t2 set a=2;

2284

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

2285

update t3 set a=2;

2286

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))

2287

update t4 set a=2;

2288

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))

2289

truncate t1;

2290

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

2291

truncate t3;

2292

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))

2293

truncate t2;

2294

truncate t4;

2295

truncate t1;

2296

truncate t3;

2297

drop table t4,t3,t2,t1;

2298

create table t1 (a varchar(255),

2299

b varchar(255),

2300

c varchar(255),

2301

d varchar(255),

2302

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

2303

drop table t1;

2304

create table t1 (a varchar(255),

2305

b varchar(255),

2306

c varchar(255),

2307

d varchar(255),

2308

e varchar(255),

2309

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

2310

ERROR 42000: Specified key was too long; max key length is 3500 bytes

2311

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

2312

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

2313

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

2314

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

2315

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

2316

ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'

2317

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

2318

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

2319

ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'

2320

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

2321

select hex(s1) from t1;

2322

hex(s1)

2323

2324

4100

2325

4120

2326

select hex(s1) from t3;

2327

hex(s1)

2328

4100

2329

2330

select hex(s1) from t4;

2331

hex(s1)

2332

4100

2333

2334

drop table t1,t3,t4;

2335

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

2336

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

2337

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

2338

insert into t2 values(0x42);

2339

insert into t2 values(0x41);

2340

select hex(s1) from t2;

2341

hex(s1)

2342

2343

2344

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

2345

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)

2346

select hex(s1) from t2;

2347

hex(s1)

2348

2349

2350

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

2351

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

2352

ERROR 22001: Data too long for column 's1' at row 1

2353

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

2354

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

2355

select hex(s1) from t2;

2356

hex(s1)

2357

2358

2359

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

2360

select hex(s1) from t2;

2361

hex(s1)

2362

2363

2364

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

2365

select hex(s1) from t2;

2366

hex(s1)

2367

2368

2369

delete from t1 where a=1;

2370

update t2 set s1=0x4120;

2371

delete from t1;

2372

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)

2373

delete from t1 where a!=3;

2374

select a,hex(s1) from t1;

2375

a hex(s1)

2376

3 4120

2377

select hex(s1) from t2;

2378

hex(s1)

2379

4120

2380

4120

2381

drop table t2,t1;

2382

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

2383

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

2384

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

2385

insert into t2 values(0x41);

2386

select hex(s1) from t2;

2387

hex(s1)

2388

2389

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

2390

select hex(s1) from t2;

2391

hex(s1)

2392

2393

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

2394

select hex(s1) from t2;

2395

hex(s1)

2396

2397

delete from t1 where a=1;

2398

delete from t1 where a=2;

2399

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)

2400

select a,hex(s1) from t1;

2401

a hex(s1)

2402

2 12

2403

select hex(s1) from t2;

2404

hex(s1)

2405

2406

drop table t2,t1;

2407

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

2408

CREATE TABLE t2(a INT) ENGINE=InnoDB;

2409

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

2410

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

2411

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

2412

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

2413

SHOW CREATE TABLE t2;

2414

Table Create Table

2415

t2 CREATE TABLE `t2` (

2416

`a` INT DEFAULT NULL,

2417

KEY `t2_ibfk_0` (`a`)

2418

) ENGINE=InnoDB COLLATE = utf8_general_ci

2419

DROP TABLE t2,t1;

2420

CREATE TABLE t1 (

2421

field1 varchar(8) NOT NULL DEFAULT '',

2422

field2 varchar(8) NOT NULL DEFAULT '',

2423

PRIMARY KEY (field1, field2)

2424

) ENGINE=InnoDB;

2425

CREATE TABLE t2 (

2426

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

2427

FOREIGN KEY (field1) REFERENCES t1 (field1)

2428

ON DELETE CASCADE ON UPDATE CASCADE

2429

) ENGINE=InnoDB;

2430

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

2431

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

2432

INSERT INTO t2 VALUES ('old');

2433

INSERT INTO t2 VALUES ('other');

2434

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

2435

ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry

2436

DROP TABLE t2;

2437

DROP TABLE t1;

2438

create table t1 (

2439

c1 bigint not null,

2440

c2 bigint not null,

2441

primary key (c1),

2442

unique key (c2)

2443

) engine=innodb;

2444

create table t2 (

2445

c1 bigint not null,

2446

primary key (c1)

2447

) engine=innodb;

2448

alter table t1 add constraint c2_fk foreign key (c2)

2449

references t2(c1) on delete cascade;

2450

show create table t1;

2451

Table Create Table

2452

t1 CREATE TABLE `t1` (

2453

`c1` BIGINT NOT NULL,

2454

`c2` BIGINT NOT NULL,

2455

PRIMARY KEY (`c1`),

2456

UNIQUE KEY `c2` (`c2`),

2457

CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE

2458

) ENGINE=InnoDB COLLATE = utf8_general_ci

2459

alter table t1 drop foreign key c2_fk;

2460

show create table t1;

2461

Table Create Table

2462

t1 CREATE TABLE `t1` (

2463

`c1` BIGINT NOT NULL,

2464

`c2` BIGINT NOT NULL,

2465

PRIMARY KEY (`c1`),

2466

UNIQUE KEY `c2` (`c2`)

2467

) ENGINE=InnoDB COLLATE = utf8_general_ci

2468

drop table t1, t2;

2469

create table t1(a date) engine=innodb;

2470

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

2471

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

2472

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

2473

select * from t1, t2

2474

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

2475

a a

2476

2005-10-01 2005-10-01

2477

drop table t1, t2;

2478

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

2479

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

2480

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

2481

primary key(id)) engine=innodb;

2482

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

2483

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

2484

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

2485

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

2486

commit;

2487

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

2488

where mm.id is null lock in share mode;

2489

id f_id f

2490

drop table t1,t2;

2491

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

2492

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

2493

commit;

2494

set autocommit = 0;

2495

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2496

update t1 set b = 5 where b = 1;

2497

set autocommit = 0;

2498

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2499

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

2500

a b

2501

7 3

2502

commit;

2503

commit;

2504

drop table t1;

2505

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

2506

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

2507

commit;

2508

set autocommit = 0;

2509

select * from t1 lock in share mode;

2510

a b

2511

1 1

2512

2 2

2513

3 1

2514

4 2

2515

5 1

2516

6 2

2517

update t1 set b = 5 where b = 1;

2518

set autocommit = 0;

2519

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

2520

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2521

commit;

2522

commit;

2523

drop table t1;

2524

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

2525

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

2526

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

2527

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

2528

commit;

2529

set autocommit = 0;

2530

select * from t2 for update;

2531

d e

2532

3 1

2533

8 6

2534

12 1

2535

set autocommit = 0;

2536

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2537

insert into t1 select * from t2;

2538

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

2539

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

2540

select * from t2;

2541

commit;

2542

commit;

2543

drop table t1, t2, t3;

2544

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

2545

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

2546

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

2547

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

2548

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

2549

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

2550

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

2551

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

2552

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

2553

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

2554

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

2555

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

2556

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

2557

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

2558

commit;

2559

set autocommit = 0;

2560

select * from t2 for update;

2561

a b

2562

3 1

2563

8 6

2564

12 1

2565

set autocommit = 0;

2566

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2567

insert into t1 select * from t2;

2568

set autocommit = 0;

2569

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2570

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

2571

set autocommit = 0;

2572

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2573

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

2574

set autocommit = 0;

2575

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2576

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

2577

set autocommit = 0;

2578

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2579

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

2580

set autocommit = 0;

2581

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2582

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

2583

set autocommit = 0;

2584

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2585

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

2586

set autocommit = 0;

2587

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2588

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

2589

set autocommit = 0;

2590

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2591

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

2592

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2593

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2594

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2595

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2596

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2597

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2598

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2599

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2600

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2601

commit;

2602

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

2603

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

2604

ERROR HY000: Can't create table 'test.t1' (errno: -1)

2605

CREATE TABLE t1 (

2606

a BIGINT NOT NULL,

2607

PRIMARY KEY (a)

2608

) ENGINE=INNODB;

2609

CREATE TABLE t2 (

2610

a BIGINT NOT NULL,

2611

b VARCHAR(128) NOT NULL,

2612

c TEXT NOT NULL,

2613

PRIMARY KEY (a,b),

2614

KEY idx_t2_b_c (b,c(200)),

2615

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

2616

ON DELETE CASCADE

2617

) ENGINE=INNODB;

2618

INSERT INTO t1 VALUES (1);

2619

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

2620

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

2621

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

2622

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

2623

SELECT * FROM t2 WHERE b = 'customer_over';

2624

a b c

2625

1 customer_over 1

2626

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

2627

a b c

2628

1 customer_over 1

2629

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

2630

2631

2632

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

2633

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

2634

2635

2636

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

2637

2638

2639

drop table t2, t1;

2640

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2641

BEGIN;

2642

INSERT INTO t1 VALUES (1);

2643

ALTER TABLE t1 ENGINE=innodb;

2644

DROP TABLE t1;

2645

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

2646

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

2647

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

2648

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

2649

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

2650

DELETE CASCADE ON UPDATE CASCADE;

2651

SHOW CREATE TABLE t2;

2652

Table Create Table

2653

t2 CREATE TABLE `t2` (

2654

`id` INT NOT NULL,

2655

`f` INT NOT NULL,

2656

PRIMARY KEY (`id`),

2657

KEY `f` (`f`),

2658

CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,

2659

CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON UPDATE CASCADE ON DELETE CASCADE

2660

) ENGINE=InnoDB COLLATE = utf8_general_ci

2661

DROP TABLE t2, t1;

2662

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

2663

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

2664

INSERT INTO t1 VALUES (1);

2665

INSERT INTO t2 VALUES (1);

2666

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

2667

ALTER TABLE t2 MODIFY a INT NOT NULL;

2668

ERROR HY000: Error on rename of '#sql-temporary' to 'test.t2' (errno: 150)

2669

DELETE FROM t1;

2670

DROP TABLE t2,t1;

2671

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

2672

ENGINE=InnoDB;

2673

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

2674

DELETE FROM t1;

2675

INSERT INTO t1 VALUES ('DDD');

2676

SELECT * FROM t1;

2677

2678

DDD

2679

DROP TABLE t1;

2680

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

2681

AUTO_INCREMENT=42;

2682

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

2683

SELECT * FROM t1;

2684

2685

2686

347

2687

348

2688

SHOW CREATE TABLE t1;

2689

Table Create Table

2690

t1 CREATE TABLE `t1` (

2691

`id` INT NOT NULL AUTO_INCREMENT,

2692

PRIMARY KEY (`id`)

2693

) ENGINE=InnoDB COLLATE = utf8_general_ci AUTO_INCREMENT=42

2694

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

2695

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

2696

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

2697

SHOW CREATE TABLE t1;

2698

Table Create Table

2699

t1 CREATE TABLE `t1` (

2700

`id` INT NOT NULL AUTO_INCREMENT,

2701

PRIMARY KEY (`id`),

2702

CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)

2703

) ENGINE=InnoDB COLLATE = utf8_general_ci

2704

DROP TABLE t1,t2;

2705

DROP TABLE IF EXISTS t1;

2706

Warnings:

2707

Note 1051 Unknown table 't1'

2708

CREATE TABLE t1(

2709

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2710

) ENGINE=InnoDB;

2711

INSERT INTO t1 VALUES(-10);

2712

SELECT * FROM t1;

2713

2714

-10

2715

INSERT INTO t1 VALUES(NULL);

2716

SELECT * FROM t1;

2717

2718

-10

2719

2720

DROP TABLE t1;

2721

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

Older »