CODEWITHSUNDEEP
mysqlstored-procedures
JonBrave
JonBrave

Reputation: 4280

MySQL (5.7) drop stored procedures via mysql.proc

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?

Upvotes: 1

Views: 602

Answers (1)

Shadow
Shadow

Reputation: 34232

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.

Upvotes: 2

Related Questions