1

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

3
  • I don't understand... does This code is executed the first time each client accesses the system, in its own database, and is executed only one time. means that this code is executed when NEW user accesses his DB for the first time, is it? If so, when DB is created? it already exists when user is logged in, or it is created when he logged in for the first time? If it exists, does default DB is set correctly for this user? there is no chance that no database is set as current when user logged in?
    – Akina
    Commented Aug 8, 2018 at 11:44
  • As a trick try to insert select sleep(1) into @dummy; before and after DROP statement.
    – Akina
    Commented Aug 8, 2018 at 11:54
  • @Akina, every time a cliente logs in the system, and in intervals after that, the system checks if it has any update to do to the client database. If it has, the update is execute only one time, and marked as updated for that client. This is a system we developed to avoid having to do a massive update to several thousand databases at the same time. I have though of a delay, will try and see, thanks. Commented Aug 8, 2018 at 15:14

1 Answer 1

0

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'"?
7
  • The proc is slightly differente in each db, so I'm unable to store it centrally. The choice to use a stored proc is that this specific update needs to go through each record of several tables and the updating service has no flow control. Each client has his own database. The "intervals after login" means that with each web request, the system checks to see if it has any update to do, but only if the last check was more than X minutes ago. When an update is to run, mechanisms exist that make all other requests (from anywhere) wait that the current update is complete, as to avoid concurrency. Commented Aug 29, 2018 at 14:48
  • @MárioPereira - is the anti-concurrency code inside the proc? Or outside the DROP-CREATE-CALL-PROC?
    – Rick James
    Commented Aug 29, 2018 at 15:00
  • The anti-concurrency code is part of the php system that runs the updates, completly outside of the sql code for each update. Commented Aug 29, 2018 at 15:02
  • Can the client login twice simultaneously?
    – Rick James
    Commented Aug 29, 2018 at 15:05
  • It can, but will be put in stand-by if any update is already running, before any sql code related to updates is run. Commented Aug 29, 2018 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.