delete error in 0.13dev5

[shenshan]

Bug Report

Description

Got an error when deleting an upstream table in princeton pipeline

Reproducibility

Include:

• OS Linux
• Python Version 3.7.3
• MySQL Version MySQL 5.5.5-10.2.22-MariaDB
• MySQL Deployment Strategy (local-native)
• DataJoint Version 0.13dev5

Error message:

DataJointError                            Traceback (most recent call last)
<ipython-input-6-1a87e0503527> in <module>
----> 1 scan_element.Scan.delete()

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in delete(self, transaction, safemode)
    395         # Cascading delete
    396         try:
--> 397             delete_count = self._delete_cascade()
    398         except:
    399             if transaction:

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in _delete_cascade(self)
    364                 else:
    365                     child &= self.proj()
--> 366                 delete_count += child._delete_cascade()
    367             else:
    368                 print("Deleting {count} rows from {table}".format(

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in _delete_cascade(self)
    361                     fk_attrs = [k.strip('`') for k in match.group('fk_attrs').split(',')]
    362                     pk_attrs = [k.strip('`') for k in match.group('pk_attrs').split(',')]
--> 363                     child &= self.proj(**dict(zip(fk_attrs, pk_attrs)))
    364                 else:
    365                     child &= self.proj()

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/expression.py in proj(self, *attributes, **named_attributes)
    346         try:
    347             raise DataJointError("Attribute `%s` already exists" % next(
--> 348                 a for a in rename_map if a in attributes.union(compute_map).union(replicate_map)))
    349         except StopIteration:
    350             pass  # all ok

DataJointError: Attribute `subject_fullname` already exists

[shenshan]

Sorry we have not been able to replicate this error on our local server yet. will dig more into this later, but here is the error message we got.

[guzman-raphael]

@shenshan If this issue is still reproducible within the production system, would you add the following debug statement to datajoint-python above this line and let me know the output so we can debug this further?

print(f'debug describe: {self.describe()}')
print(f'debug prim_keys: {self.primary_key}')
print(f'debug attributes: {attributes}')
print(f'debug named_attributes: {named_attributes}')

[shenshan]

Hi @guzman-raphael, the print outs are as follows:

In [2]: scan_element.Scan.delete()
debug error: Cannot delete or update a parent row: a foreign key constraint fails
 (`u19_scan_element`.`_scan_info`, CONSTRAINT `_scan_info_ibfk_1` 
FOREIGN KEY (`subject_fullname`, `session_date`, `session_number`, `scan_id`)
 REFERENCES `scan` (`subject_fullname`, `session_da)
-> `u19_acquisition`.`session`
scan_id              : int                          
---
-> `u19_lab`.`equipment`
-> `u19_scan_element`.`#acquisition_software`
scan_notes=""        : varchar(4095)                # free-notes

debug describe: -> `u19_acquisition`.`session`
scan_id              : int                          
---
-> `u19_lab`.`equipment`
-> `u19_scan_element`.`#acquisition_software`
scan_notes=""        : varchar(4095)                # free-notes

debug prim_keys: ['subject_fullname', 'session_date', 'session_number', 'scan_id']
debug attributes: ()
debug named_attributes: {'subject_fullname': 'subject_fullname', ' `session_date': ' `session_da'}
debug error: Cannot delete or update a parent row: a foreign key constraint fails
 (`u19_scan_element`.`_scan_info__field`, CONSTRAINT `_scan_info__field_ibfk_1` 
FOREIGN KEY (`subject_fullname`, `session_date`, `session_number`, `scan_id`) 
REFERENCES `_scan_info` (`subject_fu)
# general data about the reso/meso scans, from ScanImage header
-> `u19_scan_element`.`scan`
---
nfields              : tinyint                      # number of fields
nchannels            : tinyint                      # number of channels
ndepths              : int                          # Number of scanning depths (planes)
nframes              : int                          # number of recorded frames
nrois                : tinyint                      # number of ROIs (see scanimage's multi ROI imaging)
x                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
y                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
z                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
fps                  : float                        # (Hz) frames per second - Volumetric Scan Rate
bidirectional        : tinyint                      # true = bidirectional scanning
usecs_per_line=null  : float                        # microseconds per scan line
fill_fraction=null   : float                        # raster scan temporal fill fraction (see scanimage)

debug describe: # general data about the reso/meso scans, from ScanImage header
-> `u19_scan_element`.`scan`
---
nfields              : tinyint                      # number of fields
nchannels            : tinyint                      # number of channels
ndepths              : int                          # Number of scanning depths (planes)
nframes              : int                          # number of recorded frames
nrois                : tinyint                      # number of ROIs (see scanimage's multi ROI imaging)
x                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
y                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
z                    : float                        # (um) ScanImage's 0 point in the motor coordinate system
fps                  : float                        # (Hz) frames per second - Volumetric Scan Rate
bidirectional        : tinyint                      # true = bidirectional scanning
usecs_per_line=null  : float                        # microseconds per scan line
fill_fraction=null   : float                        # raster scan temporal fill fraction (see scanimage)

debug prim_keys: ['subject_fullname', 'session_date', 'session_number', 'scan_id']
debug attributes: ()
debug named_attributes: {'subject_fullname': 'subject_fu'}
---------------------------------------------------------------------------
DataJointError                            Traceback (most recent call last)
<ipython-input-2-1a87e0503527> in <module>
----> 1 scan_element.Scan.delete()

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in delete(self, transaction, safemode)
    396         # Cascading delete
    397         try:
--> 398             delete_count = self._delete_cascade()
    399         except:
    400             if transaction:

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in _delete_cascade(self)
    365                 else:
    366                     child &= self.proj()
--> 367                 delete_count += child._delete_cascade()
    368             else:
    369                 print("Deleting {count} rows from {table}".format(

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/table.py in _delete_cascade(self)
    362                     fk_attrs = [k.strip('`') for k in match.group('fk_attrs').split(',')]
    363                     pk_attrs = [k.strip('`') for k in match.group('pk_attrs').split(',')]
--> 364                     child &= self.proj(**dict(zip(fk_attrs, pk_attrs)))
    365                 else:
    366                     child &= self.proj()

~/anaconda3/envs/imaging_element/lib/python3.7/site-packages/datajoint/expression.py in proj(self, *attributes, **named_attributes)
    351         try:
    352             raise DataJointError("Attribute `%s` already exists" % next(
--> 353                 a for a in rename_map if a in attributes.union(compute_map).union(replicate_map)))
    354         except StopIteration:
    355             pass  # all ok

DataJointError: Attribute `subject_fullname` already exists

[guzman-raphael]

@shenshan Turns out I need primary_key info as well in the debug. Would you update your print + error stack with it? I updated my request for debug prints with the additional info necessary.

[guzman-raphael]

@shenshan Would you add another debug statement above this line? Add this:

print(f'debug error: {error.args[0]}')

[shenshan]

Hi @guzman-raphael, sorry missed the last message earlier. Now updated with the error

[shenshan]

Delete in dev7 works!