1

Summary: Can DELETE from mysql.proc be used safely from MySQL 5.7 onward?

In question Drop all stored procedures in MySQL or using temporary stored procedures, it was mentioned that in MySQL it is not possible to use DROP PROCEDURE inside a stored procedure (mysql error code 1357, "Can't drop or alter a %s from within another stored routine").

Empirically:

DELETE from mysql.proc WHERE db = <db-name> AND name LIKE '<procedure-prefix>%';

does seem to work fine instead.

In a comment, the question was asked (but not answered):

Is mysql.proc still a viable method of interacting with MySQL meta information in 5.7?

Even though I fully realise it is not "desirable", is it "OK" to use this in MySQL 5.7? Or, is there some vital stuff which DROP PROCEDURE does, such that I must not use this workaround?

JonBrave
  • 4,045
  • 3
  • 38
  • 115
  • 2
    After the delete statement, can you execute the stored proc? – Shadow Mar 14 '18 at 12:04
  • @Shadow Very, very good observation! Yes, unfortunately I still can :( From Workbench, Refresh All shows the sp no longer there, but it can still be executed in the same session. Quitting Workbench and restarting it clears the "cache". Any workaround for this too (I don't actually care about it, removal by next session would suffice), or am I now delving into an example of too much dangerous territory? – JonBrave Mar 14 '18 at 12:17
  • The `flush` statement is used in MySQL to make the server reload stuff (changing the privileges directly via update / delete and the `flush privileges` is probably the best analogy). However, I could not find any versions of flush that would affect the stored procs based on the documentation. The real danger with your approach is that it relies on an undocumented feature of MySQL. This feature may change without any notice in a future version of MySQL, making an upgrade impossible. – Shadow Mar 14 '18 at 13:01
  • Also, without actually debugging MySQL itself, it is very difficult to say if your approach has any unintended side effects. – Shadow Mar 14 '18 at 13:02
  • @Shadow Thanks for your time. If you wish to post the above as an answer I'll mark as Accepted (if I don't getting anything better soon!), or we can just leave in comments, as you please. – JonBrave Mar 14 '18 at 13:08

1 Answers1

2

The direct delete from the procs table does not seem to take an immediate effect in the current MySQL session.

The flush statement is used in MySQL to make the server reload stuff (changing the privileges directly via update / delete and the flush privileges is probably the best analogy). However, I could not find any versions of flush that would affect the stored procs based on the documentation. So, I do not think there is any way to make the deletion of the stored proc final in the current MySQL session.

The real danger with your direct deletion approach is that it relies on an undocumented feature of MySQL. This feature may change without any notice in a future version of MySQL, making an upgrade impossible. Also, without actually debugging MySQL itself, it is very difficult to say if your approach has any unintended side effects.

For these reasons, I would not recommend to use this approach in a production code. If it is a one-off exercise, then you may get away with it.

Shadow
  • 33,525
  • 10
  • 51
  • 64