diff options
| -rw-r--r-- | mysql-test/main/information_schema.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/information_schema_all_engines.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_check_constraints.result | 103 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_columns_is.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_columns_is_embedded.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_tables_is.result | 50 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_tables_is_embedded.result | 50 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/t/is_check_constraints.test | 113 | ||||
| -rw-r--r-- | sql/handler.h | 1 | ||||
| -rw-r--r-- | sql/sql_show.cc | 47 |
10 files changed, 393 insertions, 1 deletions
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 0e6b4168ea5..83f987b78db 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -51,6 +51,7 @@ c ALL_PLUGINS APPLICABLE_ROLES CHARACTER_SETS +CHECK_CONSTRAINTS CLIENT_STATISTICS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY @@ -938,6 +939,7 @@ connection user10261; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%'; TABLE_NAME COLUMN_NAME PRIVILEGES +CHECK_CONSTRAINTS TABLE_NAME select COLUMNS TABLE_NAME select COLUMN_PRIVILEGES TABLE_NAME select FILES TABLE_NAME select diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result index 8a92f0226ff..2916858b5a6 100644 --- a/mysql-test/main/information_schema_all_engines.result +++ b/mysql-test/main/information_schema_all_engines.result @@ -4,6 +4,7 @@ Tables_in_information_schema ALL_PLUGINS APPLICABLE_ROLES CHARACTER_SETS +CHECK_CONSTRAINTS CLIENT_STATISTICS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY @@ -83,6 +84,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME @@ -162,6 +164,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME @@ -247,6 +250,7 @@ table_name group_concat(t.table_schema, '.', t.table_name) num1 ALL_PLUGINS information_schema.ALL_PLUGINS 1 APPLICABLE_ROLES information_schema.APPLICABLE_ROLES 1 CHARACTER_SETS information_schema.CHARACTER_SETS 1 +CHECK_CONSTRAINTS information_schema.CHECK_CONSTRAINTS 1 CLIENT_STATISTICS information_schema.CLIENT_STATISTICS 1 COLLATIONS information_schema.COLLATIONS 1 COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1 @@ -315,6 +319,7 @@ Database: information_schema | ALL_PLUGINS | | APPLICABLE_ROLES | | CHARACTER_SETS | +| CHECK_CONSTRAINTS | | CLIENT_STATISTICS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | @@ -384,6 +389,7 @@ Database: INFORMATION_SCHEMA | ALL_PLUGINS | | APPLICABLE_ROLES | | CHARACTER_SETS | +| CHECK_CONSTRAINTS | | CLIENT_STATISTICS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | @@ -453,5 +459,5 @@ Wildcard: inf_rmation_schema | information_schema | SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 64 +information_schema 65 mysql 31 diff --git a/mysql-test/suite/funcs_1/r/is_check_constraints.result b/mysql-test/suite/funcs_1/r/is_check_constraints.result new file mode 100644 index 00000000000..13222ec3477 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result @@ -0,0 +1,103 @@ +# +# MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS +# +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 or replace database foo; +use foo; +connect con1,localhost, boo1,, foo; +drop table if exists foo.t0, foo.t1, foo.t2,foo.t3; +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 TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +ALTER TABLE t0 +ADD CONSTRAINT CHK_t0_t CHECK(t<100); +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CHK_t0_t `t` < 100 +def foo t0 CONSTRAINT_1 `t` > 32 +ALTER TABLE t0 +DROP CONSTRAINT CHK_t0_t; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint +tt int, CONSTRAINT CHK_tt CHECK(tt<100) # table constraint +) ENGINE=InnoDB; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +def foo t1 CHK_tt `tt` < 100 +def foo t1 t `t` > 2 +ALTER TABLE t1 +DROP CONSTRAINT CHK_tt; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +def foo t1 t `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 TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +def foo t1 t `t` > 2 +def foo t2 CHK_dates `start_date` is null +def foo t2 name char_length(`name`) > 2 +ALTER TABLE t1 +ADD CONSTRAINT CHK_new_ CHECK(t>tt); +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +def foo t1 CHK_new_ `t` > `tt` +def foo t1 t `t` > 2 +def foo t2 CHK_dates `start_date` is null +def foo t2 name char_length(`name`) > 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 TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE +def foo t0 CONSTRAINT_1 `t` > 32 +def foo t1 CHK_new_ `t` > `tt` +def foo t1 t `t` > 2 +def foo t2 CHK_dates `start_date` is null +def foo t2 name char_length(`name`) > 2 +def foo t3 b `b` > 0 +def foo t3 b `b` > 10 +disconnect con1; +connect(localhost,boo2,,foo,16000,/home/anel/workspace/server/mysql-test/var/tmp/mysqld.1.sock); +connect con2, localhost, boo2,, foo; +ERROR 42000: Access denied for user 'boo2'@'%' to database 'foo' +connect con2, localhost, boo2,, test; +SELECT * from information_schema.check_constraints; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_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/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index d76efef3e08..808836ab119 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -24,6 +24,11 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) select NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) select NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS TABLE_NAME 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema CLIENT_STATISTICS ACCESS_DENIED 22 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL def information_schema CLIENT_STATISTICS BINLOG_BYTES_WRITTEN 9 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL def information_schema CLIENT_STATISTICS BUSY_TIME 5 0 NO double NULL NULL 21 NULL NULL NULL NULL double select NEVER NULL @@ -557,6 +562,11 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) 3.0000 information_schema CHARACTER_SETS DESCRIPTION varchar 60 180 utf8 utf8_general_ci varchar(60) NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(3) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index b0d3c177580..ca3cac7f0fc 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -24,6 +24,11 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS TABLE_NAME 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema CLIENT_STATISTICS ACCESS_DENIED 22 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL def information_schema CLIENT_STATISTICS BINLOG_BYTES_WRITTEN 9 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL def information_schema CLIENT_STATISTICS BUSY_TIME 5 0 NO double NULL NULL 21 NULL NULL NULL NULL double NEVER NULL @@ -557,6 +562,11 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) 3.0000 information_schema CHARACTER_SETS DESCRIPTION varchar 60 180 utf8 utf8_general_ci varchar(60) NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(3) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index e0e5a82a265..5fee1e0050a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -89,6 +89,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1105,6 +1130,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index e0e5a82a265..5fee1e0050a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -89,6 +89,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1105,6 +1130,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/funcs_1/t/is_check_constraints.test b/mysql-test/suite/funcs_1/t/is_check_constraints.test new file mode 100644 index 00000000000..391b48676e0 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_check_constraints.test @@ -0,0 +1,113 @@ +--source include/have_innodb.inc +--echo # +--echo # MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS +--echo # + +CREATE user boo1; +GRANT select,create,alter,drop on foo.* to boo1; +SHOW GRANTS for boo1; + +CREATE user boo2; + +create or replace database foo; + +use foo; + +# Connect with user boo1 +connect(con1,localhost, boo1,, foo); + +--disable_warnings +drop table if exists foo.t0, foo.t1, foo.t2,foo.t3; +--enable_warnings + +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; + +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint + tt int, CONSTRAINT CHK_tt CHECK(tt<100) # 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; + +# Try to see with other user check_constraints +--error ER_DBACCESS_DENIED_ERROR : this should fail : no grant +connect(con2, localhost, boo2,, foo); + +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/sql/handler.h b/sql/handler.h index 54df7941526..51da44eeeef 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -950,6 +950,7 @@ enum enum_schema_tables SCH_ALL_PLUGINS, SCH_APPLICABLE_ROLES, SCH_CHARSETS, + SCH_CHECK_CONSTRAINTS, SCH_COLLATIONS, SCH_COLLATION_CHARACTER_SET_APPLICABILITY, SCH_COLUMNS, diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 05a1cce4175..4aad2757184 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -6788,6 +6788,39 @@ store_constraints(THD *thd, TABLE *table, const LEX_CSTRING *db_name, return schema_table_store_record(thd, table); } +static int get_check_constraints_record(THD *thd, TABLE_LIST *tables, + TABLE *table, bool res, + const LEX_CSTRING *db_name, + const LEX_CSTRING *table_name) +{ + DBUG_ENTER("get_check_constraints_record"); + if (res) + { + if (thd->is_error()) + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, + thd->get_stmt_da()->sql_errno(), + thd->get_stmt_da()->message()); + thd->clear_error(); + DBUG_RETURN(0); + } + if (!tables->view) + { + StringBuffer<MAX_FIELD_WIDTH> str(system_charset_info); + for (uint i= 0; i < tables->table->s->table_check_constraints; i++) + { + Virtual_column_info *check= tables->table->check_constraints[i]; + table->field[0]->store(STRING_WITH_LEN("def"), system_charset_info); + table->field[3]->store(check->name.str, check->name.length, + system_charset_info); + str.length(0); + check->print(&str); + table->field[4]->store(str.ptr(), str.length(), system_charset_info); + if (schema_table_store_record(thd, table)) + DBUG_RETURN(1); + } + } + DBUG_RETURN(0); +} static int get_schema_constraints_record(THD *thd, TABLE_LIST *tables, TABLE *table, bool res, @@ -9708,6 +9741,18 @@ ST_FIELD_INFO spatial_ref_sys_fields_info[]= #endif /*HAVE_SPATIAL*/ +ST_FIELD_INFO check_constraints_fields_info[]= +{ + {"CONSTRAINT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE}, + {"CONSTRAINT_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE}, + {"CONSTRAINT_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {"CHECK_CLAUSE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} +}; /* Description of ST_FIELD_INFO in table.h @@ -9723,6 +9768,8 @@ ST_SCHEMA_TABLE schema_tables[]= fill_schema_applicable_roles, 0, 0, -1, -1, 0, 0}, {"CHARACTER_SETS", charsets_fields_info, 0, fill_schema_charsets, make_character_sets_old_format, 0, -1, -1, 0, 0}, + {"CHECK_CONSTRAINTS", check_constraints_fields_info, 0, get_all_tables, 0, + get_check_constraints_record, 1, 2, 0, OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY}, {"COLLATIONS", collation_fields_info, 0, fill_schema_collation, make_old_format, 0, -1, -1, 0, 0}, {"COLLATION_CHARACTER_SET_APPLICABILITY", coll_charset_app_fields_info, |