Many entries generated in audit tables on submitting sequence files for analysis

[apetkau]

Previously (version 0.11.4), when submitting sequence files for analysis, no changes would be made to the audit tables. That is, by running select * from sequence_file_AUD where id = 1; after submission you would get:

+----+-----+---------+---------------------+---------------------+-------------------------------------------------------------------------------------+----------------------+
| id | REV | REVTYPE | created_date        | modified_date       | file_path                                                                           | file_revision_number |
+----+-----+---------+---------------------+---------------------+-------------------------------------------------------------------------------------+----------------------+
|  1 |   5 |       0 | 2016-08-12 10:11:38 | 2016-08-12 10:11:38 | /Warehouse/Temporary/aaron/sequence-files/1/1/08-5578-small_S1_L001_R2_001.fastq.gz |                    1 |
|  1 |   7 |       1 | 2016-08-12 10:11:38 | 2016-08-12 10:11:39 | /Warehouse/Temporary/aaron/sequence-files/1/2/08-5578-small_S1_L001_R2_001.fastq    |                    2 |
|  1 |   9 |       1 | 2016-08-12 10:11:38 | 2016-08-12 10:11:41 | /Warehouse/Temporary/aaron/sequence-files/1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |
+----+-----+---------+---------------------+---------------------+-------------------------------------------------------------------------------------+----------------------+

That is 3 entries (for decompression and fastqc analysis I believe).

However, afterwards (in current development) sending sequence files for analysis generates many more entries in our audit tables. That is, running the same sql select * from sequence_file_AUD where id = 1;

+----+-----+---------+---------------------+---------------------+-------------------------------------------+----------------------+---------------+
| id | REV | REVTYPE | created_date        | modified_date       | file_path                                 | file_revision_number | remote_status |
+----+-----+---------+---------------------+---------------------+-------------------------------------------+----------------------+---------------+
|  1 |   5 |       0 | 2016-08-12 09:56:45 | 2016-08-12 09:56:45 | 1/1/08-5578-small_S1_L001_R2_001.fastq.gz |                    1 |          NULL |
|  1 |   7 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:56:46 | 1/2/08-5578-small_S1_L001_R2_001.fastq    |                    2 |          NULL |
|  1 |   9 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:56:49 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  11 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:57:47 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  12 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:00 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  13 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:00 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  14 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:01 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  15 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:02 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  16 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:02 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
|  1 |  17 |       1 | 2016-08-12 09:56:45 | 2016-08-12 09:58:29 | 1/3/08-5578-small_S1_L001_R2_001.fastq    |                    3 |          NULL |
+----+-----+---------+---------------------+---------------------+-------------------------------------------+----------------------+---------------+

On submission, many more (7 in this case) entries get created in our audit tables.

Imported from GitLab issue #394. Originally posted on 2016/08/12 10:17AM

[apetkau]

Changing to older-style updates has no effect http://irida.corefacility.ca/irida/irida/commit/8c72eb5a9eecfb79454cd0dda619ef52c390dd6a.

Imported from GitLab. Originally posted on 2016/09/28 09:55AM

[apetkau]

Looking over some of the generated SQL statements, this is exactly what's going on. Any time we run analysisSubmissionService.update(submittedAnalysis) or analysisSubmissionService.save(submittedAnalysis) it's saving all referenced objects as well, which includes sequence files and reference files. It is actually overwriting old values with the same values, except for the modified date, which gets updated on every save or update.

Imported from GitLab. Originally posted on 2016/09/28 09:23AM

[apetkau]

I have a feeling this is due to the many statements like AnalysisSubmission completedSubmission = analysisSubmissionService.update(submittedAnalysis); which were changed in our code on submission of analysis. These statements may (now) save the entire object, including referenced objects (like sequence files).

Imported from GitLab. Originally posted on 2016/09/26 02:06PM