Update MySQL stored procedures after making structural changes to the database

Question

I would appreciate any help on this problem. I am running a website which uses MySQL database. I built the database model in MySQL Workbench and all SQL-queries are stored procedures. The problem is that when I make structural changes to the database, I do not know which stored procedures get affected, so I have either to manually list through all the procedures and manually make changes, or use the try-and-error method. The question - is there a way to automate stored procedures update process. I want to know exactly what stored procedures get affected when making certain structural changes to the database. If it is not possible with MySQL, then is it possible with other databases? (open-source or commercial). Thank you.

Answer 1

In Oracle a Stored Procedure does become invalid if any dependent object is altered or becomes invalid. The dependent objects include other referenced Stored Procedures, Tables, Packages etc.

To find the count of all the objects that are currently invalid one can execute

SELECT object_type, count(*)
FROM user_objects
WHERE status = 'INVALID'
GROUP BY object_type

A DBA can simply run a script ($ORACLE_HOME/rdbms/admin/utlrp.sql) to compile all the invalid objects (views, procedures, packages, functions, triggers etc.). Third party tools for Oracle (like Toad) provide a similar functionality through GUI.

Answer 2

MySQL will not give you information back on what procedures were affected by any changes, until you actually run the procedure (which would result in an error). The INFORMATION_SCHEMA has a table ROUTINES where MySQL provides information about stored routines (both procedures and functions):

USE INFORMATION_SCHEMA;
DESCRIBE ROUTINES;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM ROUTINES;

Haven't done it myself, but I'm sure you could write a SQL query that would search against a changed column name in the ROUTINE_DEFINITION rows and provide you with the list of all procedures you'd need to modify.

Remember, that any changes to a procedure needs to be done using DROP PROCEDURE / CREATE PROCEDURE commands. You cannot make any changes directly in the INFORMATION_SCHEMA database.

More information under:

• MySQL Reference Manual: 19. INFORMATION_SCHEMA Tables
• MySQL Reference Manual: 19.8. The INFORMATION_SCHEMA ROUTINES Table