Getting " WARN Retrying operation 1 of 3" lines in neo4j.log while loading data from oracle

[akshat_mittal]

Hello Team,

Getting below messages in neo4j.log while loading data from oracle db using jdbc connection -

2020-04-22 11:33:28.166+0000 WARN Retrying operation 0 of 3
2020-04-22 11:33:28.290+0000 WARN Retrying operation 0 of 3
2020-04-22 11:33:28.414+0000 WARN Retrying operation 1 of 3
2020-04-22 11:33:28.540+0000 WARN Retrying operation 2 of 3
2020-04-22 11:33:28.696+0000 WARN Retrying operation 0 of 3
2020-04-22 12:01:20.132+0000 WARN Error during iterate.commit:
2020-04-22 12:01:20.133+0000 WARN 1083 times: org.neo4j.graphdb.TransactionFailureException: Transaction was marked as successful, but unable to commit transaction so rolled back.
2020-04-22 12:01:20.133+0000 WARN 6236 times: java.lang.NullPointerException

Below is the detail snapshot after query execution is completed-

Once data load is completed , We can observed that there are less number of records loaded in label of neo4j as compared to clean data available in oracle db table.

Below is the script used to load data:

CALL apoc.periodic.iterate('
with "jdbc:oracle:thin:sqmread/Qwcbd4#g@10.147.161.47:1522/XCOMSTB2" as url
CALL apoc.load.jdbc(url,"select * from RJILCUSTOMISATIONS.RIL_UTM_L3PORT_PORT_V") YIELD row return row','
MERGE (POmw:L3PORT { L3PORTCONFIGNAME: row.L3PORTCONFIGNAME})
ON CREATE SET
POmw.PHYSICALPORT=row.PHYSICALPORT,
POmw.CREATED_DATE=datetime(),
POmw.MODIFIED_DATE=datetime()'
,{batchSize:1000, iterateList:true, retries:3,parallel:true})
YIELD batches, total , timeTaken , failedOperations , failedBatches , errorMessages , committedOperations , operations
RETURN batches, total , timeTaken , failedOperations , failedBatches , errorMessages , committedOperations , operations;

Regards
Akshat

[dana_canzano]

Your usage of apoc includes {batchSize:1000, iterateList:true, retries:3,parallel:true} and this retries:3 is why it is retrying. So that part is expected.

As to the error and missing rows, you are reading from Oracle table/view RIL_UTM_L3PORT_PORT_V and based upon the results performing a MERGE and using a lookup key into :L3PORT of L3PORTCONFIGNAME: row.L3PORTCONFIGNAME. Does your Oracle SQL statement return rows where L3PORTCONFIGNAME is null?

[akshat_mittal]

Hello Dane ,

Greetings!!

Thanks for a quick help.

Before loading the data , i gone through this view in the Oracle DB for RIL_UTM_L3PORT_PORT_V View and also ran the distinct on L3PORTCONFIGNAME column and found the 19 lacs unique values. Whereas in Neo4j , only 8 lacs values got loaded. So I missed approx. 11 lacs records.
Wondering how to solve/identify why this has happened?

Best Regards
Akshat

[akshat_mittal]

Hi Dana ,

Greetings !!
I have updated the query with retries = 0.
Still i am getting below lines in neo4j.log

2021-04-23 15:06:29.791+0530 WARN Error during iterate.commit:
2021-04-23 15:06:29.791+0530 WARN 1 times: org.neo4j.graphdb.TransactionFailureException: Transaction was marked as successful, but unable to commit transaction so rolled back.
2021-04-23 15:06:29.791+0530 WARN 1 times: java.lang.NullPointerException

Regards
Akshat