Mysql – Error 1304 when creating a procedure after a drop if exists

MySQLstored-procedures

I have some php code that runs queries stored in a database to update some thousand client databases. Now, for the first time, I needed to create a stored procedure because the update is complex. I'm executing the following queries:

DROP PROCEDURE IF EXISTS updateProc100;
DELIMITER $$
CREATE PROCEDURE updateProc100()
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
BEGIN
...
...
END$$
DELIMITER ;
CALL updateProc100();
DROP PROCEDURE updateProc100;

This code is executed the first time each client accesses the system, in its own database, and is executed only one time.

My problem is that I'm receiving error messages from a small number of client systems with the following MySQL error:

SQLSTATE[42000]: Syntax error or access violation: 1304 PROCEDURE updateProc100 already exists

No other procedures with that name exists in any of the databases and even if they existed, they should be dropped by the first statement. The number of error messages is somewhat low, but even so, I don't understand how this can be. I've manually checked each of the databases and no stored procedure exists.

I've googled about this problem, but all the answers that I find say to do what I'm already doing, executing a DROP IF EXISTS before the CREATE.

Does anyone have any idea for the cause of the error?
Thanks

Best Answer

Is every client's updateProc100 identical? Or is that proc generated by code had has code specific to the client?

If it is identical to all clients, suggest creating, but not dropping, a single copy of it in some 'common' DATABASE. Then only do

 CALL common.updateProc100();

If there is something tailored to each client, could it be an argument? And then do prepare+execute to build and run the specific stuff?

Back to what you currently have...

  • Procs are specific to a DATABASE.
  • Is each client specific to that database? That is, are you sure there was a USE (or equivalent) before doing the drop+create+call+drop?
  • What happens at "intervals after" the login?
  • Can a client be logged in from two places?
  • Does the Procedure have an interlock so that it cannot be executed twice?
  • What is the current mechanism for saying "client 'X' has already called 'proc100'"?
Related Question