Skip to content

Commit f3fa6cc

Browse files
Debarun BanerjeeHery Ramilison
Debarun Banerjee
authored and
Hery Ramilison
committed
BUG#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL IN INFORMATION_SCHEMA
 AFTER RESTART Problem : --------- Information_Schema.referential_constraints (UNIQUE_CONSTRAINT_NAME) shows NULL for a foreign key constraint after restarting the server. If any dml or query (select/insert/update/delete) is done on referenced table, then the constraint name is correctly shown. Solution : ---------- UNIQUE_CONSTRAINT_NAME column is the key name of the referenced table. In innodb, FK reference is stored as a list of columns in referenced table in INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS. The referenced column must have at least one index/key with the referenced column as prefix but the key name itself is not included in FK metadata. For this reason, the UNIQUE_CONSTRAINT_NAME is only filled up when the referenced table is actually loaded in innodb dictionary cache. The information_schema view calls handler::get_foreign_key_list() on foreign key table to read the FK metadata. The UNIQUE_CONSTRAINT_NAME information shows NULL based on whether the referenced table is already loaded or not. One way to fix this issue is to load the referenced table while reading the FK metadata information, if needed. Reviewed-by: Sunny Bains <sunny.bains@oracle.com> RB: 14654 (cherry picked from commit dc55bc8de77539b846200c0baa2f2a5715779dd9)
1 parent 8b7f92a commit f3fa6cc

File tree

3 files changed

+75
-0
lines changed

3 files changed

+75
-0
lines changed

‎mysql-test/suite/innodb/r/foreign_key.result

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,3 +124,29 @@ a b
124124
54 4
125125
55 50
126126
DROP TABLE t2, t1;
127+
#
128+
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
129+
# base bug#24818604 [GR]
130+
#
131+
CREATE TABLE t1 (c1 INT PRIMARY KEY);
132+
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
133+
INSERT INTO t1 VALUES (1);
134+
INSERT INTO t2 VALUES (1);
135+
SELECT unique_constraint_name FROM information_schema.referential_constraints
136+
WHERE table_name = 't2';
137+
unique_constraint_name
138+
PRIMARY
139+
# restart
140+
SELECT unique_constraint_name FROM information_schema.referential_constraints
141+
WHERE table_name = 't2';
142+
unique_constraint_name
143+
PRIMARY
144+
SELECT * FROM t1;
145+
c1
146+
1
147+
SELECT unique_constraint_name FROM information_schema.referential_constraints
148+
WHERE table_name = 't2';
149+
unique_constraint_name
150+
PRIMARY
151+
DROP TABLE t2;
152+
DROP TABLE t1;

‎mysql-test/suite/innodb/t/foreign_key.test

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,3 +85,30 @@ SELECT id, b FROM t1 ORDER BY id;
8585
SELECT a, b FROM t2 ORDER BY a;
8686

8787
DROP TABLE t2, t1;
88+
89+
--echo #
90+
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
91+
--echo # base bug#24818604 [GR]
92+
--echo #
93+
94+
CREATE TABLE t1 (c1 INT PRIMARY KEY);
95+
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
96+
97+
INSERT INTO t1 VALUES (1);
98+
INSERT INTO t2 VALUES (1);
99+
100+
SELECT unique_constraint_name FROM information_schema.referential_constraints
101+
WHERE table_name = 't2';
102+
103+
--source include/restart_mysqld.inc
104+
105+
SELECT unique_constraint_name FROM information_schema.referential_constraints
106+
WHERE table_name = 't2';
107+
108+
SELECT * FROM t1;
109+
110+
SELECT unique_constraint_name FROM information_schema.referential_constraints
111+
WHERE table_name = 't2';
112+
113+
DROP TABLE t2;
114+
DROP TABLE t1;

‎storage/innobase/handler/ha_innodb.cc

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14753,6 +14753,28 @@ get_foreign_key_info(
1475314753
thd, f_key_info.update_method, ptr,
1475414754
static_cast<unsigned int>(len), 1);
1475514755

14756+
/* Load referenced table to update FK referenced key name. */
14757+
if (foreign->referenced_table == NULL) {
14758+
14759+
dict_table_t* ref_table;
14760+
14761+
ut_ad(mutex_own(&dict_sys->mutex));
14762+
ref_table = dict_table_open_on_name(
14763+
foreign->referenced_table_name_lookup,
14764+
TRUE, FALSE, DICT_ERR_IGNORE_NONE);
14765+
14766+
if (ref_table == NULL) {
14767+
14768+
ib::info() << "Foreign Key referenced table "
14769+
<< foreign->referenced_table_name
14770+
<< " not found for foreign table "
14771+
<< foreign->foreign_table_name;
14772+
} else {
14773+
14774+
dict_table_close(ref_table, TRUE, FALSE);
14775+
}
14776+
}
14777+
1475614778
if (foreign->referenced_index
1475714779
&& foreign->referenced_index->name != NULL) {
1475814780
referenced_key_name = thd_make_lex_string(

0 commit comments

Comments
 (0)