~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

CREATE TABLE t2 (a INT) ENGINE=InnoDB;

533

switch to connection c1

534

SET AUTOCOMMIT=0;

535

INSERT INTO t2 VALUES (1);

536

switch to connection c2

537

SET AUTOCOMMIT=0;

538

LOCK TABLES t1 READ, t2 READ;

539

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

540

switch to connection c1

541

COMMIT;

542

INSERT INTO t1 VALUES (1);

543

switch to connection default

544

SET AUTOCOMMIT=default;

545

DROP TABLE t1,t2;

525

546

CREATE TABLE t1 (

526

547

id int NOT NULL auto_increment PRIMARY KEY,

527

548

b int NOT NULL,

532

553

CREATE TABLE t2 (

533

554

b int NOT NULL auto_increment PRIMARY KEY,

534

555

c datetime NOT NULL

535

) ENGINE= InnoDB;

556

) ENGINE= MyISAM;

536

557

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

537

558

INSERT INTO t2(c) SELECT c FROM t2;

538

559

INSERT INTO t2(c) SELECT c FROM t2;

551

572

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

552

573

set @@sort_buffer_size=8192;

553

574

Warnings:

554

Error 1292 Truncated incorrect sort_buffer_size value: '8192'

575

Warning 1292 Truncated incorrect sort_buffer_size value: '8192'

555

576

SELECT COUNT(*) FROM t1;

556

577

COUNT(*)

557

578

3072

590

611

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

591

612

BEGIN;

592

613

INSERT INTO t2 values(100,100);

593

COMMIT;

594

614

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

595

615

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

596

616

SELECT * from t2;

635

655

drop table t1,t2;

636

656

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

637

657

Warnings:

638

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

658

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

639

659

insert into t1 values('aaa');

640

660

drop table t1;

641

661

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

655

675

1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort

656

676

DROP TABLE t1;

657

677

drop table if exists t1;

678

show variables like 'innodb_rollback_on_timeout';

679

Variable_name Value

680

innodb_rollback_on_timeout OFF

658

681

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

659

682

insert into t1 values (1);

660

683

commit;

688

711

689

712

690

713

drop table t1;

714

drop table if exists t1;

715

create table t1 (a int) engine=innodb;

716

alter table t1 alter a set default 1;

717

drop table t1;

718

719

Bug#24918 drop table and lock / inconsistent between

720

perm and temp tables

721

722

Check transactional tables under LOCK TABLES

723

724

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

725

t24918_access;

726

create table t24918_access (id int);

727

create table t24918 (id int) engine=myisam;

728

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

729

create table t24918_trans (id int) engine=innodb;

730

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

731

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

732

drop table t24918;

733

select * from t24918_access;

734

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

735

drop table t24918_trans;

736

select * from t24918_access;

737

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

738

drop table t24918_trans_tmp;

739

select * from t24918_access;

740

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

741

drop table t24918_tmp;

742

select * from t24918_access;

743

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

744

unlock tables;

745

drop table t24918_access;

691

746

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

692

747

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

693

748

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

770

825

id 1

771

826

select_type SIMPLE

772

827

table t1

773

type index

828

type range

774

829

possible_keys bkey

775

830

key bkey

776

831

key_len 5

777

832

ref NULL

778

rows 32

833

rows 16

779

834

Extra Using where; Using index

780

835

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

781

836

a b

854

909

key bkey

855

910

key_len 10

856

911

ref const,const

857

rows 16

912

rows 8

858

913

Extra Using where; Using index

859

914

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

860

915

a b c

883

938

key bkey

884

939

key_len 10

885

940

ref const,const

886

rows 16

941

rows 8

887

942

Extra Using where; Using index

888

943

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

889

944

a b c

912

967

key bkey

913

968

key_len 10

914

969

ref const,const

915

rows 16

970

rows 8

916

971

Extra Using where; Using index

917

972

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

918

973

a b c

937

992

insert into t1 values('aaa');

938

993

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

939

994

Warnings:

940

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

995

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

996

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

997

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

941

998

show create table t1;

942

999

Table Create Table

943

1000

t1 CREATE TABLE `t1` (

944

`a` TEXT COLLATE utf8_general_ci,

945

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

946

) ENGINE=InnoDB COLLATE = utf8_general_ci

1001

`a` text,

1002

KEY `a` (`a`())

1003

) ENGINE=InnoDB

947

1004

drop table t1;

948

1005

CREATE TABLE t1 (

949

1006

a INT,

1023

1080

key b

1024

1081

key_len 5

1025

1082

ref const

1026

rows 2

1083

rows 1

1027

1084

Extra Using where; Using index

1028

1085

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

1029

1086

a b

1038

1095

key b

1039

1096

key_len 5

1040

1097

ref const

1041

rows 2

1098

rows 1

1042

1099

Extra Using where; Using index

1043

1100

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

1044

1101

a b

1125

1182

# - initial check;

1126

1183

1127

1184

SELECT table_schema, table_name, row_format

1128

FROM data_dictionary.TABLES

1185

FROM INFORMATION_SCHEMA.TABLES

1129

1186

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

1130

1187

table_schema table_name row_format

1131

test t1 COMPACT

1188

test t1 Compact

1132

1189

1133

1190

# - change ROW_FORMAT and check;

1134

1191

1135

1192

ALTER TABLE t1 ROW_FORMAT = REDUNDANT;

1136

1193

1137

1194

SELECT table_schema, table_name, row_format

1138

FROM data_dictionary.TABLES

1195

FROM INFORMATION_SCHEMA.TABLES

1139

1196

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

1140

1197

table_schema table_name row_format

1141

test t1 REDUNDANT

1198

test t1 Redundant

1142

1199

1143

1200

# - that's it, cleanup.

1144

1201

1146

1203

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

1147

1204

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

1148

1205

desc t1;

1149

Field Type Null Default Default_is_NULL On_Update

1150

a VARCHAR NO NO

1151

b VARCHAR NO NO

1206

Field Type Null Key Default Extra

1207

a varchar(10) NO UNI NULL

1208

b varchar(4) NO PRI NULL

1152

1209

show create table t1;

1153

1210

Table Create Table

1154

1211

t1 CREATE TABLE `t1` (

1155

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

1156

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

1212

`a` varchar(10) NOT NULL,

1213

`b` varchar(4) NOT NULL,

1157

1214

UNIQUE KEY `bb` (`b`),

1158

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

1159

) ENGINE=InnoDB COLLATE = utf8_general_ci

1215

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

1216

) ENGINE=InnoDB

1160

1217

drop table t1;

1161

1218

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

1162

1219

INSERT INTO t1 VALUES

1173

1230

set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;

1174

1231

set global innodb_autoextend_increment=8;

1175

1232

set global innodb_autoextend_increment=@my_innodb_autoextend_increment;

1233

set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;

1234

set global innodb_commit_concurrency=0;

1235

set global innodb_commit_concurrency=@my_innodb_commit_concurrency;

1176

1236

End of 5.0 tests

1177

1237

CREATE TABLE `t2` (

1178

1238

`k` int NOT NULL auto_increment,

1242

1302

11 15 1

1243

1303

12 20 1

1244

1304

drop table t2;

1305

drop table if exists t1, t2;

1306

create table t1 (i int);

1307

alter table t1 modify i int default 1;

1308

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

1309

lock table t2 write;

1310

alter table t2 modify i int default 3;

1311

unlock tables;

1312

lock table t2 write;

1313

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

1314

unlock tables;

1315

drop table t1;

1316

drop table if exists t1;

1317

create table t1 (i int);

1318

insert into t1 values ();

1319

lock table t1 write;

1320

alter table t1 modify i int default 1;

1321

insert into t1 values ();

1322

select * from t1;

1323

1324

NULL

1325

1326

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

1327

insert into t1 values ();

1328

select * from t1;

1329

1330

NULL

1331

1332

Two

1333

unlock tables;

1334

select * from t1;

1335

1336

NULL

1337

1338

Two

1339

drop tables t1;

1245

1340

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

1246

1341

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

1247

1342

insert into t1(f1) values(1);

1273

1368

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

1274

1369

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

1275

1370

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

1371

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

1277

1372

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

1278

1373

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

1279

1374

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

1296

1391

SHOW CREATE TABLE t2;

1297

1392

Table Create Table

1298

1393

t2 CREATE TABLE `t2` (

1299

`c` INT NOT NULL,

1300

`d` INT NOT NULL,

1394

`c` int NOT NULL,

1395

`d` int NOT NULL,

1301

1396

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

1302

1397

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

1303

1398

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

1399

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

1400

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

1401

) ENGINE=InnoDB

1307

1402

DROP TABLE t2;

1308

1403

DROP TABLE t1;

1309

1404

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

1348

1443

1349

1444

DROP TABLE t2;

1350

1445

DROP TABLE t1;

1351

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1352

1446

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 »