March 14, 2009 at 16:23MySQL stored procedures
On duty, you have to deal deeply with the subject.
Unfortunately, this is not the best invention of mankind, so sometimes you have to drive crutches in order to somehow use this thing.
So, there is a stored procedure created by user A.
We give access to user B.
GRANT EXECUTE ON PROCEDURETO ;
User B can use this procedure. Everyone is happy, music, champagne.
After the buffet dinner, user A recalls that it would be nice to tweak the procedure a bit so that it works faster.
And corrects. The next morning, user B discovers that he can no longer perform this procedure.
Everyone is in mourning, millions of losses, the project developers are fired, the curtain.
What happened?
The fact is that people from MySQL for some reason did not think that the need to change the procedure arises very often.
And they did not make it possible to change the procedure code. ALTER PROCEDUREIt allows you to change some unintelligible settings and does not allow you to change the procedure body. Instead, the Muscle guys suggest doing DROP and then CREATE with the new text: “ you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure . " And when you delete a procedure, all GRANTs on it, of course, disappear.
Could something be done?
As it turned out, you can.
The text of the procedures is stored in the proc table of the mysql base .
And although the comrades from Muscle do not recommend not to climb into this table with their hands, they don’t provide any other choice.
In this table, we are interested in two fields - body and body_utf8.
They contain the text of our procedure.
We take for the trunk of our DBA and do UPDATE on these fields, entering the new text of the procedure in them.
It would seem that it’s time to have a banquet again about the successful fight against Muscle.
But no. The procedure will start to work in a new way only for new sessions of user B.
But what if the connections are permanent and you need to make the changes available for them?
And here the same ALTER PROCREDURE will help .
A trivial change in the text of a comment on a procedure (for example, changing its revision number) makes changes available for all sessions:
ALTER PROCEDURE COMMENT 'r1.1';
Unfortunately, this is not the best invention of mankind, so sometimes you have to drive crutches in order to somehow use this thing.
So, there is a stored procedure created by user A.
We give access to user B.
GRANT EXECUTE ON PROCEDURE
User B can use this procedure. Everyone is happy, music, champagne.
After the buffet dinner, user A recalls that it would be nice to tweak the procedure a bit so that it works faster.
And corrects. The next morning, user B discovers that he can no longer perform this procedure.
Everyone is in mourning, millions of losses, the project developers are fired, the curtain.
What happened?
The fact is that people from MySQL for some reason did not think that the need to change the procedure arises very often.
And they did not make it possible to change the procedure code. ALTER PROCEDUREIt allows you to change some unintelligible settings and does not allow you to change the procedure body. Instead, the Muscle guys suggest doing DROP and then CREATE with the new text: “ you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure . " And when you delete a procedure, all GRANTs on it, of course, disappear.
Could something be done?
As it turned out, you can.
The text of the procedures is stored in the proc table of the mysql base .
And although the comrades from Muscle do not recommend not to climb into this table with their hands, they don’t provide any other choice.
In this table, we are interested in two fields - body and body_utf8.
They contain the text of our procedure.
We take for the trunk of our DBA and do UPDATE on these fields, entering the new text of the procedure in them.
It would seem that it’s time to have a banquet again about the successful fight against Muscle.
But no. The procedure will start to work in a new way only for new sessions of user B.
But what if the connections are permanent and you need to make the changes available for them?
And here the same ALTER PROCREDURE will help .
A trivial change in the text of a comment on a procedure (for example, changing its revision number) makes changes available for all sessions:
ALTER PROCEDURE