pysqlsync - no rows to upsert/insert - tsv2py not solving issue, syncdb not updating postgres

[NoorRashid]

Good morning folks!

After updating to the new stable release of the DAP CLI tool, I was able to smooth out some existing issues which some of you have already experienced:

TCI_KEEPIDLE issue was an OS/Python issue

"Please use initdb" when trying to use syncdb was corrected by updating to the new stable release of 1.1.0

However, my issue now is that syncdb commands run, "data successfully retrieved" but alas by tables are not updated. I noted that on another thread, it was suggested to pip upgrade tsv2py, including from source.

I am running:

Windows 11, Postgres 16.0.2, Python 9.12.13

Weird thing is, this issue ONLY applies to namespace canvas, not canvas_logs (for the web_logs) table.

I would really really appreciate any help!

[LeventeHunyadi]

The data successfully retrieved message indicates that the DAP client library has successfully fetched the snapshot/incremental query output to temporary files on your machine. The next step would be to apply the changes to your local database, which is where the process seems to fail. Any console output and log file contents that you can share with our support team would be helpful in diagnosing the specific issue.

Different namespaces have tables with different schemas, and some transformations apply to certain table columns only. For example, some (but not all) tables have UUIDs, UTC timestamps, enumeration values, etc. If you query one of the tables that doesn't have the particular feature that is causing you trouble, things will go perfectly normal.

[NoorRashid]

Hi Levente!

Thank you for the prompt reply! 

Here is a log as requested - note I manually dropped the namespace canvas tables, and reinitialised. However, when running initdb, no data has been written to the database.

Appreciate your patience and help!

[LeventeHunyadi]

From what I can see in the logs, the namespace canvas in your database already contains objects from elsewhere, e.g. there appears to be a table called geoip2_network which is not a table exposed by DAP API. As far as I am aware, Instructure doesn't officially support DAP CLI if the namespaces managed by the tool (e.g. canvas, canvas_logs or catalog) contain foreign objects.

Can you make sure DAP CLI is replicating into an empty PostgreSQL database?

[NoorRashid]

Hi Levente!

Thanks once again for such a prompt reply. I am certain that those two tables, only created two days ago, were not the cause. 

But wanting to follow your lead, I deleted everything and ran initdb for clean reinitialization of the namespace canvas. I then ran a syncdb command and saved the debug to a log file. 

Unfortunately the issue persists and no data has been inserted into the database.

Really genuinely appreciate your help here, I am baffled and somewhat exasperated!

Best wishes,

Noor

[LeventeHunyadi]

Unfortunately, I don't see anything immediately obvious.

Can you share the console output and the log file contents for both an unsuccessful run (as in the attached log source) and a successful run (e.g. web_logs in canvas_logs, which you reported to work fine)?

Can you double-check if you have the latest tsv2py installed?

pip install --upgrade tsv2py

Can you also share CPU information? This can be retrieved with the following command in an elevated command prompt:

wmic cpu get caption, deviceid, name, numberofcores, maxclockspeed, status

Can you please open a support ticket such that the team can properly investigate and track progress? Thanks in advance!

[NoorRashid]

Hi Levente,

Good news is that after uninstalling literally everything, python, dap CLI tool, etc, I am now able to initialise and write data to the canvas namespace.

Since I have just done that, there is nothing to sync but I will update this thread later when I do have a chance sync and write new data. It looks like when I updated tsv2py, there were conflicting python packages. Reinstalling everything seems to have done the trick and DAP 1.1.0 really seems to be a good stable release.

Thank you for your help, will keep you updated.

Noor