Using periodic commit for load CSV gives open transaction error

[nsmith_piano]

I am trying to load a CSV on version 3.5.14 Enterprise. Here is my query.

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM 'file:///phuse_dx.csv' AS row
WITH row
WHERE row.`ns_phuse_dx.diag_cde` IS NOT NULL
MERGE (dx:Diagnosis {diagnosisCode: row.`ns_phuse_dx.diag_cde`})

I receive this error.

Executing queries that use periodic commit in an open transaction is not possible.

If I cut down my CSV and remove USING PERIODIC COMMIT, the statement works. If I use the full CSV and remove USING PERIODIC COMMIT, I get an out of memory error.

[terryfranklin82]

apoc.load.csv() is probably what you're after. I don't fully understand the internals, or if it's possible to initiate such a transaction with cypher, but USING PERIOD COMMIT only works with auto-commit transactions -

https://neo4j.com/docs/api/python-driver/current/transactions.html#auto-commit-transactions

[anthapu]

Looks like there is a bug in the new "Neo4J Desktop Canary" Neo4J browser app.

Can you use regular browser and connect to the DB and execute the same command?

Thanks
Ravi

[nsmith_piano]

Hi Ravi,

The same command which was failing in the desktop app worked for me in the web-based browser. Do you know if the issue has already been reported?

Thank you,
Nathan

[anthapu]

Can you please list out the Neo4J desktop app version and the browser version?

It looks like it does not happen in the latest Neo4J desktop browser.

Thanks
Ravi

[nsmith_piano]

Hi Ravi,

I was using Desktop version 1.2.3, Browser 4.0.3, and database 3.5.14 Enterprise. I updated to Desktop 1.2.4, but it doesn't seem to have solved the problem. My query works in the web-based browser, but not desktop.

Thanks,
Nathan

[anthapu]

Hi Nathan,
It seems you are correct. I tried in the 4.0.3 browser and it does indeed fail. Looks like there is a bug in that browser version.

[bernardwolff]

I ran into this error in Neo4j Desktop. Switching to the web-based browser fixed it for my localhost instance of Neo4j. However, the Neo4j Aura instance fails with the error.

[alete89]

I'm also experiencing this using docker image tagged neo4j:4.0.4
Without using periodic commit, I ran out of memory.

[amartsa]

Same here. If I use "USING PERIODIC COMMIT 1000" along with my query on the app browser, it tells me there's an open transaction error.

However, if I run the exact same query on the web browser (firefox in my case), it works wonders.

For what it's worth. Nice find Ravi @anthapu!

[reinhold_kolm]

Hi, I'm a beginner and I'm not sure I understand the question. Following cypher query works in the desktop version 4.0.3. It doesn't work without ":auto" at the beginning of the query.

:auto USING PERIODIC COMMIT 1000
LOAD CSV WITH HEADERS FROM 'file:///importUs5KAddresses.csv' as p
FIELDTERMINATOR ';'
CREATE( n { ...})

Reinhold

[anthapu]

That's because in Neo4J desktop uses the explicit transaction handler and that causes conflict with the "periodic commit" statement.

By specifying ":auto" you are telling Neo4J desktop to use auto commit option rather than explicit transaction handler.

[nalindanirosh]

Please try with limit
USING PERIODIC COMMIT 1000
LOAD CSV WITH HEADERS FROM 'file:///phuse_dx.csv' AS row
WITH row
WHERE row.ns_phuse_dx.diag_cde IS NOT NULL
MERGE (dx:Diagnosis {diagnosisCode: row.ns_phuse_dx.diag_cde})

[martynscn]

Hi guys, I tried prefixing with the :auto command and the periodic commit works. Something like this:

:auto USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///MetObjects.csv" AS row
MATCH (p:Painting {id:row.`Object ID`})
WITH p, row.Medium as my_row_medium
MERGE (m:Medium {name:coalesce(my_row_medium,'Unavailable')})
WITH p, m
CREATE (p)-[:HAS_MEDIUM]->(m);

It works even on the Neo4j desktop.