H2 lucene

[Sylvain Pointeau]

Hello,

I tried using the native full text search on my db but from 2 GB, it grew up to +12GB, I had to stop the index creation.

So I tried Lucene and the database grew up to almost 6GB. Advantage of Lucene is that we can query with wildcards.

now my question: why are you still using version 3.6.2? they are now up to the version 6.

Is it possible to upgrade somehow?

Best regards,

Sylvain

[Thomas Mueller Graf]

Hi,

Patches to upgrade Lucene are welcome.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Sylvain Pointeau]

Hi Thomas,

Thank you for your reply, this morning, I re-opened my DB (created yesterday)

[2017-03-01 10:10:41] [HY000][50000] General error: "java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]" [50000-193]
[2017-03-01 10:10:41] java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]
[2017-03-01 10:10:41]     at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:765)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.getChunkIfFound(MVStore.java:955)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.getChunk(MVStore.java:937)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.readPage(MVStore.java:1945)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap.readPage(MVMap.java:736)
[2017-03-01 10:10:41]     at org.h2.mvstore.Page.getChildPage(Page.java:217)
[2017-03-01 10:10:41]     at org.h2.mvstore.Cursor.fetchNext(Cursor.java:150)
[2017-03-01 10:10:41]     at org.h2.mvstore.Cursor.next(Cursor.java:50)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap$2$1.next(MVMap.java:879)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap$2$1.next(MVMap.java:870)
[2017-03-01 10:10:41]     at org.h2.store.LobStorageMap.init(LobStorageMap.java:107)
[2017-03-01 10:10:41]     at org.h2.engine.Database.open(Database.java:790)
[2017-03-01 10:10:41]     at org.h2.engine.Database.openDatabase(Database.java:273)
[2017-03-01 10:10:41]     at org.h2.engine.Database.<init>(Database.java:267)
...

I am not sure I can really use the FTS if the DB is not reliable anymore...

Best regards,

Sylvain

[Sylvain Pointeau]

Hi Thomas,

Additionally I just noticed that FTL_SEARCH(_DATA) returns only 100 records.

is it something we can configure? I did not see any information in H2 documentation or google

Best regards,

Sylvain

[Дмитрий Моряков]

Sylvain, if you are using direct SQL query, you may try smth like that:

SELECT QUERY, SCORE FROM FTL_SEARCH('search string', 1000, 0) ORDER BY SCORE DESC

where 1000 - number of rows to fetch, 0 - offset

HTH

Regards,
--
Dmitry

[Sylvain Pointeau]

Hi Dmitry,

Yes thank you it works :-)

Do you know if this is reliable to use Lucene in H2? I just had an exception this morning. (from a database created yesterday)

[2017-03-01 10:10:41] [HY000][50000] General error: "java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]" [50000-193]
[2017-03-01 10:10:41] java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]
[2017-03-01 10:10:41]     at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:765)

Best regards,

Sylvain

--

[Thomas Mueller Graf]

Hi,

This is exception is not from Lucene, it is from H2. If you have a reproducible test case where such a corrupt database can be created, that would be great. At least I would need to know the database URL, the size of the database file, the Java version, and the stack trace.

Regards,

Thomas

[Sylvain Pointeau]

Hi Thomas,

I don't have the full stack trace anymore, just this part:

[2017-03-01 10:10:41] [HY000][50000] General error: "java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]" [50000-193]
[2017-03-01 10:10:41] java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]
[2017-03-01 10:10:41]     at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:765)

[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.getChunkIfFound(MVStore.java:955)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.getChunk(MVStore.java:937)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVStore.readPage(MVStore.java:1945)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap.readPage(MVMap.java:736)
[2017-03-01 10:10:41]     at org.h2.mvstore.Page.getChildPage(Page.java:217)
[2017-03-01 10:10:41]     at org.h2.mvstore.Cursor.fetchNext(Cursor.java:150)
[2017-03-01 10:10:41]     at org.h2.mvstore.Cursor.next(Cursor.java:50)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap$2$1.next(MVMap.java:879)
[2017-03-01 10:10:41]     at org.h2.mvstore.MVMap$2$1.next(MVMap.java:870)
[2017-03-01 10:10:41]     at org.h2.store.LobStorageMap.init(LobStorageMap.java:107)
[2017-03-01 10:10:41]     at org.h2.engine.Database.open(Database.java:790)
[2017-03-01 10:10:41]     at org.h2.engine.Database.openDatabase(Database.java:273)
[2017-03-01 10:10:41]     at org.h2.engine.Database.<init>(Database.java:267)

the url is: jdbc:h2:file:~/.myprg/FTS/mydbname;CIPHER=AES;COMPRESS=TRUE

size before using Lucene: 2'188 MB

size after Lucene FTS indexing: 5'200 MB

java version: 1.8.0_112-release-408-b6 x86

I hope it helps, I cannot make any reproducible test case, since I was using intellij database console to conduct my testing.

The day after, I had this exception, then I re-opened it and it worked.

best regards,

Sylvain