MySQL Bugs: #87534: UNION ALL query fails when useServerPrepStmts=true on database connection

Bug #87534	UNION ALL query fails when useServerPrepStmts=true on database connection
Submitted:	24 Aug 2017 21:01	Modified:	31 Jul 2018 22:22
Reporter:	Vy Ton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.43	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	prepared statement, unionall

Description:
When executing this query,

SELECT dateColumn from table
UNION ALL
SELECT dateColumn from table 

the following exception is thrown
java.sql.SQLException: Unknown type '14 in column 0 of 1 in binary-encoded result set.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.MysqlIO.extractNativeEncodedColumn(MysqlIO.java:4558)
	at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:4472)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2045)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3400)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:470)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3112)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2341)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1366)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:782)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
	at com.appiancorp.support.jdbcutilities.JDBCClient.main(JDBCClient.java:105)

How to repeat:
This is reproducible with the MySQL Server 5.7.18 and the following jdbc drivers:
mysql-connector-java-5.1.38-bin.jar
mysql-connector-java-5.1.41-bin.jar
mysql-connector-java-5.1.43-bin.jar

There must be data in the table to produce the issue. I can only reproduce the error when useServerPrepStmts=true on my database connection. The following code reproduces the issue:

      Properties props = new Properties();
      props.setProperty("user", "foo");
      props.setProperty("password", "bar");

      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds2?useServerPrepStmts=true", props);

      Statement stmt = conn.prepareStatement(query);
      ResultSet rs = ((PreparedStatement)stmt).executeQuery();

Suggested fix:
This could be similar to BUG#14609

Hello Vy Ton,

Thank you for the bug report and test case.
I tried to reproduce the issue at my end using MySQL Connector / J 5.1.43 and MySQL 5.7.19 but not seeing any issues with UNION ALL and useServerPrepStmts= ture.
Could you please provide repeatable test case (exact steps, database/create table statements, full stack trace, sample code etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hi Chiranjeevi,

This is the SQL to create my table:

CREATE TABLE `table1` (
  `transaction_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`transaction_date`) VALUES
('2017-08-01'),
('2017-08-01'),
('2017-08-01');

I can reproduce the error with the following test client:

public class JDBCClient {

  public static void main(String[] args) throws SQLException {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      try {
        System.out.println("Loading non-XA drivers ...");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        System.out.println("Non-XA drivers loaded");
      } catch (ClassNotFoundException e) {
      }

      Properties props = new Properties();
      props.setProperty("user", "foo");
      props.setProperty("password", "bar");

      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds2?useServerPrepStmts=true", props);

      String query = "SELECT transaction_date from table1 UNION ALL SELECT transaction_date from table1";

      System.out.println("Executing query: " + query);
      long startTime = System.currentTimeMillis();

      System.out.println("Preparing query...");
      stmt = conn.prepareStatement(query);
      rs = ((PreparedStatement)stmt).executeQuery();
      long endTime = System.currentTimeMillis();

      if (rs.next()) {
        System.out.println("Got a result after " + (endTime - startTime) + " ms");
      } else {
        System.out.println("No results after " + (endTime - startTime) + " ms");
      }
      System.out.println("Disconnected from database");
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (conn != null) {
        conn.close();
      }
      if (stmt != null) {
        stmt.close();
      }
      if (rs != null) {
        rs.close();
      }
    }
    System.out.println("Complete.");
  }
}

The full stack trace is in the original description. Thank you for any assistance.

Hello Vy Ton,

Thank you for the bug feedback and test case.
Verified this behavior on MySQL Connector / J 5.1.43.

Thanks,
Chiranjeevi.

run:
Loading non-XA drivers ...
Non-XA drivers loaded
Thu Aug 31 16:52:57 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Executing query: SELECT transaction_date from table1 UNION ALL SELECT transaction_date from table1
Preparing query...
java.sql.SQLException: Unknown type '14 in column 0 of 1 in binary-encoded result set.
Complete.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.MysqlIO.extractNativeEncodedColumn(MysqlIO.java:4558)
	at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:4472)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2045)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3400)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:470)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3112)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2341)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1366)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:782)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
	at javaapplication3.Bug_87534.main(Bug_87534.java:48)
BUILD SUCCESSFUL (total time: 2 seconds)

Hi Vy Ton,

There is a bug in MySQL server 5.7 that is causing this. So, it will be fixed in the server, not in the connector.

In the meantime, wrapping your UNION ALL queries with a SELECT * FROM (<your-query-here>) statement should be a workaround for this.

Thank you,

Posted by developer:
 
As there's no fix on the side of Connector/J, no documentation is required.