~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

1002 101 1

1003 101 1

100

1004 101 1

101

1005 101 1

102

1006 101 1

103

1007 101 1

104

1008 102 2

105

1010 102 2

106

1015 102 2

107

1016 103 2

108

1017 103 2

109

1018 103 2

110

1019 103 2

111

1020 103 2

112

1021 104 2

113

1023 104 2

114

1024 104 2

115

1025 105 2

116

1026 105 2

117

1027 105 2

118

1028 105 2

119

1029 105 2

120

1030 105 2

121

1031 106 2

122

1032 106 2

123

1033 106 2

124

1034 106 2

125

1035 106 2

126

1036 107 2

127

1037 107 2

128

1039 107 2

129

1041 107 2

130

1158 100 0

131

1180 105 2

132

1184 104 2

133

1194 105 2

134

1202 107 2

135

1204 107 2

136

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

137

select * from t1 where parent_id=102;

138

id parent_id level

139

1008 102 2

140

1010 102 2

141

1015 102 2

142

explain select level from t1 where level=1;

143

id select_type table type possible_keys key key_len ref rows Extra

144

1 SIMPLE t1 ref level level 4 const # Using index

145

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

146

id select_type table type possible_keys key key_len ref rows Extra

147

1 SIMPLE t1 ref level level 4 const # Using index

148

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

149

id select_type table type possible_keys key key_len ref rows Extra

150

1 SIMPLE t1 ref level level 4 const #

151

select level,id from t1 where level=1;

152

level id

153

1 1002

154

1 1003

155

1 1004

156

1 1005

157

1 1006

158

1 1007

159

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

160

level id parent_id

161

1 1002 101

162

1 1003 101

163

1 1004 101

164

1 1005 101

165

1 1006 101

166

1 1007 101

167

optimize table t1;

168

Table Op Msg_type Msg_text

169

test.t1 optimize status OK

170

show keys from t1;

171

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

172

t1 0 PRIMARY 1 id A # NULL NULL BTREE

173

t1 1 parent_id 1 parent_id A # NULL NULL BTREE

174

t1 1 level 1 level A # NULL NULL BTREE

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

optimize table t1;

192

Table Op Msg_type Msg_text

193

test.t1 optimize status OK

194

delete from t1 where a = 1;

195

select * from t1;

196

197

198

check table t1;

199

Table Op Msg_type Msg_text

200

test.t1 check status OK

201

drop table t1;

202

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

203

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

204

delete from t1 where a = 1;

205

select * from t1;

206

a b

207

2 testing

208

create index skr on t1 (a);

209

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

210

analyze table t1;

211

Table Op Msg_type Msg_text

212

test.t1 analyze status OK

213

show keys from t1;

214

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

215

t1 1 skr 1 a A # NULL NULL YES BTREE

216

drop table t1;

217

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

218

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

219

select * from t1 where a = 1;

220

a b

221

222

drop table t1;

223

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

224

set autocommit=0;

225

insert into t1 values (4);

226

rollback;

227

select n, "after rollback" from t1;

228

n after rollback

229

insert into t1 values (4);

230

commit;

231

select n, "after commit" from t1;

232

n after commit

233

4 after commit

234

commit;

235

insert into t1 values (5);

236

insert into t1 values (4);

237

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

238

commit;

239

select n, "after commit" from t1;

240

n after commit

241

4 after commit

242

5 after commit

243

set autocommit=1;

244

insert into t1 values (6);

245

insert into t1 values (4);

246

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

247

select n from t1;

248

249

250

251

252

set autocommit=0;

253

begin;

254

savepoint `my_savepoint`;

255

insert into t1 values (7);

256

savepoint `savept2`;

257

insert into t1 values (3);

258

select n from t1;

259

260

261

262

263

264

265

savepoint savept3;

266

rollback to savepoint savept2;

267

rollback to savepoint savept3;

268

ERROR 42000: SAVEPOINT savept3 does not exist

269

rollback to savepoint savept2;

270

release savepoint `my_savepoint`;

271

select n from t1;

272

273

274

275

276

277

rollback to savepoint `my_savepoint`;

278

ERROR 42000: SAVEPOINT my_savepoint does not exist

279

rollback to savepoint savept2;

280

ERROR 42000: SAVEPOINT savept2 does not exist

281

insert into t1 values (8);

282

savepoint sv;

283

commit;

284

savepoint sv;

285

set autocommit=1;

286

rollback;

287

drop table t1;

288

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

289

start transaction;

290

insert into t1 values (4);

291

flush tables with read lock;

292

commit;

293

unlock tables;

294

commit;

295

select * from t1;

296

297

298

drop table t1;

299

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

300

begin;

301

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

302

select id as afterbegin_id,nom as afterbegin_nom from t1;

303

afterbegin_id afterbegin_nom

304

1 hamdouni

305

rollback;

306

select id as afterrollback_id,nom as afterrollback_nom from t1;

307

afterrollback_id afterrollback_nom

308

set autocommit=0;

309

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

310

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

311

afterautocommit0_id afterautocommit0_nom

312

2 mysql

313

rollback;

314

select id as afterrollback_id,nom as afterrollback_nom from t1;

315

afterrollback_id afterrollback_nom

316

set autocommit=1;

317

drop table t1;

318

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

319

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

320

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

321

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

322

delete from t1;

323

delete from t1 where id = 'pippo';

324

select * from t1;

325

id val

326

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

327

set autocommit=0;

328

delete from t1;

329

rollback;

330

select * from t1;

331

id val

332

pippo 12

333

delete from t1;

334

commit;

335

select * from t1;

336

id val

337

drop table t1;

338

create table t1 (a integer) engine=innodb;

339

start transaction;

340

rename table t1 to t2;

341

create table t1 (b integer) engine=innodb;

342

insert into t1 values (1);

343

rollback;

344

drop table t1;

345

rename table t2 to t1;

346

drop table t1;

347

set autocommit=1;

348

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

349

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

350

select * from t1;

351

ID NAME

352

1 Jochen

353

drop table t1;

354

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

355

set autocommit=0;

356

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

357

COMMIT;

358

SELECT * FROM t1;

359

_userid

360

marc@anyware.co.uk

361

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

362

_userid

363

marc@anyware.co.uk

364

drop table t1;

365

set autocommit=1;

366

CREATE TABLE t1 (

367

user_id int DEFAULT '0' NOT NULL,

368

name varchar(100),

369

phone varchar(100),

370

ref_email varchar(100) DEFAULT '' NOT NULL,

371

detail varchar(200),

372

PRIMARY KEY (user_id,ref_email)

373

)engine=innodb;

374

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

375

select * from t1 where user_id=10292;

376

user_id name phone ref_email detail

377

10292 sanjeev 29153373 sansh777@hotmail.com xxx

378

10292 shirish 2333604 shirish@yahoo.com ddsds

379

10292 sonali 323232 sonali@bolly.com filmstar

380

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

381

select * from t1 where user_id=10292;

382

user_id name phone ref_email detail

383

10292 sanjeev 29153373 sansh777@hotmail.com xxx

384

10292 shirish 2333604 shirish@yahoo.com ddsds

385

10292 sonali 323232 sonali@bolly.com filmstar

386

select * from t1 where user_id>=10292;

387

user_id name phone ref_email detail

388

10292 sanjeev 29153373 sansh777@hotmail.com xxx

389

10292 shirish 2333604 shirish@yahoo.com ddsds

390

10292 sonali 323232 sonali@bolly.com filmstar

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

10293 shirish 2333604 shirish@yahoo.com ddsds

395

select * from t1 where user_id<10292;

396

user_id name phone ref_email detail

397

10291 sanjeev 29153373 sansh777@hotmail.com xxx

398

drop table t1;

399

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

400

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

401

show index from t1;

402

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

403

t1 0 PRIMARY 1 a A # NULL NULL BTREE

404

t1 0 PRIMARY 2 b A # NULL NULL BTREE

405

t1 0 c 1 c A # NULL NULL BTREE

406

t1 0 b 1 b A # NULL NULL BTREE

407

t1 1 a 1 a A # NULL NULL BTREE

408

t1 1 a_2 1 a A # NULL NULL BTREE

409

drop table t1;

410

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

411

alter table t1 engine=innodb;

412

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

413

select * from t1;

414

col1 col2

415

1 1

416

2 3

417

3 4

418

4 4

419

5 2

420

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

421

select * from t1;

422

col1 col2

423

1 1

424

2 3

425

3 4

426

4 7

427

5 2

428

alter table t1 add co3 int not null;

429

select * from t1;

430

col1 col2 co3

431

1 1 0

432

2 3 0

433

3 4 0

434

4 7 0

435

5 2 0

436

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

437

select * from t1;

438

col1 col2 co3

439

1 1 0

440

2 9 0

441

3 4 0

442

4 7 0

443

5 2 0

444

drop table t1;

445

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

446

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

447

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

448

select * from t1;

449

a b

450

1 3

451

2 3

452

3 3

453

insert into t2 select * from t1;

454

select * from t2;

455

a b

456

1 3

457

2 3

458

3 3

459

delete from t1 where b = 3;

460

select * from t1;

461

a b

462

insert into t1 select * from t2;

463

select * from t1;

464

a b

465

1 3

466

2 3

467

3 3

468

select * from t2;

469

a b

470

1 3

471

2 3

472

3 3

473

drop table t1,t2;

474

CREATE TABLE t1 (

475

user_name varchar(12),

476

password text,

477

subscribed char(1),

478

user_id int DEFAULT '0' NOT NULL,

479

quota bigint,

480

weight double,

481

access_date date,

482

access_time time,

483

approved datetime,

484

dummy_primary_key int NOT NULL auto_increment,

485

PRIMARY KEY (dummy_primary_key)

486

) ENGINE=innodb;

487

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

488

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

489

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

490

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

491

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

492

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

493

user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key

494

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

495

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

496

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

497

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

498

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

499

drop table t1;

500

CREATE TABLE t1 (

501

id int NOT NULL auto_increment,

502

parent_id int DEFAULT '0' NOT NULL,

503

level int DEFAULT '0' NOT NULL,

504

KEY (id),

505

KEY parent_id (parent_id),

506

KEY level (level)

507

) engine=innodb;

508

509

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

510

update t1 set parent_id=parent_id+100;

511

select * from t1 where parent_id=102;

512

id parent_id level

513

8 102 2

514

9 102 2

515

15 102 2

516

update t1 set id=id+1000;

517

update t1 set id=1024 where id=1009;

518

select * from t1;

519

id parent_id level

520

1001 100 0

521

1003 101 1

522

1004 101 1

523

1008 102 2

524

1024 102 2

525

1017 103 2

526

1022 104 2

527

1024 104 2

528

1028 105 2

529

1029 105 2

530

1030 105 2

531

1031 106 2

532

1032 106 2

533

1033 106 2

534

1203 107 2

535

1202 107 2

536

1020 103 2

537

1157 100 0

538

1193 105 2

539

1040 107 2

540

1002 101 1

541

1015 102 2

542

1006 101 1

543

1034 106 2

544

1035 106 2

545

1016 103 2

546

1007 101 1

547

1036 107 2

548

1018 103 2

549

1026 105 2

550

1027 105 2

551

1183 104 2

552

1038 107 2

553

1025 105 2

554

1037 107 2

555

1021 104 2

556

1019 103 2

557

1005 101 1

558

1179 105 2

559

update ignore t1 set id=id+1;

560

select * from t1;

561

id parent_id level

562

1002 100 0

563

1004 101 1

564

1005 101 1

565

1009 102 2

566

1025 102 2

567

1018 103 2

568

1023 104 2

569

1025 104 2

570

1029 105 2

571

1030 105 2

572

1031 105 2

573

1032 106 2

574

1033 106 2

575

1034 106 2

576

1204 107 2

577

1203 107 2

578

1021 103 2

579

1158 100 0

580

1194 105 2

581

1041 107 2

582

1003 101 1

583

1016 102 2

584

1007 101 1

585

1035 106 2

586

1036 106 2

587

1017 103 2

588

1008 101 1

589

1037 107 2

590

1019 103 2

591

1027 105 2

592

1028 105 2

593

1184 104 2

594

1039 107 2

595

1026 105 2

596

1038 107 2

597

1022 104 2

598

1020 103 2

599

1006 101 1

600

1180 105 2

601

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

602

select * from t1 where parent_id=102;

603

id parent_id level

604

1009 102 2

605

1025 102 2

606

1016 102 2

607

explain select level from t1 where level=1;

608

id select_type table type possible_keys key key_len ref rows Extra

609

1 SIMPLE t1 ref level level 4 const # Using index

610

select level,id from t1 where level=1;

611

level id

612

1 1004

613

1 1005

614

1 1003

615

1 1007

616

1 1008

617

1 1006

618

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

619

level id parent_id

620

1 1004 101

621

1 1005 101

622

1 1003 101

623

1 1007 101

624

1 1008 101

625

1 1006 101

626

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

627

level id

628

1 1003

629

1 1004

630

1 1005

631

1 1006

632

1 1007

633

1 1008

634

delete from t1 where level=1;

635

select * from t1;

636

id parent_id level

637

1002 100 0

638

1009 102 2

639

1025 102 2

640

1018 103 2

641

1023 104 2

642

1025 104 2

643

1029 105 2

644

1030 105 2

645

1031 105 2

646

1032 106 2

647

1033 106 2

648

1034 106 2

649

1204 107 2

650

1203 107 2

651

1021 103 2

652

1158 100 0

653

1194 105 2

654

1041 107 2

655

1016 102 2

656

1035 106 2

657

1036 106 2

658

1017 103 2

659

1037 107 2

660

1019 103 2

661

1027 105 2

662

1028 105 2

663

1184 104 2

664

1039 107 2

665

1026 105 2

666

1038 107 2

667

1022 104 2

668

1020 103 2

669

1180 105 2

670

drop table t1;

671

CREATE TABLE t1 (

672

sca_code char(6) NOT NULL,

673

cat_code char(6) NOT NULL,

674

sca_desc varchar(50),

675

lan_code char(2) NOT NULL,

676

sca_pic varchar(100),

677

sca_sdesc varchar(50),

678

sca_sch_desc varchar(16),

679

PRIMARY KEY (sca_code, cat_code, lan_code),

680

INDEX sca_pic (sca_pic)

681

) engine = innodb ;

682

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

683

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

684

count(*)

685

686

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

687

count(*)

688

689

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

690

count(*)

691

692

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

693

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

694

count(*)

695

696

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

697

count(*)

698

699

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

700

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

701

count(*)

702

703

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

704

count(*)

705

706

select sca_pic from t1 where sca_pic is null;

707

sca_pic

708

NULL

709

NULL

710

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

711

delete from t1 where sca_code='pd';

712

drop table t1;

713

set @a:=now();

714

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

715

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

716

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

717

718

719

720

721

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

722

723

724

725

726

update t1 set a=5 where a=1;

727

select a from t1;

728

729

730

731

732

drop table t1;

733

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

734

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

735

select * from t1 order by b desc;

736

a b

737

world 2

738

hello 1

739

optimize table t1;

740

Table Op Msg_type Msg_text

741

test.t1 optimize status OK

742

show keys from t1;

743

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

744

t1 0 PRIMARY 1 a A # NULL NULL BTREE

745

drop table t1;

746

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

747

insert into t1 values (1,2);

748

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

749

i j

750

1 2

751

create index ax1 on t1 (i,j);

752

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

753

i j

754

1 2

755

drop table t1;

756

CREATE TABLE t1 (

757

a int NOT NULL,

758

b int NOT NULL,

759

UNIQUE (a, b)

760

) ENGINE = innodb;

761

INSERT INTO t1 VALUES (1, 1);

762

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

763

MIN(B) MAX(b)

764

1 1

765

drop table t1;

766

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

767

INSERT INTO t1 VALUES (1);

768

SELECT * FROM t1;

769

770

771

DROP TABLE t1;

772

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;

773

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

774

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

775

id select_type table type possible_keys key key_len ref rows Extra

776

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where

777

drop table t1;

778

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

779

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

780

LOCK TABLES t1 WRITE;

781

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

782

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

783

select id from t1;

784

785

786

787

788

select id from t1;

789

790

791

792

793

UNLOCK TABLES;

794

DROP TABLE t1;

795

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

796

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

797

LOCK TABLES t1 WRITE;

798

begin;

799

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

800

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

801

select id from t1;

802

803

804

805

806

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

807

commit;

808

select id,id3 from t1;

809

id id3

810

0 0

811

1 1

812

2 2

813

100 2

814

UNLOCK TABLES;

815

DROP TABLE t1;

816

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

817

drop table t1;

818

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

819

show create table t1;

820

Table Create Table

821

t1 CREATE TABLE `t1` (

822

`a` varchar(20),

823

KEY `a` (`a`())

824

) ENGINE=InnoDB

825

drop table t1;

826

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

827

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

828

delete from t1 where a=3;

829

insert into t1 values (NULL);

830

select * from t1;

831

832

833

834

835

alter table t1 add b int;

836

select * from t1;

837

a b

838

1 NULL

839

2 NULL

840

4 NULL

841

drop table t1;

842

create table t1

843

(

844

id int auto_increment primary key,

845

name varchar(32) not null,

846

value text not null,

847

uid int not null,

848

unique key(name,uid)

849

) engine=innodb;

850

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

851

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

852

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

853

delete from t1 where uid=102;

854

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

855

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

856

select * from t1;

857

id name value uid

858

1 one one value 101

859

3 three three value 103

860

6 two other value 102

861

drop table t1;

862

create database mysqltest;

863

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

864

insert into mysqltest.t1 values(1);

865

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

866

insert into mysqltest.t2 values(1);

867

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

868

insert into mysqltest.t3 values(1);

869

commit;

870

drop database mysqltest;

871

show tables from mysqltest;

872

ERROR 42000: Unknown database 'mysqltest'

873

set autocommit=0;

874

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

875

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

876

truncate table t1;

877

commit;

878

truncate table t1;

879

truncate table t1;

880

select * from t1;

881

882

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

883

delete from t1;

884

select * from t1;

885

886

commit;

887

drop table t1;

888

set autocommit=1;

889

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

890

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

891

truncate table t1;

892

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

893

select * from t1;

894

895

896

897

truncate table t1;

898

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

899

delete from t1;

900

select * from t1;

901

902

drop table t1;

903

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

904

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

905

explain select * from t1 order by a;

906

id select_type table type possible_keys key key_len ref rows Extra

907

1 SIMPLE t1 index NULL PRIMARY 4 NULL #

908

explain select * from t1 order by b;

909

id select_type table type possible_keys key key_len ref rows Extra

910

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

911

explain select * from t1 order by c;

912

id select_type table type possible_keys key key_len ref rows Extra

913

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

914

explain select a from t1 order by a;

915

id select_type table type possible_keys key key_len ref rows Extra

916

1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index

917

explain select b from t1 order by b;

918

id select_type table type possible_keys key key_len ref rows Extra

919

1 SIMPLE t1 index NULL b 4 NULL # Using index

920

explain select a,b from t1 order by b;

921

id select_type table type possible_keys key key_len ref rows Extra

922

1 SIMPLE t1 index NULL b 4 NULL # Using index

923

explain select a,b from t1;

924

id select_type table type possible_keys key key_len ref rows Extra

925

1 SIMPLE t1 index NULL PRIMARY 4 NULL #

926

explain select a,b,c from t1;

927

id select_type table type possible_keys key key_len ref rows Extra

928

1 SIMPLE t1 ALL NULL NULL NULL NULL #

929

drop table t1;

930

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

931

desc t1;

932

Field Type Null Key Default Extra

933

t int NO MUL NULL

934

drop table t1;

935

CREATE TABLE t1 (

936

number bigint NOT NULL default '0',

937

cname char(15) NOT NULL default '',

938

carrier_id int NOT NULL default '0',

939

privacy int NOT NULL default '0',

940

last_mod_date timestamp NOT NULL,

941

last_mod_id int NOT NULL default '0',

942

last_app_date timestamp NOT NULL,

943

last_app_id int default '-1',

944

version int NOT NULL default '0',

945

assigned_scps int default '0',

946

status int default '0'

947

) ENGINE=InnoDB;

948

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

949

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

950

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

951

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

952

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

953

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

954

CREATE TABLE t2 (

955

number bigint NOT NULL default '0',

956

cname char(15) NOT NULL default '',

957

carrier_id int NOT NULL default '0',

958

privacy int NOT NULL default '0',

959

last_mod_date timestamp NOT NULL,

960

last_mod_id int NOT NULL default '0',

961

last_app_date timestamp NOT NULL,

962

last_app_id int default '-1',

963

version int NOT NULL default '0',

964

assigned_scps int default '0',

965

status int default '0'

966

) ENGINE=InnoDB;

967

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

968

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

969

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

970

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

971

select * from t1;

972

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

973

4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1

974

9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0

975

650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1

976

302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0

977

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

978

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

979

select * from t2;

980

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

981

4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1

982

9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0

983

650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1

984

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

985

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

986

select * from t1;

987

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

988

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

989

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

990

select * from t2;

991

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

992

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

993

select * from t2;

994

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

995

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

996

drop table t1,t2;

997

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;

998

BEGIN;

999

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1000

SELECT @@tx_isolation,@@global.tx_isolation;

1001

@@tx_isolation @@global.tx_isolation

1002

SERIALIZABLE REPEATABLE-READ

1003

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

1004

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

1005

id code name

1006

1 1 Tim

1007

2 1 Monty

1008

3 2 David

1009

COMMIT;

1010

BEGIN;

1011

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1012

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

1013

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

1014

id code name

1015

1 1 Tim

1016

2 1 Monty

1017

3 2 David

1018

4 2 Erik

1019

5 3 Sasha

1020

COMMIT;

1021

BEGIN;

1022

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

1023

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

1024

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

1025

id code name

1026

1 1 Tim

1027

2 1 Monty

1028

3 2 David

1029

4 2 Erik

1030

5 3 Sasha

1031

6 3 Jeremy

1032

7 4 Matt

1033

COMMIT;

1034

DROP TABLE t1;

1035

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

1036

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

1037

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

1038

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

1039

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

1040

select * from t1;

1041

n d

1042

1 10

1043

1 10

1044

select * from t2;

1045

n d

1046

1 30

1047

2 20

1048

drop table t1,t2;

1049

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

1050

insert into t1 values(20,null);

1051

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

1052

t2.b=t3.a;

1053

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

1054

NULL this is null

1055

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

1056

t2.b=t3.a order by 1;

1057

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

1058

NULL this is null

1059

insert into t1 values(10,null);

1060

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

1061

t2.b=t3.a order by 1;

1062

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

1063

NULL this is null

1064

NULL this is null

1065

drop table t1;

1066

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

1067

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

1068

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

1069

1070

drop table t1,t2;

1071

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

1072

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

1073

insert into t1 values (10, 20);

1074

insert into t2 values (10, 20);

1075

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

1076

drop table t1,t2;

1077

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

1078

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;

1079

insert into t1 set id=1;

1080

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

1081

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

1082

select * from t1;

1083

1084

select * from t2;

1085

id t1_id

1086

drop table t2,t1;

1087

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

1088

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

1089

INSERT INTO t1 VALUES(1);

1090

INSERT INTO t2 VALUES(1, 1);

1091

SELECT * from t1;

1092

1093

1094

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

1095

SELECT * from t1;

1096

1097

1098

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

1099

SELECT * from t1;

1100

1101

1102

DROP TABLE t1,t2;

1103

set autocommit=0;

1104

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

1105

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

1106

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

1107

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

1108

COMMIT;

1109

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

1110

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

1111

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

1112

SELECT * FROM t1;

1113

id value

1114

this-key will disappear

1115

SELECT * FROM t2;

1116

id value

1117

this-key will also disappear

1118

SELECT * FROM t3;

1119

id1 id2

1120

ROLLBACK;

1121

SELECT * FROM t1;

1122

id value

1123

SELECT * FROM t2;

1124

id value

1125

SELECT * FROM t3;

1126

id1 id2

1127

my-test-1 my-test-2

1128

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

1129

id1 id2

1130

my-test-1 my-test-2

1131

COMMIT;

1132

set autocommit=1;

1133

DROP TABLE t1,t2,t3;

1134

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

1135

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

1136

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

1137

SELECT * from t1;

1138

a b

1139

1 1

1140

4 4

1141

5 5

1142

6 6

1143

7 7

1144

8 8

1145

9 9

1146

102 2

1147

103 3

1148

drop table t1;

1149

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

1150

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

1151

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

1152

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

1153

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

1154

select * from t1;

1155

a b

1156

101 1

1157

102 2

1158

103 3

1159

104 4

1160

105 5

1161

106 6

1162

107 7

1163

108 8

1164

109 9

1165

110 10

1166

111 11

1167

112 12

1168

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

1169

select * from t1;

1170

a b

1171

102 2

1172

103 3

1173

104 4

1174

105 5

1175

106 6

1176

107 7

1177

108 8

1178

109 9

1179

110 10

1180

111 11

1181

112 12

1182

201 1

1183

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

1184

select * from t1;

1185

a b

1186

102 12

1187

103 3

1188

104 4

1189

105 5

1190

106 6

1191

107 7

1192

108 8

1193

109 9

1194

110 10

1195

111 11

1196

112 12

1197

201 1

1198

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

1199

select * from t1;

1200

a b

1201

102 12

1202

103 5

1203

104 6

1204

105 7

1205

106 6

1206

107 7

1207

108 8

1208

109 9

1209

110 10

1210

111 11

1211

112 12

1212

201 1

1213

select * from t2;

1214

a b

1215

1 1

1216

2 2

1217

3 13

1218

4 14

1219

5 15

1220

6 6

1221

7 7

1222

8 8

1223

9 9

1224

drop table t1,t2;

1225

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

1226

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

1227

SET AUTOCOMMIT=0;

1228

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1229

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1230

ROLLBACK;

1231

Warnings:

1232

Warning 1196 Some non-transactional changed tables couldn't be rolled back

1233

SELECT * FROM t1;

1234

B_ID

1235

drop table t1,t2;

1236

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

1237

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

1238

select distinct parent,child from t1 order by parent;

1239

parent child

1240

0 4

1241

1 2

1242

1 3

1243

2 1

1244

drop table t1;

1245

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

1246

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

1247

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

1248

insert into t2 (a) select b from t1;

1249

insert into t1 (b) select b from t2;

1250

insert into t2 (a) select b from t1;

1251

insert into t1 (a) select b from t2;

1252

insert into t2 (a) select b from t1;

1253

insert into t1 (a) select b from t2;

1254

insert into t2 (a) select b from t1;

1255

insert into t1 (a) select b from t2;

1256

insert into t2 (a) select b from t1;

1257

insert into t1 (a) select b from t2;

1258

select count(*) from t1;

1259

count(*)

1260

623

1261

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

1262

id select_type table type possible_keys key key_len ref rows Extra

1263

1 SIMPLE t1 range c c 5 NULL # Using where; Using MRR

1264

update t1 set c=a;

1265

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

1266

id select_type table type possible_keys key key_len ref rows Extra

1267

1 SIMPLE t1 ALL c NULL NULL NULL # Using where

1268

drop table t1,t2;

1269

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

1270

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

1271

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

1272

SELECT * from t1;

1273

id fk

1274

1 69

1275

2 NULL

1276

3 NULL

1277

4 NULL

1278

5 NULL

1279

drop table t1;

1280

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

1281

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

1282

SET @tmp=0;

1283

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

1284

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

1285

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

1286

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

1287

select * from t1 order by a,b;

1288

a b

1289

2 4

1290

2 5

1291

2 6

1292

3 7

1293

3 8

1294

3 9

1295

3 10

1296

3 11

1297

3 12

1298

13 2

1299

111 100

1300

111 100

1301

drop table t1;

1302

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

1303

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

1304

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

1305

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

1306

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

1307

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

1308

update t1 set a=c, b=c;

1309

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

1310

insert into t2 select * from t1;

1311

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

1312

drop table t1,t2;

1313

SET AUTOCOMMIT=1;

1314

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

1315

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

1316

truncate table t1;

1317

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

1318

SELECT * from t1;

1319

1320

1321

1322

drop table t1;

1323

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

1324

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;

1325

drop table t2,t1;

1326

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

1327

insert into `t1`values ( 1 ) ;

1328

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

1329

insert into `t2`values ( 1 ) ;

1330

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

1331

insert into `t3`values ( 1 ) ;

1332

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

1333

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

1334

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

1335

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

1336

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

1337

ERROR 42S22: Unknown column 't1.id' in 'where clause'

1338

drop table t3,t2,t1;

1339

create table t1(

1340

id int primary key,

1341

pid int,

1342

index(pid),

1343

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

1344

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

1345

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

1346

delete from t1 where id=0;

1347

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)

1348

delete from t1 where id=15;

1349

delete from t1 where id=0;

1350

drop table t1;

1351

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

1352

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

1353

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

1354

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

1355

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

1356

'20020204120000' GROUP BY col1;

1357

col1

1358

1359

1360

1361

1362

1363

drop table t1,t2;

1364

CREATE TABLE t1 (

1365

`id` int NOT NULL auto_increment,

1366

`id_object` int default '0',

1367

`id_version` int NOT NULL default '1',

1368

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

1369

`description` text,

1370

PRIMARY KEY (`id`),

1371

KEY `id_object` (`id_object`),

1372

KEY `id_version` (`id_version`)

1373

) ENGINE=InnoDB;

1374

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

1375

CREATE TABLE t2 (

1376

`id` int NOT NULL auto_increment,

1377

`id_version` int NOT NULL default '1',

1378

PRIMARY KEY (`id`),

1379

KEY `id_version` (`id_version`)

1380

) ENGINE=InnoDB;

1381

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

1382

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

1383

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

1384

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

1385

id label

1386

3382 Test

1387

102 Le Pekin (Test)

1388

1794 Test de resto

1389

1822 Test 3

1390

3524 Societe Test

1391

3525 Fournisseur Test

1392

drop table t1,t2;

1393

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

1394

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

1395

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

1396

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

1397

insert t2 select * from t1;

1398

insert t3 select * from t1;

1399

checksum table t1, t2, t3, t4 quick;

1400

Table Checksum

1401

test.t1 2948697075

1402

test.t2 NULL

1403

test.t3 NULL

1404

test.t4 NULL

1405

Warnings:

1406

Error 1146 Table 'test.t4' doesn't exist

1407

checksum table t1, t2, t3, t4;

1408

Table Checksum

1409

test.t1 2948697075

1410

test.t2 2948697075

1411

test.t3 2948697075

1412

test.t4 NULL

1413

Warnings:

1414

Error 1146 Table 'test.t4' doesn't exist

1415

checksum table t1, t2, t3, t4 extended;

1416

Table Checksum

1417

test.t1 2948697075

1418

test.t2 2948697075

1419

test.t3 2948697075

1420

test.t4 NULL

1421

Warnings:

1422

Error 1146 Table 'test.t4' doesn't exist

1423

drop table t1,t2,t3;

1424

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

1425

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

1426

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

1427

trim(name2)

1428

fff

1429

sss

1430

ttt

1431

first

1432

second

1433

third

1434

1435

1436

1437

drop table t1;

1438

create table t1 (a int) engine=innodb;

1439

create table t2 like t1;

1440

drop table t1,t2;

1441

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

1442

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

1443

show create table t1;

1444

Table Create Table

1445

t1 CREATE TABLE `t1` (

1446

`id` int NOT NULL,

1447

`id2` int NOT NULL,

1448

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

1449

) ENGINE=InnoDB

1450

show create table t2;

1451

Table Create Table

1452

t2 CREATE TABLE `t2` (

1453

`id` int NOT NULL,

1454

KEY `t1_id_fk` (`id`),

1455

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

1456

) ENGINE=InnoDB

1457

create index id on t2 (id);

1458

show create table t2;

1459

Table Create Table

1460

t2 CREATE TABLE `t2` (

1461

`id` int NOT NULL,

1462

KEY `id` (`id`),

1463

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

1464

) ENGINE=InnoDB

1465

create index id2 on t2 (id);

1466

show create table t2;

1467

Table Create Table

1468

t2 CREATE TABLE `t2` (

1469

`id` int NOT NULL,

1470

KEY `id` (`id`),

1471

KEY `id2` (`id`),

1472

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

1473

) ENGINE=InnoDB

1474

drop index id2 on t2;

1475

drop index id on t2;

1476

Got one of the listed errors

1477

show create table t2;

1478

Table Create Table

1479

t2 CREATE TABLE `t2` (

1480

`id` int NOT NULL,

1481

KEY `id` (`id`),

1482

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

1483

) ENGINE=InnoDB

1484

drop table t2;

1485

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;

1486

show create table t2;

1487

Table Create Table

1488

t2 CREATE TABLE `t2` (

1489

`id` int NOT NULL,

1490

`id2` int NOT NULL,

1491

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

1492

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

1493

) ENGINE=InnoDB

1494

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

1495

show create table t2;

1496

Table Create Table

1497

t2 CREATE TABLE `t2` (

1498

`id` int NOT NULL,

1499

`id2` int NOT NULL,

1500

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

1501

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

1502

) ENGINE=InnoDB

1503

drop table t2;

1504

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;

1505

show create table t2;

1506

Table Create Table

1507

t2 CREATE TABLE `t2` (

1508

`id` int NOT NULL,

1509

`id2` int NOT NULL,

1510

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

1511

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

1512

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

1513

) ENGINE=InnoDB

1514

drop table t2;

1515

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;

1516

show create table t2;

1517

Table Create Table

1518

t2 CREATE TABLE `t2` (

1519

`id` int NOT NULL,

1520

`id2` int NOT NULL,

1521

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

1522

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

1523

) ENGINE=InnoDB

1524

drop table t2;

1525

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;

1526

show create table t2;

1527

Table Create Table

1528

t2 CREATE TABLE `t2` (

1529

`id` int NOT NULL,

1530

`id2` int NOT NULL,

1531

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

1532

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

1533

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

1534

) ENGINE=InnoDB

1535

drop table t2;

1536

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;

1537

show create table t2;

1538

Table Create Table

1539

t2 CREATE TABLE `t2` (

1540

`id` int NOT NULL AUTO_INCREMENT,

1541

`id2` int NOT NULL,

1542

PRIMARY KEY (`id`),

1543

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

1544

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

1545

) ENGINE=InnoDB

1546

drop table t2;

1547

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;

1548

show create table t2;

1549

Table Create Table

1550

t2 CREATE TABLE `t2` (

1551

`id` int NOT NULL AUTO_INCREMENT,

1552

`id2` int NOT NULL,

1553

KEY `t1_id_fk` (`id`),

1554

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

1555

) ENGINE=InnoDB

1556

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

1557

show create table t2;

1558

Table Create Table

1559

t2 CREATE TABLE `t2` (

1560

`id` int NOT NULL AUTO_INCREMENT,

1561

`id2` int NOT NULL,

1562

KEY `id_test` (`id`),

1563

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

1564

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

1565

) ENGINE=InnoDB

1566

drop table t2;

1567

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

1568

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

1569

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

1570

show create table t2;

1571

Table Create Table

1572

t2 CREATE TABLE `t2` (

1573

`a` int NOT NULL AUTO_INCREMENT,

1574

`b` int,

1575

PRIMARY KEY (`a`),

1576

UNIQUE KEY `b_2` (`b`),

1577

KEY `b` (`b`),

1578

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

1579

) ENGINE=InnoDB

1580

drop table t2;

1581

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;

1582

show create table t2;

1583

Table Create Table

1584

t2 CREATE TABLE `t2` (

1585

`a` int NOT NULL AUTO_INCREMENT,

1586

`b` int,

1587

PRIMARY KEY (`a`),

1588

UNIQUE KEY `b` (`b`),

1589

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

1590

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

1591

) ENGINE=InnoDB

1592

drop table t2, t1;

1593

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

1594

ERROR 42S21: Duplicate column name 'c'

1595

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

1596

ERROR 42S21: Duplicate column name 'c1'

1597

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

1598

ERROR 42S21: Duplicate column name 'c1'

1599

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

1600

ERROR 42S21: Duplicate column name 'c1'

1601

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

1602

alter table t1 add key (c1,c1);

1603

ERROR 42S21: Duplicate column name 'c1'

1604

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

1605

ERROR 42S21: Duplicate column name 'c1'

1606

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

1607

ERROR 42S21: Duplicate column name 'c1'

1608

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

1609

ERROR 42S21: Duplicate column name 'c1'

1610

drop table t1;

1611

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

1612

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

1613

select distinct concat(a, b) from t1;

1614

concat(a, b)

1615

11113333

1616

drop table t1;

1617

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

1618

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

1619

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

1620

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

1621

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

1622

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;

1623

a_id b_list

1624

1 1,2,3

1625

2 4,5

1626

3 NULL

1627

DROP TABLE t2;

1628

DROP TABLE t1;

1629

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

1630

insert into t1 values (4711);

1631

truncate t1;

1632

insert into t1 values (42);

1633

select * from t1;

1634

1635

1636

drop table t1;

1637

create table t1 (a int) engine=innodb;

1638

insert into t1 values (4711);

1639

truncate t1;

1640

insert into t1 values (42);

1641

select * from t1;

1642

1643

1644

drop table t1;

1645

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;

1646

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

1647

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

1648

a b c d e

1649

1 1 a 1 1

1650

2 2 b 2 2

1651

3 3 ab 3 3

1652

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

1653

id select_type table type possible_keys key key_len ref rows Extra

1654

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

1655

drop table t1;

1656

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

1657

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

1658

select min(a) from t1;

1659

min(a)

1660

1661

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

1662

min(b)

1663

1664

drop table t1;

1665

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

1666

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

1667

select * from t1;

1668

1669

1152921504606846960

1670

1152921504606846961

1671

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

1672

count(*)

1673

1674

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

1675

count(*)

1676

1677

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

1678

count(*)

1679

1680

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

1681

count(*)

1682

1683

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

1684

count(*)

1685

1686

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

1687

id select_type table type possible_keys key key_len ref rows Extra

1688

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

1689

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

1690

count(*)

1691

1692

select * from t1 where x > -16;

1693

1694

1152921504606846960

1695

1152921504606846961

1696

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

1697

count(*)

1698

1699

drop table t1;

1700

show variables like "innodb_sync_spin_loops";

1701

Variable_name Value

1702

innodb_sync_spin_loops 20

1703

set global innodb_sync_spin_loops=1000;

1704

show variables like "innodb_sync_spin_loops";

1705

Variable_name Value

1706

innodb_sync_spin_loops 1000

1707

set global innodb_sync_spin_loops=0;

1708

show variables like "innodb_sync_spin_loops";

1709

Variable_name Value

1710

innodb_sync_spin_loops 0

1711

set global innodb_sync_spin_loops=20;

1712

show variables like "innodb_sync_spin_loops";

1713

Variable_name Value

1714

innodb_sync_spin_loops 20

1715

show variables like "innodb_thread_concurrency";

1716

Variable_name Value

1717

innodb_thread_concurrency 8

1718

set global innodb_thread_concurrency=1001;

1719

Warnings:

1720

Warning 1292 Truncated incorrect thread_concurrency value: '1001'

1721

show variables like "innodb_thread_concurrency";

1722

Variable_name Value

1723

innodb_thread_concurrency 1000

1724

set global innodb_thread_concurrency=0;

1725

show variables like "innodb_thread_concurrency";

1726

Variable_name Value

1727

innodb_thread_concurrency 0

1728

set global innodb_thread_concurrency=16;

1729

show variables like "innodb_thread_concurrency";

1730

Variable_name Value

1731

innodb_thread_concurrency 16

1732

show variables like "innodb_concurrency_tickets";

1733

Variable_name Value

1734

innodb_concurrency_tickets 500

1735

set global innodb_concurrency_tickets=1000;

1736

show variables like "innodb_concurrency_tickets";

1737

Variable_name Value

1738

innodb_concurrency_tickets 1000

1739

set global innodb_concurrency_tickets=0;

1740

Warnings:

1741

Warning 1292 Truncated incorrect concurrency_tickets value: '0'

1742

show variables like "innodb_concurrency_tickets";

1743

Variable_name Value

1744

innodb_concurrency_tickets 1

1745

set global innodb_concurrency_tickets=500;

1746

show variables like "innodb_concurrency_tickets";

1747

Variable_name Value

1748

innodb_concurrency_tickets 500

1749

show variables like "innodb_thread_sleep_delay";

1750

Variable_name Value

1751

innodb_thread_sleep_delay 10000

1752

set global innodb_thread_sleep_delay=100000;

1753

show variables like "innodb_thread_sleep_delay";

1754

Variable_name Value

1755

innodb_thread_sleep_delay 100000

1756

set global innodb_thread_sleep_delay=0;

1757

show variables like "innodb_thread_sleep_delay";

1758

Variable_name Value

1759

innodb_thread_sleep_delay 0

1760

set global innodb_thread_sleep_delay=10000;

1761

show variables like "innodb_thread_sleep_delay";

1762

Variable_name Value

1763

innodb_thread_sleep_delay 10000

1764

set storage_engine=INNODB;

1765

drop table if exists t1,t2,t3;

1766

--- Testing varchar ---

1767

--- Testing varchar ---

1768

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

1769

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

1770

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

1771

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

1772

Warnings:

1773

Note 1265 Data truncated for column 'v' at row 1

1774

Note 1265 Data truncated for column 'c' at row 1

1775

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

1776

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

1777

*+ *+ *+ *

1778

*+ *+ *+ *

1779

show create table t1;

1780

Table Create Table

1781

t1 CREATE TABLE `t1` (

1782

`v` varchar(10),

1783

`c` varchar(10),

1784

`t` text

1785

) ENGINE=InnoDB

1786

create table t2 like t1;

1787

show create table t2;

1788

Table Create Table

1789

t2 CREATE TABLE `t2` (

1790

`v` varchar(10),

1791

`c` varchar(10),

1792

`t` text

1793

) ENGINE=InnoDB

1794

create table t3 select * from t1;

1795

show create table t3;

1796

Table Create Table

1797

t3 CREATE TABLE `t3` (

1798

`v` varchar(10),

1799

`c` varchar(10),

1800

`t` text

1801

) ENGINE=InnoDB

1802

alter table t1 modify c varchar(10);

1803

show create table t1;

1804

Table Create Table

1805

t1 CREATE TABLE `t1` (

1806

`v` varchar(10),

1807

`c` varchar(10),

1808

`t` text

1809

) ENGINE=InnoDB

1810

alter table t1 modify v char(10);

1811

show create table t1;

1812

Table Create Table

1813

t1 CREATE TABLE `t1` (

1814

`v` varchar(10),

1815

`c` varchar(10),

1816

`t` text

1817

) ENGINE=InnoDB

1818

alter table t1 modify t varchar(10);

1819

Warnings:

1820

Note 1265 Data truncated for column 't' at row 2

1821

show create table t1;

1822

Table Create Table

1823

t1 CREATE TABLE `t1` (

1824

`v` varchar(10),

1825

`c` varchar(10),

1826

`t` varchar(10)

1827

) ENGINE=InnoDB

1828

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

1829

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

1830

*+ *+ *+ *

1831

*+ *+ *+ *

1832

drop table t1,t2,t3;

1833

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

1834

show create table t1;

1835

Table Create Table

1836

t1 CREATE TABLE `t1` (

1837

`v` varchar(10),

1838

`c` varchar(10),

1839

`t` text,

1840

KEY `v` (`v`),

1841

KEY `c` (`c`),

1842

KEY `t` (`t`())

1843

) ENGINE=InnoDB

1844

select count(*) from t1;

1845

count(*)

1846

270

1847

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

1848

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

1849

count(*)

1850

1851

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

1852

count(*)

1853

1854

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

1855

count(*)

1856

1857

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

1858

count(*)

1859

1860

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

1861

count(*)

1862

1863

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

1864

count(*)

1865

1866

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

1867

count(*)

1868

1869

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

1870

count(*)

1871

1872

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

1873

count(*)

1874

1875

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

1876

count(*)

1877

1878

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

1879

count(*)

1880

1881

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

1882

count(*)

1883

1884

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

1885

id select_type table type possible_keys key key_len ref rows Extra

1886

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

1887

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

1888

id select_type table type possible_keys key key_len ref rows Extra

1889

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

1890

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

1891

id select_type table type possible_keys key key_len ref rows Extra

1892

1 SIMPLE t1 ref t t 43 const # Using where

1893

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

1894

id select_type table type possible_keys key key_len ref rows Extra

1895

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

1896

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

1897

id select_type table type possible_keys key key_len ref rows Extra

1898

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

1899

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

1900

id select_type table type possible_keys key key_len ref rows Extra

1901

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

1902

alter table t1 add unique(v);

1903

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

1904

alter table t1 add key(v);

1905

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

1906

1907

*a*a*a*

1908

*a *a *a *

1909

*a *a *a *

1910

*a *a *a *

1911

*a *a *a *

1912

*a *a *a *

1913

*a *a *a *

1914

*a *a *a *

1915

*a *a *a *

1916

*a *a *a *

1917

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

1918

id select_type table type possible_keys key key_len ref rows Extra

1919

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

1920

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

1921

v count(*)

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

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

1945

v count(c)

1946

a 1

1947

a 10

1948

b 10

1949

c 10

1950

d 10

1951

e 10

1952

f 10

1953

g 10

1954

h 10

1955

i 10

1956

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

1957

v count(t)

1958

a 1

1959

a 10

1960

b 10

1961

c 10

1962

d 10

1963

e 10

1964

f 10

1965

g 10

1966

h 10

1967

i 10

1968

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

1969

v count(c)

1970

a 1

1971

a 10

1972

b 10

1973

c 10

1974

d 10

1975

e 10

1976

f 10

1977

g 10

1978

h 10

1979

i 10

1980

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

1981

c count(*)

1982

a 1

1983

a 10

1984

b 10

1985

c 10

1986

d 10

1987

e 10

1988

f 10

1989

g 10

1990

h 10

1991

i 10

1992

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

1993

c count(t)

1994

a 1

1995

a 10

1996

b 10

1997

c 10

1998

d 10

1999

e 10

2000

f 10

2001

g 10

2002

h 10

2003

i 10

2004

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

2005

c count(t)

2006

a 1

2007

a 10

2008

b 10

2009

c 10

2010

d 10

2011

e 10

2012

f 10

2013

g 10

2014

h 10

2015

i 10

2016

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

2017

t count(*)

2018

a 1

2019

a 10

2020

b 10

2021

c 10

2022

d 10

2023

e 10

2024

f 10

2025

g 10

2026

h 10

2027

i 10

2028

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

2029

t count(t)

2030

a 1

2031

a 10

2032

b 10

2033

c 10

2034

d 10

2035

e 10

2036

f 10

2037

g 10

2038

h 10

2039

i 10

2040

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

2041

t count(t)

2042

a 1

2043

a 10

2044

b 10

2045

c 10

2046

d 10

2047

e 10

2048

f 10

2049

g 10

2050

h 10

2051

i 10

2052

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

2053

Warnings:

2054

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

2055

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

2056

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

2057

show create table t1;

2058

Table Create Table

2059

t1 CREATE TABLE `t1` (

2060

`v` varchar(300),

2061

`c` varchar(10),

2062

`t` text,

2063

KEY `c` (`c`),

2064

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

2065

KEY `v` (`v`())

2066

) ENGINE=InnoDB

2067

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

2068

count(*)

2069

2070

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

2071

count(*)

2072

2073

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

2074

count(*)

2075

2076

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

2077

count(*)

2078

2079

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

2080

count(*)

2081

2082

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

2083

count(*)

2084

2085

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

2086

id select_type table type possible_keys key key_len ref rows Extra

2087

1 SIMPLE t1 ref v v 767 const # Using where

2088

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

2089

id select_type table type possible_keys key key_len ref rows Extra

2090

1 SIMPLE t1 range v v 767 NULL # Using where

2091

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

2092

id select_type table type possible_keys key key_len ref rows Extra

2093

1 SIMPLE t1 ref v v 767 const # Using where

2094

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

2095

id select_type table type possible_keys key key_len ref rows Extra

2096

1 SIMPLE t1 ref v v 767 const # Using where

2097

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

2098

id select_type table type possible_keys key key_len ref rows Extra

2099

1 SIMPLE t1 ref v v 767 const # Using where

2100

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

2101

v count(*)

2102

a 1

2103

a 10

2104

b 10

2105

c 10

2106

d 10

2107

e 10

2108

f 10

2109

g 10

2110

h 10

2111

i 10

2112

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

2113

v count(t)

2114

a 1

2115

a 10

2116

b 10

2117

c 10

2118

d 10

2119

e 10

2120

f 10

2121

g 10

2122

h 10

2123

i 10

2124

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

2125

v count(t)

2126

a 1

2127

a 10

2128

b 10

2129

c 10

2130

d 10

2131

e 10

2132

f 10

2133

g 10

2134

h 10

2135

i 10

2136

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

2137

show create table t1;

2138

Table Create Table

2139

t1 CREATE TABLE `t1` (

2140

`v` varchar(300),

2141

`c` varchar(10),

2142

`t` text,

2143

KEY `c` (`c`),

2144

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

2145

KEY `v` (`v`())

2146

) ENGINE=InnoDB

2147

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

2148

count(*)

2149

2150

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

2151

count(*)

2152

2153

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

2154

count(*)

2155

2156

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

2157

count(*)

2158

2159

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

2160

count(*)

2161

2162

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

2163

count(*)

2164

2165

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

2166

id select_type table type possible_keys key key_len ref rows Extra

2167

1 SIMPLE t1 ref v v 123 const # Using where

2168

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

2169

id select_type table type possible_keys key key_len ref rows Extra

2170

1 SIMPLE t1 range v v 123 NULL # Using where

2171

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

2172

id select_type table type possible_keys key key_len ref rows Extra

2173

1 SIMPLE t1 ref v v 123 const # Using where

2174

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

2175

id select_type table type possible_keys key key_len ref rows Extra

2176

1 SIMPLE t1 ref v v 123 const # Using where

2177

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

2178

id select_type table type possible_keys key key_len ref rows Extra

2179

1 SIMPLE t1 ref v v 123 const # Using where

2180

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

2181

v count(*)

2182

a 1

2183

a 10

2184

b 10

2185

c 10

2186

d 10

2187

e 10

2188

f 10

2189

g 10

2190

h 10

2191

i 10

2192

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

2193

v count(t)

2194

a 1

2195

a 10

2196

b 10

2197

c 10

2198

d 10

2199

e 10

2200

f 10

2201

g 10

2202

h 10

2203

i 10

2204

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

2205

v count(t)

2206

a 1

2207

a 10

2208

b 10

2209

c 10

2210

d 10

2211

e 10

2212

f 10

2213

g 10

2214

h 10

2215

i 10

2216

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

2217

Warnings:

2218

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

2219

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

2220

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

2221

show create table t1;

2222

Table Create Table

2223

t1 CREATE TABLE `t1` (

2224

`v` varchar(600),

2225

`c` varchar(10),

2226

`t` text,

2227

KEY `c` (`c`),

2228

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

2229

KEY `v` (`v`())

2230

) ENGINE=InnoDB

2231

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

2232

v count(*)

2233

a 1

2234

a 10

2235

b 10

2236

c 10

2237

d 10

2238

e 10

2239

f 10

2240

g 10

2241

h 10

2242

i 10

2243

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

2244

v count(t)

2245

a 1

2246

a 10

2247

b 10

2248

c 10

2249

d 10

2250

e 10

2251

f 10

2252

g 10

2253

h 10

2254

i 10

2255

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

2256

v count(t)

2257

a 1

2258

a 10

2259

b 10

2260

c 10

2261

d 10

2262

e 10

2263

f 10

2264

g 10

2265

h 10

2266

i 10

2267

drop table t1;

2268

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

2269

insert into t1 values ('a ');

2270

insert into t1 values ('a ');

2271

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

2272

alter table t1 modify a varchar(10);

2273

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

2274

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

2275

insert into t1 values ('a ');

2276

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

2277

insert into t1 values ('a ');

2278

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

2279

insert into t1 values ('a ');

2280

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

2281

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

2282

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

2283

concat(a,'.')

2284

a .

2285

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

2286

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

2287

concat(a,'.')

2288

a .

2289

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

2290

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

2291

concat(a,'.')

2292

a .

2293

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

2294

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

2295

concat(a,'.')

2296

a .

2297

drop table t1;

2298

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

2299

show create table t1;

2300

Table Create Table

2301

t1 CREATE TABLE `t1` (

2302

`v` varchar(10),

2303

`c` varchar(10),

2304

`t` text,

2305

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

2306

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

2307

KEY `t` (`t`())

2308

) ENGINE=InnoDB

2309

drop table t1;

2310

create table t1 (v char(10));

2311

show create table t1;

2312

Table Create Table

2313

t1 CREATE TABLE `t1` (

2314

`v` varchar(10)

2315

) ENGINE=InnoDB

2316

drop table t1;

2317

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

2318

Warnings:

2319

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

2320

show create table t1;

2321

Table Create Table

2322

t1 CREATE TABLE `t1` (

2323

`v` varchar(10),

2324

`c` varchar(10)

2325

) ENGINE=InnoDB ROW_FORMAT=FIXED

2326

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

2327

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

2328

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

2329

*a*a*

2330

*a *a *

2331

drop table t1;

2332

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

2333

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

2334

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

2335

id select_type table type possible_keys key key_len ref rows Extra

2336

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

2337

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

2338

a b

2339

20 NULL

2340

drop table t1;

2341

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

2342

Warnings:

2343

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

2344

drop table t1;

2345

create table t1 (v varchar(16383));

2346

show create table t1;

2347

Table Create Table

2348

t1 CREATE TABLE `t1` (

2349

`v` varchar(16383)

2350

) ENGINE=InnoDB

2351

drop table t1;

2352

create table t1 (v varchar(16383));

2353

show create table t1;

2354

Table Create Table

2355

t1 CREATE TABLE `t1` (

2356

`v` varchar(16383)

2357

) ENGINE=InnoDB

2358

drop table t1;

2359

set storage_engine=InnoDB;

2360

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

2361

drop table t1;

2362

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

2363

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

2364

select min(a) from t1;

2365

min(a)

2366

2367

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

2368

min(b)

2369

2370

drop table t1;

2371

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

2372

insert into t1 (b) values (1);

2373

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

2374

select * from t1;

2375

a b

2376

2 2

2377

3 1

2378

4 3

2379

truncate table t1;

2380

insert into t1 (b) values (1);

2381

replace into t1 (b) values (2);

2382

replace into t1 (b) values (1);

2383

replace into t1 (b) values (3);

2384

select * from t1;

2385

a b

2386

2 2

2387

3 1

2388

4 3

2389

drop table t1;

2390

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

2391

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

2392

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

2393

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

2394

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

2395

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

2396

select * from t1;

2397

rowid val

2398

3 1

2399

4 2

2400

drop table t1;

2401

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

2402

insert into t1 (val) values (1);

2403

update t1 set a=2 where a=1;

2404

insert into t1 (val) values (1);

2405

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

2406

select * from t1;

2407

a val

2408

2 1

2409

drop table t1;

2410

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

2411

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

2412

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

2413

GRADE

2414

252

2415

SELECT GRADE FROM t1 WHERE GRADE= 151;

2416

GRADE

2417

151

2418

DROP TABLE t1;

2419

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

2420

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

2421

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

2422

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

2423

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

2424

select * from t1;

2425

f1 f2

2426

drop table t1,t2;

2427

CREATE TABLE t1 (

2428

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

2429

) ENGINE=InnoDB;

2430

CREATE TABLE t2 (

2431

id INTEGER NOT NULL,

2432

FOREIGN KEY (id) REFERENCES t1 (id)

2433

) ENGINE=InnoDB;

2434

INSERT INTO t1 (id) VALUES (NULL);

2435

SELECT * FROM t1;

2436

2437

2438

TRUNCATE t1;

2439

INSERT INTO t1 (id) VALUES (NULL);

2440

SELECT * FROM t1;

2441

2442

2443

DELETE FROM t1;

2444

TRUNCATE t1;

2445

INSERT INTO t1 (id) VALUES (NULL);

2446

SELECT * FROM t1;

2447

2448

2449

DROP TABLE t2, t1;

2450

CREATE TABLE t1

2451

(

2452

id INT PRIMARY KEY

2453

) ENGINE=InnoDB;

2454

CREATE TEMPORARY TABLE t2

2455

(

2456

id INT NOT NULL PRIMARY KEY,

2457

b INT,

2458

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

2459

) ENGINE=InnoDB;

2460

Got one of the listed errors

2461

DROP TABLE t1;

2462

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

2463

engine = innodb;

2464

Warnings:

2465

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

2466

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

2467

engine = innodb;

2468

Warnings:

2469

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

2470

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

2471

engine = innodb;

2472

Warnings:

2473

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

2474

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

2475

engine = innodb;

2476

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

2477

engine = innodb;

2478

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

2479

engine = innodb;

2480

Warnings:

2481

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

2482

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

2483

engine = innodb;

2484

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

2485

engine = innodb;

2486

Warnings:

2487

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

2488

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

2489

show create table t9;

2490

Table Create Table

2491

t9 CREATE TABLE `t9` (

2492

`col1` varchar(512),

2493

`col2` varchar(512),

2494

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

2495

) ENGINE=InnoDB

2496

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

2497

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

2498

engine = innodb;

2499

Warnings:

2500

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

2501

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

2502

engine = innodb;

2503

Warnings:

2504

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

2505

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

2506

engine = innodb;

2507

Warnings:

2508

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

2509

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

2510

engine = innodb;

2511

Warnings:

2512

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

2513

show create table t1;

2514

Table Create Table

2515

t1 CREATE TABLE `t1` (

2516

`col1` varchar(768),

2517

KEY `col1` (`col1`())

2518

) ENGINE=InnoDB

2519

drop table t1, t2, t3, t4;

2520

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

2521

engine = innodb;

2522

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

2523

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

2524

engine = innodb;

2525

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

2526

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

2527

engine = innodb;

2528

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

2529

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

2530

engine = innodb;

2531

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

2532

CREATE TABLE t1

2533

(

2534

id INT PRIMARY KEY

2535

) ENGINE=InnoDB;

2536

CREATE TABLE t2

2537

(

2538

v INT,

2539

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

2540

) ENGINE=InnoDB;

2541

INSERT INTO t2 VALUES(2);

2542

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

2543

INSERT INTO t1 VALUES(1);

2544

INSERT INTO t2 VALUES(1);

2545

DELETE FROM t1 WHERE id = 1;

2546

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

2547

DROP TABLE t1;

2548

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

2549

SET FOREIGN_KEY_CHECKS=0;

2550

DROP TABLE t1;

2551

SET FOREIGN_KEY_CHECKS=1;

2552

INSERT INTO t2 VALUES(3);

2553

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

2554

DROP TABLE t2;

2555

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

2556

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

2557

set autocommit=0;

2558

checksum table t1;

2559

Table Checksum

2560

test.t1 1531596814

2561

insert into t1 values(3);

2562

checksum table t1;

2563

Table Checksum

2564

test.t1 1531596814

2565

commit;

2566

checksum table t1;

2567

Table Checksum

2568

test.t1 2050879373

2569

commit;

2570

drop table t1;

2571

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

2572

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

2573

set autocommit=1;

2574

checksum table t1;

2575

Table Checksum

2576

test.t1 1531596814

2577

set autocommit=1;

2578

insert into t1 values(3);

2579

checksum table t1;

2580

Table Checksum

2581

test.t1 2050879373

2582

drop table t1;

2583

set foreign_key_checks=0;

2584

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

2585

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

2586

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

2587

set foreign_key_checks=1;

2588

drop table t2;

2589

set foreign_key_checks=0;

2590

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

2591

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

2592

alter table t1 modify column a int;

2593

Got one of the listed errors

2594

set foreign_key_checks=1;

2595

drop table t2,t1;

2596

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

2597

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

2598

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

2599

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

2600

insert into t1 values(1);

2601

insert into t3 values(1);

2602

insert into t2 values(2);

2603

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

2604

insert into t4 values(2);

2605

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

2606

insert into t2 values(1);

2607

insert into t4 values(1);

2608

update t1 set a=2;

2609

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

2610

update t2 set a=2;

2611

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

2612

update t3 set a=2;

2613

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

2614

update t4 set a=2;

2615

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

2616

truncate t1;

2617

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

2618

truncate t3;

2619

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

2620

truncate t2;

2621

truncate t4;

2622

truncate t1;

2623

truncate t3;

2624

drop table t4,t3,t2,t1;

2625

create table t1 (a varchar(255),

2626

b varchar(255),

2627

c varchar(255),

2628

d varchar(255),

2629

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

2630

Warnings:

2631

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

2632

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

2633

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

2634

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

2635

drop table t1;

2636

create table t1 (a varchar(255),

2637

b varchar(255),

2638

c varchar(255),

2639

d varchar(255),

2640

e varchar(255),

2641

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

2642

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

2643

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

2644

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

2645

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

2646

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

2647

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

2648

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

2649

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

2650

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

2651

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

2652

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

2653

select hex(s1) from t1;

2654

hex(s1)

2655

2656

4100

2657

4120

2658

select hex(s1) from t3;

2659

hex(s1)

2660

4100

2661

2662

select hex(s1) from t4;

2663

hex(s1)

2664

4100

2665

2666

drop table t1,t3,t4;

2667

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

2668

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

2669

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

2670

insert into t2 values(0x42);

2671

insert into t2 values(0x41);

2672

select hex(s1) from t2;

2673

hex(s1)

2674

2675

2676

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

2677

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)

2678

select hex(s1) from t2;

2679

hex(s1)

2680

2681

2682

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

2683

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

2684

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

2685

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

2686

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

2687

select hex(s1) from t2;

2688

hex(s1)

2689

2690

2691

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

2692

select hex(s1) from t2;

2693

hex(s1)

2694

2695

2696

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

2697

select hex(s1) from t2;

2698

hex(s1)

2699

2700

2701

delete from t1 where a=1;

2702

update t2 set s1=0x4120;

2703

delete from t1;

2704

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)

2705

delete from t1 where a!=3;

2706

select a,hex(s1) from t1;

2707

a hex(s1)

2708

3 4120

2709

select hex(s1) from t2;

2710

hex(s1)

2711

4120

2712

4120

2713

drop table t2,t1;

2714

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

2715

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

2716

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

2717

insert into t2 values(0x41);

2718

select hex(s1) from t2;

2719

hex(s1)

2720

2721

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

2722

select hex(s1) from t2;

2723

hex(s1)

2724

2725

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

2726

select hex(s1) from t2;

2727

hex(s1)

2728

2729

delete from t1 where a=1;

2730

delete from t1 where a=2;

2731

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)

2732

select a,hex(s1) from t1;

2733

a hex(s1)

2734

2 12

2735

select hex(s1) from t2;

2736

hex(s1)

2737

2738

drop table t2,t1;

2739

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

2740

CREATE TABLE t2(a INT) ENGINE=InnoDB;

2741

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

2742

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

2743

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

2744

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

2745

SHOW CREATE TABLE t2;

2746

Table Create Table

2747

t2 CREATE TABLE `t2` (

2748

`a` int,

2749

KEY `t2_ibfk_0` (`a`)

2750

) ENGINE=InnoDB

2751

DROP TABLE t2,t1;

2752

CREATE TABLE t1 (

2753

field1 varchar(8) NOT NULL DEFAULT '',

2754

field2 varchar(8) NOT NULL DEFAULT '',

2755

PRIMARY KEY (field1, field2)

2756

) ENGINE=InnoDB;

2757

CREATE TABLE t2 (

2758

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

2759

FOREIGN KEY (field1) REFERENCES t1 (field1)

2760

ON DELETE CASCADE ON UPDATE CASCADE

2761

) ENGINE=InnoDB;

2762

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

2763

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

2764

INSERT INTO t2 VALUES ('old');

2765

INSERT INTO t2 VALUES ('other');

2766

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

2767

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

2768

DROP TABLE t2;

2769

DROP TABLE t1;

2770

create table t1 (

2771

c1 bigint not null,

2772

c2 bigint not null,

2773

primary key (c1),

2774

unique key (c2)

2775

) engine=innodb;

2776

create table t2 (

2777

c1 bigint not null,

2778

primary key (c1)

2779

) engine=innodb;

2780

alter table t1 add constraint c2_fk foreign key (c2)

2781

references t2(c1) on delete cascade;

2782

show create table t1;

2783

Table Create Table

2784

t1 CREATE TABLE `t1` (

2785

`c1` bigint NOT NULL,

2786

`c2` bigint NOT NULL,

2787

PRIMARY KEY (`c1`),

2788

UNIQUE KEY `c2` (`c2`),

2789

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

2790

) ENGINE=InnoDB

2791

alter table t1 drop foreign key c2_fk;

2792

show create table t1;

2793

Table Create Table

2794

t1 CREATE TABLE `t1` (

2795

`c1` bigint NOT NULL,

2796

`c2` bigint NOT NULL,

2797

PRIMARY KEY (`c1`),

2798

UNIQUE KEY `c2` (`c2`)

2799

) ENGINE=InnoDB

2800

drop table t1, t2;

2801

create table t1(a date) engine=innodb;

2802

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

2803

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

2804

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

2805

select * from t1, t2

2806

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

2807

a a

2808

2005-10-01 2005-10-01

2809

drop table t1, t2;

2810

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

2811

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

2812

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

2813

primary key(id)) engine=innodb;

2814

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

2815

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

2816

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

2817

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

2818

commit;

2819

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

2820

WHERE mm.id IS NULL;

2821

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

2822

where mm.id is null lock in share mode;

2823

id f_id f

2824

drop table t1,t2;

2825

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

2826

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

2827

commit;

2828

set autocommit = 0;

2829

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2830

update t1 set b = 5 where b = 1;

2831

set autocommit = 0;

2832

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2833

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

2834

a b

2835

7 3

2836

commit;

2837

commit;

2838

drop table t1;

2839

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

2840

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

2841

commit;

2842

set autocommit = 0;

2843

select * from t1 lock in share mode;

2844

a b

2845

1 1

2846

2 2

2847

3 1

2848

4 2

2849

5 1

2850

6 2

2851

update t1 set b = 5 where b = 1;

2852

set autocommit = 0;

2853

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

2854

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2855

commit;

2856

commit;

2857

drop table t1;

2858

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

2859

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

2860

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

2861

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

2862

commit;

2863

set autocommit = 0;

2864

select * from t2 for update;

2865

d e

2866

3 1

2867

8 6

2868

12 1

2869

set autocommit = 0;

2870

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2871

insert into t1 select * from t2;

2872

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

2873

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

2874

select * from t2;

2875

commit;

2876

commit;

2877

drop table t1, t2, t3;

2878

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

2879

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

2880

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

2881

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

2882

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

2883

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

2884

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

2885

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

2886

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

2887

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

2888

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

2889

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

2890

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

2891

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

2892

commit;

2893

set autocommit = 0;

2894

select * from t2 for update;

2895

a b

2896

3 1

2897

8 6

2898

12 1

2899

set autocommit = 0;

2900

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2901

insert into t1 select * from t2;

2902

set autocommit = 0;

2903

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2904

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

2905

set autocommit = 0;

2906

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2907

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

2908

set autocommit = 0;

2909

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2910

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

2911

set autocommit = 0;

2912

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2913

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

2914

set autocommit = 0;

2915

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2916

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

2917

set autocommit = 0;

2918

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2919

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

2920

set autocommit = 0;

2921

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2922

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

2923

set autocommit = 0;

2924

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2925

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

2926

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2927

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2928

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2929

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2930

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2931

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2932

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2933

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2934

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

2935

commit;

2936

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

2937

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

2938

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

2939

CREATE TABLE t1 (

2940

a BIGINT NOT NULL,

2941

PRIMARY KEY (a)

2942

) ENGINE=INNODB;

2943

CREATE TABLE t2 (

2944

a BIGINT NOT NULL,

2945

b VARCHAR(128) NOT NULL,

2946

c TEXT NOT NULL,

2947

PRIMARY KEY (a,b),

2948

KEY idx_t2_b_c (b,c(200)),

2949

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

2950

ON DELETE CASCADE

2951

) ENGINE=INNODB;

2952

Warnings:

2953

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

2954

INSERT INTO t1 VALUES (1);

2955

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

2956

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

2957

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

2958

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

2959

SELECT * FROM t2 WHERE b = 'customer_over';

2960

a b c

2961

1 customer_over 1

2962

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

2963

a b c

2964

1 customer_over 1

2965

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

2966

2967

2968

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

2969

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

2970

2971

2972

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

2973

2974

2975

drop table t2, t1;

2976

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2977

BEGIN;

2978

INSERT INTO t1 VALUES (1);

2979

OPTIMIZE TABLE t1;

2980

Table Op Msg_type Msg_text

2981

test.t1 optimize status OK

2982

DROP TABLE t1;

2983

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

2984

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

2985

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

2986

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

2987

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

2988

DELETE CASCADE ON UPDATE CASCADE;

2989

SHOW CREATE TABLE t2;

2990

Table Create Table

2991

t2 CREATE TABLE `t2` (

2992

`id` int NOT NULL,

2993

`f` int NOT NULL,

2994

PRIMARY KEY (`id`),

2995

KEY `f` (`f`),

2996

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

2997

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

2998

) ENGINE=InnoDB

2999

DROP TABLE t2, t1;

3000

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

3001

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

3002

INSERT INTO t1 VALUES (1);

3003

INSERT INTO t2 VALUES (1);

3004

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

3005

ALTER TABLE t2 MODIFY a INT NOT NULL;

3006

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

3007

DELETE FROM t1;

3008

DROP TABLE t2,t1;

3009

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

3010

ENGINE=InnoDB;

3011

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

3012

DELETE FROM t1;

3013

INSERT INTO t1 VALUES ('DDD');

3014

SELECT * FROM t1;

3015

3016

DDD

3017

DROP TABLE t1;

3018

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

3019

AUTO_INCREMENT=42;

3020

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

3021

SELECT * FROM t1;

3022

3023

3024

347

3025

348

3026

SHOW CREATE TABLE t1;

3027

Table Create Table

3028

t1 CREATE TABLE `t1` (

3029

`id` int NOT NULL AUTO_INCREMENT,

3030

PRIMARY KEY (`id`)

3031

) ENGINE=InnoDB AUTO_INCREMENT=349

3032

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

3033

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

3034

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

3035

SHOW CREATE TABLE t1;

3036

Table Create Table

3037

t1 CREATE TABLE `t1` (

3038

`id` int NOT NULL AUTO_INCREMENT,

3039

PRIMARY KEY (`id`),

3040

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

3041

) ENGINE=InnoDB AUTO_INCREMENT=349

3042

DROP TABLE t1,t2;

3043

DROP TABLE IF EXISTS t1;

3044

Warnings:

3045

Note 1051 Unknown table 't1'

3046

CREATE TABLE t1(

3047

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

3048

) ENGINE=InnoDB;

3049

INSERT INTO t1 VALUES(-10);

3050

SELECT * FROM t1;

3051

3052

-10

3053

INSERT INTO t1 VALUES(NULL);

3054

SELECT * FROM t1;

3055

3056

-10

3057

3058

DROP TABLE t1;

Older »