~drizzle-trunk/drizzle/development

Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)

280

SELECT * FROM t1 CROSS JOIN t2;

280

SELECT * FROM t1, t2;

281

i i

282

1 1

283

DROP TABLE t2, t1;

316

WHERE t1.name LIKE 'A%';

317

id select_type table type possible_keys key key_len ref rows Extra

318

1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where

319

1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index

319

1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index

320

EXPLAIN

321

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id

322

WHERE t1.name LIKE 'A%' OR FALSE;

347

DELETE FROM t1;

348

EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';

349

id select_type table type possible_keys key key_len ref rows Extra

350

1 SIMPLE t1 range name name 164 NULL # Using where; Using index for group-by

350

1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by

351

SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';

352

name dept

353

DROP TABLE t1;

354

drop table if exists t1;

355

show variables like 'innodb_rollback_on_timeout';

356

Variable_name Value

357

innodb_rollback_on_timeout OFF

355

358

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

356

359

insert into t1 values (1);

357

360

commit;

406

409

tes 1234

407

410

drop table test;

408

411

drop table if exists t1;

412

show variables like 'innodb_rollback_on_timeout';

413

Variable_name Value

414

innodb_rollback_on_timeout OFF

409

415

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

410

416

insert into t1 values (1);

411

417

commit;

479

485

4 a 2

480

486

drop table t1;

481

487

create table t1(a int) engine=innodb;

482

alter table t1 comment='123';

488

alter table t1 comment '123';

483

489

show create table t1;

484

490

Table Create Table

485

491

t1 CREATE TABLE `t1` (

486

`a` INT DEFAULT NULL

487

) ENGINE=InnoDB COMMENT='123' COLLATE = utf8_general_ci

492

`a` int

493

) ENGINE=InnoDB COMMENT='123'

488

494

drop table t1;

489

495

CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB;

490

496

INSERT INTO t1 VALUES ('uk'),('bg');

509

515

UPDATE t2 SET a = 'us' WHERE a = 'uk';

510

516

SELECT * FROM t2 WHERE a = 'uk';

511

517

512

CREATE TEMPORARY TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;

518

CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;

513

519

INSERT INTO t3 VALUES ('uk'),('bg');

514

520

SELECT * FROM t3 WHERE a = 'uk';

515

521

522

528

SELECT * FROM t3 WHERE a = 'uk';

523

529

524

530

DROP TABLE t1,t2,t3;

531

create table t1 (a int) engine=innodb;

532

select * from bug29807;

533

ERROR 42S02: Table 'test.bug29807' doesn't exist

534

drop table t1;

535

drop table bug29807;

536

ERROR 42S02: Unknown table 'bug29807'

537

create table bug29807 (a int);

538

drop table bug29807;

539

CREATE TABLE t1 (a INT) ENGINE=InnoDB;

540

CREATE TABLE t2 (a INT) ENGINE=InnoDB;

541

switch to connection c1

542

SET AUTOCOMMIT=0;

543

INSERT INTO t2 VALUES (1);

544

switch to connection c2

545

SET AUTOCOMMIT=0;

546

LOCK TABLES t1 READ, t2 READ;

547

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

548

switch to connection c1

549

COMMIT;

550

INSERT INTO t1 VALUES (1);

551

switch to connection default

552

SET AUTOCOMMIT=default;

553

DROP TABLE t1,t2;

525

554

CREATE TABLE t1 (

526

555

id int NOT NULL auto_increment PRIMARY KEY,

527

556

b int NOT NULL,

532

561

CREATE TABLE t2 (

533

562

b int NOT NULL auto_increment PRIMARY KEY,

534

563

c datetime NOT NULL

535

) ENGINE= InnoDB;

564

) ENGINE= MyISAM;

536

565

INSERT INTO t2(c) VALUES ('2007-01-01');

537

566

INSERT INTO t2(c) SELECT c FROM t2;

538

567

INSERT INTO t2(c) SELECT c FROM t2;

551

580

INSERT INTO t1(b,c) SELECT b,c FROM t2;

552

581

set @@sort_buffer_size=8192;

553

582

Warnings:

554

Error 1292 Truncated incorrect sort_buffer_size value: '8192'

583

Warning 1292 Truncated incorrect sort_buffer_size value: '8192'

555

584

SELECT COUNT(*) FROM t1;

556

585

COUNT(*)

557

586

3072

590

619

CREATE TABLE t2 (a int, b int, primary key (a));

591

620

BEGIN;

592

621

INSERT INTO t2 values(100,100);

593

COMMIT;

594

622

CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;

595

623

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

596

624

SELECT * from t2;

635

663

drop table t1,t2;

636

664

create table t1(f1 varchar(800) not null, key(f1));

637

665

Warnings:

638

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

666

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

639

667

insert into t1 values('aaa');

640

668

drop table t1;

641

669

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;

655

683

1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort

656

684

DROP TABLE t1;

657

685

drop table if exists t1;

686

show variables like 'innodb_rollback_on_timeout';

687

Variable_name Value

688

innodb_rollback_on_timeout OFF

658

689

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

659

690

insert into t1 values (1);

660

691

commit;

688

719

689

720

690

721

drop table t1;

722

drop table if exists t1;

723

create table t1 (a int) engine=innodb;

724

alter table t1 alter a set default 1;

725

drop table t1;

726

727

Bug#24918 drop table and lock / inconsistent between

728

perm and temp tables

729

730

Check transactional tables under LOCK TABLES

731

732

drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,

733

t24918_access;

734

create table t24918_access (id int);

735

create table t24918 (id int) engine=myisam;

736

create temporary table t24918_tmp (id int) engine=myisam;

737

create table t24918_trans (id int) engine=innodb;

738

create temporary table t24918_trans_tmp (id int) engine=innodb;

739

lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;

740

drop table t24918;

741

select * from t24918_access;

742

ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES

743

drop table t24918_trans;

744

select * from t24918_access;

745

ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES

746

drop table t24918_trans_tmp;

747

select * from t24918_access;

748

ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES

749

drop table t24918_tmp;

750

select * from t24918_access;

751

ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES

752

unlock tables;

753

drop table t24918_access;

691

754

CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;

692

755

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

693

756

INSERT INTO t1 SELECT a + 8, 2 FROM t1;

770

833

id 1

771

834

select_type SIMPLE

772

835

table t1

773

type index

836

type range

774

837

possible_keys bkey

775

838

key bkey

776

839

key_len 5

777

840

ref NULL

778

rows 32

841

rows 16

779

842

Extra Using where; Using index

780

843

SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;

781

844

a b

854

917

key bkey

855

918

key_len 10

856

919

ref const,const

857

rows 16

920

rows 8

858

921

Extra Using where; Using index

859

922

SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;

860

923

a b c

883

946

key bkey

884

947

key_len 10

885

948

ref const,const

886

rows 16

949

rows 8

887

950

Extra Using where; Using index

888

951

SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;

889

952

a b c

912

975

key bkey

913

976

key_len 10

914

977

ref const,const

915

rows 16

978

rows 8

916

979

Extra Using where; Using index

917

980

SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;

918

981

a b c

937

1000

insert into t1 values('aaa');

938

1001

alter table t1 add index(a(1024));

939

1002

Warnings:

940

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

1003

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

1004

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

1005

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

941

1006

show create table t1;

942

1007

Table Create Table

943

1008

t1 CREATE TABLE `t1` (

944

`a` TEXT COLLATE utf8_general_ci,

945

KEY `a` (`a`(255))

946

) ENGINE=InnoDB COLLATE = utf8_general_ci

1009

`a` text,

1010

KEY `a` (`a`())

1011

) ENGINE=InnoDB

947

1012

drop table t1;

948

1013

CREATE TABLE t1 (

949

1014

a INT,

1023

1088

key b

1024

1089

key_len 5

1025

1090

ref const

1026

rows 2

1091

rows 1

1027

1092

Extra Using where; Using index

1028

1093

SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;

1029

1094

a b

1038

1103

key b

1039

1104

key_len 5

1040

1105

ref const

1041

rows 2

1106

rows 1

1042

1107

Extra Using where; Using index

1043

1108

SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;

1044

1109

a b

1125

1190

# - initial check;

1126

1191

1127

1192

SELECT table_schema, table_name, row_format

1128

FROM data_dictionary.TABLES

1193

FROM INFORMATION_SCHEMA.TABLES

1129

1194

WHERE table_schema = DATABASE() AND table_name = 't1';

1130

1195

table_schema table_name row_format

1131

test t1 COMPACT

1196

test t1 Compact

1132

1197

1133

1198

# - change ROW_FORMAT and check;

1134

1199

1135

1200

ALTER TABLE t1 ROW_FORMAT = REDUNDANT;

1136

1201

1137

1202

SELECT table_schema, table_name, row_format

1138

FROM data_dictionary.TABLES

1203

FROM INFORMATION_SCHEMA.TABLES

1139

1204

WHERE table_schema = DATABASE() AND table_name = 't1';

1140

1205

table_schema table_name row_format

1141

test t1 REDUNDANT

1206

test t1 Redundant

1142

1207

1143

1208

# - that's it, cleanup.

1144

1209

1146

1211

create table t1(a char(10) not null, unique key aa(a(1)),

1147

1212

b char(4) not null, unique key bb(b(4))) engine=innodb;

1148

1213

desc t1;

1149

Field Type Null Default Default_is_NULL On_Update

1150

a VARCHAR NO NO

1151

b VARCHAR NO NO

1214

Field Type Null Key Default Extra

1215

a varchar(10) NO UNI NULL

1216

b varchar(4) NO PRI NULL

1152

1217

show create table t1;

1153

1218

Table Create Table

1154

1219

t1 CREATE TABLE `t1` (

1155

`a` VARCHAR(10) COLLATE utf8_general_ci NOT NULL,

1156

`b` VARCHAR(4) COLLATE utf8_general_ci NOT NULL,

1220

`a` varchar(10) NOT NULL,

1221

`b` varchar(4) NOT NULL,

1157

1222

UNIQUE KEY `bb` (`b`),

1158

UNIQUE KEY `aa` (`a`(1))

1159

) ENGINE=InnoDB COLLATE = utf8_general_ci

1223

UNIQUE KEY `aa` (`a`())

1224

) ENGINE=InnoDB

1160

1225

drop table t1;

1161

1226

CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;

1162

1227

INSERT INTO t1 VALUES

1173

1238

set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;

1174

1239

set global innodb_autoextend_increment=8;

1175

1240

set global innodb_autoextend_increment=@my_innodb_autoextend_increment;

1241

set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;

1242

set global innodb_commit_concurrency=0;

1243

set global innodb_commit_concurrency=@my_innodb_commit_concurrency;

1176

1244

End of 5.0 tests

1177

1245

CREATE TABLE `t2` (

1178

1246

`k` int NOT NULL auto_increment,

1242

1310

11 15 1

1243

1311

12 20 1

1244

1312

drop table t2;

1313

drop table if exists t1, t2;

1314

create table t1 (i int);

1315

alter table t1 modify i int default 1;

1316

alter table t1 modify i int default 2, rename t2;

1317

lock table t2 write;

1318

alter table t2 modify i int default 3;

1319

unlock tables;

1320

lock table t2 write;

1321

alter table t2 modify i int default 4, rename t1;

1322

unlock tables;

1323

drop table t1;

1324

drop table if exists t1;

1325

create table t1 (i int);

1326

insert into t1 values ();

1327

lock table t1 write;

1328

alter table t1 modify i int default 1;

1329

insert into t1 values ();

1330

select * from t1;

1331

1332

NULL

1333

1334

alter table t1 change i c char(10) default "Two";

1335

insert into t1 values ();

1336

select * from t1;

1337

1338

NULL

1339

1340

Two

1341

unlock tables;

1342

select * from t1;

1343

1344

NULL

1345

1346

Two

1347

drop tables t1;

1245

1348

create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT

1246

1349

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

1247

1350

insert into t1(f1) values(1);

1273

1376

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;

1274

1377

CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),

1275

1378

CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;

1276

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

1379

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

1277

1380

CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),

1278

1381

CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;

1279

1382

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

1296

1399

SHOW CREATE TABLE t2;

1297

1400

Table Create Table

1298

1401

t2 CREATE TABLE `t2` (

1299

`c` INT NOT NULL,

1300

`d` INT NOT NULL,

1402

`c` int NOT NULL,

1403

`d` int NOT NULL,

1301

1404

PRIMARY KEY (`c`,`d`),

1302

1405

CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,

1303

1406

CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,

1304

CONSTRAINT `f3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,

1305

CONSTRAINT `t2_ibfk_4` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION

1306

) ENGINE=InnoDB COLLATE = utf8_general_ci

1407

CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,

1408

CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION

1409

) ENGINE=InnoDB

1307

1410

DROP TABLE t2;

1308

1411

DROP TABLE t1;

1309

1412

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

1348

1451

1349

1452

DROP TABLE t2;

1350

1453

DROP TABLE t1;

1351

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1352

1454

End of 5.1 tests

1353

1354

# Test for bug #39932 "create table fails if column for FK is in different

1355

# case than in corr index".

1356

1357

drop tables if exists t1, t2;

1358

create table t1 (pk int primary key) engine=InnoDB;

1359

# Even although the below statement uses uppercased field names in

1360

# foreign key definition it still should be able to find explicitly

1361

# created supporting index. So it should succeed and should not

1362

# create any additional supporting indexes.

1363

create table t2 (fk int, key x (fk),

1364

constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;

1365

show create table t2;

1366

Table Create Table

1367

t2 CREATE TABLE `t2` (

1368

`fk` INT DEFAULT NULL,

1369

KEY `x` (`fk`),

1370

CONSTRAINT `x` FOREIGN KEY (`FK`) REFERENCES `t1` (`PK`)

1371

) ENGINE=InnoDB COLLATE = utf8_general_ci

1372

drop table t2, t1;

1373

1374

# Bug#55826: create table .. select crashes with when KILL_BAD_DATA

1375

# is returned

1376

1377

CREATE TABLE t1(a INT) ENGINE=innodb;

1378

INSERT INTO t1 VALUES (0);

1379

CREATE TABLE t2

1380

SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;

1381

ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 1

1382

DROP TABLE IF EXISTS t1,t2;

1383

Warnings:

1384

Note 1051 Unknown table 't2'

Older »