Description:
DatabaseMetaDataUsingInfoSchema#getImportedKeys from JDBC driver returns incorrect data in case two tables with equals names in different schemas. FK names also are equals. But one of these keys case another delete rule.
DatabaseMetaDataUsingInfoSchema#getImportedKeys returns both of them, not one. That means that we have, in this case, two keys in resultSet, not one. And delete/update rules they have different. But we do not know - thst from this two keys is correct.
How to repeat:
Create database lessondb, create:
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(100) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
DROP TABLE IF EXISTS `lessons`;
CREATE TABLE `lessons` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`student_id` int unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lessons_student_id_fk` (`student_id`),
CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
create database lessondb_test and switch to it to create:
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(100) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
DROP TABLE IF EXISTS `lessons`;
CREATE TABLE `lessons` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`student_id` int unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lessons_student_id_fk` (`student_id`),
CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Use DatabaseMetaDataUsingInfoSchema#getImportedKeys
Expected result:
lessondb_test account id lessondb_test lessons student_id 1 1 1 lessons_student_id_fk PRIMARY 7
Actual result:
lessondb_test account id lessondb_test lessons student_id 1 1 0 lessons_student_id_fk PRIMARY 7
lessondb_test account id lessondb_test lessons student_id 1 1 1 lessons_student_id_fk PRIMARY 7
Suggested fix:
The issue is here in this row:
"sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) ");"
If I change it:
"sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_SCHEMA) ");"
It works fine for me (because CONSTRAINT_SCHEMA must be in the JOIN too).
© 2023, Oracle Corporation and/or its affiliates
Description: DatabaseMetaDataUsingInfoSchema#getImportedKeys from JDBC driver returns incorrect data in case two tables with equals names in different schemas. FK names also are equals. But one of these keys case another delete rule. DatabaseMetaDataUsingInfoSchema#getImportedKeys returns both of them, not one. That means that we have, in this case, two keys in resultSet, not one. And delete/update rules they have different. But we do not know - thst from this two keys is correct. How to repeat: Create database lessondb, create: DROP TABLE IF EXISTS `account`; CREATE TABLE `account` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `email` varchar(100) NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; DROP TABLE IF EXISTS `lessons`; CREATE TABLE `lessons` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `student_id` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `lessons_student_id_fk` (`student_id`), CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; create database lessondb_test and switch to it to create: DROP TABLE IF EXISTS `account`; CREATE TABLE `account` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `email` varchar(100) NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; DROP TABLE IF EXISTS `lessons`; CREATE TABLE `lessons` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `student_id` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `lessons_student_id_fk` (`student_id`), CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; Use DatabaseMetaDataUsingInfoSchema#getImportedKeys Expected result: lessondb_test account id lessondb_test lessons student_id 1 1 1 lessons_student_id_fk PRIMARY 7 Actual result: lessondb_test account id lessondb_test lessons student_id 1 1 0 lessons_student_id_fk PRIMARY 7 lessondb_test account id lessondb_test lessons student_id 1 1 1 lessons_student_id_fk PRIMARY 7 Suggested fix: The issue is here in this row: "sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) ");" If I change it: "sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_SCHEMA) ");" It works fine for me (because CONSTRAINT_SCHEMA must be in the JOIN too).