Description:
0. Connector/J - 5.1.44, MySQL Enterprise Server - 5.7.16
1. Stored procedure with out parameter(BIGINT type).
2. URL = "jdbc:mysql://10.137.49.160:3306/admin?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=500&prepStmtCacheSqlLimit=2048&useOldAliasMetadataBehavior=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=100";
3. Code to execute stored procedure:
4. If (1) and (2) are omitted then it works as expected, in they present exception will be thrown.
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 23
at com.mysql.jdbc.SingleByteCharsetConverter.toString(SingleByteCharsetConverter.java:303)
at com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:699)
at com.mysql.jdbc.BufferRow.getString(BufferRow.java:527)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2682)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2650)
at com.mysql.jdbc.CallableStatement.getLong(CallableStatement.java:1347)
at com.test.mysql.Main.main(Main.java:52)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
5.If I don't use "useCursorFetch=true&defaultFetchSize=100",it run normally.Output as follows:
C:\jdk1.6.0_45_x64\bin\java -Didea.launcher.port=7532 -Didea.launcher.bin.path=D:\Tools\JetBrains\ideaIU-2016.3.4\bin -classpath C:\Users\w00192156\AppData\Local\Temp\classpath.jar -Dfile.encoding=UTF-8 com.intellij.rt.execution.application.AppMain com.test.mysql.Main
Thu Sep 07 17:00:34 GMT+08:00 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.
30314567
STR30314567
How to repeat:
step1. Create stored procedure with integer or bigint out parameter
DROP PROCEDURE PROC_TEST;
CREATE PROCEDURE PROC_TEST(
IN PARAMIN BIGINT,
OUT PARAM_OUT_LONG BIGINT,
OUT PARAM_OUT_STR VARCHAR(100)
)
BEGIN
SET PARAM_OUT_LONG = PARAMIN + 100000;
SET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG);
END;
step2:Create connection to DB with useCursorFetch=true&defaultFetchSize=100
for example:
private static final String urladmin = "jdbc:mysql://10.137.49.160:3306/admin?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=500&prepStmtCacheSqlLimit=2048&useOldAliasMetadataBehavior=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=100";
step3: Create java.sql.CallableStatement for this stored procedure
public static void main(String args[]) throws SQLException, XAException
{
// Connection con = DriverManager.getConnection(url, "admin", "Abc1234%");
Connection con = DriverManager.getConnection(urladmin, "admin", "Abc1234%");
CallableStatement callableStatement = null;
try
{
callableStatement = con.prepareCall("call PROC_TEST(?,?,?)");
callableStatement.setLong(1, 30214567L);
callableStatement.registerOutParameter(2, Types.BIGINT);
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.execute();
System.out.println(callableStatement.getLong(2));
System.out.println(callableStatement.getString(3));
}finally
{
if(callableStatement != null)
{
callableStatement.close();
}
if(con != null)
{
con.close();
}
}
4. Register type of out parameter.
5. Execute it.
6. Exception will be thrown.
Description: 0. Connector/J - 5.1.44, MySQL Enterprise Server - 5.7.16 1. Stored procedure with out parameter(BIGINT type). 2. URL = "jdbc:mysql://10.137.49.160:3306/admin?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=500&prepStmtCacheSqlLimit=2048&useOldAliasMetadataBehavior=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=100"; 3. Code to execute stored procedure: 4. If (1) and (2) are omitted then it works as expected, in they present exception will be thrown. Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 23 at com.mysql.jdbc.SingleByteCharsetConverter.toString(SingleByteCharsetConverter.java:303) at com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:699) at com.mysql.jdbc.BufferRow.getString(BufferRow.java:527) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2682) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2650) at com.mysql.jdbc.CallableStatement.getLong(CallableStatement.java:1347) at com.test.mysql.Main.main(Main.java:52) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) 5.If I don't use "useCursorFetch=true&defaultFetchSize=100",it run normally.Output as follows: C:\jdk1.6.0_45_x64\bin\java -Didea.launcher.port=7532 -Didea.launcher.bin.path=D:\Tools\JetBrains\ideaIU-2016.3.4\bin -classpath C:\Users\w00192156\AppData\Local\Temp\classpath.jar -Dfile.encoding=UTF-8 com.intellij.rt.execution.application.AppMain com.test.mysql.Main Thu Sep 07 17:00:34 GMT+08:00 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. 30314567 STR30314567 How to repeat: step1. Create stored procedure with integer or bigint out parameter DROP PROCEDURE PROC_TEST; CREATE PROCEDURE PROC_TEST( IN PARAMIN BIGINT, OUT PARAM_OUT_LONG BIGINT, OUT PARAM_OUT_STR VARCHAR(100) ) BEGIN SET PARAM_OUT_LONG = PARAMIN + 100000; SET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG); END; step2:Create connection to DB with useCursorFetch=true&defaultFetchSize=100 for example: private static final String urladmin = "jdbc:mysql://10.137.49.160:3306/admin?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=500&prepStmtCacheSqlLimit=2048&useOldAliasMetadataBehavior=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=100"; step3: Create java.sql.CallableStatement for this stored procedure public static void main(String args[]) throws SQLException, XAException { // Connection con = DriverManager.getConnection(url, "admin", "Abc1234%"); Connection con = DriverManager.getConnection(urladmin, "admin", "Abc1234%"); CallableStatement callableStatement = null; try { callableStatement = con.prepareCall("call PROC_TEST(?,?,?)"); callableStatement.setLong(1, 30214567L); callableStatement.registerOutParameter(2, Types.BIGINT); callableStatement.registerOutParameter(3, Types.VARCHAR); callableStatement.execute(); System.out.println(callableStatement.getLong(2)); System.out.println(callableStatement.getString(3)); }finally { if(callableStatement != null) { callableStatement.close(); } if(con != null) { con.close(); } } 4. Register type of out parameter. 5. Execute it. 6. Exception will be thrown.