Stored procedures doesn't execute when stopping Java

Question

I created a stored procedure in MySQL and called it from a Java application with JPA EclipseLink. Once the procedure is called, it has a "sleep(sec)" method inside, and then it executes something successfully unless the application is turned off, it seems like the procedure is canceled too which is not what I want. The same thing I tried using JDBC PreparedStatements, the same result. Is there any workaround to make the stored procedure work even if the app was shut down after the procedure call.

Stored Procedure

DELIMITER //
DROP PROCEDURE IF EXISTS session_procedure//
CREATE PROCEDURE session_procedure
(
IN userID INT
)
BEGIN
SELECT SLEEP(30);
UPDATE users 
SET users.Active = 0 
WHERE users.Id = userID;

END//
DELIMITER ;

Procedure call in Java

public static void destroySessionCountdown(EntityManager entityManager, Account akk){
        int accountId = akk.getId();

        StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("session_procedure");
        storedProcedure.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
        storedProcedure.setParameter(1, accountId);

        try {
            storedProcedure.execute();
        }catch(Exception e){
            e.printStackTrace();
        }
        // force logout
    }

Note: there is quite a bit of speculation on my part. Without further information, I would suspect the stored procedure to be executed in a transaction opened by JPA. Thus, if the Java process terminates before the procedure has successfully executed, the transaction has to be rolled back to guarantee atomicity. I am not sure, however, if it is possible to influence/configure this behaviour. — Turing85, Dec 30 '19 at 00:04

score 1 · Accepted Answer · answered Dec 30 '19 at 00:44

I suppose that when you closed connection to the DB, all processes related to it were cancelled including the running call of this stored procedure. I don't think you can avoid it.

What you are trying to implement is a kind of scheduled job. I would suggest to use cron instead. For the procedure you shown a simple SQL instead of stored procedure would be sufficient. The logic related to delays and to the execution time could be placed to a shell script and to the cron.

Found a workaround. Replaced the stored procedure with an Event and triggered it in a Transaction. — Gicu Mironica, Dec 31 '19 at 18:12

Stored procedures doesn't execute when stopping Java

1 Answers1