[h2] what data is removed during DEFRAG
My databases (mvstore) are growing rapidly, much more than I would expect based on the entered data. I also see CPU usage increasing. Performing a DEFRAG reduces the database size significantly, sometimes by 60-70%. Running SCRIPT TO / RUNSCRIPT also has a similar effect. How can I identify what 'pollution' is consuming so much space? Browsing through the INFORMATION_SCHEMA has not provided any clues. We recently started using "CREATE MEMORY LOCAL TEMPORARY TABLE IF NOT EXISTS" more frequently. Could this be related to the problem? -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4d828811-d255-415f-98ad-943c1255b8a4n%40googlegroups.com.
[h2] Can aliases made by CREATE ALIAS , be retrieved by SQL?
Can aliases made by CREATE ALIAS , be retrieved by SQL? could not find any documentation about this also seached the system tables without any result. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c930849a-c17c-4ddf-9356-03ad1f65aef8n%40googlegroups.com.
[h2] Re: what about *.mv.db files
forget this question. The files mentioned are empty databases. They still cause an exception during backup (duplicate entry) Op zaterdag 27 april 2024 om 09:44:00 UTC+2 schreef Rinse Lemstra: > Just moved to mvstore. After conversion in some directories there is a > *.mv.db file, all have the same size ~ 20k. > > These mv.db files cause an exception while making a backup. > > What are these files? > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/faa26db5-5131-4d7b-831c-2f488a4db0een%40googlegroups.com.
[h2] what about *.mv.db files
Just moved to mvstore. After conversion in some directories there is a *.mv.db file, all have the same size ~ 20k. These mv.db files cause an exception while making a backup. What are these files? -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1bfbff4d-f068-4d98-a9ce-d9f0c4f33ec1n%40googlegroups.com.
[h2] how much indexes are used for optimization
from the H2 website: If a table has multiple indexes, *sometimes more than one index could be used.* Example: if there is a table TEST(ID, NAME, FIRSTNAME) and an index on each column, then two indexes could be used for the query SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B', the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time. In what conditions more then one index can be used? regards, Rinse -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4e853fca-fab5-4955-b648-14083480ccc4n%40googlegroups.com.
[h2] Using field value as part of expression??
Suppose the field 'Abbr' does contain the value "ABC" and here is also a field with the name "ABC_budget". Is there an SQL expression possible to get the value of field ABC_budget using the contents of field Abbr ? Thanks in advance -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6ce94693-9482-4f82-a560-4a612875221bn%40googlegroups.com.
[h2] Unexpetced result when calculate with fields
Hi, For this test I use this table: drop table if exists test; create table test(minutes numeric(12,2)); insert into test values(1200); select sum(minutes)/60 from test; When I execute this I get as result 2E+1. How can I get a more readable output? -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e8cafc31-c533-4342-a51b-cc6273da42f4n%40googlegroups.com.
[h2] suggestion/feature request CSVREAD respect column names
with CSVREAD one can insert/merge records from a .csv file. Although the csv file does containt column names, they are not respected. Wouldn't it be nice if CSVREAD could respect these column names while importing the data? regards, Rinse -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/fcb3efb8-74d9-473b-bbee-9dae23b0e828%40googlegroups.com.
[h2] Can INFORMATION_SCHEMA be used to find last_modified of a table
I am looking for a fast way to determine the last_modified of each table. I found the LAST_MODIFIED column in INFORMATION_SCHEMA\TABLES, however this field is allways 0 Is there any way to use the INFORMATION_SCHEMA to quickly find the tables wich are modified since a specific timestamp ? Thanks, Rinse -- 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] Re: Recovery of encrypted databases
Realy no one has experience with recovery of encrypted databases ? Op dinsdag 29 mei 2018 09:25:04 UTC+2 schreef Rinse Lemstra: > > Until now we have used UnEncrypted databases. Every now and then we need > the recovery tool to repair a corrupted database, succes rate ~98%. > > We do considder using database encryption. > > What can be said about the succes rate of using the recovery tool on > encrypted databases ? > > regards, > > Rinse > > -- 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] Recovery of encrypted databases
Until now we have used UnEncrypted databases. Every now and then we need the recovery tool to repair a corrupted database, succes rate ~98%. We do considder using database encryption. What can be said about the succes rate of using the recovery tool on encrypted databases ? regards, Rinse -- 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] SET MODE MSSQLSERVER
not sure what to expect from this. Can a H2 server in MSSQLSERVER mode replace a M$ SQL server ? (so clients only able to connect with M$ can work with H2) ? regards, Rinse -- 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] Re: What will happen if......
Any other suggestions how curruptions like below could arise ? Caused by: org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "22081 of 21060". Possible solution: use the recovery tool [90030-190] at org.h2.store.PageStore.readPage(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.getPage(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.index.PageDataIndex.getPage(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.index.PageDataIndex.(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.table.RegularTable.(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.addMeta(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.readMetaData(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.recover(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.openExisting(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.store.PageStore.open(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Database.getPageStore(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Database.open(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Database.openDatabase(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Database.(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Engine.openSession(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Engine.openSession(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Engine.createSessionAndValidate(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Engine.createSession(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.Engine.createSession(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.engine.SessionRemote.connectEmbeddedOrServer(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.jdbc.JdbcConnection.(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.jdbc.JdbcConnection.(Unknown Source) ~[h2-1.4.190.jar:1.4.190] at org.h2.Driver.connect(Unknown Source) ~[h2-1.4.190.jar:1.4.190] Op dinsdag 8 november 2016 14:21:49 UTC+1 schreef Rinse Lemstra: > > We open our database with: > > > > Connection connection = new > org.h2.Driver().connect(“jdbc:h2:file:\\Data;FILE_LOCK=FILE;AUTO_SERVER=TRUE;MODE=REGULAR;MV_STORE=FALSE;DB_CLOSE_DELAY=0", > > new Properties() ); > > > > We close our database with: > > > > connection.close(); > > > > > > What will happen if the user in (auto_server) server role performs a > connection.close while a user in (auto_server) client role is still > performing updates? > > > > Could this lead to database corruptions? > > > > regards, > > > Rinse > > > -- 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] What will happen if......
We open our database with: Connection connection = new org.h2.Driver().connect(“jdbc:h2:file:\\Data;FILE_LOCK=FILE;AUTO_SERVER=TRUE;MODE=REGULAR;MV_STORE=FALSE;DB_CLOSE_DELAY=0", new Properties() ); We close our database with: connection.close(); What will happen if the user in (auto_server) server role performs a connection.close while a user in (auto_server) client role is still performing updates? Could this lead to database corruptions? regards, Rinse -- 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] Is there a fast and reliable way to test if an index up-to-date
Some users of our application who have choosen to use the AUTO_SERVER option, do experience index-curruption every now and then. We think there are two approches to this problem: 1st, avoid the corruption and 2nd minimize the effects by detecting curruption. Is there a fast and reliable way to test if an index is up-to-data or corrupted ? Thanks in advance, Rinse -- 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] Error when connencting to tcp server (when using auto_server)
necessary must be unnecessary Op dinsdag 19 april 2016 14:35:18 UTC+2 schreef Rinse Lemstra: > > It looks necessary and might hide a bug > > Op dinsdag 19 april 2016 11:59:08 UTC+2 schreef Noel Grandin: >> >> If the connection works, why do you care? >> > -- 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] Error when connencting to tcp server (when using auto_server)
It looks necessary and might hide a bug Op dinsdag 19 april 2016 11:59:08 UTC+2 schreef Noel Grandin: > > If the connection works, why do you care? > -- 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] Database size keeps growing when creating and deleting CLOBs
Is SCRIPT TO / RUNSCRIPT not more or less the same ? regards, Rinse > > -- 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 DB in Auto Server Mode
Op dinsdag 6 oktober 2015 07:45:03 UTC+2 schreef Thomas Mueller: > > Hi, > > Yes it should work. I'm wondering which shared file system you use? > > Regards, > Thomas > There are versions of SMB witch use caching. On these versions it can take up to 10 seconds before a new file becomes visible for other users, also file delete is a affected by this delay. regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Is there a way to detect ' rowCount expected' corruptions ?
Now these corruptions can appear everywhere in my program, I would like te test for this corruption after opening the database. Is there a light weight solution for this ? Thanks in advance, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] ALTER TABLE DROP COLUMN does not support multiple columns
Hello, Is it correct that ALTER TABLE DROP COLUMN does not support multiple columns in one statement ? As I have to drop more then one column in a rather large table it would be very efficient to do this in one statement. regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Is there a setting for letting null and empty values sort equal
Now empty fields and null values are sorted in two groups. Is there a setting or command to let both values sort equaly ? regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Is there a setting for letting null and empty values sort equal
The resultset contains fields from two joined tables. The result field from the joined table is a varchar. a) If there is a match on ID between the two tables and the field has a value, the value is returned b) If there is a match on ID between the two tables and the field has no value, the value returned is an empty string c) If theere is no match on ID between the two tables, the returned value is null I would like to have b) and c) to return the same result so they would sort equaly. regards, Rinse Op donderdag 18 juni 2015 18:33:10 UTC+2 schreef Rami Ojares: What do you mean by empty fields? On 18 Jun 2015 19:29, Rinse Lemstra r.le...@xso.nl javascript: wrote: Now empty fields and null values are sorted in two groups. Is there a setting or command to let both values sort equaly ? regards, Rinse -- 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...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.com javascript:. Visit this group at http://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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Can I controll the index usage ?
The query below takes about 5 sconds to run select uren.MEDEW_ID as MEDEW_ID, uren.START as UREN_WEEK, sum(uren.DUUR) as DUUR from uren as uren where uren.START = '2015-05-25' and uren.START '2015-06-01' and *uren.type = 0* group by uren.MEDEW_ID, UREN_WEEK When I remove the *uren.type = 0* where clause, the query runs in a few miliseconds. When I EXPLAIN the query I see it uses the index on the field TYPE, when I remove the *uren.type = 0*, the query uses the index on START. Also, when I remove the index on the field TYPE the query runs fast. SELECT UREN.MEDEW_ID AS MEDEW_ID, UREN.START AS UREN_WEEK, SUM(UREN.DUUR) AS DUUR FROM PUBLIC.UREN UREN /* PUBLIC.UREN_TYPE: TYPE = 0 */ /* scanCount: 632096 */ WHERE (UREN.TYPE = 0) AND ((UREN.START = '2015-05-14') AND (UREN.START '2015-05-28')) GROUP BY UREN.MEDEW_ID, PUBLIC.GETWEEKSTRING(UREN.START) /* total: 25688 UREN.UREN_DATA read: 22925 (89%) UREN.UREN_TYPE read: 2763 (10%) If I am right, the existance of an index is not allways a performance improvement, in this case it causes the poor performance. How to recognize such situations, and how to controll them ? regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Can I controll the index usage ?
Hello Noel, Thanks for your prompt response. Op donderdag 4 juni 2015 10:22:01 UTC+2 schreef Noel Grandin: Change your index so that it is indexing on (START, TYPE) That way, things that just need an index on START can use it as well. In this example it works fine, however I also have query's (with filters on TYPE) witch are not related to START. regards, Rinse On 2015-06-04 10:16 AM, Rinse Lemstra wrote: The query below takes about 5 sconds to run select uren.MEDEW_ID as MEDEW_ID, uren.START as UREN_WEEK, sum(uren.DUUR) as DUUR from uren as uren where uren.START = '2015-05-25' and uren.START '2015-06-01' and *uren.type = 0* group by uren.MEDEW_ID, UREN_WEEK When I remove the *uren.type = 0* where clause, the query runs in a few miliseconds. When I EXPLAIN the query I see it uses the index on the field TYPE, when I remove the *uren.type = 0*, the query uses the index on START. Also, when I remove the index on the field TYPE the query runs fast. -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Can I controll the index usage ?
Op donderdag 4 juni 2015 10:59:28 UTC+2 schreef Stig Christensen: Just add two indexes I tried, then H2 prefers using the single index and is slow again. -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Could data corruption being caused by non-accurate .lock.db information ?
Ok, certain versions of windows do have this behaviour, see: https://groups.google.com/forum/?utm_source=digestutm_medium=email#!topic/h2-database/w65UQETmo7U Rinse Op donderdag 22 januari 2015 21:36:09 UTC+1 schreef Thomas Mueller: Hi, on some windows-version the .lock.db file is not visible for remote connections for up to 10 seconds OK, if you have a file system where a new file is not visible for up to 10 seconds, then this is not something that can be supported by the auto-server mode (by this file locking method). In that case, the auto-server mode can't be used. If you do use it, then probably the database gets corrupt. 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Could data corruption being caused by non-accurate .lock.db information ?
What could be the result if H2 thinks it is the first user ( no .lock.db file ), but in reality some other user opened the database just before ? (on some windows-version the .lock.db file is not visible for remote connections for up to 10 seconds) regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Database corruption after upgrade to 1.3.176 (from 1.3.169)
Today put a new version of our product live, containing 1.4.184 in a AUTO_SERVER scenario. Got serveral exceptions not seen before: Caused by: org.h2.jdbc.JdbcSQLException: Error opening database: \Concurrent update\ [8000-184] org.h2.store.FileLock.getExceptionFatal(Unknown Source) org.h2.store.FileLock.lockFile(Unknown Source) org.h2.store.FileLock.lock(Unknown Source) org.h2.engine.Database.open(Unknown Source) org.h2.engine.Database.openDatabase(Unknown Source) org.h2.engine.Database.init(Unknown Source) org.h2.engine.Engine.openSession(Unknown Source) org.h2.engine.Engine.openSession(Unknown Source) org.h2.engine.Engine.unknown(Unknown Source) org.h2.engine.Engine.createSession(Unknown Source) org.h2.engine.Engine.unknown(Unknown Source) org.h2.engine.SessionRemote.connectEmbeddedOrServer(Unknown Source) org.h2.jdbc.JdbcConnection.init(Unknown Source) org.h2.jdbc.JdbcConnection.unknown(Unknown Source) org.h2.Driver.connect(Unknown Source) Maybe it helps to find the reason for this problems. We decided to revert to some previous version. regards, Rinse Op maandag 12 januari 2015 02:01:40 UTC+1 schreef marcolopes: Since we upgrade to *1.3.176* (from 1.3.169) in our final product, dozens of users are experiencing problems with damaged databases. Connection STRING used hasn't changed, and is: jdbc:h2:file:database;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=1 Here are a few logs: org.h2.jdbc.JdbcSQLException: *General error: java.lang.RuntimeException: page[35624] data leaf table:206 T206 entries:0 parent:20233 keys:null offsets:null parent 20233 expected 0 [5-176]* at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.convert(DbException.java:294) at org.h2.engine.Database.openDatabase(Database.java:291) at org.h2.engine.Database.init(Database.java:254) at org.h2.engine.Engine.openSession(Engine.java:57) at org.h2.engine.Engine.openSession(Engine.java:164) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:142) at org.h2.engine.Engine.createSession(Engine.java:125) at org.h2.engine.Engine.createSession(Engine.java:27) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:331) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:107) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:91) at org.h2.Driver.connect(Driver.java:74) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.projectocolibri.rcp.colibri.dao.database.connection.ConnectionManager$DRIVERS.checkConnection(ConnectionManager.java:220) at org.projectocolibri.rcp.colibri.dao.database.connection.DatabaseParameters.checkConnection(DatabaseParameters.java:76) at org.projectocolibri.rcp.colibri.dao.database.ColibriLogin.initialize(ColibriLogin.java:93) at org.projectocolibri.rcp.colibri.dao.database.ColibriLogin.process(ColibriLogin.java:60) at org.projectocolibri.rcp.colibri.workbench.shells.login.LoginShell$1.widgetSelected(LoginShell.java:178) at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248) at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84) at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053) at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169) at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758) at org.dma.eclipse.swt.custom.CustomShell.openAndSleep(CustomShell.java:47) at org.projectocolibri.rcp.colibri.workbench.shells.login.LoginShell.init(LoginShell.java:86) at org.projectocolibri.rcp.Application.start(Application.java:44) at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180) 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.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622) at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577) at org.eclipse.equinox.launcher.Main.run(Main.java:1410) Caused by: java.lang.RuntimeException: page[35624] data leaf table:206 T206 entries:0 parent:20233 keys:null offsets:null
[h2] Directory caching on SMBv2 shares
We recently had an issue where backup.zips made by H2 where not visible to remote clients for some time (up to 10 seconds) We now found the reason for this behaviour, it is caused by directory caching of windows running the SMBv2 protocol. see: http://technet.microsoft.com/en-us/library/ff686200(v=ws.10).aspx This could also apply to the AUTO_SERVER behaviour where the election of the server role is based on the .lock.db file. A possible solution could adding a WatchService on the database directory. http://docs.oracle.com/javase/tutorial/essential/io/notification.html regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] What would be the best update-candidate
Hello, We are currently using version h2-1.3.176 In AUTO_SERVER mode we experiece corruption of various kinds: index not found 202 File corrupted while reading record: \[1280] stream data key:1344 pos:11 remaining:0\. rowCount expected 11462 got 11466 What would be the best / most stable update candidate ? (with the least ore none modification to our source ) Thanks in advance, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] What would be the best update-candidate
Connecting to H2 database: jdbc:h2:file:c:\TimeWriter5Std\lib\..\data\xso_standard\default\Data.std.h2.db H2 properties: {AUTO_SERVER=TRUE, MODE=REGULAR, CACHE_SIZE=16384, DB_CLOSE_DELAY=0, IGNORECASE=TRUE, DATABASE_TO_UPPER=TRUE} Does this make sense ? regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] What would be the best update-candidate
Thanks for your advice. One final question: 1.3.176 is the last version with the 'Beta' addition. Am I right that this 'Beta' addition applies particulary on the MVstore code ? regards, Rinse Op vrijdag 9 januari 2015 09:36:20 UTC+1 schreef Noel Grandin: OK, that's good, there is nothing problematic in your settings. Probably the best update-candidate is 1.4.184, but add MV_STORE=false to your URL to keep using the old(stable) storage engine. On 2015-01-09 10:23 AM, Rinse Lemstra wrote: Connecting to H2 database: jdbc:h2:file:c:\TimeWriter5Std\lib\..\data\xso_standard\default\Data.std.h2.db H2 properties: {AUTO_SERVER=TRUE, MODE=REGULAR, CACHE_SIZE=16384, DB_CLOSE_DELAY=0, IGNORECASE=TRUE, DATABASE_TO_UPPER=TRUE} Does this make sense ? -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] What is kept in PageStores and when is it released ?
We have put a new (H2-based) product live. Now the numbers of users are growing, the memory consumption also grows. JVisualVM learns us that the top 20 memory consuming objects are org.h2.store.PageStore#xx and org.h2.util.CacheLRU#xx Could someone explain what is kept in these objects ? when will they being released ? regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Backup.zip not immediately available
Hello Thomas, Yes, we wait until the calling class does get control back, then we check if the file exist with new File( [backup.zip] ).exist() regards, Rinse Op donderdag 11 december 2014 18:25:29 UTC+1 schreef Thomas Mueller: Hi, OK. Do you want for the command to return? How do you know the file is not available (does the file not exist, is the size incorrect,...)? Regards, Thomas On Thu, Dec 11, 2014 at 9:39 AM, Rinse Lemstra r.lems...@xso.nl wrote: I have a database connection made with the URL below jdbc:h2:file:D:\TW\lib\..\data\xso_standard\default\Data.std;AUTO_SERVER=TRUE;MODE=REGULAR;CACHE_SIZE=16384;DB_CLOSE_DELAY=0;IGNORECASE=TRUE;DATABASE_TO_UPPER=TRUE The database is located on a shared (remote) disk, when the database is on a local disk I cannot reproduce the problem. I invoke the sql command 'BACKUP TO [zipname].zip' Then I check with a FileExist if the \\fullPath\[zipname].zip does exist. If I am the first user of the database (so I am running the 'server-role' in the AUTO_SERVER concept, I cannot reproduce the problem If I am the second (or more) user of the database (so I am running the 'client-role' in the AUTO_SERVER conccept, I can have the behaviour as descibed (zip avalable after some dealy) regards, Rinse Op dinsdag 9 december 2014 18:03:36 UTC+1 schreef Thomas Mueller: Hi, Could you describe what you do exactly (how you create the backup, how you copy it)? How do you know the file is not available? Regards, Thomas On Sunday, December 7, 2014, Rinse Lemstra r.le...@xso.nl wrote: We found that a backup.zip is not immediately ready for copying after creating the backup, it can take up to 5 seconds before the backup.zip is available. This behavior does not occur in all situations, it only occurs if the database is located on a share on a server, it may be related to the OS of the server. In particular Wndows small business server is one of them. Did any of you see this behavior, and if so, what could be done to eliminate this delay. regards, Rinse -- 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 http://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 http://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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Backup.zip not immediately available
I have a database connection made with the URL below jdbc:h2:file:D:\TW\lib\..\data\xso_standard\default\Data.std;AUTO_SERVER=TRUE;MODE=REGULAR;CACHE_SIZE=16384;DB_CLOSE_DELAY=0;IGNORECASE=TRUE;DATABASE_TO_UPPER=TRUE The database is located on a shared (remote) disk, when the database is on a local disk I cannot reproduce the problem. I invoke the sql command 'BACKUP TO [zipname].zip' Then I check with a FileExist if the \\fullPath\[zipname].zip does exist. If I am the first user of the database (so I am running the 'server-role' in the AUTO_SERVER concept, I cannot reproduce the problem If I am the second (or more) user of the database (so I am running the 'client-role' in the AUTO_SERVER conccept, I can have the behaviour as descibed (zip avalable after some dealy) regards, Rinse Op dinsdag 9 december 2014 18:03:36 UTC+1 schreef Thomas Mueller: Hi, Could you describe what you do exactly (how you create the backup, how you copy it)? How do you know the file is not available? Regards, Thomas On Sunday, December 7, 2014, Rinse Lemstra r.le...@xso.nl javascript: wrote: We found that a backup.zip is not immediately ready for copying after creating the backup, it can take up to 5 seconds before the backup.zip is available. This behavior does not occur in all situations, it only occurs if the database is located on a share on a server, it may be related to the OS of the server. In particular Wndows small business server is one of them. Did any of you see this behavior, and if so, what could be done to eliminate this delay. regards, Rinse -- 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 http://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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Backup.zip not immediately available
We found that a backup.zip is not immediately ready for copying after creating the backup, it can take up to 5 seconds before the backup.zip is available. This behavior does not occur in all situations, it only occurs if the database is located on a share on a server, it may be related to the OS of the server. In particular Wndows small business server is one of them. Did any of you see this behavior, and if so, what could be done to eliminate this delay. regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] how to become a donor
Hi Thomas en Noel, Thanks for your response... The key point of my message was: I think it would help if there was an email address for paypal donations... In other words: I want to become a donor, but I don't know wich email-address to use for my donation. regards, Rins -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] how to become a donor
The H2 website does contain a list of donors, however there is no information on how te become a donor I think it would help if there was an email address for paypal donations... regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Server side cursors
Server side cursors could be a usefull feature for our product. In the roadmap I see it has priority 1 on the current version. Just for sake of our internal planning: in what therms of time could we expect first results of this feaure ( weeks, month, years ?) regards, Rinse -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Most efficient way to duplicate tables
I have to ( frequently ) duplicate tables to another database, what is the most efficient ( and generic) way to do so ? Found the Create table [table] as Select way, however this does not duplicate the indexes... regards, Rinse Lemstra -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Best approach for using H2 over public internet
What would be the best approach to use H2 over a public internet, in terms of stability and performance ? Wrapping JDBC in a compression-class ? Serverlets ? .? Thanks in advance, Rinse -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
infinite loop when using user defined variables
Hello, While researching a performance issue when using user defined variables ( @variable ) I ran into a infinite loop. Running the query below without a variable the query runs in a few miliseconds. When running it with a user define variable the server runs for several hours. The EXPLAIN command shows some weird results.. (database available at request) set @SourceGroupType ='GR_MED'; explain select stam.naam, stam.id from stam where stam.id in ( select rel.id2 as id /*IT_MED - GR_MED - GR_AT - IT_AT */ from relaties as rel where rel.type2 = 'IT_AT2' and rel.id1 in ( select subrel.id2 from relaties as subrel where subrel.type2 = 'GR_AT2' and subrel.id1 in ( select subsubrel.id1 from relaties as subsubrel where subsubrel.ID2 = 402 and subsubrel.type1 = @SourceGroupType ) ) ) SELECT STAM.NAAM, STAM.ID FROM PUBLIC.STAM /* PUBLIC.STAM_ID: ID IN(SELECT REL.ID2 AS ID FROM PUBLIC.RELATIES REL /++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBREL.ID2 FROM PUBLIC.RELATIES SUBREL /++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) ++/ WHERE (SUBREL.TYPE2 = 'GR_AT2') AND (SUBREL.ID1 IN( SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE ++/ WHERE (REL.TYPE2 = 'IT_AT2') AND (REL.ID1 IN( SELECT SUBREL.ID2 FROM PUBLIC.RELATIES SUBREL /++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) ++/ WHERE (SUBREL.TYPE2 = 'GR_AT2') AND (SUBREL.ID1 IN( SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) */ WHERE STAM.ID IN( SELECT REL.ID2 AS ID FROM PUBLIC.RELATIES REL /* PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBREL.ID2 FROM PUBLIC.RELATIES SUBREL /++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) ++/ WHERE (SUBREL.TYPE2 = 'GR_AT2') AND (SUBREL.ID1 IN( SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE */ WHERE (REL.TYPE2 = 'IT_AT2') AND (REL.ID1 IN( SELECT SUBREL.ID2 FROM PUBLIC.RELATIES SUBREL /* PUBLIC.RELATIES_ID1: ID1 IN(SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) */ WHERE (SUBREL.TYPE2 = 'GR_AT2') AND (SUBREL.ID1 IN( SELECT SUBSUBREL.ID1 FROM PUBLIC.RELATIES SUBSUBREL /* PUBLIC.RELATIES_ID2: ID2 = 402 */ WHERE (SUBSUBREL.ID2 = 402) AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE)) -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Optimize joins ?
In our application we have defined skeleton query's where we add/ replace some clauses when the query is executed. Example: Select [fields] from MasterTable left join detail1 as Stem1 on MasterTable.id1 = Stem1.id left join detail2 as Stem2 on MasterTable.id2 = Stem2.id left join detail3 as Stem3 on MasterTable.id3 = Stem3.id [where] [group] [order] The sections [fields], [where] , [group] ,[order] are replace by actual values based on what the end-user has defined. If the end-user did not choose any outputfield or filtercondition from detail1, detail2 or detail3 the peformance of this query is needles slowed down. Should it be appropriate for the database engine to remove all join statements if there are no references to the aliases envolved ? regards, Rinse -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Website question
When visiting the website ( h2database.com ) with IE8 I am not able to select text and copy it to the clipboard. Only selecting all text (Ctrl-A) does work. I found that the line below does cause this behaviour. td class=nav style=cursor: e-resize; onmousedown=return mouseDown(event) onmouseup=return mouseUp(event)nbsp;/td As the beaviour in firefox is not influenced by this code, I assume that this behaviour is not by design. regards, Rinse -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
off-line download
Zipped versions of the pages and files associated with this group will be available for download until August 31, 2011. After this date, this feature and the zip file downloads will be turned off permanently. Download pages | Download Files When I try to download these files, they appear to have no (or very less) content. regards, Rinse -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.