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:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.10 OS:Any
Assigned to: CPU Architecture:Any

[5 Jan 2010 0:00] Alexander Hristov
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");
	}

}
[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.