search

wfau / ScienceArchives

0 stars 0 forks source link

Fix SyncDb / Outgester - bulk copy for varchar(256) #591

Open wfastrononomer opened 1 month ago

wfastrononomer commented 1 month ago

SyncDb has started failing for tables with columns of varchar(256). This happens with but copy, and seems to be in Outgester/Ingester. A similar problem also occurs in BinaryToCsv and is related to the struct conversions.

@esutorius and @wfastrononomer have looked into this. @wfastrononomer has made a hacky fix to SyncDb that forces it to use SQL copy for MultiframeDetector which fixes the problem for the most pressing cases. @Ross-ROE may be able to help a bit as he wrote the Outgester code.

This seems to have occurred in the last 2 months, although I don't think changes have occurred to either the schema or code in that time.

wfastrononomer commented 1 month ago

Ross suggested that bulk copy using csv rather than binary works and that the code should select csv if there are varchar. I have checked through Trac to see if SyncDb or DbSession have had any changes in the last 2 months that could affect whether a binary or csv file is selected. I can't see any. I will try and figure out how csv/binary selection is done and work out why it is not happening.

Ross-ROE commented 1 month ago

I had a quick look myself: I think SyncDb did always do binary copies even with varchar - I seem to remember now that the issue was that you can't convert a binary to a CSV if the binary contains a varchar, but if you outgest from one table to ingest to another table, as long as the schema of both tables are identical, then you can use a binary file as the intermediate, even if it contains a varchar. So I suspect there might be an issue with a schema difference. However, if SQL copy works, then just do that - it's always an option...