diff options
Diffstat (limited to 'mysql-test/suite')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_check_constraint.result | 121 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/t/is_check_constraint.test | 92 | ||||
| -rw-r--r-- | mysql-test/suite/galera_3nodes/t/GAL-501.opt | 1 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/instant_varchar_enlarge.result | 9 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/instant_varchar_enlarge.test | 8 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/foreign.result | 9 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/foreign.test | 14 |
7 files changed, 254 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_check_constraint.result b/mysql-test/suite/funcs_1/r/is_check_constraint.result new file mode 100644 index 00000000000..be44a8867e8 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/is_check_constraint.result @@ -0,0 +1,121 @@ +# +# MDEV-17323: Backport INFORMATION_SCHEMA.CHECK_CONSTRAINTS to 10.2 +# +CREATE user boo1; +GRANT select,create,alter,drop on foo.* to boo1; +SHOW GRANTS for boo1; +Grants for boo1@% +GRANT USAGE ON *.* TO 'boo1'@'%' +GRANT SELECT, CREATE, DROP, ALTER ON `foo`.* TO 'boo1'@'%' +CREATE user boo2; +create database foo; +CONNECT con1,localhost, boo1,, foo; +SET check_constraint_checks=1; +CREATE TABLE t0 +( +t int, check (t>32) # table constraint +) ENGINE=myisam; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CONSTRAINT_1 t0 `t` > 32 +ALTER TABLE t0 +ADD CONSTRAINT CHK_t0_t CHECK(t<100); +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CHK_t0_t t0 `t` < 100 +def foo CONSTRAINT_1 t0 `t` > 32 +ALTER TABLE t0 +DROP CONSTRAINT CHK_t0_t; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CONSTRAINT_1 t0 `t` > 32 +ALTER TABLE t0 +ADD CONSTRAINT CHECK(t<50); +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint +tt int, +CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints +CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint +) ENGINE=InnoDB; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CHK_tt t1 `tt` < 100 +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_1 t1 `tt` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +def foo CONSTRAINT_2 t1 `tt` < 50 +def foo t t1 `t` > 2 +ALTER TABLE t1 +DROP CONSTRAINT CHK_tt; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_1 t1 `tt` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +def foo CONSTRAINT_2 t1 `tt` < 50 +def foo t t1 `t` > 2 +CREATE TABLE t2 +( +name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint +start_date DATE, +end_date DATE, +CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint +)ENGINE=Innodb; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CHK_dates t2 `start_date` is null +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_1 t1 `tt` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +def foo CONSTRAINT_2 t1 `tt` < 50 +def foo name t2 char_length(`name`) > 2 +def foo t t1 `t` > 2 +ALTER TABLE t1 +ADD CONSTRAINT CHK_new_ CHECK(t>tt); +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CHK_dates t2 `start_date` is null +def foo CHK_new_ t1 `t` > `tt` +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_1 t1 `tt` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +def foo CONSTRAINT_2 t1 `tt` < 50 +def foo name t2 char_length(`name`) > 2 +def foo t t1 `t` > 2 +CREATE TABLE t3 +( +a int, +b int check (b>0), # field constraint named 'b' +CONSTRAINT b check (b>10) # table constraint +) ENGINE=InnoDB; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def foo CHK_dates t2 `start_date` is null +def foo CHK_new_ t1 `t` > `tt` +def foo CONSTRAINT_1 t0 `t` > 32 +def foo CONSTRAINT_1 t1 `tt` > 32 +def foo CONSTRAINT_2 t0 `t` < 50 +def foo CONSTRAINT_2 t1 `tt` < 50 +def foo b t3 `b` > 0 +def foo b t3 `b` > 10 +def foo name t2 char_length(`name`) > 2 +def foo t t1 `t` > 2 +disconnect con1; +CONNECT con2, localhost, boo2,, test; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +disconnect con2; +CONNECT con1, localhost, boo1,,foo; +DROP TABLE t0; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP DATABASE foo; +disconnect con1; +connection default; +DROP USER boo1; +DROP USER boo2; diff --git a/mysql-test/suite/funcs_1/t/is_check_constraint.test b/mysql-test/suite/funcs_1/t/is_check_constraint.test new file mode 100644 index 00000000000..30a72d02b34 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_check_constraint.test @@ -0,0 +1,92 @@ +--source include/have_innodb.inc +--source include/not_embedded.inc +--echo # +--echo # MDEV-17323: Backport INFORMATION_SCHEMA.CHECK_CONSTRAINTS to 10.2 +--echo # +CREATE user boo1; +GRANT select,create,alter,drop on foo.* to boo1; +SHOW GRANTS for boo1; +CREATE user boo2; +create database foo; +# Connect with user boo1 +CONNECT(con1,localhost, boo1,, foo); + +SET check_constraint_checks=1; +CREATE TABLE t0 +( + t int, check (t>32) # table constraint +) ENGINE=myisam; +--sorted_result +SELECT * from information_schema.check_constraints; + +ALTER TABLE t0 +ADD CONSTRAINT CHK_t0_t CHECK(t<100); +--sorted_result +SELECT * from information_schema.check_constraints; + +ALTER TABLE t0 +DROP CONSTRAINT CHK_t0_t; +--sorted_result +SELECT * from information_schema.check_constraints; + +ALTER TABLE t0 +ADD CONSTRAINT CHECK(t<50); +--sorted_result +SELECT * from information_schema.check_constraints; + +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint + tt int, + CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints + CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint +) ENGINE=InnoDB; + --sorted_result +SELECT * from information_schema.check_constraints; + +ALTER TABLE t1 +DROP CONSTRAINT CHK_tt; +--sorted_result +SELECT * from information_schema.check_constraints; + +CREATE TABLE t2 +( +name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint +start_date DATE, +end_date DATE, +CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint +)ENGINE=Innodb; + --sorted_result +SELECT * from information_schema.check_constraints; + +ALTER TABLE t1 +ADD CONSTRAINT CHK_new_ CHECK(t>tt); +--sorted_result +SELECT * from information_schema.check_constraints; + +# Create table with same field and table check constraint name +CREATE TABLE t3 +( +a int, +b int check (b>0), # field constraint named 'b' +CONSTRAINT b check (b>10) # table constraint +) ENGINE=InnoDB; + --sorted_result +SELECT * from information_schema.check_constraints; + +DISCONNECT con1; +CONNECT(con2, localhost, boo2,, test); + --sorted_result +SELECT * from information_schema.check_constraints; + +DISCONNECT con2; +CONNECT(con1, localhost, boo1,,foo); +DROP TABLE t0; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP DATABASE foo; + +DISCONNECT con1; +--CONNECTION default +DROP USER boo1; +DROP USER boo2; diff --git a/mysql-test/suite/galera_3nodes/t/GAL-501.opt b/mysql-test/suite/galera_3nodes/t/GAL-501.opt new file mode 100644 index 00000000000..c2bb4d156af --- /dev/null +++ b/mysql-test/suite/galera_3nodes/t/GAL-501.opt @@ -0,0 +1 @@ +--bind-address=:: diff --git a/mysql-test/suite/innodb/r/instant_varchar_enlarge.result b/mysql-test/suite/innodb/r/instant_varchar_enlarge.result new file mode 100644 index 00000000000..14f16bd4fe2 --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_varchar_enlarge.result @@ -0,0 +1,9 @@ +create table t (a varchar(100)) engine=innodb; +select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +name pos mtype prtype len +a 0 1 524303 100 +alter table t modify a varchar(110), algorithm=inplace; +select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +name pos mtype prtype len +a 0 1 524303 110 +drop table t; diff --git a/mysql-test/suite/innodb/t/instant_varchar_enlarge.test b/mysql-test/suite/innodb/t/instant_varchar_enlarge.test new file mode 100644 index 00000000000..42689deca11 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_varchar_enlarge.test @@ -0,0 +1,8 @@ +--source include/have_innodb.inc + +# LEN must increase here +create table t (a varchar(100)) engine=innodb; +select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +alter table t modify a varchar(110), algorithm=inplace; +select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +drop table t; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 8169ddeaa92..a5dbd3fca36 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -292,3 +292,12 @@ select count(*) from subchild; count(*) 0 drop table subchild, child, parent; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=InnoDB; +CREATE TABLE t2 (f2 INT, FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=InnoDB WITH SYSTEM VERSIONING; +SET FOREIGN_KEY_CHECKS= OFF; +INSERT IGNORE INTO t2 VALUES (1); +SET FOREIGN_KEY_CHECKS= ON; +UPDATE t2 SET f2= 2; +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) +DELETE FROM t2; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 566d481c2a8..075f15a216c 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -317,4 +317,18 @@ select count(*) from subchild; drop table subchild, child, parent; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=InnoDB; +CREATE TABLE t2 (f2 INT, FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=InnoDB WITH SYSTEM VERSIONING; + +SET FOREIGN_KEY_CHECKS= OFF; +INSERT IGNORE INTO t2 VALUES (1); + +SET FOREIGN_KEY_CHECKS= ON; +--error ER_NO_REFERENCED_ROW_2 +UPDATE t2 SET f2= 2; +DELETE FROM t2; + +DROP TABLE t2, t1; + + --source suite/versioning/common_finish.inc |