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

279

SELECT * FROM t1, t2;

281

280

i i

282

281

1 1

283

282

DROP TABLE t2, t1;

316

315

WHERE t1.name LIKE 'A%';

317

316

id select_type table type possible_keys key key_len ref rows Extra

318

317

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

318

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

320

319

EXPLAIN

321

320

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

322

321

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

347

346

DELETE FROM t1;

348

347

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

349

348

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

349

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

351

350

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

352

351

name dept

353

352

DROP TABLE t1;

354

353

drop table if exists t1;

354

show variables like 'innodb_rollback_on_timeout';

355

Variable_name Value

356

innodb_rollback_on_timeout OFF

355

357

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

356

358

insert into t1 values (1);

357

359

commit;

406

408

tes 1234

407

409

drop table test;

408

410

drop table if exists t1;

411

show variables like 'innodb_rollback_on_timeout';

412

Variable_name Value

413

innodb_rollback_on_timeout OFF

409

414

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

410

415

insert into t1 values (1);

411

416

commit;

479

484

4 a 2

480

485

drop table t1;

481

486

create table t1(a int) engine=innodb;

482

alter table t1 comment='123';

487

alter table t1 comment '123';

483

488

show create table t1;

484

489

Table Create Table

485

490

t1 CREATE TABLE `t1` (

486

`a` INT DEFAULT NULL

487

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

491

`a` int DEFAULT NULL

492

) ENGINE=InnoDB COMMENT='123'

488

493

drop table t1;

489

494

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

490

495

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

590

595

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

591

596

BEGIN;

592

597

INSERT INTO t2 values(100,100);

593

COMMIT;

594

598

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

595

599

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

596

600

SELECT * from t2;

635

639

drop table t1,t2;

636

640

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

637

641

Warnings:

638

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

642

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

639

643

insert into t1 values('aaa');

640

644

drop table t1;

641

645

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

655

659

1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort

656

660

DROP TABLE t1;

657

661

drop table if exists t1;

662

show variables like 'innodb_rollback_on_timeout';

663

Variable_name Value

664

innodb_rollback_on_timeout OFF

658

665

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

659

666

insert into t1 values (1);

660

667

commit;

770

777

id 1

771

778

select_type SIMPLE

772

779

table t1

773

type index

780

type range

774

781

possible_keys bkey

775

782

key bkey

776

783

key_len 5

777

784

ref NULL

778

rows 32

785

rows 16

779

786

Extra Using where; Using index

780

787

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

781

788

a b

854

861

key bkey

855

862

key_len 10

856

863

ref const,const

857

rows 16

864

rows 8

858

865

Extra Using where; Using index

859

866

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

860

867

a b c

883

890

key bkey

884

891

key_len 10

885

892

ref const,const

886

rows 16

893

rows 8

887

894

Extra Using where; Using index

888

895

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

889

896

a b c

912

919

key bkey

913

920

key_len 10

914

921

ref const,const

915

rows 16

922

rows 8

916

923

Extra Using where; Using index

917

924

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

918

925

a b c

937

944

insert into t1 values('aaa');

938

945

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

939

946

Warnings:

940

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

947

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

941

948

show create table t1;

942

949

Table Create Table

943

950

t1 CREATE TABLE `t1` (

944

`a` TEXT COLLATE utf8_general_ci,

945

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

946

) ENGINE=InnoDB COLLATE = utf8_general_ci

951

`a` text,

952

KEY `a` (`a`(191))

953

) ENGINE=InnoDB

947

954

drop table t1;

948

955

CREATE TABLE t1 (

949

956

a INT,

1023

1030

key b

1024

1031

key_len 5

1025

1032

ref const

1026

rows 2

1033

rows 1

1027

1034

Extra Using where; Using index

1028

1035

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

1029

1036

a b

1038

1045

key b

1039

1046

key_len 5

1040

1047

ref const

1041

rows 2

1048

rows 1

1042

1049

Extra Using where; Using index

1043

1050

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

1044

1051

a b

1147

1154

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

1148

1155

desc t1;

1149

1156

Field Type Null Default Default_is_NULL On_Update

1150

a VARCHAR NO NO

1151

b VARCHAR NO NO

1157

a VARCHAR FALSE FALSE

1158

b VARCHAR FALSE FALSE

1152

1159

show create table t1;

1153

1160

Table Create Table

1154

1161

t1 CREATE TABLE `t1` (

1155

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

1156

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

1162

`a` varchar(10) NOT NULL,

1163

`b` varchar(4) NOT NULL,

1157

1164

UNIQUE KEY `bb` (`b`),

1158

1165

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

1159

) ENGINE=InnoDB COLLATE = utf8_general_ci

1166

) ENGINE=InnoDB

1160

1167

drop table t1;

1161

1168

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

1162

1169

INSERT INTO t1 VALUES

1173

1180

set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;

1174

1181

set global innodb_autoextend_increment=8;

1175

1182

set global innodb_autoextend_increment=@my_innodb_autoextend_increment;

1183

set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;

1184

set global innodb_commit_concurrency=0;

1185

set global innodb_commit_concurrency=@my_innodb_commit_concurrency;

1176

1186

End of 5.0 tests

1177

1187

CREATE TABLE `t2` (

1178

1188

`k` int NOT NULL auto_increment,

1273

1283

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

1274

1284

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

1275

1285

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

1286

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

1277

1287

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

1278

1288

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

1279

1289

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

1296

1306

SHOW CREATE TABLE t2;

1297

1307

Table Create Table

1298

1308

t2 CREATE TABLE `t2` (

1299

`c` INT NOT NULL,

1300

`d` INT NOT NULL,

1309

`c` int NOT NULL,

1310

`d` int NOT NULL,

1301

1311

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

1302

1312

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

1303

1313

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

1314

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

1315

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

1316

) ENGINE=InnoDB

1307

1317

DROP TABLE t2;

1308

1318

DROP TABLE t1;

1309

1319

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

1348

1358

1349

1359

DROP TABLE t2;

1350

1360

DROP TABLE t1;

1351

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1352

1361

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 »