Upgraded database fails on import with `null value "acquisition_device_type" violates not-null constraint`

Issue #415 resolved
Ed McDonagh created an issue

Prevents import of fluoro from RDSR - not sure about others. CT is fine.

Comments (24)

  1. David Platten

    Is that the database field that was never populated? If so, what happens if you manually delete the field?

  2. Ed McDonagh reporter

    Manually deleting the field makes it work. I'm just hating the idea people need to do this.

  3. Ed McDonagh reporter

    Instructions for deleting this field using the command line (tested with postgres):

    cd path/to/openrem
    python manage.py dbshell
    

    Enter password when prompted - you can find this in your openrem/openremproject/local_settings.py file.

    ALTER TABLE remapp_projectionxrayradiationdose DROP COLUMN acquisition_device_type;
    \q
    
  4. David Platten

    I haven't tried this, but what about a migration file that adds the field and then deletes it?

  5. David Platten

    Hi Ed,

    We can have a migration file that runs a line of SQL that deletes the field. I've tried this and it works when the field already exists in the database. If the field isn't there then it produces an error complaining that it does not exist.

    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    
    from django.db import models, migrations
    from django.conf import settings
    from django.db.models.loading import get_model
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            migrations.swappable_dependency(settings.AUTH_USER_MODEL),
            ('remapp', '0001_initial'),
        ]
    
        operations = [
            migrations.RunSQL("ALTER TABLE remapp_projectionxrayradiationdose DROP COLUMN acquisition_device_type;"),
        ]
    
  6. Ed McDonagh reporter

    Thanks @dplatten. If you don't have the field, then you are left with an unapplied migration. This can be fixed with a subsequent python manage.py migrate remapp --fake.

    Question is, how do we distribute this file and the instructions. A new release? Along with the necessary instructions?

  7. Ed McDonagh reporter

    psql has DROP COLUMN [ IF EXISTS ] which might avoid the error - I don't know at this stage if that is standard SQL

  8. Ed McDonagh reporter

    I think it is standard SQL, and if it is there then the migration can proceed successfully whether the column is there or not.

  9. David Platten

    The following deletes the field if it's there, and also runs without error if there is no field there:

    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    
    from django.db import models, migrations
    from django.conf import settings
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            migrations.swappable_dependency(settings.AUTH_USER_MODEL),
            ('remapp', '0003_delete_field_if_exists'),
        ]
    
        operations = [
            migrations.RunSQL("ALTER TABLE remapp_projectionxrayradiationdose DROP COLUMN IF EXISTS acquisition_device_type;"),
        ]
    
  10. Ed McDonagh reporter

    Installed 0.7.2b1 on a server with postgresql and had been upgraded 0.6.x to 0.7.1. The command to migrate remapp resulted in the following error:

    CommandError: Conflicting migrations detected (0002_upgrade_0_7_from_0_6, 000x_delete_060_acq_field in remapp).
    To fix them run 'python manage.py makemigrations --merge'
    

    Running the suggested command resulted in:

    Merging remapp
      Branch 0002_upgrade_0_7_from_0_6                                                                                                                   [0/2447]
        - Create model DicomDeleteSettings
        - Create model DicomQRRspImage
        - Create model DicomQRRspSeries
        - Create model DicomQRRspStudy
        - Create model DicomQuery
        - Create model DicomRemoteQR
        - Create model DicomStoreSCP
        - Create model PatientIDSettings
        - Create model UniqueEquipmentNames
        - Create model SkinDoseMapCalcSettings
        - Add field reference_point_definition_text to irradeventxraydata
        - Add field export_user to exports
        - Add field includes_pid to exports
        - Add field accession_hashed to generalstudymoduleattr
        - Add field acquisition_device_type_cid to projectionxrayradiationdose
        - Add field study_workload_chart_time to generalstudymoduleattr
        - Raw Python operation
        - Add field id_hashed to patientmoduleattr
        - Add field name_hashed to patientmoduleattr
        - Add field median_available to userprofile
        - Add field plotAverageChoice to userprofile
        - Add field plotCTInitialSortingChoice to userprofile
        - Add field plotCTRequestFreq to userprofile
        - Add field plotCTRequestMeanDLP to userprofile
        - Add field plotCTStudyMeanCTDI to userprofile
        - Add field plotDXAcquisitionMeankVpOverTime to userprofile
        - Add field plotDXAcquisitionMeanmAsOverTime to userprofile
        - Add field plotDXInitialSortingChoice to userprofile
        - Add field plotInitialSortingDirection to userprofile
        - Add field plotDXStudyMeanDAP to userprofile
        - Add field plotDXStudyFreq to userprofile
        - Add field plotDXRequestMeanDAP to userprofile
        - Add field plotDXRequestFreq to userprofile
        - Add field plotSeriesPerSystem to userprofile
        - Add field plotHistogramBins to userprofile
        - Alter field accession_number on generalstudymoduleattr
        - Alter unique_together for uniqueequipmentnames (1 constraint(s))
        - Add field qr_scp_fk to dicomquery
        - Add field store_scp_fk to dicomquery
        - Add field dicom_query to dicomqrrspstudy
        - Add field dicom_qr_rsp_study to dicomqrrspseries
        - Add field dicom_qr_rsp_series to dicomqrrspimage
        - Add field unique_equipment_name to generalequipmentmoduleattr
        - Add field plotHistograms to userprofile
        - Add field plotMGAGDvsThickness to userprofile
        - Add field plotMGStudyPerDayAndHour to userprofile
        - Add field plotRFInitialSortingChoice to userprofile
        - Add field plotRFStudyDAP to userprofile
        - Add field plotRFStudyFreq to userprofile
        - Add field plotRFStudyPerDayAndHour to userprofile
        - Raw Python operation
        - Raw SQL operation
      Branch 000x_delete_060_acq_field
        - Raw SQL operation
    
    Merging will only work if the operations printed above do not conflict
    with each other (working on different fields or models)
    Do you want to merge these migration branches? [y/N]
    

    Responding y to this created 0003_merge.py with a showmigrations looking like this:

    remapp
     [X] 0001_initial
     [ ] 000x_delete_060_acq_field
     [X] 0002_upgrade_0_7_from_0_6
     [ ] 0003_merge
    

    The merge migration file itself looked like this:

    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    
    from django.db import migrations, models
    
    
    class Migration(migrations.Migration):
    
        dependencies = [
            ('remapp', '0002_upgrade_0_7_from_0_6'),
            ('remapp', '000x_delete_060_acq_field'),
        ]
    
        operations = [
        ]
    

    The migrate remapp operation then ran successfully, leaving showmigrations looking like:

    remapp
     [X] 0001_initial
     [X] 000x_delete_060_acq_field
     [X] 0002_upgrade_0_7_from_0_6
     [X] 0003_merge
    
  11. Log in to comment