| Bug #50066 | DatabaseMetaData.getImportedKeys throws exception with useInformationSchema | ||
|---|---|---|---|
| Submitted: | 5 Jan 2010 0:00 | Modified: | 1 Feb 2010 13:38 |
| Reporter: | Alexander Hristov (Candidate Quality Contributor) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Feb 2010 13:38]
Tonci Grgin
Hi Alexander and thanks for your report.
There is a test for this in our test suite (testsuite.simple/MetadataTest.java, public void testGetImportedKeysUsingInfoSchema() throws Exception {) which works perfectly for me (and yes, I did add props.put("pedantic", "true");). Below is the relevant query from general query log which also shows no neuralgic points concerning your problem.
I think we might have a problem with your MySQL server mode, mine is empty.
10 Query /* mysql-connector-java-5.1.11 ( Revision: ${svn.Revision} ) */SELECT @@session.auto_increment_increment
10 Query SHOW COLLATION
10 Query SET autocommit=1
10 Query SET sql_mode='STRICT_TRANS_TABLES'
10 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
10 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
10 Query SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT,NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE='CASCADE' THEN 0 WHEN R.UPDATE_RULE='SET NULL' THEN 2 WHEN R.UPDATE_RULE='SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE='RESTRICT' THEN 1 WHEN R.UPDATE_RULE='NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE='CASCADE' THEN 0 WHEN R.DELETE_RULE='SET NULL' THEN 2 WHEN R.DELETE_RULE='SET DEFAULT' THEN 4 WHEN R.DELETE_RULE='RESTRICT' THEN 1 WHEN R.DELETE_RULE='NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_SCHEMA LIKE 'test' AND A.TABLE_NAME='child' AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL ORDER BY A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, A.ORDINAL_POSITION
100201 14:24:52 9 Query DROP TABLE IF EXISTS child
9 Query DROP TABLE If EXISTS parent
10 Quit
Can't repeat with latest sources.
Description: getImportedKeys throws the following exception if the useInformationSchema property is set to true: Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'REFERENCED_TABLE_NAME' in where clause is ambiguous at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885) at com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:50) at com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.getImportedKeys(DatabaseMetaDataUsingInfoSchema.java:780) How to repeat: import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.util.Properties; public class TestCase { public static void main(String[] args)throws Exception { Class.forName("com.mysql.jdbc.Driver"); Properties properties = new Properties(); properties.setProperty("user","root"); properties.setProperty("password","xxxxxx"); properties.setProperty("nullCatalogMeansCurrent","false"); properties.setProperty("pedantic","true"); properties.setProperty("useInformationSchema","true"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mysql",properties); DatabaseMetaData dbmt = con.getMetaData(); ResultSet rs = dbmt.getImportedKeys("mysql", null, "user"); } }