How do I check if a stored procedure exists before trying to create it in MySQL?

Question

I'm trying to do CREATE PROCEDURE IF NOT EXISTS in MySQL. However it looks like MySQL does not natively support this, which seems like a major oversight to me.

I've created a Spring Batch Java application which contains business schema definitions, which includes both table definitions and the stored procedure definitions. The table definitions are fine, since they say CREATE TABLE IF NOT EXISTS. I would like the stored procedure definitions to be the same, since this business schema script will be run any time the application is launched.

MySQL does have DROP PROCEDURE IF EXISTS, but I worry that running that before a create runs into a possible race condition. What if I've got multiple instances of this application running, and one of the other instances is running one of the stored procedures when I do the DROP and subsequent CREATE? That seems like an all around bad idea. I don't want it to interrupt anything already running.

Someone else must have encountered this same problem by now. What's the solution?

digging, found this, references mysql issue, still digging http://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures — Drew, May 28 '15 at 16:39

Up_One · Answer 1 · 2015-05-28T17:42:46.267

2

Just query the routines table in the information_schema. Example:

select ROUTINE_BODY from routines where ROUTINE_NAME like '%BLA BLA%';

Or maybe create a function to see if the proc exists:

use db_where_you_wanna_see_if_proc_exists;
delimiter $$

CREATE FUNCTION PROC_EXISTS(_proc_name VARCHAR(45))
RETURNS BOOLEAN
DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _exists  TINYINT(1) DEFAULT 0;

    SELECT COUNT(*) INTO _exists
    FROM information_schema.routines 
    WHERE ROUTINE_SCHEMA =  DATABASE()
    AND ROUTINE_NAME =  _proc_name;

    RETURN _exists;

END$$

delimiter ;


SELECT PROC_EXISTS('proc_name') as _exists;

edited May 28 '15 at 17:42

answered May 28 '15 at 17:34

Up_One

5,213
3
33
65

1

So, I already know how to select something to determine whether or not the procedure exists, but the problem I'm running into is: how do I use that result? How do I wrap the CREATE PROCEDURE call inside of an "IF" statement in MySQL? – soapergem May 28 '15 at 17:46

score 1 · Answer 2 · answered Mar 04 '20 at 09:30

1

You could use the following query: SHOW PROCEDURE STATUS WHERE NAME='procedureName' Also check these answers here: List of Stored Procedures/Functions Mysql Command Line

answered Mar 04 '20 at 09:30

Andrei T

511
1
10
16

Uueerdo · Answer 3 · 2015-05-28T16:57:42.460

I would not recommend relying on your application to create your database structure. From long personal experience, it only causes headaches down the line. CREATE TABLE IF NOT EXISTS is simple enough, but eventually you need to "ALTER TABLE if it doesn't match what I expect" and things start getting ugly. That said, you can find stored routines in information_schema.routines.

The only way I can think to get around multiple processes running through this code at the same time is to add another table for use as a sort of lock. Each application executes UPDATE sentinelTable SET beingHandledBy = user() WHERE beingHandledBy IS NOT NULL; followed by SELECT beingHandledBy = user() FROM sentinelTable; the process that gets back a "true" result then continues with the database normalization code, and when finished sets beingHandledBy back to NULL.

Edit: Actually, CONNECTION_ID() might be a better function to use instead of USER(). If you have multiple instances or threads connecting independently from the same machine, they would have the same value for USER().

I saw that they're in information_schema.routines; I also saw they're in mysql.proc, but I've been bashing my head against my desk all morning trying to figure out the syntax to actually be able to use that. It seems like I can't start a query with an IF statement in MySQL, so I'm not sure how to utilize this. — soapergem, May 28 '15 at 16:29
The proper use would be to `SELECT 1 FROM routines WHERE routine_schema = '[mySchema]' AND routine_name = '[theRoutineName]'` and only execute your create if you get an empty resultset. That means two queries, instead of one with a condition in it. (But this doesn't get around the issue of multiple applications passing through this code simultaneously.) — Uueerdo, May 28 '15 at 16:32

How do I check if a stored procedure exists before trying to create it in MySQL?

3 Answers3