René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Procedure update under load could result irreversible loss of code.","articleBody":"### Description\n\nI believe this exists in ALL versions of HeidiSQL and MySQL (and possibly for other engines as well).   \n\nWhen procedure is updated the following sequence of commands is being executed:\n1. SELECT ROUTINE_NAME FROM `information_schema`.`ROUTINES` WHERE....\n2. CREATE PROCEDURE `HeidiSQL_temproutine_1`....\n3. DROP PROCEDURE IF EXISTS `HeidiSQL_temproutine_1`\\\\\n4. DROP PROCEDURE IF EXISTS `SP_NAME`\\\\\n5. CREATE PROCEDURE `SP_NAME`....\n\nThis works fine on an idle DB.\nHowever, 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**\"). \nIt 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.\n\nSo, 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.\nThis 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.\n\nI don't know how this should be solved (considering problems putting DDL in transactions) \nbut 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.\n\n\u003cimg width=\"271\" height=\"46\" alt=\"Image\" src=\"https://github.com/user-attachments/assets/5c2657d8-6bd0-459b-b7fc-c12a5f122961\" /\u003e\n\n### HeidiSQL version\n\n12.14.0.7165\n\n### Database server version\n\nMySQL 8.0 \n\n### Reproduction recipe\n\n1. Create procedure.\n2. Call it in a loop.\n3. Modify SP code and press \"Save\"\n4. Check \"Waiting for stored procedure metadata lock\" for CREATE statement connection indefinite until step#2 is active\n5. Kill connection with CREATE statement\n6. Check that SP is lost in DB.\n\n### Error/Backtrace\n\n```shell\n\n```","author":{"url":"https://github.com/OXYAMINE","@type":"Person","name":"OXYAMINE"},"datePublished":"2026-01-22T07:29:56.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/2379/HeidiSQL/issues/2379"}