summaryrefslogtreecommitdiff
path: root/mysql-test/suite
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite')
-rw-r--r--mysql-test/suite/innodb/r/innodb-fkcheck.result8
-rw-r--r--mysql-test/suite/innodb/r/innodb.result12
-rw-r--r--mysql-test/suite/innodb/r/instant_alter_crash.result16
-rw-r--r--mysql-test/suite/innodb/r/truncate_foreign.result10
-rw-r--r--mysql-test/suite/innodb/t/innodb-fkcheck.test13
-rw-r--r--mysql-test/suite/innodb/t/innodb.test12
-rw-r--r--mysql-test/suite/innodb/t/instant_alter_crash.test19
-rw-r--r--mysql-test/suite/innodb/t/truncate_foreign.test11
-rw-r--r--mysql-test/suite/innodb_fts/r/misc_debug.result1
-rw-r--r--mysql-test/suite/innodb_fts/t/misc_debug.test1
10 files changed, 89 insertions, 14 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result
index 2c2be83a3ff..f86ba50597f 100644
--- a/mysql-test/suite/innodb/r/innodb-fkcheck.result
+++ b/mysql-test/suite/innodb/r/innodb-fkcheck.result
@@ -33,11 +33,19 @@ b bigint unsigned NOT NULL,
d1 date NOT NULL,
PRIMARY KEY (b,d1)
) ENGINE=InnoDB;
+DROP TABLE b;
+set foreign_key_checks = 1;
+CREATE TABLE b (
+b bigint unsigned NOT NULL,
+d1 date NOT NULL,
+PRIMARY KEY (b,d1)
+) ENGINE=InnoDB;
ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `b`
+set foreign_key_checks = 0;
DROP TABLE IF EXISTS d;
Warnings:
Note 1051 Unknown table 'bug_fk.d'
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index edbdcd2b28e..47e5f0f5c94 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -2531,9 +2531,19 @@ disconnect b;
set foreign_key_checks=0;
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
-ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
set foreign_key_checks=1;
+insert into t2 values (1,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
+set foreign_key_checks=0;
+drop table t1;
+set foreign_key_checks=1;
+insert into t2 values (1,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
drop table t2;
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+drop table t1;
set foreign_key_checks=0;
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
diff --git a/mysql-test/suite/innodb/r/instant_alter_crash.result b/mysql-test/suite/innodb/r/instant_alter_crash.result
index 843f33fddd1..f0fedcc7673 100644
--- a/mysql-test/suite/innodb/r/instant_alter_crash.result
+++ b/mysql-test/suite/innodb/r/instant_alter_crash.result
@@ -34,13 +34,15 @@ ROLLBACK;
InnoDB 0 transactions not purged
INSERT INTO t2 VALUES
(16,1551,'Omnium enim rerum'),(128,1571,' principia parva sunt');
+BEGIN;
+UPDATE t1 SET c2=c2+1;
connect ddl, localhost, root;
SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere';
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-UPDATE t1 SET c2=c2+1;
+COMMIT;
# Kill the server
disconnect ddl;
# restart
@@ -61,6 +63,8 @@ DELETE FROM t2;
ROLLBACK;
InnoDB 0 transactions not purged
INSERT INTO t2 VALUES (64,42,'De finibus bonorum'), (347,33101,' et malorum');
+BEGIN;
+DELETE FROM t1;
connect ddl, localhost, root;
ALTER TABLE t2 DROP COLUMN c3;
SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
@@ -68,7 +72,7 @@ ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum');
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
# Kill the server
disconnect ddl;
# restart
@@ -138,6 +142,8 @@ InnoDB 0 transactions not purged
#
# MDEV-24323 Crash on recovery after kill during instant ADD COLUMN
#
+BEGIN;
+INSERT INTO t1 VALUES(0,0);
connect ddl, localhost, root;
CREATE TABLE t3(id INT PRIMARY KEY, c2 INT, v2 INT AS(c2) VIRTUAL, UNIQUE(v2))
ENGINE=InnoDB;
@@ -147,7 +153,7 @@ ALTER TABLE t3 ADD COLUMN c3 TEXT NOT NULL DEFAULT 'sic transit gloria mundi';
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-INSERT INTO t1 VALUES(0,0);
+COMMIT;
# Kill the server
disconnect ddl;
# restart
@@ -183,13 +189,15 @@ DROP TABLE t2,t3;
#
CREATE TABLE t2(a INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+BEGIN;
+DELETE FROM t1;
connect ddl, localhost, root;
SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
ALTER TABLE t2 ADD COLUMN b TINYINT UNSIGNED NOT NULL DEFAULT 42 FIRST;
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
# Kill the server
disconnect ddl;
# restart
diff --git a/mysql-test/suite/innodb/r/truncate_foreign.result b/mysql-test/suite/innodb/r/truncate_foreign.result
index 3154674aabf..e587baa5288 100644
--- a/mysql-test/suite/innodb/r/truncate_foreign.result
+++ b/mysql-test/suite/innodb/r/truncate_foreign.result
@@ -80,9 +80,19 @@ SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a), ALGORITHM=COPY;
INSERT INTO t1 VALUES (1,1);
LOCK TABLES t1 WRITE;
+SET FOREIGN_KEY_CHECKS=1;
TRUNCATE t1;
ERROR HY000: Cannot add foreign key constraint for `t1`
INSERT INTO t1 VALUES (2,2);
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+SELECT * FROM t1;
+pk a
+1 1
+UNLOCK TABLES;
+INSERT INTO t1 VALUES (2,2);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
+SET FOREIGN_KEY_CHECKS=0;
+INSERT INTO t1 VALUES (2,2);
SELECT * FROM t1;
pk a
1 1
diff --git a/mysql-test/suite/innodb/t/innodb-fkcheck.test b/mysql-test/suite/innodb/t/innodb-fkcheck.test
index 4657edc4d65..5ff3533fce1 100644
--- a/mysql-test/suite/innodb/t/innodb-fkcheck.test
+++ b/mysql-test/suite/innodb/t/innodb-fkcheck.test
@@ -46,7 +46,15 @@ show create table c;
#
# Note that column b has different type in parent table
#
---error 1005
+CREATE TABLE b (
+ b bigint unsigned NOT NULL,
+ d1 date NOT NULL,
+ PRIMARY KEY (b,d1)
+) ENGINE=InnoDB;
+DROP TABLE b;
+
+set foreign_key_checks = 1;
+--error ER_CANT_CREATE_TABLE
CREATE TABLE b (
b bigint unsigned NOT NULL,
d1 date NOT NULL,
@@ -54,6 +62,7 @@ CREATE TABLE b (
) ENGINE=InnoDB;
show warnings;
+set foreign_key_checks = 0;
DROP TABLE IF EXISTS d;
@@ -64,7 +73,7 @@ CREATE TABLE d (
CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
) ENGINE=InnoDB;
-show warnings;
+show warnings;
set foreign_key_checks = 1;
diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test
index 3a8c12dfbbd..3b3b2770df7 100644
--- a/mysql-test/suite/innodb/t/innodb.test
+++ b/mysql-test/suite/innodb/t/innodb.test
@@ -1598,12 +1598,22 @@ disconnect b;
set foreign_key_checks=0;
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+set foreign_key_checks=1;
+--error ER_NO_REFERENCED_ROW_2
+insert into t2 values (1,1);
+set foreign_key_checks=0;
+drop table t1;
+set foreign_key_checks=1;
+--error ER_NO_REFERENCED_ROW_2
+insert into t2 values (1,1);
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
--error ER_CANT_CREATE_TABLE
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
-set foreign_key_checks=1;
drop table t2;
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+drop table t1;
# test that FKs between different charsets are not accepted in CREATE even
# when f_k_c is 0
diff --git a/mysql-test/suite/innodb/t/instant_alter_crash.test b/mysql-test/suite/innodb/t/instant_alter_crash.test
index 4d211ece106..0bd983a2b4c 100644
--- a/mysql-test/suite/innodb/t/instant_alter_crash.test
+++ b/mysql-test/suite/innodb/t/instant_alter_crash.test
@@ -47,6 +47,9 @@ ROLLBACK;
INSERT INTO t2 VALUES
(16,1551,'Omnium enim rerum'),(128,1571,' principia parva sunt');
+BEGIN;
+UPDATE t1 SET c2=c2+1;
+
connect ddl, localhost, root;
SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
--send
@@ -55,7 +58,7 @@ ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere';
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-UPDATE t1 SET c2=c2+1;
+COMMIT;
--source include/kill_mysqld.inc
disconnect ddl;
@@ -73,6 +76,8 @@ ROLLBACK;
--source include/wait_all_purged.inc
INSERT INTO t2 VALUES (64,42,'De finibus bonorum'), (347,33101,' et malorum');
+BEGIN;
+DELETE FROM t1;
connect ddl, localhost, root;
ALTER TABLE t2 DROP COLUMN c3;
@@ -83,7 +88,7 @@ ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum');
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
--source include/kill_mysqld.inc
disconnect ddl;
@@ -177,6 +182,9 @@ DELETE FROM t2;
--echo #
--echo # MDEV-24323 Crash on recovery after kill during instant ADD COLUMN
--echo #
+BEGIN;
+INSERT INTO t1 VALUES(0,0);
+
connect ddl, localhost, root;
CREATE TABLE t3(id INT PRIMARY KEY, c2 INT, v2 INT AS(c2) VIRTUAL, UNIQUE(v2))
ENGINE=InnoDB;
@@ -189,7 +197,7 @@ ALTER TABLE t3 ADD COLUMN c3 TEXT NOT NULL DEFAULT 'sic transit gloria mundi';
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-INSERT INTO t1 VALUES(0,0);
+COMMIT;
--source include/kill_mysqld.inc
disconnect ddl;
@@ -207,6 +215,9 @@ DROP TABLE t2,t3;
CREATE TABLE t2(a INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+BEGIN;
+DELETE FROM t1;
+
connect ddl, localhost, root;
SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
--send
@@ -215,7 +226,7 @@ ALTER TABLE t2 ADD COLUMN b TINYINT UNSIGNED NOT NULL DEFAULT 42 FIRST;
connection default;
SET DEBUG_SYNC='now WAIT_FOR ddl';
SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
--source include/kill_mysqld.inc
disconnect ddl;
diff --git a/mysql-test/suite/innodb/t/truncate_foreign.test b/mysql-test/suite/innodb/t/truncate_foreign.test
index e40029e18be..abbe1b3df87 100644
--- a/mysql-test/suite/innodb/t/truncate_foreign.test
+++ b/mysql-test/suite/innodb/t/truncate_foreign.test
@@ -92,8 +92,19 @@ SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a), ALGORITHM=COPY;
INSERT INTO t1 VALUES (1,1);
LOCK TABLES t1 WRITE;
+SET FOREIGN_KEY_CHECKS=1;
--error ER_CANNOT_ADD_FOREIGN
TRUNCATE t1;
+# Whether TRUNCATE succeeds or fails, it will reload FOREIGN KEY constraints.
+# As a result, ha_innobase::referenced_by_foreign_key() will retun TRUE
+# (for the self-referential key), and the statement will fail.
+--error ER_TABLE_NOT_LOCKED
+INSERT INTO t1 VALUES (2,2);
+SELECT * FROM t1;
+UNLOCK TABLES;
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t1 VALUES (2,2);
+SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (2,2);
SELECT * FROM t1;
DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_fts/r/misc_debug.result b/mysql-test/suite/innodb_fts/r/misc_debug.result
index 18f715b9d95..11df7d89f0b 100644
--- a/mysql-test/suite/innodb_fts/r/misc_debug.result
+++ b/mysql-test/suite/innodb_fts/r/misc_debug.result
@@ -62,7 +62,6 @@ SET @saved_debug_dbug= @@debug_dbug;
CREATE TABLE t1 (b CHAR(12), FULLTEXT KEY(b)) engine=InnoDB;
SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
TRUNCATE t1;
-ERROR HY000: Got error -1 "Internal error < 0 (Not system error)" from storage engine InnoDB
SET debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
# End of 10.3 tests
diff --git a/mysql-test/suite/innodb_fts/t/misc_debug.test b/mysql-test/suite/innodb_fts/t/misc_debug.test
index c086348b631..9246d27a704 100644
--- a/mysql-test/suite/innodb_fts/t/misc_debug.test
+++ b/mysql-test/suite/innodb_fts/t/misc_debug.test
@@ -91,7 +91,6 @@ SET @saved_debug_dbug= @@debug_dbug;
CREATE TABLE t1 (b CHAR(12), FULLTEXT KEY(b)) engine=InnoDB;
SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
---error ER_GET_ERRNO
TRUNCATE t1;
SET debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
View Lorry Controller Status