Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 10.4(EOL), 10.10(EOL), 11.0(EOL)
-
None
Description
InnoDB is used to get a foreign key, otherwise it is not specific to InnoDB.
--source include/have_innodb.inc
|
|
create database db; |
create table db.t1 (x int, key(x)) engine=InnoDB; |
create table db.t2 (a int, b int, c int, unique(b), check(c>b), foreign key(c) references db.t1(x)) engine=InnoDB; |
|
create user u@localhost; |
grant select (a) on db.t2 to u@localhost; |
|
--connect (con1,localhost,u,,db)
|
--echo #
|
--echo # This is OK:
|
select table_name, column_name from information_schema.columns where table_name in ('t1','t2'); |
--echo #
|
--echo # This is not:
|
select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2'); |
select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2'); |
--echo #
|
--echo # This is possibly not OK either:
|
select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2'); |
select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2'); |
show index in t2; |
|
# Cleanup
|
--disconnect con1
|
--connection default
|
drop user u@localhost; |
drop database db; |
Actual result, 10.4 b1c8ea83 |
# This is OK: |
select table_name, column_name from information_schema.columns where table_name in ('t1','t2'); |
table_name column_name
|
t2 a
|
#
|
# This is not: |
select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2'); |
table_name column_name
|
t2 b
|
t2 c
|
select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2'); |
table_name unique_constraint_name referenced_table_name
|
t2 x t1
|
#
|
# This is possibly not OK either: |
select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2'); |
table_name constraint_name
|
t2 b
|
t2 CONSTRAINT_1
|
t2 t2_ibfk_1
|
select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2'); |
table_name constraint_name constraint_type
|
t2 b UNIQUE |
t2 CONSTRAINT_1 CHECK |
t2 t2_ibfk_1 FOREIGN KEY |
show index in t2; |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment |
t2 0 b 1 b A 0 NULL NULL YES BTREE |
t2 1 c 1 c A 0 NULL NULL YES BTREE |
The expected result is probably only to return one row to the first query and nothing else, but maybe it's trickier than that.
Attachments
Issue Links
- causes
-
MDEV-34838 SELECT Grant restricts visibility to referential_constraints table
- Closed
- relates to
-
MDEV-33647 SELECT FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS empty set when specific privileges on table
- Closed
-
MDEV-32501 INFORMATION_SCHEMA.KEY_PERIOD_USAGE reveals information to unprivileged user
- Closed