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
select sleep(1) into @dummy;
before and after DROP statement.