Bug #74723 'Unknown table XXX in information_schema' when query view using CachedRowSet
Submitted: 7 Nov 2014 3:33 Modified: 7 Feb 2017 23:46
Reporter: FeiYong Jiang Email Updates:
Status: Can't repeat Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-5.1.34 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[7 Nov 2014 3:33] FeiYong Jiang
when using CachedRowSet to execute a view query,a error occured below:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'XXX' in information_schema

	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
	at com.mysql.jdbc.Util.getInstance(Util.java:360)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
	at com.mysql.jdbc.Field.getCollation(Field.java:448)
	at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:549)
	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:704)
	at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:621)
	at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.createSqlRowSet(SqlRowSetResultSetExtractor.java:79)
	at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.extractData(SqlRowSetResultSetExtractor.java:62)
	at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.extractData(SqlRowSetResultSetExtractor.java:45)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:447)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:457)

How to repeat:
[JAVA code]
String sql = "select * from wx.v_gen_dataset ";
JdbcTemplate jt = new JdbcTemplate(DbUtil.getInstance().getDataSource());
    SqlRowSet rs = jt.queryForRowSet(sql);
    while (rs.next()) {

        table_name VARCHAR(40) NOT NULL,
        column_name VARCHAR(50) NOT NULL,
        COMMENT VARCHAR(100),
        PRIMARY KEY (table_name, column_name)
CREATE VIEW v_gen_dataset1 AS select col.column_name attrname,
       col.ordinal_position colindex,
       col.column_name columnname,
       col.table_name tablename,
       comm.comment title
  from information_schema.columns col left join wx.t_cd_tabcomm comm on
  (col.table_name = comm.table_name
   and col.column_name = comm.column_name)
 order by col.ordinal_position
[12 Nov 2014 0:43] Filipe Silva
Hi FeiYong Jiang,

Thank you for this bug report.

Looking at the stack trace you provided we can see that the entry point in the Connector/J classes is the line "at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:549)".
I tried to reproduce this error using your table and view, and by consulting the ResultSetMetaData.isCaseSensitive() for each one of the fields of the view, but with no success. It worked perfectly.

There may be, however, other questions to consider. I'd like to know if you are setting any non-default connection properties in your connection string. It would also be useful to identify the queries that were run once this piece of code started executing, by turn on the general query log in your MySQL server. Could you please provide this information?
[12 Dec 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 Nov 2015 22:33] Adam Rauch
We're now experiencing this issue, immediately after upgrading the server from 5.6 to 5.7 (exact same code worked fine against the 5.6 server). I'm using "mysql-connector-java-5.1.36" against the 5.7.9 server.

My sample code issues a simple query against sakila.Actor (essentially "SELECT * FROM sakila.Actor") and then calls isCaseSensitive() on the ResultSet's ResultSetMetaData, which results in the driver issuing a query to determine column collation. However, this query's SQL appears to be invalid: SHOW FULL COLUMNS FROM `sakila`.`*`

I've spent just a couple minutes stepping through the driver code. In Field.getCollation(), getOriginalTableName() returns "*", which seems suspect. Field.toString() returns:

com.mysql.jdbc.Field@4a576476[catalog=sakila,tableName=x,originalTableName=*,columnName=first_name,originalColumnName=first_name,mysqlType=253(FIELD_TYPE_VAR_STRING),flags=, charsetIndex=33, charsetName=utf8]

I'll look at working up simple repro steps.
[9 Nov 2015 1:40] Adam Rauch
"SELECT * FROM sakila.Actor" is not sufficient, but adding a simple sub-select results in:

   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'sakila.*' doesn't exist 

Code below demonstrates the problem, when run against the 5.7.9 server:

package org.labkey;

import java.sql.*;
import java.util.Properties;

public class Main {
    public static void main(String[] args) throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", args[0]);
        connectionProps.put("password", args[1]);

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/", connectionProps)) {
            testQuery(conn, "SELECT first_name FROM sakila.Actor");                    // Works fine
            testQuery(conn, "SELECT * FROM (SELECT first_name FROM sakila.Actor) x");  // Throws exception when run against MySQL 5.7.9

    private static void testQuery(Connection conn, String sql) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            try (ResultSet rs = stmt.executeQuery(sql)) {
[11 Dec 2015 15:45] Filipe Silva
Hi Adam,

Thank you for your input.

I'm not convinced that your example is the exact same issue initially reported, but they are somehow related for sure. I'll take it as a valid case I can reproduce and set this bug as verified.

Once again, thank you.
[11 Dec 2015 17:33] Adam Rauch
Agree that my issue might not be the same as the original. Given that, I recently opened a new issue with these steps: http://bugs.mysql.com/bug.php?id=79449

No need to investigate the same issue twice, of course, so feel free to close this one as a duplicate, etc.

[29 Aug 2016 11:49] Filipe Silva
Adam's report is being tracked in Bug#79449.

There's still no response from the original reporter regarding the requested information.
[30 Sep 2016 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 Feb 2017 23:41] Filipe Silva
There's a high probability of this bug not being observable anymore by using a MySQL server version 5.7.17 or higher as it seems to be related to the Bug#79641 which was fixed on this server version.