FileTransferSubsystem: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'

[htran-21]

Hi,

There are many Thingworx SQL errors in ApplicationLog

2019-11-28 03:14:18.840-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-3] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (08ec7aa0-1844-4ac7-bd31-71d8271f30d3).]
2019-11-28 03:14:18.840-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-18] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (5de4e265-4319-439d-bc24-ffeeb9f89da6).]
2019-11-28 03:14:18.840-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-17] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (5c29b673-2e49-4765-a7a1-d90b816cbed5).]
2019-11-28 03:14:18.840-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-19] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (5e185fa1-e32f-4eee-aba0-05a96dfb6cb9).]
2019-11-28 03:14:18.840-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-6] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (0f5aaab0-c4f5-4bad-979d-cfed8adbb79b).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-11] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (302415f0-6952-4ac3-9e8f-0b1539e4d827).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-16] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (46568797-2530-4871-a58a-d433adaefda7).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-12] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (3144532e-3719-4e69-a8d8-5e8d53524ac6).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-9] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (2afb1d28-cade-4d38-b069-56492732036b).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-22] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (75adabd6-0235-472b-b533-6c478fd70858).]
2019-11-28 03:14:18.841-0600 [L: ERROR] [O: c.t.p.m.MssqlModelExceptionTranslator] [I: ] [U: System] [S: ] [T: FileTransfer-15] [message: Violation of PRIMARY KEY constraint 'file_transfer_job_pkey'. Cannot insert duplicate key in object 'twschema.file_transfer_job'. The duplicate key value is (45695124-b951-4668-98a7-91fd9212071b).]

I wonder what are causing these errors.

My Bests,

Hung Tran

[raluca_edu]

Hi,

This seems that system is trying to insert duplicate id in table file_transfer_job of your MSSQL database.

Please look in the table file_transfer_job and see if there are really existing id like :

08ec7aa0-1844-4ac7-bd31-71d8271f30d3

5de4e265-4319-439d-bc24-ffeeb9f89da6

 ....

Please check your services/tasks that are uploading files and see what is wrong and why these duplicates are sent to the database.

Hope it helps,

Raluca Edu

[htran-21]

Hi Raluca Edu,

I understood what the error means, however that's Thingworx code and the table file_transfer_job is a thingworx table, we do not have any code related. I do believe that is a logical bug in FileTransferSubsystem. I don't feel that I can do anything on my side.

My Bests,

Hung Tran

[raluca_edu]

Hi,

But when these errors are happening? You are not running/executing anything? Or transferring files? Or an Edge/Remote thing that is transferring files?

When you notice this error? Do you have any reproducible steps to try internally?

Thank you,

Raluca Edu

[htran-21]

Hi Raluca Edu,

That happens in a PRODUCTION server during the ThingworxServer is re-starting. Of course, thousands of edges are transferring files to the server and vice versa. 

My Bests,

Hung Tran

[raluca_edu]

Hi,

Try to identify which edge is transferring the files with id from the error. Then we need to investigate what is happening on the edge/Thingworx related to that files, why are sent/transferred twice.

If this is  Production issue and it is impacting the business, I propose you to open a case to PTC Support.

Thank you,

Raluca Edu

[htran-21]

Hi Raluca Edu,

There are ten thousands of devices sending files regularly, it is impossible for me to send you such logs, and I don't know how to identify a file with an id, everything was happening internally in Thingworx code.

My Bests,

Hung Tran

[Constantine]

Hello @htran-21,

You might get more details about duplicate jobs by calling GetQueuedTransferJobs / GetActiveTransferJobs on FileTransferSubsystem. Those services have versions which take job ID as parameter.

/ Constantine

[htran-21]

Hi Constantine,

That won't work cause GetQueuedTransferJobs / GetActiveTransferJobs are JIT services, the exception occurred internally in Thingworx code, you will be unable to catch it and execute these services.

how could FileTransferSubsystem generate the same key for different transfers or may be the same transfer but it is inserted more than one time, why?

My Bests,

Hung Tran