MySQL stored procedures
On duty, you have to deeply deal with the subject.
Unfortunately, this is not the best invention of mankind, so sometimes you have to drive crutches to at least somehow use this thing.
So, there is a stored procedure created by user A.
We give it access to user B.
GRANT EXECUTE ON PROCEDURE <procedure name> TO <B>;
User B can use this procedure. Everyone is happy, music, champagne.
After the reception, user A remembers that it would be nice to tweak the procedure a bit to make it work faster.
And corrects. The next morning, user B discovers that he can no longer perform this procedure.
All in mourning, the loss of millions, 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 occurs very often.
And did not make it possible to change the procedure code. ALTER PROCEDURE allows you to change some little intelligible settings and does not change the body of the procedure. Instead, the guys from Muscoul offer to do her a DROP and then CREATE with a new text: “ you cannot use this procedure; to make such changes . And when the procedure is deleted, all the GRANTs on it, of course, disappear.
Could anything have been done?
As it turned out, you can.
The text of the procedures is stored in the proc table of the mysql database.
And although the comrades from Muskul do not recommend not to go into this table with their hands, they do not give 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 in them the new text of the procedure.
It would seem that time again to organize a buffet table on the occasion of a successful struggle with the Muscle.
But no. The procedure will begin to work in a new way only for new sessions of user B.
But what if connections are permanent and you need to make changes available to them?
And here that ALTER PROCREDURE will help.
A trivial change to the text of a comment to a procedure (for example, a change in its revision number) makes changes available for all sessions:
ALTER PROCEDURE <procedure name> COMMENT 'r1.1';
Unfortunately, this is not the best invention of mankind, so sometimes you have to drive crutches to at least somehow use this thing.
So, there is a stored procedure created by user A.
We give it access to user B.
GRANT EXECUTE ON PROCEDURE <procedure name> TO <B>;
User B can use this procedure. Everyone is happy, music, champagne.
After the reception, user A remembers that it would be nice to tweak the procedure a bit to make it work faster.
And corrects. The next morning, user B discovers that he can no longer perform this procedure.
All in mourning, the loss of millions, 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 occurs very often.
And did not make it possible to change the procedure code. ALTER PROCEDURE allows you to change some little intelligible settings and does not change the body of the procedure. Instead, the guys from Muscoul offer to do her a DROP and then CREATE with a new text: “ you cannot use this procedure; to make such changes . And when the procedure is deleted, all the GRANTs on it, of course, disappear.
Could anything have been done?
As it turned out, you can.
The text of the procedures is stored in the proc table of the mysql database.
And although the comrades from Muskul do not recommend not to go into this table with their hands, they do not give 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 in them the new text of the procedure.
It would seem that time again to organize a buffet table on the occasion of a successful struggle with the Muscle.
But no. The procedure will begin to work in a new way only for new sessions of user B.
But what if connections are permanent and you need to make changes available to them?
And here that ALTER PROCREDURE will help.
A trivial change to the text of a comment to a procedure (for example, a change in its revision number) makes changes available for all sessions:
ALTER PROCEDURE <procedure name> COMMENT 'r1.1';
Source: https://habr.com/ru/post/54436/
All Articles