DB constraint violation bug

simon-20 commented 9 months ago

There appears to be one (maybe more) dataset which causes the overnight processing done by this tool to throw a DB constraint error, which potentially means that a batch of data (5000 records) is failing to be written to the database, and won't appear in the dashboards.

Traceback (most recent call last):
  File "/home/iaticdfdbackend/iaticdfdbackend/.ve/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_
single_context
    self.dialect.do_execute(
  File "/home/iaticdfdbackend/iaticdfdbackend/.ve/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 921, in do_e
xecute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: null value in column "reporting_organisation_type" violates not-null constraint
DETAIL:  Failing row contains (9ab5c316cf33bbf61a4e0a3d14b1cccdc937c47b63e17e72193f170ed3d103f7, BE-BCE_KBO-0410057701-prg2022-14-XX, BE-BCE_KBO-0410057701, null, C01, 110, 30, Institute of Tropical Medicine [BE-BCE_KBO-0410057701], Institut de Médecine Tropicale [BE-BCE_KBO-0410057701], Institute of Tropical Medicine [BE-BCE_KBO-0410057701], Instituto de Medicina Tropical [BE-BCE_KBO-0410057701], null, , , , , , budget, 998, f, 120, 12181, f, 2022, Q1, 2022 Q1, https://d-portal.org/q.html?aid=BE-BCE_KBO-0410057701-prg2022-14..., 998882.5026575342, 961666.0273972602, 0, , fce3978d82e656342a5e6edb55dc75d51f05f846e55bb222513036c65bc43965, e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855).

This happens on dev and live; seems to have started occurring in October.

markbrough commented 9 months ago

I notice this is maybe having an effect for FCDO as well: according to data downloaded from datastore.codeforiati.org, FCDO's total disbursements in 2023 to 998 should be around 3,750,689,379.37.

Download data from here as XLSX
Add a column transaction_year (calculate from transaction date => =LEFT(B2,4)
Create a pivot table
Filter to transaction types disbursements and expenditure
Sum by value_usd

In CDFD, they are recorded as 2,018,514,764.

simon-20 commented 8 months ago

Fixed by commit https://github.com/iati-data-access/data-backend/commit/bf9cdb7a9b0739039ad98d1ec3011c830dd51f5a

iati-data-access / data-backend

DB constraint violation bug #23