Mysql Question posted in
The official MySQL documentation can be found here.

Are data in temporary table in stored procedure of mysql not cleared automatically?

Using temporary table in stored procedure of mysql, looks like that data in temporary table are added any procedure calling without clearing the data :

DROP procedure IF EXISTS sp_fillUserTasksReport;

create procedure sp_fillUserTasksReport(
    OUT out_returnCode bigint unsigned
)
BEGIN
    DECLARE userId int unsigned;
    DECLARE userName varchar(100);
    DECLARE userEmail varchar(100);
    DECLARE activeTasksCount int unsigned;
    DECLARE completedTasksCount int unsigned;
    DECLARE hasActiveTeamLeadTasks int unsigned;
    DECLARE taskJson json;

    DECLARE done INT DEFAULT FALSE;
    DECLARE usersCursor cursor
        for SELECT users.id, users.name, users.email
            FROM users
            ORDER BY users.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS tempUserTasksReport
    (
        user_id                    bigint unsigned,
        user_name                  varchar(100),
        user_email                 varchar(100),
        active_tasks_count         int unsigned,
        completed_tasks_count      int unsigned,
        has_active_team_lead_tasks int unsigned,
        assigned_tasks             json DEFAULT NULL
    );

    -- DELETE FROM tempUserTasksReport;
    -- I HAD TO UNCOMMENT ROW ABOVE TO CLEAR DATA - BUT THAT SEEMS not very good decision

    SET out_returnCode := 0;

    open usersCursor;
    usersLoop:
    loop
        -- get all children of in_taskId and check them
        fetch usersCursor into userId, userName, userEmail;
        IF done THEN
            LEAVE usersLoop;
        END IF;

        select count(*)
        into activeTasksCount
        from users
        where userId in (select distinct task_user.user_id
                         from task_user,
                              tasks
                         where tasks.status = 'T'
                           and task_user.task_id = tasks.id);

        select count(*)
        into completedTasksCount
        from users
        where userId in (select distinct task_user.user_id
                         from task_user,
                              tasks
                         where tasks.status = 'D'
                           and task_user.task_id = tasks.id);

        select count(*)
        into hasActiveTeamLeadTasks
        from users
        where userId in (select distinct task_user.user_id
                         from task_user,
                              tasks
                         where tasks.status = 'T'
                           and task_user.task_id = tasks.id
                           and task_user.team_lead = 1);

        SELECT JSON_ARRAYAGG(JSON_OBJECT('id', task_user.task_id, 'title', tasks.title))
        INTO taskJson
        FROM task_user,
             tasks
        WHERE task_user.user_id = userId
          AND task_user.task_id = tasks.id;

        -- Insert into the temporary table
        INSERT INTO tempUserTasksReport (user_id, user_name, user_email, active_tasks_count, completed_tasks_count,
                                         has_active_team_lead_tasks, assigned_tasks)
        VALUES (userId, userName, userEmail, activeTasksCount, completedTasksCount, hasActiveTeamLeadTasks, taskJson);

        SET out_returnCode := out_returnCode + 1;

    end loop usersLoop;
    close usersCursor;


    INSERT INTO user_tasks_report (user_id, user_name, user_email, active_tasks, completed_tasks, has_active_team_lead_tasks, assigned_tasks, created_at)
   select user_id, user_name, user_email, active_tasks_count, completed_tasks_count, has_active_team_lead_tasks, assigned_tasks, NOW() from tempUserTasksReport;

END;

Not sure, if deleting the table data in the beginning of the procedure is good decision ?

8.0.34-0ubuntu0.22.04.1

Thanks in advance!

Tags:

2

Answers


  1. 0

    The docs explain how temporary tables work.

    You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

    A session is a connection, so you would need to close the connection for mysql to remove the table. Or alternatively, as you mentioned, to drop the table if it exists at the start of the script if the connection is reused.

    Login or Signup to reply.
  2. 0

    Augusto has already answered your question about the lifespan of a temporary table, so this addresses the other issues with your current approach.

    You should steer clear of the very old comma separated tables list in favour of the more explicit join syntax.

    As already pointed out by Shadow in the comments, you appear to be overcomplicating things. Furthermore, unless I am completing misunderstanding what is going on, your count queries are incorrect.

    For each user, your are counting all users if current userId is in the subquery result. Let’s take your first count query as an example:

    select count(*)
into activeTasksCount
from users
where userId in (select distinct task_user.user_id
                  from task_user,
                       tasks
                  where tasks.status = 'T'
                    and task_user.task_id = tasks.id);

    The outer query is assigning the count of all users to the variable activeTasksCount if userId (current loop iteration userId var) has at least 1 active task. Surely, you want the count of active tasks assigned to userId?

    select count(*)
into activeTasksCount
from task_user tu
join tasks t on tu.task_id = t.id
where t.status = 'T'
and tu.user_id = userId;

    As Shadow pointed out, your separate count queries can be combined using conditional aggregation, something like:

    SELECT
    u.id AS userID, u.name AS userName, u.email AS userEmail,
    SUM(t.status = 'T') AS activeTasksCount,
    SUM(t.status = 'D') AS completedTasksCount,
    SUM(t.status = 'T' AND tu.team_lead = 1) AS hasActiveTeamLeadTasks,
    JSON_ARRAYAGG(JSON_OBJECT('id', t.id, 'title', t.title)) AS taskJson
FROM users u
LEFT JOIN task_user tu ON u.id = tu.user_id
LEFT JOIN tasks t ON tu.task_id = t.id
GROUP BY u.id
ORDER BY u.id;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search