~drizzle-trunk/drizzle/development

# r2667 http://bazaar.launchpad.net/~mysql/mysql-server/mysql-5.1/revision/davi%40mysql.com-20080617141221-8yre8ys9j4uw3xx5?start_revid=joerg%40mysql.com-20080630105418-7qoe5ehomgrcdb89

251

# r2667 MySQL changed the behavior to do full table copy when creating PRIMARY INDEX

252

# r2667 on a non-NULL column instead of calling ::add_index() which would fail (and

253

# r2667 this is what we were testing here). Before r2667 the code execution path was

254

# r2667 like this (when adding PRIMARY INDEX on a non-NULL column with ALTER TABLE):

255

# r2667

256

# r2667 mysql_alter_table()

257

# r2667 compare_tables() // would return ALTER_TABLE_INDEX_CHANGED

258

# r2667 ::add_index() // would fail with "primary index cannot contain NULL"

259

# r2667

260

# r2667 after r2667 the code execution path is the following:

261

# r2667

262

# r2667 mysql_alter_table()

263

# r2667 compare_tables() // returns ALTER_TABLE_DATA_CHANGED

264

# r2667 full copy is done, without calling ::add_index()

265

# r2667

266

# r2667 To enable, remove "# r2667: " below.

267

# r2667

268

# r2667: insert into t1 values (null,null,null,'null');

269

insert into t1

270

select a,left(repeat(d,100*a),65535),repeat(d,20*a),d from t2,t3;

271

drop table t2, t3;

272

select count(*) from t1 where a=44;

273

select a,

274

length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;

275

# r2667: --error ER_PRIMARY_CANT_HAVE_NULL

276

# r2667: alter table t1 add primary key (a), add key (b(20));

277

# r2667: delete from t1 where d='null';

278

--error ER_DUP_ENTRY

279

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

280

delete from t1 where a%2;

281

check table t1;

282

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

283

select count(*) from t1 where a=44;

284

select a,

285

length(b),b=left(repeat(d,100*a),65535),length(c),c=repeat(d,20*a),d from t1;

286

show create table t1;

287

check table t1;

288

explain select * from t1 where b like 'adfd%';

289

290

291

# Test locking

292

293

294

create table t2(a int, b varchar(255), primary key(a,b)) engine=innodb;

295

insert into t2 select a,left(b,255) from t1;

296

drop table t1;

297

rename table t2 to t1;

298

299

connect (a,localhost,root,,);

300

connect (b,localhost,root,,);

301

connection a;

302

set innodb_lock_wait_timeout=1;

303

begin;

304

# Obtain an IX lock on the table

305

select a from t1 limit 1 for update;

306

connection b;

307

set innodb_lock_wait_timeout=1;

308

# This would require an S lock on the table, conflicting with the IX lock.

309

--error ER_LOCK_WAIT_TIMEOUT

310

create index t1ba on t1 (b,a);

311

connection a;

312

commit;

313

begin;

314

# Obtain an IS lock on the table

315

select a from t1 limit 1 lock in share mode;

316

connection b;

317

# This will require an S lock on the table. No conflict with the IS lock.

318

create index t1ba on t1 (b,a);

319

# This would require an X lock on the table, conflicting with the IS lock.

320

--error ER_LOCK_WAIT_TIMEOUT

321

drop index t1ba on t1;

322

connection a;

323

commit;

324

explain select a from t1 order by b;

325

--send

326

select a,sleep(2+a/100) from t1 order by b limit 3;

327

328

# The following DROP INDEX will succeed, altough the SELECT above has

329

# opened a read view. However, during the execution of the SELECT,

330

# MySQL should hold a table lock that should block the execution

331

# of the DROP INDEX below.

332

333

connection b;

334

select sleep(1);

335

drop index t1ba on t1;

336

337

# After the index was dropped, subsequent SELECTs will use the same

338

# read view, but they should not be accessing the dropped index any more.

339

340

connection a;

341

reap;

342

explain select a from t1 order by b;

343

select a from t1 order by b limit 3;

344

commit;

345

346

connection default;

347

disconnect a;

348

disconnect b;

349

350

drop table t1;

351

352

let $per_table=`select @@innodb_file_per_table`;

353

let $format=`select @@innodb_file_format`;

354

set global innodb_file_per_table=on;

355

set global innodb_file_format='Barracuda';

356

# Test creating a table that could lead to undo log overflow.

357

# In the undo log, we write a 768-byte prefix (REC_MAX_INDEX_COL_LEN)

358

# of each externally stored column that appears as a column prefix in an index.

359

# For this test case, it would suffice to write 1 byte, though.

360

create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,

361

i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob,

362

q blob,r blob,s blob,t blob,u blob)

363

engine=innodb row_format=dynamic;

364

create index t1a on t1 (a(1));

365

create index t1b on t1 (b(1));

366

create index t1c on t1 (c(1));

367

create index t1d on t1 (d(1));

368

create index t1e on t1 (e(1));

369

create index t1f on t1 (f(1));

370

create index t1g on t1 (g(1));

371

create index t1h on t1 (h(1));

372

create index t1i on t1 (i(1));

373

create index t1j on t1 (j(1));

374

create index t1k on t1 (k(1));

375

create index t1l on t1 (l(1));

376

create index t1m on t1 (m(1));

377

create index t1n on t1 (n(1));

378

create index t1o on t1 (o(1));

379

create index t1p on t1 (p(1));

380

create index t1q on t1 (q(1));

381

create index t1r on t1 (r(1));

382

create index t1s on t1 (s(1));

383

create index t1t on t1 (t(1));

384

--error 139

385

create index t1u on t1 (u(1));

386

--error 139

387

create index t1ut on t1 (u(1), t(1));

388

create index t1st on t1 (s(1), t(1));

389

show create table t1;

390

--error 139

391

create index t1u on t1 (u(1));

392

alter table t1 row_format=compact;

393

create index t1u on t1 (u(1));

394

395

drop table t1;

396

eval set global innodb_file_per_table=$per_table;

397

eval set global innodb_file_format=$format;

398

399

400

# Test to check whether CREATE INDEX handles implicit foreign key

401

# constraint modifications (Issue #70, Bug #38786)

402

403

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

404

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

405

406

CREATE TABLE t1(

407

c1 BIGINT(12) NOT NULL,

408

PRIMARY KEY (c1)

409

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

410

411

CREATE TABLE t2(

412

c1 BIGINT(16) NOT NULL,

413

c2 BIGINT(12) NOT NULL,

414

c3 BIGINT(12) NOT NULL,

415

PRIMARY KEY (c1)

416

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

417

418

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

419

FOREIGN KEY (c3) REFERENCES t1(c1);

420

421

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

422

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

423

424

SHOW CREATE TABLE t2;

425

426

CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);

427

428

SHOW CREATE TABLE t2;

429

430

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

431

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

432

433

--error ER_NO_REFERENCED_ROW_2

434

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

435

INSERT INTO t1 VALUES(0);

436

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

437

438

DROP TABLE t2;

439

440

CREATE TABLE t2(

441

c1 BIGINT(16) NOT NULL,

442

c2 BIGINT(12) NOT NULL,

443

c3 BIGINT(12) NOT NULL,

444

PRIMARY KEY (c1,c2,c3)

445

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

446

447

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

448

FOREIGN KEY (c3) REFERENCES t1(c1);

449

450

SHOW CREATE TABLE t2;

451

452

CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);

453

454

SHOW CREATE TABLE t2;

455

--error ER_NO_REFERENCED_ROW_2

456

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

457

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

458

--error ER_ROW_IS_REFERENCED_2

459

DELETE FROM t1;

460

DELETE FROM t2;

461

462

DROP TABLE t2;

463

DROP TABLE t1;

464

465

CREATE TABLE t1(

466

c1 BIGINT(12) NOT NULL,

467

c2 INT(4) NOT NULL,

468

PRIMARY KEY (c2,c1)

469

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

470

471

CREATE TABLE t2(

472

c1 BIGINT(16) NOT NULL,

473

c2 BIGINT(12) NOT NULL,

474

c3 BIGINT(12) NOT NULL,

475

PRIMARY KEY (c1)

476

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

477

478

--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/

479

--error ER_CANT_CREATE_TABLE

480

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

481

FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);

482

--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/

483

--error ER_CANT_CREATE_TABLE

484

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

485

FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);

486

--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/

487

--error ER_CANT_CREATE_TABLE

488

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

489

FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);

490

ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;

491

--replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/

492

--error ER_CANT_CREATE_TABLE

493

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

494

FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);

495

496

ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca

497

FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);

498

SHOW CREATE TABLE t1;

499

SHOW CREATE TABLE t2;

500

CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);

501

SHOW CREATE TABLE t2;

502

CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);

503

SHOW CREATE TABLE t2;

504

CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);

505

SHOW CREATE TABLE t2;

506

507

DROP TABLE t2;

508

DROP TABLE t1;

Older »