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

Bug #115249	Second stored procedure call with cacheCallableStmts might fail
Submitted:	7 Jun 2024 7:33	Modified:	7 Jun 2024 13:47
Reporter:	Patrick Beuks	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.1.0,8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cacheCallableStmts, parameter meta data, Stored pocedures

Description:
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

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

I forgot to add the error message:

java.sql.SQLException: Parameter number 1 is not an OUT parameter

Small type in the sql code (left trailing comma)

```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);
```

Hello Patrick,

Thank you for the bug report.
Please upgrade to latest version and report us back if issue persist even in latest version along with test case. 

Also, see Bug #73774 which is fixed in Connector/J 8.3.0. Thank you.

Regards,
Ashwini Patil

Tested with
mysql-connector-j-8.4.0 (Revision: 1c3f5c149e0bfe31c7fbeb24e2d260cd890972c4)

Still have the same problem

"along with test case"
I have provided reproduction steps. What kind of test case would you like to see?

Duplicate of Bug#115265.