从.sql文件顺序调用存储过程

Question

我被困在这里了。

我有一个程序，我想要连续运行X次。(*X是几千倍)

基于输入数据的过程是这样做的：

LEAVEs.

• Looks

1. 查找actions.id，如果未找到actions.id，则为users.id查找actions.id，如果未找到，则创建actions.id并使用LAST_INSERT_ID()；

3-5。查找summaries.id (3种类型，总计、每日和每月)，如果未找到，则创建一个类型并使用它的ID.

• ，一旦收集了所有必需的ID，INSERT的新行将变为操作，并更新事务中的汇总行，因此，如果有任何失败，则不会对结果SELECT的消息造成损害( ROLLBACK )。

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;

现在，我有了原始数据，我想要输入到这个过程中。目前大约有3000行。

我尝试了所有我知道的解决方案：

A. # mysql -uuser -ppass DB < calls.sql --使用php，我基本上创建了如下所示的调用列表：

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');  
...

这在第452行总是失败(尝试过几次)，在第452行中它找到了两个摘要ID(步骤3)。

我认为这可能是因为前面(第375-376行)有对同一个用户的相同操作的调用。

就好像mysql没有及时更新表一样，因此在执行第376行时，CALL中从第375行创建的摘要行还不可见--因此创建了另一个摘要行。

我会尝试推迟电话..。

B.使用mysql的SLEEP(duration)。

这并没有改变什么。执行再次在相同的调用时停止。

我现在没有主意了。

建议和帮助非常感谢。

注意：：操作名称和用户名重复。

PS。记住，这是我写的第一批程序之一。

PS2。运行mysql 5.1.52-社区-日志64位(Windows 7U)、PHP 5.3.2和Apache 2.2.17

编辑

我已经将问题的相关部分移到了一个separate question here中。

EDIT2

好的，我已经删除了.sql文件中的前200个调用。出于某种原因，它通过了之前停止执行的行。现在它停在第1618排。

这意味着，有一段时间新的INSERTed摘要行是不可见的，因此，当发生以下迭代之一想要对其进行SELECT时，它们还无法访问它。那是MySQL的错误吗？

EDIT3

还有一件有趣的事我注意到了。我调查了在哪里创建了两个users_summaries。当有两个调用引用相同的user和action时(并不总是发生，但如果是，那么就发生了)。它们可以是相邻的，也可以是一个或两个不同的电话。

如果我将其中的一个(在.sql文件中)移动为50-100行，比它更低(更早地执行)，就可以了。我甚至设法使.sql文件作为一个整体工作。但这仍然不能真正解决问题。有3000行也没那么糟，但如果我有100000行的话，我就迷路了。我不能依靠手动调整.sql文件。

Answer 1

这不是真正的解决方案，而是一种解决办法。

为了澄清这一点，汇总表将id列作为PRIMARY KEY，在user_id和action_id列中都有AUTO_INCREMENT选项和索引。

我的研究表明，虽然我的过程是寻找一个条目，在某些情况下使用WHERE user_id = u_id AND action_id = a_id存在，但它没有发现它会导致用相同的user_id和action_id值插入新行--这是我不想要的。

调试过程表明，我正在寻找的摘要行(虽然不能用WHERE user_id = u_id AND action_id = a_id条件访问)在调用它的id - PRIMARY KEY时正确返回。

有了这个发现，我决定将id列的格式从UNASIGNED INT With AUTO_INCEREMENT改为CHAR(32)，其中包括：

<user_id>|<action_id>

这意味着我确切地知道我想要的行的id是什么，甚至在它存在之前。这确实解决了这个问题。它还使我能够使用INSERT ... ON DUPLICATE KEY UPDATE ...构造。

以下是我更新的程序：

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))
  SQL SECURITY INVOKER

lbl_proc: BEGIN
    DECLARE a_id, u_id, a_day, a_month, error INT;
    DECLARE us_id, usd_id, usm_id CHAR(48);
    DECLARE sep CHAR(1);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET error = 1;

    SET sep = '|';
    SET error = 0;
    SET a_day = DATE_FORMAT(SUBSTRING(a_date ,1,10), '%Y%m%d');
    SET a_month = SUBSTRING(a_day, 1, 6);

    /* RETREIVING action.id */
    SET a_id = (SELECT `id` FROM `game_actions` WHERE `name` = a_name);
    IF a_id IS NULL THEN
      SELECT 'error';
      LEAVE lbl_proc;
    END IF;

    /* RETREIVING users.id */
    SET u_id = (SELECT `id` FROM `game_users` WHERE `name` = u_name);
    IF u_id IS NULL THEN
      INSERT INTO `game_users` (name) VALUES (u_name);
      SET u_id = LAST_INSERT_ID();
    END IF;

    /* SETTING summaries ids */
    SET us_id = CONCAT(u_id, sep, a_id);
    SET usd_id = CONCAT(a_day, sep, u_id, sep, a_id);
    SET usm_id = CONCAT(a_month, sep, u_id, sep, a_id);

    /* SAVING action AND UPDATING summaries */
    SET autocommit = 0;
    START TRANSACTION;
      INSERT INTO `game_users_actions` (`date`, `user_id`, `action_id`, `chars`, `url`, `ip`)
        VALUES (a_date, u_id, a_id, a_chars, url, ip);
      INSERT INTO `game_users_summaries` (`id`, `user_id`, `action_id`, `qty`, `chars`)
        VALUES (us_id, u_id, a_id, 1, a_chars)
        ON DUPLICATE KEY UPDATE qty = qty + 1, chars = chars + a_chars;
      INSERT INTO `game_users_summaries_days` (`id`, `day`, `user_id`, `action_id`, `qty`, `chars`)
        VALUES (usd_id, a_day, u_id, a_id, 1, a_chars)
        ON DUPLICATE KEY UPDATE qty = qty + 1, chars = chars + a_chars;
      INSERT INTO `game_users_summaries_months` (`id`, `month`, `user_id`, `action_id`, `qty`, `chars`)
        VALUES (usm_id, a_month, u_id, a_id, 1, a_chars)
        ON DUPLICATE KEY UPDATE qty = qty + 1, chars = chars + a_chars;   

      IF error = 1 THEN
        SELECT 'error';
        ROLLBACK;
        LEAVE lbl_proc;
      ELSE
        SELECT 'success';
        COMMIT;
      END IF;
  END

无论如何，我仍然认为MySQL中存在某种bug，但我认为问题已经解决了。