neo4j cypher - Unable to execute query [400], 'StackOverflowError' exception

Question

I am getting this exception when I try to run a long query:

Unable to execute query [400]:\nHeaders: Array\n(\n [Content-Type] => application/json; charset=UTF-8; stream=true\n [Access-Control-Allow-Origin] => *\n [Transfer-Encoding] => chunked\n [Server] => Jetty(9.0.5.v20130815)\n)\nBody: Array\n(\n [exception] => StackOverflowError\n [fullname] => java.lang.StackOverflowError\n [stacktrace] => Array\n (\n [0] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [1] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [2] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [3] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [4] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [5] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [6] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [7] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [8] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [9] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [10] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [11] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [12] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [13] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [14] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [15] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [16] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [17] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [18] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [19] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [20] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [21] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [22] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [23] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [24] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [25] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [26] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [27] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [28] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [29] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [30] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [31] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [32] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [33] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [34] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [35] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [36] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [37] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [38] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [39] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [40] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [41] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [42] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [43] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [44] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [45] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [46] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [47] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [48] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [49] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [50] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [51] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [52] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [53] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [54] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [55] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [56] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [57] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [58] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [59] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [60] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [61] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [62] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [63] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [64] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [65] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [66] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [67] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [68] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [69] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [70] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [71] => scala.collection.AbstractIterator.toList(Iterator.scala:1157)\n [72] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter.apply(Rewritable.scala:137)\n [73] => org.neo4j.cypher.internal.compiler.v2_1.bottomUp$BottomUpRewriter$$anonfun$1.apply(Rewritable.scala:137)\n [74] => scala.collection.Iterator$$anon$11.next(Iterator.scala:328)\n [75] => scala.collection.Iterator$class.foreach(Iterator.scala:727)\n [76] => scala.collection.AbstractIterator.foreach(Iterator.scala:1157)\n [77] => scala.collection.generic.Growable$class.$plus$plus$eq(Growable.scala:48)\n [78] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:176)\n [79] => scala.collection.mutable.ListBuffer.$plus$plus$eq(ListBuffer.scala:45)\n [80] => scala.collection.TraversableOnce$class.to(TraversableOnce.scala:273)\n [81] => scala.collection.AbstractIterator.to(Iterator.scala:1157)\n [82] => scala.collection.TraversableOnce$class.toList(TraversableOnce.scala:257)\n [83] => scal

Query:

start k=node:node_auto_index("title:abc*") where k.id in [123,456,...] return distinct k.prop;

Above where has list of around 3000 ids. I am using neo4j-community-2.1.2 and neo4jphp. Is there any work around to this?

Answer 1

You will probably fix your overflow issue if you tune the JVM settings to your data. This page in particular deals with it but the entire "Configuration and Performance" setting is crucial reading for anyone using Neo4j.

But with that in mind, I'd try to avoid queries like this. The fact that this type of indexing is referred to as "legacy" should be a warning and, in general, I find it's best to avoid it completely if there are better options out there. Legacy indexing, as far as I can tell from everything I've read, does not let you take advantage of labels, so you're missing out on one of the most useful aspects of Neo4j 2.0+.

The presence of that asterisk suggests you want a full-text search against an index. Legacy indexing is the only way to do that natively, but the compromise is a massive query like this. Ignore the index for a moment and you can see how you'd do it if you weren't using legacy indexes. Since there are a lot of nodes that all have something in common, you could put a label on them and then search against the label. That'd be as easy as this:

MATCH (n:`MyLabel`) WHERE n.name =~ "abc*" return n

Even with an index on MyLabel:name, the regex search would skip it and do it in Cypher, but it's so much easier to read, manage, and it is the modern way of searching the database.

The alternative, which I use whenever I need fulltext search, is Elasticsearch. It uses lucene indexes, same as Neo4j. The plugin I linked in the comment, https://github.com/sksamuel/elasticsearch-river-neo4j, will index the properties of given labels in your database. You can search against that and it will return the nodes that match. The downside to this is that it's extra plumbing and I'm not sure how efficient it is -- it says that it updates all nodes on every scrape of the database, which doesn't seem great but it's a start and I'm sure it could be improved -- BUT you have the benefit of not resorting to outdated features or clunky queries to get the information you need. I see you're using PHP, so you may be able to write a script that handles it more efficiently at an app level.

TL;DR: Tune your server to fix the crash; fix your query and your indexes to simplify your code.