Description:
Using mysql-connector-java-5.1.36 against 5.7.9, invoking ResultSetMetaData.isCaseSensitive() against ResultSets returned from simple queries yields the exception below. This was not the case when executing the same queries against the 5.6 server. The call to isCaseSensitive() causes the driver to issue a query to determine column collation. However, the driver constructs invalid SQL: SHOW FULL COLUMNS FROM `sakila`.`*`
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'sakila.*' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432)
at com.mysql.jdbc.Field.getCollation(Field.java:448)
at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:549)
at org.labkey.Main.testQuery(Main.java:21)
at org.labkey.Main.main(Main.java:14)
These details were originally posted as an update to https://bugs.mysql.com/bug.php?id=74723; I've opened as a new issue to bring attention to this problem.
How to repeat:
This (Java 8) code reproduces 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));
}
}
}
}
Suggested fix:
The fact that invoking a boolean method on ResultSetMetaData causes a roundtrip to the server is surprising; this seems inefficient and destined for failure (can the driver construct the correct SHOW FULL COLUMNS query for arbitrarily complex queries?). I don't know anything about the driver or the server protocol, but returning this metadata with the original results seems like the most reasonable approach.
I've spent just a couple minutes stepping through the driver code. In Field.getCollation(), getOriginalTableName() returns "*", which is 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]
