Full text Lucene not working with last version

[Damian Robert]

Hello,

For a long time I've been using h2db in my java desktop application alongside Lucene for Full text indexing. So far i've been using h2db version 1.3.160 and lucene-core version 3.4.0. Now I am trying to upgrade my h2db version to 1.4.199 but I can't seem to find the right lucene-core version to match. On the official tutorial page (http://www.h2database.com/html/tutorial.html) it is mentioned that I could use any version between 5.5.5 and 7.7.1, but I get some errors no matter which version I choose.

For example, when I use lucene-core 6.6.0 (or any other down to 5.5.5) I get the following Exception in the log of my application:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Class "org.h2.fulltext.FullTextLucene" not found; SQL statement:
SELECT COUNT(*) as `nr` FROM   FTL_SEARCH_DATA('+store10173 ', 0, 0) WHERE  `SCHEMA` = 'ARICIUL'        AND `TABLE`='LISTA' [90086-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:194)
    at org.h2.util.JdbcUtils.loadUserClass(JdbcUtils.java:202)
    at org.h2.engine.FunctionAlias.loadClass(FunctionAlias.java:144)
    at org.h2.engine.FunctionAlias.load(FunctionAlias.java:120)
    at org.h2.engine.FunctionAlias.findJavaMethod(FunctionAlias.java:264)
    at org.h2.expression.function.JavaFunction.<init>(JavaFunction.java:34)
    at org.h2.command.Parser.readJavaFunction(Parser.java:3301)
    at org.h2.command.Parser.readFunction(Parser.java:3495)
    at org.h2.command.Parser.readTableFunction(Parser.java:1937)
    at org.h2.command.Parser.readTableFilter(Parser.java:1892)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:2641)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:2788)
    at org.h2.command.Parser.parseSelectSub(Parser.java:2636)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:2469)
    at org.h2.command.Parser.parseSelect(Parser.java:2440)
    at org.h2.command.Parser.parsePrepared(Parser.java:814)
    at org.h2.command.Parser.parse(Parser.java:788)
    at org.h2.command.Parser.parse(Parser.java:760)
    at org.h2.command.Parser.prepareCommand(Parser.java:683)
    at org.h2.engine.Session.prepareLocal(Session.java:627)
    at org.h2.engine.Session.prepareCommand(Session.java:565)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:78)
    at dbConnection.statements.CommonStatementsAriciul.executeGetNrRezultate(CommonStatementsAriciul.java:67)
    at dbConnection.search.ariciul.QueryListaAriciul.calculeazaNumarRezultate(QueryListaAriciul.java:51)
    at dbConnection.search.QueryBase.inainte(QueryBase.java:366)
    at guiutilizator.mainviews.deviz.lista.CallableListaUtils.getMapareBdLista(CallableListaUtils.java:50)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.fillListaFromConnector(CallableObtineListaWorker.java:95)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.call(CallableObtineListaWorker.java:75)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.call(CallableObtineListaWorker.java:17)
    at exenne.components.swingworkers.progressworker.JProgressSwingWorker.doInBackground(JProgressSwingWorker.java:279)
    at javax.swing.SwingWorker$1.call(SwingWorker.java:295)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at javax.swing.SwingWorker.run(SwingWorker.java:334)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NoClassDefFoundError: org/apache/lucene/analysis/standard/StandardAnalyzer
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at org.h2.util.JdbcUtils.loadUserClass(JdbcUtils.java:191)
    ... 35 more
Caused by: java.lang.ClassNotFoundException: org.apache.lucene.analysis.standard.StandardAnalyzer
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 38 more

But if I use version 7.7.1 or below the exception changes to:

SELECT COUNT(*) as `nr` FROM   FTL_SEARCH_DATA('+store10173 ', 0, 0) WHERE  `SCHEMA` = 'ARICIUL'        AND `TABLE`='LISTA' [90105-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:194)
    at org.h2.message.DbException.convertInvocation(DbException.java:374)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:473)
    at org.h2.expression.function.JavaFunction.getValue(JavaFunction.java:40)
    at org.h2.table.FunctionTable.getValueResultSet(FunctionTable.java:205)
    at org.h2.table.FunctionTable.getResult(FunctionTable.java:176)
    at org.h2.index.FunctionIndex.find(FunctionIndex.java:54)
    at org.h2.index.BaseIndex.find(BaseIndex.java:132)
    at org.h2.index.IndexCursor.find(IndexCursor.java:163)
    at org.h2.table.TableFilter.next(TableFilter.java:475)
    at org.h2.command.dml.Select.gatherGroup(Select.java:528)
    at org.h2.command.dml.Select.queryGroup(Select.java:495)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:879)
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
    at org.h2.command.dml.Query.query(Query.java:435)
    at org.h2.command.dml.Query.query(Query.java:397)
    at org.h2.command.CommandContainer.query(CommandContainer.java:145)
    at org.h2.command.Command.executeQuery(Command.java:202)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:85)
    at dbConnection.statements.CommonStatementsAriciul.executeGetNrRezultate(CommonStatementsAriciul.java:67)
    at dbConnection.search.ariciul.QueryListaAriciul.calculeazaNumarRezultate(QueryListaAriciul.java:51)
    at dbConnection.search.QueryBase.inainte(QueryBase.java:366)
    at guiutilizator.mainviews.deviz.lista.CallableListaUtils.getMapareBdLista(CallableListaUtils.java:50)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.fillListaFromConnector(CallableObtineListaWorker.java:95)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.call(CallableObtineListaWorker.java:75)
    at guiutilizator.mainviews.deviz.lista.CallableObtineListaWorker.call(CallableObtineListaWorker.java:17)
    at exenne.components.swingworkers.progressworker.JProgressSwingWorker.doInBackground(JProgressSwingWorker.java:279)
    at javax.swing.SwingWorker$1.call(SwingWorker.java:295)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at javax.swing.SwingWorker.run(SwingWorker.java:334)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NoSuchFieldError: CONSTANT_SCORE_AUTO_REWRITE_DEFAULT
    at org.apache.lucene.queryparser.flexible.standard.config.StandardQueryConfigHandler.<init>(StandardQueryConfigHandler.java:211)
    at org.apache.lucene.queryparser.flexible.standard.StandardQueryParser.<init>(StandardQueryParser.java:119)
    at org.apache.lucene.queryparser.flexible.standard.StandardQueryParser.<init>(StandardQueryParser.java:138)
    at org.h2.fulltext.FullTextLucene.search(FullTextLucene.java:437)
    at org.h2.fulltext.FullTextLucene.searchData(FullTextLucene.java:251)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:460)

Could you please suggest a way to get around any of those exceptions?

Thanks in advance!

[Evgenij Ryazanov]

Hello.

There is a bug in latest releases of H2 and very recent releases of Lucene aren't really supported.

You can build H2 from its current sources if you need them for a some reason.

Lucene 5.5.5 should work in released versions properly. Exception that you see means that your classpath doesn't contain all necessary libraries.

You need the following three libraries for H2:

lucene-core-5.5.5.jar

lucene-analyzers-common-5.5.5.jar

lucene-queryparser-5.5.5.jar

[Damian Robert]

Thanks a lot. It worked well with all the suggestions above.

But I still have some issues if you are able to help.

1. The indexes I created using the older versions don't seem to work anymore. This is a problem since the database is already distributed to all of our users.

Here "don't seem to work" means that I find no results after searching for a keyword that I am sure exists among the data in the databases (and that returned a lot of results before)

2. We are still considering sticking to the older version (1.3.160 with lucene-core 3.4.0 for compatibility with all the users we have so far). There are a few issues we have when using this version:

- the results returned seem to be very limited for example the query:

SELECT COUNT(*) FROM BURSUCUL.NORMA WHERE NORMADESCRIERE LIKE '%beton%'

returns about 2000, but

SELECT COUNT(*) FROM FTL_SEARCH_DATA("+beton", 0, 0)

returns about 70 results, although the column "NORMADESCRIERE" in "BURSUCUL.NORMA" is indexed

- considering that, I tried to drop the indexes and recreate them but when I do:

CALL FTL_CREATE_INDEX("BURSUCUL", "NORMA", "NORMACOD, NORMADESCRIERE")

i get:

Error: org.h2.jdbc.JdbcSQLException: Error creating or initializing trigger "FTL_NORMA" object, class "org.h2.fulltext.

FullTextLucene$FullTextTrigger", cause: "java.sql.SQLException: Column not found: NORMACOD"; see root cause for details; SQL statement:
CREATE
 TRIGGER IF NOT EXISTS "BURSUCUL"."FTL_NORMA" AFTER INSERT, UPDATE,
DELETE, ROLLBACK ON "BURSUCUL"."NORMA" FOR EACH ROW CALL
"org.h2.fulltext.FullTextLucene$FullTextTrigger" [90043-160]

My first thought was that it must be a typo, but I checked a few times and I know I wrote it well. Here is the structure of my table:

sql> SHOW COLUMNS FROM BURSUCUL.NORMA;
FIELD            | TYPE          | NULL | KEY | DEFAULT
normaid          | integer(10)   | NO   | PRI | (NEXT VALUE FOR BURSUCUL.SYSTEM_SEQUENCE_A35B0D2E_EB1B_4869_B3C8_80767DBE85DE)
normalinkarticol | integer(10)   | NO   |     | NULL
normacod         | varchar(256)  | NO   |     | NULL
normaum          | varchar(256)  | NO   |     | NULL
normadescriere   | varchar(2048) | NO   |     | NULL

- I also tried to use the native fulltext indexer in this older version but then I get:

sql> CALL FT_INIT();
Error: org.h2.jdbc.JdbcSQLException: Syntax
error in SQL statement "CREATE TABLE IF NOT EXISTS FT.ROWS(ID IDENTITY,
HASH INT, INDEXID INT, KEY VARCHAR,[*] UNIQUE(HASH, INDEXID, KEY))";
expected "("; SQL statement:
CREATE TABLE IF NOT EXISTS FT.ROWS(ID IDENTITY, HASH INT, INDEXID INT, KEY VARCHAR, UNIQUE(HASH, INDEXID, KEY)) [42001-160]Enter code here...

Please let me know if you have a suggestion regarding any of the issues above.

[Evgenij Ryazanov]

The indexes I created using the older versions don't seem to work anymore.

They aren't compatible with newer versions of Lucene. They should be re-created, but it may take some time.

We are still considering sticking to the older version (1.3.160 with lucene-core 3.4.0 for compatibility with all the users we have so far). There are a few issues we have when using this version

When you use historic versions of H2 you're on your own.

SELECT COUNT(*) FROM FTL_SEARCH_DATA("+beton", 0, 0)

returns about 70 results, although the column "NORMADESCRIERE" in "BURSUCUL.NORMA" is indexed

The second argument of FTL_SEARCH_DATA is a limit. The javadoc of FullTextLucene.seach() is not correct, if 0 is specified the actual limit is about 100.