MySQL 일치하지 않는 쿼리로 인해 임시 테이블(빈 결과 집합)이 있는 절차가 발생함

MySQL 일치하지 않는 쿼리로 인해 임시 테이블(빈 결과 집합)이 있는 절차가 발생함

저도 이 질문을 dba.stack exchange에 올렸지만, 더 많은 답변을 기대하며 여기에 올립니다.

당사 서버는 AWS EC2 인스턴스에서 10.0.30-MariaDB를 실행합니다.우리는 여러 개의 온도 테이블을 만들고 그들 사이를 결합하여 최종 결과를 도출하는 저장 프로시저를 가지고 있습니다.이 절차를 처음(새로운 연결에서) 호출하면 항상 결과가 나타납니다.다른 매개 변수를 사용하여 이 절차를 다시 호출하면 해당 연결에 대한 결과 집합이 항상 비어 있습니다.새 연결을 시작하고 두 번째 통화를 다시 수행하면 이 통화는 올바른 결과를 반환하지만 첫 번째 통화는 비어 있습니다.아래의 간소화된 도식을 참조:

Connection 1:
call my_procedure("a"); > 1 row returned
call my_procedure("b"); > 0 rows returned
call my_procedure("a"); > 1 row returned

Connection 2:
call my_procedure("b"); > 1 row returned
call my_procedure("a"); > 0 rows returned
call my_procedure("b"); > 1 row returned

우리는 항상 이런 식으로 행동을 재현할 수 있습니다.

또한 절차를 변경하면(댓글만 추가해도) 다음 호출은 새 연결에 호출된 것처럼 반응합니다.그래서 우리가 먼저 "a"라고 부르면 결과가 나오지만, "b"는 절대로 작동하지 않고 비자와 반대입니다.

우리 프로시저는 유효한 sql이며 정확한 데이터를 반환합니다. 온도 테이블은 올바르게 삭제됩니다. 그러나 어떤 이유로 인해 데이터는 반환되지 않습니다.

우리는 내부의 '한계'에 부딪혔다는 인상을 받습니다.우리는 이미 다양한 시도를 해보았지만, 어디를 찾아야 할지, 디버그하는 방법을 선택할 수 없게 되었습니다.

그러한 상황을 디버그하는 방법에 대한 조언은 환영합니다.

절차는 아래를 참조하십시오.제가 좀 단순화를 시켰습니다.dba.stack exchange post에서는 대체 전체 버전을 확인할 수 있습니다.

PROCEDURE `select_products`(IN arg_uuid VARCHAR(36),
                            IN arg_application VARCHAR(36),
                            IN arg_app_version VARCHAR(20),
                            IN arg_installation VARCHAR(50),
                            IN arg_user VARCHAR(36),
                            IN arg_product VARCHAR(36),
                            IN arg_license VARCHAR(36),
                            IN arg_type VARCHAR(10),
                            IN arg_code VARCHAR(255),
                            IN arg_group VARCHAR(100))
BEGIN

  DECLARE var_b_user INT DEFAULT NULL; 

  SET var_b_user = (select bu.id from b_user bu JOIN user u on u.id = bu.user where u.`key` = arg_user limit 1);


  /********************* PLTA **********************/

  DROP TEMPORARY TABLE IF EXISTS plta;
  CREATE TEMPORARY TABLE plta (product_id INT, license_id INT, token_id INT, activation_id INT, INDEX(product_id, license_id))
  SELECT
    l.product as product_id, l.id as license_id, lt.token as token_id, null as activation_id
  FROM
    license l,
    license_token lt,
    activation a,
    `user` u
  WHERE
        a.current = TRUE
    AND l.id = lt.license
    AND a.`user` = u.id
    AND a.installation = arg_installation
    AND u.`key` = arg_user
    AND lt.token = a.token
    group by product_id, license_id, token_id;

    UPDATE plta, activation a, `user` u
    SET activation_id = a.id
    WHERE
        u.id = a.user
    and a.current = TRUE
    AND u.`key` = arg_user
    AND a.installation = arg_installation
    AND a.license = plta.license_id
    AND a.token = plta.token_id;


  /********************* TMP_PP **********************/

  DROP TEMPORARY TABLE IF EXISTS tmp_pp;
  CREATE TEMPORARY TABLE tmp_pp (product INT,INDEX(product))
  SELECT DISTINCT
    p.id AS product
  FROM
    product p
  JOIN
    application n
  ON
       p.application = n.id
   AND n.`key` = arg_application
  LEFT JOIN
    (
      SELECT
        gp.product
      FROM
        product_group__product gp,
        product_group g
      WHERE
            gp.enabled = TRUE
        AND gp.product_group = g.id
        AND g.`key` = arg_group
     ) g
   ON
     p.id = g.product

  WHERE
        (   p.`key` = arg_product
         OR (    arg_product IS NULL
             AND p.enabled = TRUE))
    AND (   g.product IS NOT NULL
         OR arg_group IS NULL)
    AND (   p.app_version IS NULL
         OR arg_app_version IS NULL
         OR (INET_ATON(p.app_version) <= INET_ATON(arg_app_version)));

  select * from tmp_pp;


  /********************* TMP_LT **********************/

  DROP TEMPORARY TABLE IF EXISTS tmp_lt;
  CREATE TEMPORARY TABLE tmp_lt (license INT,token INT,INDEX(license),INDEX(token))
    SELECT
      lt.license,
      t.id as token
    FROM
      license_token lt,
      token t
    WHERE
          lt.token = t.id
      AND t.`type` = arg_type
      AND (  (    arg_code IS NOT NULL
              AND t.code = arg_code)
           OR arg_type = 'free');


  select * from tmp_lt;


  /********************* PLT_PROD **********************/

  SELECT
    p.id AS product,
    l.id AS license,
    tmp_lt.token,
    NULL AS activation,
    FALSE AS license_auto_activation
  FROM
    product p
  JOIN
    tmp_pp
  ON
    p.id = tmp_pp.product
  JOIN
    license l
  ON
        (   l.`key` = arg_license 
         OR arg_license IS NULL)
    AND l.enabled = TRUE
    AND l.product = p.id
    AND (   l.app_version IS NULL
         OR arg_app_version IS NULL
         OR (INET_ATON(l.app_version) <= INET_ATON(arg_app_version))) 
  LEFT JOIN
    tmp_lt
  ON
    l.id = tmp_lt.license  
  WHERE
     (   arg_type IS NULL
         OR arg_type = 'auto'
         OR tmp_lt.token IS NOT NULL);

END

또한 주목해야 할 '재미있는' 것은 만약 우리가 마지막 WHERE를 단순한 것으로 바꾼다면.WHERE arg_type = 'auto';이 문제는 더 이상 발생하지 않습니다(물론 결과는 동일하지 않습니다).

언급URL : https://stackoverflow.com/questions/43363500/mysql-inconsistent-query-results-in-procedure-with-temp-tables-empty-resultset