Re: [h2] H2 lucene
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 On Wednesday, March 1, 2017, Sylvain Pointeau wrote: > 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][5] General error: > "java.lang.IllegalStateException: Chunk 5451 no longer exists > [1.4.193/9]" [5-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.newIl > legalStateException(DataUtils.java:765) > > Best regards, > Sylvain > > > > > On Wed, Mar 1, 2017 at 12:37 PM, Дмитрий Моряков > wrote: > >> 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 >> >> -- >> 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+unsubscr...@googlegroups.com >> >> . >> To post to this group, send email to h2-database@googlegroups.com >> . >> Visit this group at https://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > 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+unsubscr...@googlegroups.com > > . > To post to this group, send email to h2-database@googlegroups.com > . > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] High processor usage when no transactions taking place - revisited
Hi, Yes, this is the background thread trying to compact (garbage collect) the database file. It should only occur if there is no other write activity, and should not affect write performance. Regards, Thomas On Tuesday, February 28, 2017, andreas fw wrote: > Hi, > > I am having an issue, which may be related to the post by Stephen 3/12/14 > titled "High processor usage when no transactions taking place". > > I have a Linux box that has been running fine for 3 weeks with a Java app > that uses an embedded H2 file database. Suddenly at 3am without any real > activity within a minute the average CPU load went from 8% to 25-30%, where > a single Java thread "MVStore background writer" takes up 100% when no > reads or writes are taking place. Some details about the system: > > 1 proc box with 1 SSD > 1 Java app with an embedded H2 file database (1.4.192). The size of > the db file is ~500 MB. > The usage profile: > Writes 10-100 rows every minute and few random smaller reads (only > a few users connected) > The Java app connection parameter: > jdbc:h2:file:/mnt/data/x/x-db > (i.e. no special H2 config) > Hardware > 1 x Intel Celeron 1900 quad core > OS > Ubuntu 16.04 Server edition > Linux kernel 4.4.64 > > > Running jstack and jvmtop showed that 100% of a thread is spent in the > MVStore : > > org.h2.store.fs.FileNio.read(FilePathNio.java:74) > > > (no other threads are taking up time or have to do with H2) > > "MVStore background writer nio:/mnt/data/x/x-db.mv.db" #104 daemon prio=5 > os_prio=0 tid=0x7ff09c299000 nid=0x7208 runnable [0x7ff0832f3000] >java.lang.Thread.State: RUNNABLE > at sun.nio.ch.NativeThread.current(Native Method) > at sun.nio.ch.NativeThreadSet.add(NativeThreadSet.java:46) > at sun.nio.ch.FileChannelImpl.readInternal(FileChannelImpl.java:737) > at sun.nio.ch.FileChannelImpl.read(FileChannelImpl.java:727) > at org.h2.store.fs.FileNio.read(FilePathNio.java:74) > at > org.h2.mvstore.cache.FilePathCache$FileCache.read(FilePathCache.java:87) > at org.h2.mvstore.DataUtils.readFully(DataUtils.java:429) > at org.h2.mvstore.FileStore.readFully(FileStore.java:98) > at org.h2.mvstore.Page.read(Page.java:190) > at org.h2.mvstore.MVStore.readPage(MVStore.java:1944) > at org.h2.mvstore.MVMap.readPage(MVMap.java:736) > at org.h2.mvstore.Page.getChildPage(Page.java:217) > at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) > at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) > at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) > at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) > at org.h2.mvstore.MVMap.rewrite(MVMap.java:782) > at org.h2.mvstore.MVStore.compactRewrite(MVStore.java:1910) > at org.h2.mvstore.MVStore.compact(MVStore.java:1802) > - locked <0x87734c88> (a java.lang.Object) > at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2504) > > > This couldn't be normal, I think..? Does this have to do with the MVStore > compaction? > Thanks in advance for your assistance! > > Kind Regards > > Andreas W > > -- > 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+unsubscr...@googlegroups.com > > . > To post to this group, send email to h2-database@googlegroups.com > . > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] How to delete duplicate rows?
Hi, This is a generic SQL question, not related to H2, best asked at StackOverflow.com. Regards, Thomas On Wednesday, March 1, 2017, Imran Ahmed wrote: > Greetings, > > How do I go about deleting duplicate rows? In other words, if multiple > rows have a same value for a specific column, how do I go about deleting > all but one rows? > > Thanks, > Imran. > > -- > 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+unsubscr...@googlegroups.com > > . > To post to this group, send email to h2-database@googlegroups.com > . > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] H2 lucene
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][5] General error: "java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]" [5-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 On Wed, Mar 1, 2017 at 12:37 PM, Дмитрий Моряков wrote: > 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 > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] "Hexadecimal string contains non-hex character" when opening db
Hi, Could you post the exception stack trace please? Regards, Thomas On Wed, Mar 1, 2017 at 11:27 AM, JohanBerg wrote: > A user with H2 in an application gets this error code: > "org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex > character: "-psn_0_57358" [90004-176]". The application is written in Java > 8 and runs on macOS sierra (10.12.1). > > The error code appears directly after opening the database with the > following argument: "jdbc:h2:file:" + database + > ";CIPHER=XTEA;PASSWORD_HASH=TRUE;IFEXISTS=TRUE". > > Has anyone seen something similar? Could meta-data get corrupted and cause > this? > > Best regards, > Johan Berg > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] H2 lucene
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 -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] H2 lucene
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 On Wed, Mar 1, 2017 at 10:58 AM, Sylvain Pointeau < sylvain.point...@gmail.com> wrote: > Hi Thomas, > > Thank you for your reply, this morning, I re-opened my DB (created > yesterday) > > [2017-03-01 10:10:41] [HY000][5] General error: > "java.lang.IllegalStateException: > Chunk 5451 no longer exists [1.4.193/9]" [5-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.( > Database.java:267) > ... > > I am not sure I can really use the FTS if the DB is not reliable anymore... > > Best regards, > Sylvain > > > On Wed, Mar 1, 2017 at 7:46 AM, Thomas Mueller Graf < > thomas.tom.muel...@gmail.com> wrote: > >> Hi, >> >> Patches to upgrade Lucene are welcome. >> >> Regards, >> Thomas >> >> >> On Tuesday, February 28, 2017, Sylvain Pointeau < >> sylvain.point...@gmail.com> wrote: >> >>> 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 >>> >>> -- >>> 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+unsubscr...@googlegroups.com. >>> To post to this group, send email to h2-database@googlegroups.com. >>> Visit this group at https://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- >> 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+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> Visit this group at https://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] "Hexadecimal string contains non-hex character" when opening db
A user with H2 in an application gets this error code: "org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex character: "-psn_0_57358" [90004-176]". The application is written in Java 8 and runs on macOS sierra (10.12.1). The error code appears directly after opening the database with the following argument: "jdbc:h2:file:" + database + ";CIPHER=XTEA;PASSWORD_HASH=TRUE;IFEXISTS=TRUE". Has anyone seen something similar? Could meta-data get corrupted and cause this? Best regards, Johan Berg -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] H2 lucene
Hi Thomas, Thank you for your reply, this morning, I re-opened my DB (created yesterday) [2017-03-01 10:10:41] [HY000][5] General error: "java.lang.IllegalStateException: Chunk 5451 no longer exists [1.4.193/9]" [5-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.(Database.java:267) ... I am not sure I can really use the FTS if the DB is not reliable anymore... Best regards, Sylvain On Wed, Mar 1, 2017 at 7:46 AM, Thomas Mueller Graf < thomas.tom.muel...@gmail.com> wrote: > Hi, > > Patches to upgrade Lucene are welcome. > > Regards, > Thomas > > > On Tuesday, February 28, 2017, Sylvain Pointeau < > sylvain.point...@gmail.com> wrote: > >> 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 >> >> -- >> 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+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> Visit this group at https://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.