DAP initdb on submissions table throws a date error

[LoganBrewer1]

When I run initdb on the submissions table I get a "year 0 is out of range" error. It seems like I'm dealing with a bad row of data. What are the best options for resolving this? I assume I could ask our canvas admin to clean up that row of data, but that doesn't guarantee there won't be other/future rows with the same problem. How do I move forward? 

Database error message:

2023-12-27 19:00:56 UTC::@:[397]:LOG: checkpoint complete: wrote 2116 buffers (0.9%); 0 WAL file(s) added, 0 removed, 4 recycled; write=269.014 s, sync=0.001 s, total=269.222 s; sync files=53, longest=0.001 s, average=0.000 s; distance=1048735 kB, estimate=1066216 kB
2023-12-27 19:06:06 UTC::@:[397]:LOG: checkpoint starting: wal
2023-12-27 19:06:41 UTC:10.0.84.40(59903):chQCTRivSduIPOun@canvas_lms:[18530]:ERROR: COPY from stdin failed: year 0 is out of range
2023-12-27 19:06:41 UTC:10.0.84.40(59903):chQCTRivSduIPOun@canvas_lms:[18530]:CONTEXT: COPY submissions, line 755772
2023-12-27 19:06:41 UTC:10.0.84.40(59903):chQCTRivSduIPOun@canvas_lms:[18530]:STATEMENT: COPY "canvas"."submissions"("id", "attachment_id", "course_id", "user_id", "created_at", "updated_at", "workflow_state", "assignment_id", "media_comment_id", "media_comment_type", "attachment_ids", "posted_at", "group_id", "score", "attempt", "submitted_at", "quiz_submission_id", "extra_attempts", "grading_period_id", "grade", "submission_type", "processed", "grade_matches_current_submission", "published_score", "published_grade", "graded_at", "student_entered_score", "grader_id", "submission_comments_count", "media_object_id", "turnitin_data", "cached_due_date", "excused", "graded_anonymously", "late_policy_status", "points_deducted", "seconds_late_override", "lti_user_id", "anonymous_id", "last_comment_at", "cached_quiz_lti", "cached_tardiness", "resource_link_lookup_uuid", "redo_request", "body", "url") FROM STDIN (FORMAT binary)
2023-12-27 19:06:41 UTC:10.0.84.40(59903):chQCTRivSduIPOun@canvas_lms:[18530]:LOG: could not send data to client: Broken pipe
2023-12-27 19:06:41 UTC:10.0.84.40(59903):chQCTRivSduIPOun@canvas_lms:[18530]:FATAL: connection to client lost
2023-12-27 19:10:36 UTC::@:[397]:LOG: checkpoint complete: wrote 7585 buffers (3.1%); 0 WAL file(s) added, 0 removed, 20 recycled; write=269.313 s, sync=0.001 s, total=269.527 s; sync files=54, longest=0.001 s, average=0.000 s; distance=1049820 kB, estimate=1064577 kB

 DAP CLI error message:

root@88cacae1f19c:/app# dap initdb --table submissions
2023-12-26 17:56:40,163 - INFO - Query started with job ID: ad18f80a-2f64-4c3d-b634-f31c3df72bfd
2023-12-26 17:56:40,592 - INFO - Data has been successfully retrieved: (Returns json snipet with status marked as complete and json.gz) 
2023-12-26 17:59:21,809 - INFO - Files from server downloaded to folder: instructure_dap_temp
2023-12-26 18:03:09,949 - INFO - Removed folder: instructure_dap_temp
2023-12-26 18:03:10,287 - ERROR - year 0 is out of range
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/dap/conversion_common_json.py", line 121, in _get_optional_timestamp_json_value
    return cast(value)
  File "/usr/local/lib/python3.10/site-packages/dap/timestamp.py", line 38, in valid_naive_datetime
    return datetime.fromisoformat(s[:-1])
ValueError: year 0 is out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/dap/__main__.py", line 133, in console_entry
    main()
  File "/usr/local/lib/python3.10/site-packages/dap/__main__.py", line 125, in main
    asyncio.run(dapCommand.execute(args))
  File "/usr/local/lib/python3.10/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/local/lib/python3.10/asyncio/base_events.py", line 649, in run_until_complete
    return future.result()
  File "/usr/local/lib/python3.10/site-packages/dap/commands/commands.py", line 31, in execute
    executed = await super().execute(args)
  File "/usr/local/lib/python3.10/site-packages/dap/commands/base.py", line 49, in execute
    if await subcommand.execute(args):
  File "/usr/local/lib/python3.10/site-packages/dap/commands/base.py", line 45, in execute
    await self._execute_impl(args)
  File "/usr/local/lib/python3.10/site-packages/dap/commands/initdb_command.py", line 31, in _execute_impl
    await init_db(
  File "/usr/local/lib/python3.10/site-packages/dap/actions/init_db.py", line 16, in init_db
    await SQLReplicator(session, db_connection).initialize(
  File "/usr/local/lib/python3.10/site-packages/dap/replicator/sql.py", line 67, in initialize
    await client.download(
  File "/usr/local/lib/python3.10/site-packages/dap/downloader.py", line 120, in download
    await wait_n(
  File "/usr/local/lib/python3.10/site-packages/dap/concurrency.py", line 49, in wait_n
    raise exc
  File "/usr/local/lib/python3.10/site-packages/dap/downloader.py", line 118, in save
    await self.load_file_to_db(db_lock, context_aware_object, file_paths[index], processor=processor)
  File "/usr/local/lib/python3.10/site-packages/dap/downloader.py", line 65, in load_file_to_db
    await processor.process(obj, records)
  File "/usr/local/lib/python3.10/site-packages/dap/integration/base_processor.py", line 19, in process
    await self.process_impl(obj, self._convert(records))
  File "/usr/local/lib/python3.10/site-packages/dap/plugins/postgres/init_processor.py", line 63, in process_impl
    await self._db_connection.execute(
  File "/usr/local/lib/python3.10/site-packages/dap/integration/base_connection.py", line 41, in execute
    return await query(self._raw_connection)
  File "/usr/local/lib/python3.10/site-packages/dap/plugins/postgres/queries.py", line 26, in __call__
    return await self._query_func(asyncpg_conn)
  File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 1081, in copy_records_to_table
    return await self._protocol.copy_in(
  File "asyncpg/protocol/protocol.pyx", line 561, in copy_in
  File "asyncpg/protocol/protocol.pyx", line 478, in asyncpg.protocol.protocol.BaseProtocol.copy_in
  File "/usr/local/lib/python3.10/site-packages/dap/plugins/postgres/init_processor.py", line 81, in _convert_records
    yield tuple(converter(record) for converter in self._converters)
  File "/usr/local/lib/python3.10/site-packages/dap/plugins/postgres/init_processor.py", line 81, in <genexpr>
    yield tuple(converter(record) for converter in self._converters)
  File "/usr/local/lib/python3.10/site-packages/dap/conversion_common_json.py", line 123, in _get_optional_timestamp_json_value
    return _handle_exceptional_json_datetime(record_json, column_name)
  File "/usr/local/lib/python3.10/site-packages/dap/conversion_common_json.py", line 47, in _handle_exceptional_json_datetime
    clamped_value = clamp_naive_datetime(value)
  File "/usr/local/lib/python3.10/site-packages/dap/timestamp.py", line 65, in clamp_naive_datetime
    return valid_naive_datetime(s)
  File "/usr/local/lib/python3.10/site-packages/dap/timestamp.py", line 38, in valid_naive_datetime
    return datetime.fromisoformat(s[:-1])
ValueError: year 0 is out of range​

[J-J-Jason]

You could always try to find the data row. Look in the instructure_dap_temp directory and around line 755772 see if there's a bad date value.

As for preventing this from happening I don't know if you can.

Jason

[LoganBrewer1]

How do I look at the instructure_dap_temp directory? I looked through my directory and couldn't find it. Doesn't dap delete it right after it's created? 

[J-J-Jason]

The directory might be deleted automatically. You could do a download instead of a sync and then load those rows into excel or something that can handle that many rows. Just spitballing. For the date use the value in the meta table that dap keeps.

dap incremental  --table submissions --namespace canvas --since <date>

If you can get those rows loaded somewhere you can look at them maybe you can find the bad date value.

Jason

[LoganBrewer1]

It does seem to be an issue with bad data. There are 49 submissions with a submitted date of 1/1/0001, which we expect to be causing the problem. What do we do with bad dates in the download files?

[LoganBrewer1 · Accepted solution]

The problem was on line 37 inside the timestamp.py in the dap package. It was creating a datetime object with a value of 1/1/0001, which Python didn't like. So we wrote an exception handler at line 37, and if the datetime object creation was bad, it would change the date to 1/1/1999. Once the tables were initialized, the syncdb command worked like normal. The exception handler looked like this:

try:
       dt = datetime.fromisoformat(s[:-1])
       return dt
except ValueError:
       return datetime.fromisoformat('19990101')