从.sql文件顺序调用存储过程 [英] Calling stored procedure sequentially from .sql file

问题描述

我被困在这里.

我有一个要连续运行X *次的过程. (* X是数千次)
基于输入数据的过程将执行以下操作:
1.寻找一个action.id(如果找不到LEAVE s.
). 2.查找users.id(如果找不到)，创建一个并使用LAST_INSERT_ID();
3-5.查找summaries.id(共有3种类型，总计，每日和每月)，如果找不到，则创建一个并使用其ID.
6.收集完所有必需的ID后，INSERT会将新行放入操作中，或者更新事务中的摘要行，因此，如果有任何失败-它会执行ROLLBACK-不会造成危害.
7.取决于结果SELECT消息.

I've got a Procedure that I want to run X* times in a row. (*X is couple of thousands times)
The procedure based on input data does this:
1. Looks for an actions.id, if not found LEAVEs.
2. Looks for users.id, if not found, creates one and uses LAST_INSERT_ID();
3-5. Looks for summaries.id (3 types, total, daily and monthly), if not found, creates one and uses it's ID.
6. Once all required ids are collected, INSERTs new row into actions and either updates the summaries rows in a transaction, so if any fails - it does a ROLLBACK - no harm done.
7. Depending on the outcome SELECTs message.

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行.

Now, I've got raw data that I want to feed into this procedure. There's currently about 3000 rows.

我尝试了所有已知的解决方案:

I tried all the solutions I knew:

A. # mysql -uuser -ppass DB < calls.sql-使用php，我基本上创建了这样的调用列表:

A. # mysql -uuser -ppass DB < calls.sql - Using php I've basically created a list of calls like this:

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行中找到了两个摘要ID(第3步).
我认为这可能是由于以下事实:较早的(第375-376行)调用同一用户执行相同的操作.
就像mysql没有及时更新表一样，因此当执行第376行时，在CALL中从375行创建的摘要行尚不可见-因此创建了另一条摘要行. 我想尝试延迟通话...

This fails always (tried few times) at row 452 where it found two summary IDs (step 3).
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.
As if mysql didn't update tables in time, so the summary row created in CALL from line 375 isn't yet visible when line 376 gets executed - therefore creating another summary line.
Tought I'd try delaying calls...

B.使用mysql的SLEEP(duration).
这并没有改变任何东西.执行再次在相同的CALL处停止.

B. Using mysql's SLEEP(duration).
This didn't change anything. Execution stops at the very same CALL again.

我现在没主意了.
建议和帮助深表感谢.

I'm out of ideas now.
Suggestions and help hugely appreciated.

注意 :重复操作名称和用户名称.

NOTE: action names and user names repeat.

PS.请记住，这是我编写的第一个程序.
PS2.运行mysql 5.1.52-community-log 64位(Windows 7U)，PHP 5.3.2和Apache 2.2.17

PS. Bear in mind this is one of my first procedures ever written.
PS2. Running mysql 5.1.52-community-log 64bit (Windows 7U), PHP 5.3.2 and Apache 2.2.17

编辑

我已将问题的PHP相关部分移至单独的问题在这里.

I've removed PHP related part of question to a separate question here.

EDIT2

好的，我已经从.sql文件中删除了前200个调用.由于某种原因，它顺利通过了上一行停止执行的行.现在它停在第1618行.
这意味着，在某个时刻，新的INSERTed摘要行暂时不会显示，因此，当以下迭代之一想要对其进行SELECT时，它们尚无法访问.那是MySQL的错误吗?

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.
This would mean, that at one point a newly INSERTed summary row is no visible for a moment, therefore when it happens that one of the following iterations want to SELECT it, it's not yet accessible for them. Is that a MySQL bug?

EDIT3

现在，我注意到了另外一件有趣的事情.我调查了创建两个users_summaries的位置.当两个CALL紧密地引用相同的user和action时，就会发生这种情况(并非总是如此，如果是，那么会发生).它们可以彼此相邻，也可以被1或2个不同的电话隔开.

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 user and action in close proximity. They could be next to each other or separated by 1 or 2 different calls.

如果我将其中之一(在.sql文件中)移动的位置比正常情况低50-100行(较早执行).我什至设法使.sql文件作为一个整体工作.但这仍然不能真正解决问题.有3000行，还不错，但是如果我有100000行，那我就迷路了.我不能依靠手动调整.sql文件.

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.

推荐答案

这并不是真正的解决方案，而是一种解决方法.

This isn't really a solution, but a workaround.

只需澄清一下，汇总表的id列为PRIMARY KEY，带有AUTO_INCREMENT选项，并且在user_id和action_id列上都有索引.

Just to clarify, summary tables had id column as PRIMARY KEY with AUTO_INCREMENT option and indexes on both user_id and action_id column.

我的调查表明，尽管我的程序正在寻找在某些情况下使用WHERE user_id = u_id AND action_id = a_id 存在的条目，但并未发现它导致使用相同的user_id和action_id值-我不想要的东西.

My investigation showed that although my procedure was looking for an entry that existed using WHERE user_id = u_id AND action_id = a_id in certain situations it didn't find it causing new row being inserted with the same user_id and action_id values - something I did not want.

调试过程表明，我正在寻找的摘要行，尽管不能通过WHERE user_id = u_id AND action_id = a_id条件访问，但在调用它的id-PRIMARY KEY时已正确返回.
通过这个发现，我决定将id列的格式从UNASIGNED INT和AUTO_INCEREMENT更改为CHAR(32)，其组成为:

Debugging the procedure showed that the summary row I was looking for, although not accessible with WHERE user_id = u_id AND action_id = a_id condition, was properly returned when calling it's id - PRIMARY KEY.
With this find I decided to change format of id column, from UNASIGNED INT with AUTO_INCEREMENT to a CHAR(32) which consisted of:

<user_id>|<action_id>

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

This meant that I knew exactly what the id of the row I wanted is even before it existed. This solved the problem really. It also enabled me to use INSERT ... ON DUPLICATE KEY UPDATE ... construct.

在我更新的程序下面:

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中存在某种错误，但我认为问题已解决.

Anyway, I still think there's some kind of a bug in MySQL, but I consider problem solved.

这篇关于从.sql文件顺序调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！