Project 'Mutua16s' always appears in the obsolete project list

During the purge process, the alert mail for the Mutua16s project has been sent 19 times. There should be only one mail per project.

changed due date to February 08, 2021

changed milestone to %V3.2.8

added Dysfunction label

First question, does the problem come from the user?

Apache access log:

• 138.102.213.101 - - [29/Jan/2021:18:19:15 +0100] "GET /index.php?eID=tx_nG6&type=refresh_purge_list&max_retention_date=31/01/2021&filter_space=longterm_bioinfo&create_users=&without_lab=&with_lab= HTTP/1.1" 200 94056 "https://ng6.toulouse.inra.fr/index.php?id=58" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0"
• 138.102.213.101 - - [29/Jan/2021:18:19:27 +0100] "GET /index.php?eID=tx_nG6&type=purge_managment_mail&project=1218,319,1023,409,1075,794,410,742,1250&user_id=1305 HTTP/1.1" 200 15 "https://ng6.toulouse.inra.fr/index.php?id=58" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0"
• 138.102.213.101 - - [29/Jan/2021:18:19:40 +0100] "GET /index.php?eID=tx_nG6&type=purge_managment_mail&project=1218,319,578,935,771,173,863,1279,7&user_id=1305 HTTP/1.1" 200 15 "https://ng6.toulouse.inra.fr/index.php?id=58" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0"
• 138.102.213.101 - - [29/Jan/2021:18:19:53 +0100] "GET /index.php?eID=tx_nG6&type=purge_managment_mail&project=1218,319,1279,1140,725,213,1047,1065,963&user_id=1305 HTTP/1.1" 200 15 "https://ng6.toulouse.inra.fr/index.php?id=58" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0"

The problem seems to concern 4 projects, 'Mutua16s', 'INTIMIO', 'DACR' and 'REIDSOCS'. Even when the purge alert is sent, those project still appear in the "purgeable" list. So if a user selects all projects by default, the alert is sent a second time. They are not filtered from the list properly.

More info:

The project Mutua16s is not filtered out because some of its runs don't have a 'tx_nG6_run'.'purge_demand_id' set (it is NULL).

So next question, why are some expired runs not included in the project's purge demand?

Some runs were not included because their expiration dates were after the purge launched by Didier:

• Purge : ven. 29/01/2021 18:22
• Run 14807 expiration : Friday 29 January 2021 23:00:00

(Runs concerned : 14806,14807,14808,14809,14813,14814,14815,14816)

New better question : Why did 19 purge alert mails have been sent for the same set of runs?

Table tx_nG6_purge_demand, project_id = 319

After the mails have been sent, the run table has not been updated to set the 'mail_sent_date' and 'purge_demand_id' values. (That's why the project was still in the list, and the runs were still considered purgeable.)

So, the code has been reviewed, the requests have been tested, traces have been added and the purge alert mail has been tested with 2 supposedly bugged projects (DACR and REIDSOCS).

Those project were removed from the "purgeable" list, the purge demands were well constructed, no bug to see here.

I'm going to put this issue on hold, and get back to it during the next purge if we can get some additionnal exemples or leads to investigate.

removed Dysfunction label

added Bug label

mentioned in commit daaf9194

mentioned in commit fb2a24f1

mentioned in merge request !113 (merged)

Need a trigger to prevent the insertion of a second purge demand per project?

DELIMITER ;;

CREATE TRIGGER check_demand_insert BEFORE INSERT ON `tx_nG6_purge_demand`
FOR EACH ROW
BEGIN

DECLARE var_demand_state, var_project_id, var_count int;

SELECT demand_state, project_id, count(*) as count
INTO var_demand_state, var_project_id, var_count
FROM tx_nG6_purge_demand
WHERE demand_state = 'sent' AND project_id = new.project_id 
GROUP BY demand_state, project_id;

IF var_count > 0 THEN
set @message_text = concat('A purge demand already exists for project ', new.project_id);
signal sqlstate '45000' 
set message_text = @message_text;
END IF;

END;
;; 

mentioned in commit 530ee80d

mentioned in merge request !116 (merged)

mentioned in commit b85d50a1

mentioned in commit e450a49e

mentioned in merge request !117 (closed)

mentioned in commit b81c8185

mentioned in commit dbad254f

mentioned in commit 0d951e86

mentioned in commit 19dd974e

mentioned in commit c09faba0

mentioned in merge request !118 (merged)

changed milestone to %V3.2.7.5

Resolved with the following tag: https://forgemia.inra.fr/genotoul-bioinfo/ng6/-/tags/V3.2.7.5

closed