Keep procedure running even connection dies?



  • I want to create a procedure in my database that is expected to sleep for very long periods of time, like 9 hours.

    They perform some operations, sleep for hours, then do other stuff and exit.

    I will be calling the procedure from a bash script or other means that I can integrate with zabbix.

    Can I safely expect that the procedure will run even if the connections die, or any other timeouts that may apply to the SQL connection?

    If this is not the correct approach to handle my situation please let me know the best way. I need to be sure that once this procedure is started, it will finish unless mariadb crashes.



  • One solution could be to use the https://mariadb.com/kb/en/events/ system:

    Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals.

    They function very similarly to the Windows Task Scheduler or Unix cron jobs.

    This should allow you to create tasks that are executed even if the client connection dies before it finishes. The only limitation is that you can't read the output of that command unless you store it in a table.

    For example, if you have a stored procedure called MyProc(), you can call it with:

    CREATE EVENT my_event
    ON SCHEDULE AT CURRENT_TIMESTAMP
    DO
     CALL MyProc();
    

    The reason this works is because the ON SCHEDULE AT CURRENT_TIMESTAMP causes the event to trigger immediately after it has been created. Once the event execution is complete, the event is automatically deleted.

    Usually you'd use this to delay the execution of some task but in this case we just want to offload the execution to the event subsystem in MariaDB.


    The only problem you can run into is the uniqueness of the event names. As the execution is scheduled to take place immediately, it's probably not a problem in practice but it's good to keep in mind.

    Another thing to take notice of is a limitation ( https://jira.mariadb.org/browse/MDEV-18031 ) in MariaDB that prevents events from being created inside stored procedures or compound statements.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2