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

483

482

show create table t1;

484

483

Table Create Table

485

484

t1 CREATE TABLE `t1` (

486

`a` INT DEFAULT NULL

487

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

485

`a` int DEFAULT NULL

486

) ENGINE=InnoDB COMMENT='123'

488

487

drop table t1;

489

488

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

490

489

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

590

589

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

591

590

BEGIN;

592

591

INSERT INTO t2 values(100,100);

593

COMMIT;

594

592

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

595

593

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

596

594

SELECT * from t2;

770

768

id 1

771

769

select_type SIMPLE

772

770

table t1

773

type index

771

type range

774

772

possible_keys bkey

775

773

key bkey

776

774

key_len 5

777

775

ref NULL

778

rows 32

776

rows 16

779

777

Extra Using where; Using index

780

778

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

781

779

a b

854

852

key bkey

855

853

key_len 10

856

854

ref const,const

857

rows 16

855

rows 8

858

856

Extra Using where; Using index

859

857

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

860

858

a b c

883

881

key bkey

884

882

key_len 10

885

883

ref const,const

886

rows 16

884

rows 8

887

885

Extra Using where; Using index

888

886

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

889

887

a b c

912

910

key bkey

913

911

key_len 10

914

912

ref const,const

915

rows 16

913

rows 8

916

914

Extra Using where; Using index

917

915

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

918

916

a b c

941

939

show create table t1;

942

940

Table Create Table

943

941

t1 CREATE TABLE `t1` (

944

`a` TEXT COLLATE utf8_general_ci,

942

`a` text,

945

943

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

946

) ENGINE=InnoDB COLLATE = utf8_general_ci

944

) ENGINE=InnoDB

947

945

drop table t1;

948

946

CREATE TABLE t1 (

949

947

a INT,

1023

1021

key b

1024

1022

key_len 5

1025

1023

ref const

1026

rows 2

1024

rows 1

1027

1025

Extra Using where; Using index

1028

1026

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

1029

1027

a b

1038

1036

key b

1039

1037

key_len 5

1040

1038

ref const

1041

rows 2

1039

rows 1

1042

1040

Extra Using where; Using index

1043

1041

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

1044

1042

a b

1128

1126

FROM data_dictionary.TABLES

1129

1127

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

1130

1128

table_schema table_name row_format

1131

test t1 COMPACT

1129

test t1 DEFAULT

1132

1130

1133

1131

# - change ROW_FORMAT and check;

1134

1132

1138

1136

FROM data_dictionary.TABLES

1139

1137

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

1140

1138

table_schema table_name row_format

1141

test t1 REDUNDANT

1139

test t1 DEFAULT

1142

1140

1143

1141

# - that's it, cleanup.

1144

1142

1147

1145

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

1148

1146

desc t1;

1149

1147

Field Type Null Default Default_is_NULL On_Update

1150

a VARCHAR NO NO

1151

b VARCHAR NO NO

1148

a VARCHAR FALSE FALSE

1149

b VARCHAR FALSE FALSE

1152

1150

show create table t1;

1153

1151

Table Create Table

1154

1152

t1 CREATE TABLE `t1` (

1155

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

1156

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

1153

`a` varchar(10) NOT NULL,

1154

`b` varchar(4) NOT NULL,

1157

1155

UNIQUE KEY `bb` (`b`),

1158

1156

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

1159

) ENGINE=InnoDB COLLATE = utf8_general_ci

1157

) ENGINE=InnoDB

1160

1158

drop table t1;

1161

1159

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

1162

1160

INSERT INTO t1 VALUES

1173

1171

set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;

1174

1172

set global innodb_autoextend_increment=8;

1175

1173

set global innodb_autoextend_increment=@my_innodb_autoextend_increment;

1174

set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;

1175

set global innodb_commit_concurrency=0;

1176

set global innodb_commit_concurrency=@my_innodb_commit_concurrency;

1176

1177

End of 5.0 tests

1177

1178

CREATE TABLE `t2` (

1178

1179

`k` int NOT NULL auto_increment,

1273

1274

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

1274

1275

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

1275

1276

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

1277

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

1277

1278

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

1278

1279

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

1279

1280

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

1296

1297

SHOW CREATE TABLE t2;

1297

1298

Table Create Table

1298

1299

t2 CREATE TABLE `t2` (

1299

`c` INT NOT NULL,

1300

`d` INT NOT NULL,

1300

`c` int NOT NULL,

1301

`d` int NOT NULL,

1301

1302

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

1302

1303

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

1303

1304

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

1305

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

1306

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

1307

) ENGINE=InnoDB

1307

1308

DROP TABLE t2;

1308

1309

DROP TABLE t1;

1309

1310

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

1348

1349

1350

DROP TABLE t2;

1350

1351

DROP TABLE t1;

1351

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1352

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 »