Description

I believe this exists in ALL versions of HeidiSQL and MySQL (and possibly for other engines as well).

When procedure is updated the following sequence of commands is being executed:

1. SELECT ROUTINE_NAME FROM information_schema.ROUTINES WHERE....
2. CREATE PROCEDURE HeidiSQL_temproutine_1....
3. DROP PROCEDURE IF EXISTS HeidiSQL_temproutine_1\
4. DROP PROCEDURE IF EXISTS SP_NAME\
5. CREATE PROCEDURE SP_NAME....

This works fine on an idle DB.
However, I noticed when DB is under load (in my case it's a flow of SP calls with 50-150ms execution time each, but could be long running call as well) step 4 was execute already, but step 5 stuck in the waiting state ("Waiting for stored procedure metadata lock").
It is logical that it cannot update SP while it's being executed. I don't know why the lock is not released immediately when SP execution finishes, but nevertheless it cannot run the CREATE statement even with small executions.

So, there is a situation when old procedure has been deleted already but new one was not created yet. This is very dangerous as workload starts to error (as procedure was deleted) and in my case it enters the loop keeping the lock.
This could last indefinitely. What people do when application hangs? they close it! So did I. When HeidiSQL was closed my procedure was gone. Even if I don't close app, a connection could be lost at any time due to network and result will be the same. Even without any problems on a client side, metadata lock will end with timeout after some time.

I don't know how this should be solved (considering problems putting DDL in transactions)
but seems like creating temporary routine first and deleting it at the very end could help a bit. At least HeidiSQL could set a specific waiting period and kill query after it, and restore saved (old) version. Not ideal, but much better than a complete loss of an object in DB.

HeidiSQL version

12.14.0.7165

Database server version

MySQL 8.0

Reproduction recipe

1. Create procedure.
2. Call it in a loop.
3. Modify SP code and press "Save"
4. Check "Waiting for stored procedure metadata lock" for CREATE statement connection indefinite until step#2 is active
5. Kill connection with CREATE statement
6. Check that SP is lost in DB.

Error/Backtrace