We're updating the issue view to help you get more done. 

Update and Get through Mysql Stored Procedure

Description

DB: MySQL

We are using hibernate orm. Please refer the version in the attached snapshot.
In the stored procedure we were running the below sql on the same row.
1. SELECT
2. UPDATE
3. SELECT

This is to get old and updated new entity to our backend api server.
So we were expecting two result set from the stored procedure.
One result set with before update and another with after update.
This is working fine in MySqlWorkBench, where we got two result grid (OLD ROW + NEW/UPDATED ROW).

But when we try the same in Hibernate with the below code, we are getting the same old record twice, both result set is having the same old record before update.

The below is the code.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 private EntityManagerFactory entityManagerFactory = (EntityManagerFactory) context.getBean("entityManagerFactory"); public void callStoredProcedure() { try { EntityManager entityManager = entityManagerFactory.createEntityManager(); StoredProcedureQuery query = entityManager.createStoredProcedureQuery("new_procedure", Credit.class); query.registerStoredProcedureParameter("id", String.class, ParameterMode.IN); query.registerStoredProcedureParameter("amount", Double.class, ParameterMode.IN); query.registerStoredProcedureParameter("success", Integer.class, ParameterMode.OUT); query.setParameter("id", "b707f94d-6286-4cd3-a035-c6a99ead92bc"); query.setParameter("amount", 10.0); query.execute(); List<Credit> credit = query.getResultList(); System.out.println("OldCredit " + credit); if (query.hasMoreResults()) { List<Credit> newCredit = query.getResultList(); System.out.println("NewCredit " + newCredit); } System.out.println("get update " + query.getOutputParameterValue("success")); } catch (Exception e) { e.printStackTrace(); } }

The problem is only when the both the result set has the same select record, means
1. SELECT * FROM table WHERE id = 'idOne'
2. UPDATE table SET column_1 = 'value' WHERE id = 'idOne'
3. SELECT * FROM table WHERE id = 'idOne'

But it is not reproducible when the below two different records are returned from the stored procedure.
1. SELECT * FROM table WHERE id = 'idOne'
3. SELECT * FROM table WHERE id = 'idTwo'

Let me know if any further details required.

Workaround:

Looks like it is a cache issue,

after adding entityManager.clear(); after query.execute(); solved my issue, However this is not nice workaround; we need fix.

Environment

None

Status

Assignee

Unassigned

Reporter

Vikesh Kumar

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

4.3.6

Priority

Critical