Various improvements/bug fixes for the delete_imaging_upload.pl script

nicolasbrossard commented 2 months ago

This PR applies the following fixes/improvements to the delete_imaging_upload.pl script:

When running mysqldump, option --single-transaction is not used by default anymore.
mysqldump now assumes that ~/.my.cnf contains the password used to connect to the database. Users that do not have this file can use the new option --extra_mysqlcnf=file to specify an alternate path for the file containing the password used to log into the database. This avoids passing the password on the command line, which is a security risk.
The output of mysqldump is always shown to the user (useful for debugging).
More robust/clearer checking of the mysqldump return code.
Update of the script documentation.

Fixes #1107

MaximeMulder commented 1 month ago

I tried to run this script on my VM but failed, is there anything wrong with my command ?

(loris-mri-python) lorisadmin@mmulder-dev:/data/tmp$ delete_imaging_upload.pl -profile prod -uploadID 110 -extra_mysqlcnf mysqlextra.cnf

Backing up files related to the upload(s) to delete...
[...]
Backup of table notification_spool failed with the following output:
-- Warning: column statistics not supported by the server.
mysqldump: Got error: 1044: Access denied for user 'mmulder_user'@'132.206.37.11' to database 'mmulder_dev' when doing LOCK TABLES
Aborting
Segmentation fault (core dumped)

My mysqlextra.cnf file looks like this :

[client]
host=mariadb10-dev.acelab.ca
user=mmulder_user
password=PASSWORD
port=3306

CamilleBeau commented 1 month ago

I checked out this branch and ran (loris-mri-python) lorisadmin@cbeaudoin-test-26:/opt/Loris-MRI/bin/mri/tools$ perl delete_imaging_upload.pl -profile prod -uploadID 90 -extra_mysqlcnf ~/dbpass.txt.

The output listed many files to backup before deleting that didn't seem to be related to the upload. I took some of those as examples here. The output ended with errors

(loris-mri-python) lorisadmin@cbeaudoin-test-26:/opt/Loris-MRI/bin/mri/tools$ perl delete_imaging_upload.pl -profile prod -uploadID 90 -extra_mysqlcnf ~/dbpass.txt WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version. WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.

Backing up files related to the upload(s) to delete... /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_t1_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_t1_002.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_t2_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapDWI_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_002.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_dwi25_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_003.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_004.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_005.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fMRI_001.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fMRI_002.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fMRI_003.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapDWI_002.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_006.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_fieldmapBOLD_007.mnc /data/loris/data/assembly/947103/V6/mri/native/demo_947103_V6_dwi65_001.mnc … /data/loris/data/tarchive/2018/DCM_2018-03-03_ImagingUpload-14-12-1wz2vw.tar /data/loris/data/tarchive/2018/DCM_2018-01-06_ImagingUpload-14-34-Vn6xg9.tar /data/loris/data/tarchive/2018/DCM_2018-04-20_ImagingUpload-13-42-zcoZR0.tar /data/loris/data/tarchive/2018/DCM_2018-01-01_ImagingUpload-10-43-QrnTPO.tar /data/loris/data/tarchive/2018/DCM_2018-03-03_ImagingUpload-14-30-gRhCrT.tar /data/loris/data/tarchive/2018/DCM_2018-04-20_ImagingUpload-14-45-UKBZmY.tar /data/loris/data/tarchive/2018/DCM_2018-04-20_ImagingUpload-14-42-AEcK_S.tar /data/loris/data/tarchive/2018/DCM_2018-01-01_ImagingUpload-14-26-gU_8Vw.tar … /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-17-53-aFGbGE.tar /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-18-0-cIhIqz.tar /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-17-41-TyOD7N.tar /data/loris/data/tarchive/2016/DCM_2016-08-19_ImagingUpload-18-6-TFLiY1.tar /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-18-37-sGGJmc.tar /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-17-33-eJIECH.tar /data/loris/data/tarchive/2016/DCM_2016-08-19_ImagingUpload-18-41-C4ZlTl.tar /data/loris/data/tarchive/2016/DCM_2016-08-15_ImagingUpload-18-13-XVExak.tar … /data/loris/data/pic/400221/demo_400221_V5_fieldmapBOLD_005_392_check.jpg /data/loris/data/pic/400221/demo_400221_V5_t2_001_384_check.jpg /data/loris/data/pic/400221/demo_400221_V5_dwi65_001_389_check.jpg /data/loris/data/pic/400221/demo_400221_V5_fieldmapBOLD_003_390_check.jpg /data/loris/data/pic/400221/demo_400221_V5_fMRI_003_395_check.jpg /data/loris/data/pic/400221/demo_400221_V5_fieldmapBOLD_001_386_check.jpg /data/loris/data/pic/400221/demo_400221_V5_fieldmapBOLD_002_387_check.jpg … /data/loris/data/logs/TarLoad-18-29-iMr0B4.log /data/loris/data/logs/DCC2018-01-01676061.log.gz /data/loris/data/logs/TarLoad-14-54-tJYEmk.log /data/loris/data/logs/TarLoad-14-39-oeXGIO.log /data/loris/data/logs/TarLoad-14-29-TzP0Bj.log /data/loris/data/logs/TarLoad-17-37-_4JjCn.log /data/loris/data/logs/TarLoad-14-24-VdyfHv.log /data/loris/data/logs/TarLoad-14-45-kkTS9x.log /data/loris/data/logs/TarLoad-14-54-D2fYfz.log /data/loris/data/logs/TarLoad-14-54-fwmWxM.log /data/loris/data/logs/TarLoad-14-33-l91Chi.log … /data/loris/data/trashbin/TarLoad-17-59-aQ3vgj/mtl144_300144_v1_20160815_173702_20_mri.mnc /data/loris/data/trashbin/TarLoad-17-59-aQ3vgj/mtl144_300144_v1_20160815_173702_10d1_mri.mnc /data/loris/data/trashbin/TarLoad-17-55-ihdkFV/ /data/loris/data/trashbin/TarLoad-17-55-ihdkFV/ott170_300170_v1_20160819_150346_8d1_mri.mnc /data/loris/data/trashbin/TarLoad-17-55-ihdkFV/ott170_300170_v1_20160819_150346_7d1_mri.mnc … /data/loris/data/batch_output/defacingstdout.log1 /data/loris/data/batch_output/imuploadstderr.log7 /data/loris/data/batch_output/imuploadstderr.log17 /data/loris/data/batch_output/defacingstderr.log8 /data/loris/data/batch_output/defacingstdout.log3 /data/loris/data/batch_output/defacingstderr.log11 … /data/loris/data/BIDS_export/BIDS_LORIS25_test3/sub-400168/ /data/loris/data/BIDS_export/BIDS_LORIS25_test3/sub-400168/ses-V2/ /data/loris/data/BIDS_export/BIDS_LORIS25_test3/sub-400168/ses-V2/sub-400168_ses-V2_scans.tsv /data/loris/data/BIDS_export/BIDS_LORIS25_test3/sub-400168/ses-V2/func/ /data/loris/data/BIDS_export/BIDS_LORIS25_test3/sub-400168/ses-V2/func/sub-400168_ses-V2_task-rest_run-1_bold.nii.gz … /data/loris/data/BIDS_export/BIDS_LORIS25_test5/.bids-validator-config.json /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/ /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/sub-475906_ses-V4_scans.tsv /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/sub-475906_ses-V4_scans.json /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/func/ /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/func/sub-475906_ses-V4_task-rest_run-2_bold.nii.gz /data/loris/data/BIDS_export/BIDS_LORIS25_test5/sub-475906/ses-V4/func/sub-475906_ses-V4_task-rest_run-1_bold.nii.gz tar: imaging_upload_backup.tar: Wrote only 2048 of 10240 bytes tar: Error is not recoverable: exiting now backup command failed: Inappropriate ioctl for device Segmentation fault

MaximeMulder commented 1 month ago

I changed my database user to my LORIS admin database user and also got a segumentation fault (but apparently different ?)

(loris-mri-python) lorisadmin@mmulder-dev:/data/tmp$ cat mysqlextra.cnf
[client]
host=mariadb10-dev.acelab.ca
user=mmulder_admin
password=PASSWORD
port=3306

(loris-mri-python) lorisadmin@mmulder-dev:/data/tmp$ delete_imaging_upload.pl -profile prod -uploadID 110 -extra_mysqlcnf mysqlextra.cnf

Backing up files related to the upload(s) to delete...
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_t1_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_t2_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapDWI_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_002.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi25_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi65_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_003.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_004.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_005.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_002.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_003.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_t1_001.nii
/data/raisinbread/pic/400248/demo_400248_V6_t1_001_399_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_t2_001.nii
/data/raisinbread/pic/400248/demo_400248_V6_t2_001_400_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapDWI_001.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapDWI_001_401_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_001.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapBOLD_001_402_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_002.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapBOLD_002_403_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi25_001.nii
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi25_001.bval
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi25_001.bvec
/data/raisinbread/pic/400248/demo_400248_V6_dwi25_001_404_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi65_001.nii
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi65_001.bval
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_dwi65_001.bvec
/data/raisinbread/pic/400248/demo_400248_V6_dwi65_001_405_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_003.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapBOLD_003_406_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_004.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapBOLD_004_407_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_005.nii
/data/raisinbread/pic/400248/demo_400248_V6_fieldmapBOLD_005_408_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_001.nii
/data/raisinbread/pic/400248/demo_400248_V6_fMRI_001_409_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_002.nii
/data/raisinbread/pic/400248/demo_400248_V6_fMRI_002_410_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fMRI_003.nii
/data/raisinbread/pic/400248/demo_400248_V6_fMRI_003_411_check.jpg
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_001.mnc
/data/raisinbread/assembly/400248/V6/mri/native/demo_400248_V6_fieldmapBOLD_002.mnc
/data/raisinbread/tarchive/2016/DCM_2016-08-15_ImagingUpload-18-37-sGGJmc.tar

Backup of table notification_spool failed with the following output:
mysqldump: Got error: 1045: Access denied for user 'mmulder_user'@'netmind.cbrain.mcgill.ca' (using password: YES) when trying to connect
Aborting
Segmentation fault (core dumped)

driusan commented 1 month ago

@cmadjar why did you add the 26 milestone to this? It seems like it's a refactoring that's not ready to go and should be for a future release?

nicolasbrossard commented 1 month ago

@CamilleBeau I pushed new changes. Can you please test again on your system?

CamilleBeau commented 4 weeks ago

Hi @nicolasbrossard, I don't see any new commits to this PR other than the 3 original commits from May 3rd

nicolasbrossard commented 4 weeks ago

@CamilleBeau Check right above the message I sent you: you'll see the commit "Fixed bug found in Camille's testing".

CamilleBeau commented 3 weeks ago

Passing manual tests

(loris-mri-python) lorisadmin@cbeaudoin-test-26:/opt/Loris-MRI/bin/mri/tools$ perl delete_imaging_upload.pl -profile prod -uploadID 100 -extra_mysqlcnf ~/dbpass.txt WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version. WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.

Backing up files related to the upload(s) to delete... /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_t1_001.mnc /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_t2_001.mnc /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.mnc /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_t1_001.nii /data/loris/data/pic/300181/demo_300181_V2_t1_001_270_check.jpg /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_t2_001.nii /data/loris/data/pic/300181/demo_300181_V2_t2_001_271_check.jpg /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.nii /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.bval /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.bvec /data/loris/data/pic/300181/demo_300181_V2_dwi25_001_272_check.jpg /data/loris/data/trashbin/TarLoad-17-59-dRlglP/ott181_300181_v2_20160815_160502_5d1_mri.mnc /data/loris/data/trashbin/TarLoad-17-59-dRlglP/ott181_300181_v2_20160815_160502_6d1_mri.mnc /data/loris/data/trashbin/TarLoad-17-59-dRlglP/ott181_300181_v2_20160815_160502_7d1_mri.mnc /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.mnc /data/loris/data/assembly/300181/V2/mri/native/demo_300181_V2_dwi25_001.mnc /data-raisinbread/tarchive/2016/DCM_2016-08-15_ImagingUpload-17-58-3WoNic.tar

Backup of table notification_spool succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table tarchive_files succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table tarchive_series succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table parameter_file succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table files succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table mri_protocol_violated_scans succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table mri_violations_log succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table mri_upload succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Backup of table tarchive succeeded but a warning was issued: -- Warning: column statistics not supported by the server. . Added imaging_upload_restore.sql to the backup file. Warning! File '/data-raisinbread/tarchive/2016/DCM_2016-08-15_ImagingUpload-17-58-3WoNic.tar' could not be deleted: Read-only file system Wrote imaging_upload_backup.tar.gz Successfully deleted data for upload 100.

aces / Loris-MRI

Various improvements/bug fixes for the delete_imaging_upload.pl script #1121