MySQL Bugs: #74723: 'Unknown table XXX in information_schema' when query view using CachedRowSet

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:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql-connector-java-5.1.34	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
when using CachedRowSet to execute a view query,a error occured below:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'XXX' in information_schema

[StackTrace]
	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()) {
        System.out.println(rs.getString(1));
    }

[sql]
CREATE TABLE
    t_cd_tabcomm
    (
        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

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?

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".

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.

"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)) {
                System.out.println(rs.getMetaData().isCaseSensitive(1));
            }
        }
    }
}

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.

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.

Thanks,
Adam

Adam's report is being tracked in Bug#79449.

There's still no response from the original reporter regarding the requested information.

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".

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.