Description:
Throwing ArrayIndexOutOfBoundsException on attempt to get value from ResultSet. It happens only when all three factors are combined:
1. connection created with parameter 'useServerPrepStmts=true'
2. ResultSet contains a column with blob or text type
3. Value of a last column in this ResultSet is NULL
---------------
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 10
at com.mysql.cj.mysqla.io.Buffer.readInteger(Buffer.java:284)
at com.mysql.cj.mysqla.result.BinaryBufferRow.getValue(BinaryBufferRow.java:227)
at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:908)
at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:920)
at UseServerPreparedStatementTest.main(UseServerPreparedStatementTest.java:138)
---------------
How to repeat:
// 1. Create connection with parameter 'useServerPrepStmts=true'
// 2. ResultSet should have a column with blob or text type
// 3. Value of a last column in this ResultSet should return NULL
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author Alexander Kunin
*/
public class UseServerPreparedStatementTest {
private static String USER = "root";
private static String PASS = "root";
private static String CATALOG = "use_server_prepared_statement_test";
private static String URL = "jdbc:mysql://localhost:3306/"; //
private static String USE_SERVER_PREP_STMTS = "?useServerPrepStmts=true"; //
private static String VALUE_ONE = "bar";
// private static String URL = "jdbc:mysql://replicaproduction.c3d8kiy4prcb.us-east-1.rds.amazonaws.com:3306";
// private static String URL = "jdbc:mysql://replicaproduction.c3d8kiy4prcb.us-east-1.rds.amazonaws.com:3306/?useServerPrepStmts=true";
private static String DRIVER = "com.mysql.cj.jdbc.Driver";
/**
* Constructor
*/
public UseServerPreparedStatementTest() {
}
public static void main(String[] args) {
boolean dbExists = false;
Connection conn1 = null;
Connection conn2 = null;
Statement st = null;
PreparedStatement pstm1 = null, pstm2 = null, pstm3 = null, pstm4 = null;
ResultSet rs = null, rs1 = null, rs2 = null, rs3 = null, rs4 = null;
try {
Class.forName(DRIVER);
System.out.println("Connecting to SQL server ...");
conn1 = DriverManager.getConnection(URL, USER, PASS);
st = conn1.createStatement();
st.execute("DROP DATABASE IF EXISTS `use_server_prepared_statement_test`");
rs = st.executeQuery("SHOW DATABASES");
while (rs.next()) {
String dbName = rs.getString(1);
if (CATALOG.equals(dbName)) {
// we are good to go. Don't need to create DB.
System.out.println("Database " + CATALOG + " found");
dbExists = true;
break;
}
}
if (!dbExists) {
dbExists = UseServerPreparedStatementTest.createDb(conn1, CATALOG);
st.execute("use " + CATALOG);
// table with blob (mediumblob, text etc.) column type
st.execute("CREATE TABLE `foo`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `val_one` VARCHAR(10), `val_blob` blob, `val_three` VARCHAR(10), PRIMARY KEY (`id`) )");
st.execute("INSERT INTO `foo`(`id`,`val_one`,`val_blob`, `val_three`) VALUES ( NULL,'" + VALUE_ONE
+ "',NULL,NULL)");
}
// test 1 - OK
conn1.setCatalog(CATALOG);
pstm1 = conn1.prepareStatement("select id, val_one, val_blob, val_three from foo where val_one = ?");
pstm1.setString(1, VALUE_ONE);
rs1 = pstm1.executeQuery();
System.out.println("Test 1");
for (int i = 0; i < rs1.getMetaData().getColumnCount(); i++) {
System.out.print(rs1.getMetaData().getColumnName(i + 1) + "("
+ rs1.getMetaData().getColumnTypeName(i + 1) + ") ");
}
System.out.println();
if (rs1.next()) {
System.out.println(rs1.getString("id"));
System.out.println(rs1.getString("val_one"));
System.out.println(rs1.getString("val_blob"));
System.out.println(rs1.getString("val_three"));
}
System.out.println("OK");
System.out.println("---------------------------------------------");
// - test 2 - OK
// let's test with setting useServerPrepStmts=true
conn2 = DriverManager.getConnection(URL + USE_SERVER_PREP_STMTS, USER, PASS);
conn2.setCatalog(CATALOG);
pstm2 = conn2.prepareStatement("select id, val_one, val_three from foo where val_one = ?"); // let's not request val_blob for now
pstm2.setString(1, VALUE_ONE);
rs2 = pstm2.executeQuery();
System.out.println("Test 2");
for (int i = 0; i < rs2.getMetaData().getColumnCount(); i++) {
System.out.print(rs2.getMetaData().getColumnName(i + 1) + "("
+ rs2.getMetaData().getColumnTypeName(i + 1) + ") ");
}
System.out.println();
if (rs2.next()) {
System.out.println(rs2.getString("id"));
System.out.println(rs2.getString("val_one"));
System.out.println(rs2.getString("val_three"));
}
System.out.println("OK");
System.out.println("---------------------------------------------");
// test 3 - OK!
// let's call query to make sure value of a last column in a result set is not NULL - val_one is not NULL and this is now last column in result set
pstm3 = conn2.prepareStatement("select id, val_blob, val_three, val_one from foo where val_one = ?");
pstm3.setString(1, VALUE_ONE);
rs3 = pstm3.executeQuery();
System.out.println("Test 3");
for (int i = 0; i < rs3.getMetaData().getColumnCount(); i++) {
System.out.print(rs3.getMetaData().getColumnName(i + 1) + "("
+ rs3.getMetaData().getColumnTypeName(i + 1) + ") ");
}
System.out.println();
if (rs3.next()) {
System.out.println(rs3.getString("id"));
System.out.println(rs3.getBlob("val_one"));
System.out.println(rs3.getString("val_blob"));
System.out.println(rs3.getString("val_three"));
}
System.out.println("OK");
System.out.println("---------------------------------------------");
// test 4 - fails! Combination of three factors:
// 1. useServerPrepStmts=true
// 2. result set has column with mediumblob type
// 3. value of a last column in result set is NULL
pstm4 = conn2.prepareStatement("select id, val_one, val_blob, val_three from foo where val_one = ?");
pstm4.setString(1, VALUE_ONE);
rs4 = pstm4.executeQuery();
System.out.println("Test 3");
for (int i = 0; i < rs4.getMetaData().getColumnCount(); i++) {
System.out.print(rs4.getMetaData().getColumnName(i + 1) + "("
+ rs4.getMetaData().getColumnTypeName(i + 1) + ") ");
}
System.out.println();
if (rs4.next()) {
System.out.println(rs4.getString("id"));
System.out.println(rs4.getString("val_one"));
System.out.println(rs4.getBlob("val_blob"));
System.out.println(rs4.getString("val_three"));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
UseServerPreparedStatementTest.close(rs);
UseServerPreparedStatementTest.close(st);
UseServerPreparedStatementTest.close(rs1);
UseServerPreparedStatementTest.close(rs2);
UseServerPreparedStatementTest.close(rs3);
UseServerPreparedStatementTest.close(rs4);
UseServerPreparedStatementTest.close(pstm1);
UseServerPreparedStatementTest.close(pstm2);
UseServerPreparedStatementTest.close(pstm3);
UseServerPreparedStatementTest.close(pstm4);
UseServerPreparedStatementTest.close(conn1);
UseServerPreparedStatementTest.close(conn2);
}
}
public static void close(Statement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
}
}
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
}
}
/** create database and use it
* @throws IOException */
public static boolean createDb(Connection conn, String catalog) throws IOException {
Statement st = null;
try {
st = conn.createStatement();
st.execute("CREATE DATABASE `" + catalog + "`;");
st.execute("USE `" + catalog + "`;");
System.out.println("Database " + catalog + " created");
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
close(st);
}
}
}