MySQL Bugs: #97924: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException

Bug #97924	java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException
Submitted:	9 Dec 2019 11:25	Modified:	5 Feb 2020 14:51
Reporter:	zhongxuchen chen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.17, 8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException

Description:
java.lang.ArrayIndexOutOfBoundsException
java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:441)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)

How to repeat:
connection properties:

rewriteBatchedStatements=true;useServerPrepStmts=true;cachePrepStmts=true;prepStmtCacheSize=250;prepStmtCacheSqlLimit=2048

CREATE TABLE ubmpdb.mysql_bug_test (
	id varchar(100) NULL,
	name varchar(100) NULL
)
INSERT INTO mysql_bug_test (id,name) VALUES 
('1','test1')
,('2','test2')

to running:
select * from mysql_bug_test where id=?

don't shutdown application and to add column for table:
ALTER TABLE ubmpdb.mysql_bug_test ADD segment1 varchar(100) NULL;

repeat running（keep before session，client and server preparedStatement cache ）:
select * from mysql_bug_test where id=?

then java.lang.ArrayIndexOutOfBoundsException

Hello zhongxuchen chen,

Thank you for the report and feedback.
Verified as described with C/J 8.0.18.

regards,
Umesh

#97455 is marked as duplicate of this one

Hi zhongxuchen chen,

This is not a bug, Connector/J doesn't track changes in the database structure because MySQL does not provide any standard way to do it.

In your case, cached prepared statements keep the results metadata received during the prepareStatement() phase. Later you change the table structure without restarting the application thus you get this exceptions because the number of fields in the result set now doesn't match the one in the cached metadata.

In case you still want to proceed with altering table while running the application (that's not the best practice but anyways) you need to change your application to detect this situation. For example, catch this exception and then, in a catch block, do:

 ((JdbcConnection) conn).decachePreparedStatement(pstmt);
 pstmt = conn.prepareStatement("select * from mysql_bug_test where id=?");

Then repeate the execute call.

We encounter this bug today too. After adding a column to a frequently used table requires a restart for those applications.
All the neat explanations at https://dev.mysql.com/doc/refman/8.0/en/statement-caching.html how well metadata changes are detected are useless if Connector/J fails like this.

> This is not a bug, Connector/J doesn't track changes in the database structure because MySQL does not provide any standard way to do it.
Then please develop a way to notify the connector to flush the particular client side caches. The current behavior should be considered as a bug and should be documented in the Connector/J reference for cachePrepStmts property.

> ((JdbcConnection) conn).decachePreparedStatement(pstmt);
> pstmt = conn.prepareStatement("select * from mysql_bug_test where id=?");
This adds a direct dependency to mysql connectorj (com.mysql.cj.jdbc.JdbcConnection) - not a ideal workaround.