MySQL Bugs: #115265: Second stored procedure call with cacheCallableStmts might fail

Bug #115265	Second stored procedure call with cacheCallableStmts might fail
Submitted:	10 Jun 2024 11:35	Modified:	7 Oct 2024 21:00
Reporter:	Patrick Beuks	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.4.0,8.1.0	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	cacheCallableStmts, parameter meta data, Stored pocedures

Description:
Reopening ticket 115249 as it has been reproduced on 8.4.0
------------------

When you use cacheCallableStmts a second call to the same procedure can fail under the following conditions

- There is an OUT parameter
- the number of parameters defined is not equal to the amount of bound parameters, eg. on of the parameters is hardcoded
- You make a second call

The error you get is: `java.sql.SQLException: Parameter number 2 is not an OUT parameter`

From an initial investigation it looks to go wrong here:
https://github.com/mysql/mysql-connector-j/blob/release/8.x/src/main/user-impl/java/com/my...

Where in the first constructor it takes the length of the paramMap and the second it takes the number of params returned by the schema.

How to repeat:
Have a call like
```java
@Select(
"{CALL my_proc(" +
  "0," +
  "#{inField,mode=IN,jdbcType=VARCHAR}," +
  "#{outField,mode=OUT,jdbcType=VARCHAR}" +
  ")}"
)
@Options(statementType = StatementType.CALLABLE)
void myProc(Map<String, Object> paramMap);
```
(where my_proc has two in fields and one out field, body of proc does not seem to matter)

Call this function twice:
```java
@Test
public void testProc() {
  final Map<String, Object> paramMap1 = new HashMap<>();
  paramMap1.put("inField", "my_field");
  paramMap1.put("outField", "");
  assertDoesNotThrow(() -> myDao.myProc(paramMap1));

  final Map<String, Object> paramMap2 = new HashMap<>();
  paramMap2.put("inField", "my_field2");
  paramMap2.put("outField", "");
  assertDoesNotThrow(() -> myDao.myProc(paramMap2));
}
```

If you defined the first param also in the map there is no error

Suggested fix:
When getting the param info from cache here: https://github.com/mysql/mysql-connector-j/blob/1c3f5c149e0bfe31c7fbeb24e2d260cd890972c4/s...

It should set the correct number of parameters

Hi Patrick,

Thank you for your interest in MySQL Connector/J and for taking the time to report this bug. I confirm I'm able to reproduce it as described.

Posted by developer:
 
Added the following entry to the Connector/J 9.1.0 changelog:

"Repeated calls of the same procedure or function would fail when using callableStatements due to the failure of Connector/J to cache and map the correct information for the procedure or function parameters."