Can't create full text index (H2: v1.4.200 , lucene: v7.7.2 or v8.2.0)

104 views
Skip to first unread message

Bengui Xie

unread,
Oct 15, 2019, 11:11:43 PM10/15/19
to H2 Database
Run scripts below in H2 Console:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR 
"org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR, 
LAST_NAME VARCHAR);
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');
INSERT INTO TEST VALUES(1, 'John', 'Wayne');
INSERT INTO TEST VALUES(2, 'Elton', 'John');
SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
CALL FTL_DROP_ALL();

Result:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
Update count: 0
(0 ms)

CALL FTL_INIT();
PUBLIC.FTL_INIT()  
null
(1 row, 2 ms)

DROP TABLE IF EXISTS TEST;
Update count: 0
(2 ms)

CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR,
LAST_NAME VARCHAR);
Update count: 0
(1 ms)

CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');


INSERT INTO TEST VALUES(1, 'John', 'Wayne');
Update count: 1
(0 ms)

INSERT INTO TEST VALUES(2, 'Elton', 'John');
Update count: 1
(1 ms)

SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SCHEMA  TABLE  COLUMNS  KEYS  SCORE  
(no rows, 221 ms)

SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
SCHEMA  TABLE  COLUMNS  KEYS  SCORE  
(no rows, 1 ms)

CALL FTL_DROP_ALL();
PUBLIC.FTL_DROP_ALL()  
null
(1 row, 43 ms)

Bengui Xie

unread,
Oct 15, 2019, 11:16:00 PM10/15/19
to H2 Database

Evgenij Ryazanov

unread,
Oct 16, 2019, 1:38:46 AM10/16/19
to H2 Database
Hello.

I cannot reproduce this issue with Lucene 7.7.1 and Lucene 8.0.0. I can test 7.7.2 and 8.2.0 too, but I don't think that results will be different.

You need to click on the error message to see the complete stack trace, it could describe the situation better.

Most likely you don't really have lucene-core-*.jar, lucene-analyzers-common-*.jar, and lucene-queryparser-*.jar (all three jars with the same version are necessary) in the classpath of the Console.

Bengui Xie

unread,
Oct 16, 2019, 3:30:35 AM10/16/19
to H2 Database
I'm sure that all lucene-related jars  are in the classpath. 

Stack trace is below. But I don't know how to figure it.

CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');

org.h2.jdbc.JdbcSQLSyntaxErrorException: Error creating or initializing trigger "FTL_TEST" object, class "org.h2.fulltext.FullTextLucene$FullTextTrigger", cause: "java.lang.reflect.InvocationTargetException"; see root cause for details; SQL statement:

CREATE TRIGGER IF NOT EXISTS "PUBLIC"."FTL_TEST" AFTER INSERT, UPDATE, DELETE, ROLLBACK ON "PUBLIC"."TEST" FOR EACH ROW CALL "
org.h2.fulltext.FullTextLucene$FullTextTrigger" [90043-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:576)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
    at org.h2.message.DbException.get(DbException.java:194)
    at org.h2.schema.TriggerObject.load(TriggerObject.java:90)
    at org.h2.schema.TriggerObject.setTriggerAction(TriggerObject.java:144)
    at org.h2.schema.TriggerObject.setTriggerClassName(TriggerObject.java:126)
    at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:115)
    at org.h2.command.CommandContainer.update(CommandContainer.java:198)
    at org.h2.command.Command.executeUpdate(Command.java:251)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:228)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201)
    at org.h2.fulltext.FullTextLucene.createOrDropTrigger(FullTextLucene.java:305)
    at org.h2.fulltext.FullTextLucene.createTrigger(FullTextLucene.java:283)
    at org.h2.fulltext.FullTextLucene.createIndex(FullTextLucene.java:161)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:441)
    at org.h2.expression.function.JavaFunction.getValue(JavaFunction.java:40)
    at org.h2.command.dml.Call.query(Call.java:65)
    at org.h2.command.CommandContainer.query(CommandContainer.java:285)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:222)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1459)
    at org.h2.server.web.WebApp.query(WebApp.java:1116)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1078)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1065)
    at org.h2.server.web.WebThread.process(WebThread.java:178)
    at org.h2.server.web.WebThread.run(WebThread.java:94)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at org.h2.schema.TriggerObject.load(TriggerObject.java:80)
    ... 28 more
Caused by: java.lang.NoSuchMethodError: org.apache.lucene.document.FieldType.<init>(Lorg/apache/lucene/document/FieldType;)V
    at org.h2.fulltext.FullTextLucene$FullTextTrigger.<init>(FullTextLucene.java:512)
    ... 33 more

Evgenij Ryazanov

unread,
Oct 16, 2019, 3:52:29 AM10/16/19
to H2 Database
Oh, I see. We definitely have a problem.

Try some older version of Lucene such as 6.6.*.

7.0.0 and newer versions are source compatible but binary incompatible, if you need them, you need to recompile H2 with that version.

I'll try to fix the binary compatibility for a next version of H2. I filled a new issue on GitHub:

Bengui Xie

unread,
Oct 16, 2019, 4:30:11 AM10/16/19
to H2 Database
Return to Lucene 6.6.6. I get this:

CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME');
Exception calling user-defined function: "createIndex(conn2: url=jdbc:default:connection user=SA, PUBLIC, TEST, FIRST_NAME,LAST_NAME): org.apache.lucene.index.IndexWriter.commit()V"; SQL statement:
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME') [90105-200] 90105/90105 (Help)
org.h2.jdbc.JdbcSQLNonTransientException: Exception calling user-defined function: "createIndex(conn2: url=jdbc:default:connection user=SA, PUBLIC, TEST, FIRST_NAME,LAST_NAME): org.apache.lucene.index.IndexWriter.commit()V"; SQL statement:
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', 'FIRST_NAME,LAST_NAME') [90105-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)

    at org.h2.expression.function.JavaFunction.getValue(JavaFunction.java:40)
    at org.h2.command.dml.Call.query(Call.java:65)
    at org.h2.command.CommandContainer.query(CommandContainer.java:285)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:222)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1459)
    at org.h2.server.web.WebApp.query(WebApp.java:1116)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1078)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1065)
    at org.h2.server.web.WebThread.process(WebThread.java:178)
    at org.h2.server.web.WebThread.run(WebThread.java:94)
    at java.lang.Thread.run(Unknown Source)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:441)
    ... 13 more

Evgenij Ryazanov

unread,
Oct 16, 2019, 4:37:56 AM10/16/19
to H2 Database
The situation is even worse, the binary compatibility with 6 is broken too.

So you need to use 5.5.5 (it was used for compilation of official release of H2 1.4.200), or you can build H2 from its current sources if you definitely need a newer version of Lucene for a some reason.

Bengui Xie

unread,
Oct 16, 2019, 4:39:15 AM10/16/19
to H2 Database
Yes. 5.5.5 is normal.

Satt Iqbal

unread,
Nov 26, 2019, 11:08:33 PM11/26/19
to H2 Database
I have the same problem.

I Used H2 2019 03 13, Lucene-core-8.0.0.jar, Lucene-analyzer-common-8.0.0.jar, and  Lucene-queryparser-8.0.0.jar in my CLASSPATH variable.

the Error Message still show

Evgenij Ryazanov

unread,
Nov 26, 2019, 11:40:58 PM11/26/19
to H2 Database
Unfortunately, you need to recompile H2 with your version of Lucene, or use Lucene 5.5.5.

1.4.200 was source compatible, but binary incompatible with more recent versions.

Satt Iqbal

unread,
Nov 27, 2019, 3:02:35 AM11/27/19
to H2 Database
thank you its work...
my problem was solved.

so I need to completed the *.jar file with  5.5.5 version, include lucene-analyzer-common, queryparser.

my problem doesnt solved If we follow the Instruction in the official website of H2database.

thank you for the help.

thank you for the attention..
Reply all
Reply to author
Forward
0 new messages