title: Calling stored procedure sequentially from .sql file
body: <p>I'm stuck here.</p>
  
  <p>I've got a Procedure that I want to run X* times in a row. (*X is couple of thousands times)<br>
  The procedure based on input data does this:<br>
  1. Looks for an actions.id, if not found <code>LEAVE</code>s.<br>
  2. Looks for users.id, if not found, creates one and uses <code>LAST_INSERT_ID()</code>;<br>
  3-5. Looks for summaries.id (3 types, total, daily and monthly), if not found, creates one and uses it's ID.<br>
  6. Once all required ids are collected, <code>INSERT</code>s new row into actions and either updates the summaries rows in a transaction, so if any fails - it does a <code>ROLLBACK</code> - no harm done.<br>
  7. Depending on the outcome <code>SELECT</code>s message.</p>
  
  <pre><code>CREATE PROCEDURE NEW_ACTION(
    IN a_date TIMESTAMP,
    IN u_name VARCHAR(255),
    IN a_name VARCHAR(255),
    IN a_chars INT,
    IN url VARCHAR(255),
    IN ip VARCHAR(15))
  
    lbl_proc: BEGIN
      DECLARE a_id, u_id, us_id, usd_id, usm_id, a_day, a_month, error INT;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET error = 1;
  
      SET error = 0;
      SET a_day = DATE_FORMAT(SUBSTRING(a_date ,1,10), '%Y%m%d');
      SET a_month = SUBSTRING(a_day, 1, 6);
  
      /* 1. RETREIVING action.id */
      SET a_id = (SELECT `id` FROM `actions` WHERE `name` = a_name);
      IF a_id IS NULL THEN
        SELECT 'error';
        LEAVE lbl_proc;
      END IF;
  
      /* 2. RETREIVING users.id */
      SET u_id = (SELECT `id` FROM `users` WHERE `name` = u_name);
      IF u_id IS NULL THEN
        INSERT INTO `users` (name) VALUES (u_name);
        SET u_id = (SELECT LAST_INSERT_ID());
      END IF;
  
      /* 3. RETREIVING user_summaries.id */
      SET us_id = (SELECT `id` FROM `users_summaries` WHERE `user_id` = u_id AND `action_id` = a_id);
      IF us_id IS NULL THEN
        INSERT INTO `users_summaries` (user_id, action_id) VALUES (u_id, a_id);
        SET us_id = (SELECT LAST_INSERT_ID());
      END IF;
  
      /* 4. RETREIVING user_summaries_days.id */
      SET usd_id = (SELECT `id` FROM `users_summaries_days` WHERE `day` = a_day AND `user_id` = u_id AND `action_id` = a_id);
      IF usd_id IS NULL THEN
        INSERT INTO `users_summaries_days` (day, user_id, action_id) VALUES (a_day, u_id, a_id);
        SET usd_id = (SELECT LAST_INSERT_ID());
      END IF;
  
      /* 5. RETREIVING user_summaries_months.id */
      SET usm_id = (SELECT `id` FROM `users_summaries_months` WHERE `month` = a_month AND `user_id` = u_id AND `action_id` = a_id);
      IF usm_id IS NULL THEN
        INSERT INTO `users_summaries_months` (month, user_id, action_id) VALUES (a_month, u_id, a_id);
        SET usm_id = (SELECT LAST_INSERT_ID());
      END IF;
  
      /* 6. SAVING action AND UPDATING summaries */
      SET autocommit = 0;
      START TRANSACTION;
        INSERT INTO `users_actions` (`date`, `user_id`, `action_id`, `chars`, `url`, `ip`) VALUES (a_date, u_id, a_id, a_chars, url, ip);
        UPDATE `users_summaries` SET qty = qty + 1, chars = chars + a_chars WHERE id = us_id;
        UPDATE `users_summaries_days` SET qty = qty + 1, chars = chars + a_chars WHERE id = usd_id;
        UPDATE `users_summaries_months` SET qty = qty + 1, chars = chars + a_chars WHERE id = usm_id;
  
        IF error = 1 THEN
          SELECT 'error';
          ROLLBACK;
          LEAVE lbl_proc;
        ELSE
          SELECT 'success';
          COMMIT;
        END IF;
    END;
  </code></pre>
  
  <p>Now, I've got raw data that I want to feed into this procedure. There's currently about 3000 rows. </p>
  
  <p>I tried all the solutions I knew:</p>
  
  <p>A. <code># mysql -uuser -ppass DB &lt; calls.sql</code> - Using php I've basically created a list of calls like this:</p>
  
  <pre><code>CALL NEW_ACTION('2010-11-01 13:23:00', 'username1', 'actionname1', '100', 'http://example.com/', '0.0.0.0');  
  CALL NEW_ACTION('2010-11-01 13:23:00', 'username2', 'actionname1', '100', 'http://example.com/', '0.0.0.0');  
  CALL NEW_ACTION('2010-11-01 13:23:00', 'username1', 'actionname2', '100', 'http://example.com/', '0.0.0.0');  
  ...
  </code></pre>
  
  <p>This fails always (tried few times) at row 452 where it found two summary IDs (step 3).<br>
  I thought this could be due to the fact that earlier (rows 375-376) there are calls for the same user for the same action.<br>
  As if mysql didn't update tables in time, so the summary row created in <code>CALL</code> from line 375 isn't yet visible when line 376 gets executed - therefore creating another summary line.<br>
  Tought I'd try delaying calls...</p>
  
  <p>B. Using mysql's <code>SLEEP(duration)</code>.<br>
  This didn't change anything. Execution stops at the very same CALL again.</p>
  
  <p>I'm out of ideas now.<br>
  Suggestions and help hugely appreciated.</p>
  
  <p><strong><em>NOTE</em></strong>: action names and user names repeat.  </p>
  
  <p>PS. Bear in mind this is one of my first procedures ever written.<br>
  PS2. Running mysql 5.1.52-community-log 64bit (Windows 7U), PHP 5.3.2 and Apache 2.2.17</p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>I've removed PHP related part of question to a <a href="https://stackoverflow.com/questions/4285837/calling-stored-procedure-in-foreach-loop-only-first-executed">separate question here</a>.</p>
  
  <hr>
  
  <p><strong>EDIT2</strong></p>
  
  <p>Ok, I've deleted the first 200 calls from the .sql file. For some reason it went fine past the previous line that was stopping execution. Now it stopped at row 1618.<br>
  This would mean, that at one point a newly <code>INSERTed</code> summary row is no visible for a moment, therefore when it happens that one of the following iterations want to <code>SELECT</code> it, it's not yet accessible for them. Is that a MySQL bug?</p>
  
  <hr>
  
  <p><strong>EDIT3</strong></p>
  
  <p>Now there's another interesting thing I noticed. I investigated where two users_summaries get created. This happens (not always, but if, then it is) when there are two CALLs referring to the same <code>user</code> and <code>action</code> in close proximity. They could be next to each other or separated by 1 or 2 different calls.</p>
  
  <p>If I move one of them (within .sql file) like 50-100 rows lower (executed earlier) than it's fine. I even managed to make the .sql file work as a whole. But this still doesn't really solve the problem. With 3000 rows it's not that bad, but if I had 100000, I'm lost. I can't rely on manual tweaks to .sql file.</p>