[h2] Re: H2 seems to create a huge number of orphan lobs since 1.4.183.
hi, If license is an issue we can always create something simple as geodb wrapper UDig (main application) license is BSD. In order to be able to link with GPL based source code you need to include the GPL as well as the original license, and you need to make it clear that each individual file as a whole is covered by the GPL, as well as BSD for portions of it. Have you ever tried H2 as a spatial database behind some GIS interface like UDig or other desktop Java GIS app? It is definitely great db with excellent performance. We develop our own desktop GIS OrbisGIS 5.1 which is working with H2GIS under the hood (or PostGIS) with direct access to SQL. http://www.orbisgis.org/download/ Erwan B. have successfully linked GeoServer with H2GIS too. Regards, -- Nicolas Fortin IRSTV FR CNRS 2488 GIShttp://orbisgis.org Spatial DB http://h2gis.org http://www.google.com/url?q=http%3A%2F%2Fh2gis.orgsa=Dsntz=1usg=AFQjCNH3xVHyQCTdh2pCB5uHdgu5F0u-yg Noise http://noisemap.orbisgis.org -- 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] NPE in at org.h2.store.LobStorageBackend.copyLob(LobStorageBackend.java:453) becuase of TRIGGER initialization..
Hi, There were some changes in this area, and an important bugfix in version 1.4.187. If the LOB data was already removed before that, then I'm afraid the data can not be restored easily. See also the change log for details. Regards, Thomas On Sunday, May 3, 2015, Vitali vita...@gmail.com wrote: Hi. H2 containts triggers that at initialization time are trying to read from some table from LOB column. Because it occurs inorg.h2.engine.Database.open(Database.java:735): org.h2.jdbc.JdbcSQLException: General error: java.lang.NullPointerException; SQL statement: select * from SMGIS.SYM_ON_U_FOR_TRG_TSS_HNK_FLD_CONFIG [5-187] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:168) at org.h2.message.DbException.convert(DbException.java:295) at org.h2.command.Command.executeQuery(Command.java:209) at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:79) at org.jumpmind.symmetric.db.AbstractEmbeddedTrigger.getTemplates(AbstractEmbeddedTrigger.java:265) at org.jumpmind.symmetric.db.AbstractEmbeddedTrigger.init(AbstractEmbeddedTrigger.java:78) at org.jumpmind.symmetric.db.h2.H2Trigger.init(H2Trigger.java:51) at org.h2.schema.TriggerObject.load(TriggerObject.java:81) at org.h2.schema.TriggerObject.setTriggerAction(TriggerObject.java:136) at org.h2.schema.TriggerObject.setTriggerClassName(TriggerObject.java:118) at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:115) at org.h2.engine.MetaRecord.execute(MetaRecord.java:58) at org.h2.engine.Database.open(Database.java:735) at org.h2.engine.Database.openDatabase(Database.java:266) at org.h2.engine.Database.init(Database.java:260) at org.h2.engine.Engine.openSession(Engine.java:60) at org.h2.engine.Engine.openSession(Engine.java:167) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:145) at org.h2.engine.Engine.createSession(Engine.java:128) at org.h2.engine.Engine.createSession(Engine.java:26) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:347) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:108) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:92) at org.h2.Driver.connect(Driver.java:72) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) We get NPE as: Caused by: java.lang.NullPointerException at org.h2.store.LobStorageBackend.copyLob(LobStorageBackend.java:453) at org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:495) at org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:38) at org.h2.result.LocalResult.cloneLobs(LocalResult.java:265) at org.h2.result.LocalResult.addRow(LocalResult.java:281) at org.h2.command.dml.Select.queryFlat(Select.java:585) at org.h2.command.dml.Select.queryWithoutCache(Select.java:685) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) because getLobStorage().init(); line is a bit later in Database class. Seems there was no problem in 1.4.181 that we have been using. Were there any changes in initialization logic of Database? Is loading from third-party tables in trigger a legal approach now? Vitali. -- 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 javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.com javascript:_e(%7B%7D,'cvml','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] MVCC + Multithreaded
Hi, Yes this could explain the problem. See also the change log. Regards, Thomas On Monday, May 4, 2015, Rami Ojares rami.oja...@gmail.com wrote: It seems that the database was running 187 but my webserver (and dev client) was running 186. Could that explain the problem? - Rami On 4.5.2015 15:54, Noel Grandin wrote: You seem to be running 1.4.186. Do you know that 1.4.187 is out? -- 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] Don't add FK single column index if column is leading key of existing index
Hi, Sorry it will take a few days until I can switch. Would it be possible for you to build H2 yourself, using the build script (build.sh / build.bat)? Regards, Thomas On Tuesday, May 5, 2015, wesona...@gmail.com wrote: Last night or tonight (Tues.)? Sent from my Cyanogen phone On May 4, 2015 10:45 PM, Thomas Mueller thomas.tom.muel...@gmail.com javascript:_e(%7B%7D,'cvml','thomas.tom.muel...@gmail.com'); wrote: Hi, The nightly build is currently created with Java 8 (on a Raspberry Pi by the way). I will switch to Java 7. Regards, Thomas On Friday, May 1, 2015, Wes Clark wesona...@gmail.com javascript:_e(%7B%7D,'cvml','wesona...@gmail.com'); wrote: I am using the nightly build ( http://www.h2database.com/automated/h2-latest.jar), but when I run I get java.lang.UnsupportedClassVersionError: org/h2/Driver : Unsupported major.minor version 52.0 trying to load the H2 driver. Is there a Java compiler mismatch? We're using Java 1.7 64-bit. On Apr 22, 2015 10:44 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, It should be available in the nightly build, see build / automated build. The next release is announced on Twitter, on this mailing list, and on the news mailing list. Regards, Thomas On Wednesday, April 22, 2015, Wes Clark wesona...@gmail.com wrote: Let me know when it is released. On Tue, Apr 21, 2015 at 10:59 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Thanks a lot! Yes, I think it is a bug. I have a fix for it now. This is related to a bugfix in version 1.4.179, change log Referential integrity constraints sometimes used the wrong index, such that updating a row in the referenced table incorrectly failed with a constraint violation. - but the bugfix was not completely correct. Regards, Thomas On Tuesday, April 21, 2015, Wes Clark wesona...@gmail.com wrote: Is this something that would be considered a bug and fixable? On Friday, April 17, 2015 at 10:41:55 AM UTC-7, Wes Clark wrote: Yep, that worked. Script attached. If you run in the browser console, you can browse the schema and see the two indexes. On Thursday, April 16, 2015 at 10:43:47 PM UTC-7, Thomas Mueller wrote: Hi, Yes, I think there was a change in this area because of a bug, but I don't fully remember. Could you please create a simple test case that shows the problem? I mean something like: drop all objects; create table parent(id int primary key); create table child(id int primary key, parent_id int, x int); create index y on child(parent_id, x); alter table child add constraint z foreign key(parent_id) references parent(id); script nosettings; Regards, Thomas On Friday, April 17, 2015, Wes Clark weso...@gmail.com wrote: My company, Guidewire, is attempting to move from H2 1.2 to 1.4 to take advantage of the MVStore feature that should greatly reduce deadlocks. Is it possible to change the behavior of H2 1.4 so that when a FK constraint is created it will not also create a single column index on that column if a multicolumn index already exists with that column as a leading key? in our case, many of our tables have a RETIRED column that is non-zero for rows that have been logically deleted. For columns being turned into a FK, there is already a two column index on the FK column and RETIRED, so we don't need and or want or expect a separate single column index on that FK. The behavior seems to have changed since 1.3. -- 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 a topic in the Google Groups H2 Database group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe . To unsubscribe from this group and all its topics, 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
Re: [h2] Bug in class org.h2.value.DataType (regarding type RESULT_SET)
Hi,
Yes, I think this is a bug. I'm still working on a test case.
Regards,
Thomas
On Tuesday, April 28, 2015, christoff.schm...@finaris.de wrote:
Hi,
in my opinion, the code below contains a bug in the last line. Shouldn't
it be
*return ValueResultSet.get(x); *
instead of
*return ValueResultSet.get(rs);*
?
The current code causes the current result set to be returned (rs), not
the result set within the column (x). (Affects last stable 1.3. and last
1.4 beta version)
org.h2.value.DataType:
[...]
case Value.RESULT_SET: {
ResultSet x = (ResultSet) rs.getObject(columnIndex);
if (x == null) {
return ValueNull.INSTANCE;
}
*return ValueResultSet.get(rs);*
}
[...]
Kind regards,
Christoff Schmitz
F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main
Fon: +49 (0)69 / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax: +49 (0)69 / 254 98 - 50
eMail:mailto:christoff.schm...@finaris.de
javascript:_e(%7B%7D,'cvml','christoff.schm...@finaris.de');
www: http://www.finaris.de und http://www.rapidrep.com
Disclaimer
The information contained in this e - mail and any attachments ( together
the message) is intended for the addressee only and
may contain confidential and/or privileged information. If you have
received the message by mistake please delete it and notify
the sender and do not copy or distribute it or disclose its contents to
anyone.
FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322
Frankfurt/Main, Germany
Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf.
Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl
--
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
javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com');
.
To post to this group, send email to h2-database@googlegroups.com
javascript:_e(%7B%7D,'cvml','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] JdbcPreparedStatement in Oracle mode don't honor treatEmptyStringsAsNull
Hi,
Sorry I don't understand. What is the problem exactly? Where is the
behavior of H2 different from Oracle?
Regards,
Thomas
On Monday, May 4, 2015, Peter Fich peterf...@gmail.com wrote:
While using H2 as a replacement for Oracle in test, I have come across
this problem.
Searching the group I have found nothing that indicates this is a know
issue. If it is, please can someone help me find the relevant thread.
I have added a test to TestCompatibilityOracle, that I believe shows the
problem:
stat.execute(CREATE TABLE F (ID NUMBER, X VARCHAR2(1)));
stat.execute(INSERT INTO F VALUES (1, 'a'));
PreparedStatement preparedStatement = conn.prepareStatement(INSERT
INTO F VALUES (2, ?));
preparedStatement.setString(1, );
preparedStatement.execute();
assertResult(2, stat, SELECT COUNT(*) FROM F);
assertResult(1, stat, SELECT COUNT(*) FROM F WHERE X IS NULL);
assertResult(0, stat, SELECT COUNT(*) FROM F WHERE X = '');
assertResult(new Object[][]{{1, a}, {2, null}}, stat, SELECT *
FROM F);
In the setString method JdbcPreparedStatement, ValueString.get(x) is
called without the flag. But getting the mode in there in an elegant way
seems hard to me.
Dose anyone have any suggestions on how to fix this?
Cheers,
Peter
--
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
javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com');
.
To post to this group, send email to h2-database@googlegroups.com
javascript:_e(%7B%7D,'cvml','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] Suitability of mvstore (stand-alone) in Android : robustness, processes killed, etc
Hi, The MVStore should work well with Android. The main differences to SQLite are: * SQLite overwrites / writes blocks of the size 4 KB. With flash or solid state disks this is not that great, as internally (by the firmware or OS) those writes are translated to much larger writes, for example 128 KB (this is called write amplification I think). H2 MVStore overwrites / writes chunks of size 1 MB. This is good for flash / solid state disks. * SQLite first writes to the transaction log, then calls fsync, then writes to the main data area. H2 MVStore only writes to one file (one write per change). * SQLite reclaims disk space much earlier; almost immediately. The H2 MVStore leaves old data for at least 45 seconds, which will result in higher (visible) disk space usage. On a lower, invisible level, for solid state disks and flash memory, the space usage is very similar. * In a crash, for SQLite, the last committed transaction is typically stored. For MVStore, the last second of transactions is typically lost. * The code of the MVStore is simpler, but SQLite is much older and more mature. The risk of bugs in SQLite is therefore smaller. Regards, Thomas On Saturday, May 2, 2015, alexrhel...@gmail.com wrote: Hello, I would like to use mvstore in my Android application. In Android, it is very common that your app (process) will get killed by the OS (no longer needed, *even if it has a Service is running*) or by the user (when swiped away from Recents). The bundled sqlite is extremely robust. In fact, if you look at most Android apps that use sqlite - it is opened / initialized in a Singleton or ContentProvider and *is never closed.* This is because the app usually doesn't have a chance to shut things down cleanly, the process is simply killed. So I would like to know, how well would mvstore do in this scenario? I would open the store, and leave it open for the app to use, but never really find a good moment to close() it. If data from in-flight transactions is lost, that is fine of course - but will the integrity of the file be OK? Thanks -Alex -- 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 javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.com javascript:_e(%7B%7D,'cvml','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] Using index to speed up order by in joins
Hi, No, I'm sorry, I think there is currently no way to influence this. Regards, Thomas On Monday, May 4, 2015, Peter peter.nilsson...@gmail.com wrote: I am having some problems with indices and order by in H2 1.3.176. I managed to get it to work when I only query one table by: * making sure the index has DESC specified as that is what I query on * including the fields from the where clause even though it should be unnecessary: explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 WHERE t166.OrderBookId = 'ABC' AND t166.MarketId = 123 AND t166.ExchangeCreationTimestamp = '2015-04-01T00:00:00.000+02:00' AND t166.ExchangeCreationTimestamp = '2015-05-04T23:59:59.999+02:00' ORDER BY t166.MarketId,t166.orderbookid,t166.ExchangeCreationTimestamp DESC; SELECT T166.TRADEID FROM PUBLIC.TRADES T166 /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = 'ABC' AND MARKETID = 123 */ /* scanCount: 1 */ WHERE (T166.EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00') AND ((T166.EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00') AND ((T166.ORDERBOOKID = 'ABC') AND (T166.MARKETID = 123))) ORDER BY =T166.MARKETID, =T166.ORDERBOOKID, =T166.EXCHANGECREATIONTIMESTAMP DESC LIMIT 11 /* index sorted */ /* total: 3 TRADES.FOO4 read: 3 (100%) */ But when I join with another table H2 chooses to process the other table first with the effect that the index cannot be used for sorting order (ie no index sorted): explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 INNER JOIN INSTRUMENTS t167 ON t166.OrderBookId = t167.OrderBookId AND t166.MarketId = t167.MarketId WHERE t167.Currency_CPT_UPPER = 'SEK' AND t166.ExchangeCreationTimestamp = '2015-04-01T00:00:00.000+02:00' AND t166.ExchangeCreationTimestamp = '2015-05-04T23:59:59.999+02:00' ORDER BY t166.ExchangeCreationTimestamp DESC; SELECT T166.TRADEID FROM PUBLIC.INSTRUMENTS T167 /* PUBLIC.INSTRUMENTS_12: CURRENCY_CPT_UPPER = 'SEK' */ /* WHERE T167.CURRENCY_CPT_UPPER = 'SEK' */ /* scanCount: 2785 */ INNER JOIN PUBLIC.TRADES T166 /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = T167.ORDERBOOKID AND MARKETID = T167.MARKETID */ ON 1=1 /* scanCount: 296814 */ WHERE ((T166.EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00') AND ((T167.CURRENCY_CPT_UPPER = 'SEK') AND (T166.EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00'))) AND ((T166.ORDERBOOKID = T167.ORDERBOOKID) AND (T166.MARKETID = T167.MARKETID)) ORDER BY =T166.EXCHANGECREATIONTIMESTAMP DESC LIMIT 11 /* total: 301063 INSTRUMENTS.INSTRUMENTS_12 read: 30 (0%) INSTRUMENTS.INSTRUMENTS_DATA read: 1995 (0%) TRADES.FOO4 read: 6589 (2%) TRADES.TRADES_DATA read: 292449 (97%) */ It does not matter if I include the join fields in the order by: explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 INNER JOIN INSTRUMENTS t167 ON t166.OrderBookId = t167.OrderBookId AND t166.MarketId = t167.MarketId WHERE t167.Currency_CPT_UPPER = 'SEK' AND t166.ExchangeCreationTimestamp = '2015-04-01T00:00:00.000+02:00' AND t166.ExchangeCreationTimestamp = '2015-05-04T23:59:59.999+02:00' ORDER BY t166.MarketId,t166.orderbookid,t166.ExchangeCreationTimestamp DESC; SELECT T166.TRADEID FROM PUBLIC.INSTRUMENTS T167 /* PUBLIC.INSTRUMENTS_12: CURRENCY_CPT_UPPER = 'SEK' */ /* WHERE T167.CURRENCY_CPT_UPPER = 'SEK' */ /* scanCount: 2785 */ INNER JOIN PUBLIC.TRADES T166 /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = T167.ORDERBOOKID AND MARKETID = T167.MARKETID */ ON 1=1 /* scanCount: 296814 */ WHERE ((T166.EXCHANGECREATIONTIMESTAMP = '2015-05-04T23:59:59.999+02:00') AND ((T167.CURRENCY_CPT_UPPER = 'SEK') AND (T166.EXCHANGECREATIONTIMESTAMP = '2015-04-01T00:00:00.000+02:00'))) AND ((T166.ORDERBOOKID = T167.ORDERBOOKID) AND (T166.MARKETID = T167.MARKETID)) ORDER BY =T166.MARKETID, =T166.ORDERBOOKID, =T166.EXCHANGECREATIONTIMESTAMP DESC LIMIT 11 /* total: 300673 INSTRUMENTS.INSTRUMENTS_12 read: 30 (0%) INSTRUMENTS.INSTRUMENTS_DATA read: 1995 (0%) TRADES.FOO4 read: 6592 (2%) TRADES.TRADES_DATA read: 292056 (97%) */ Here are the relevant indices: CREATE INDEX PUBLIC.FOO4 ON PUBLIC.TRADES(MARKETID, ORDERBOOKID, EXCHANGECREATIONTIMESTAMP DESC) CREATE INDEX PUBLIC.INSTRUMENTS_12 ON PUBLIC.INSTRUMENTS(CURRENCY_CPT_UPPER) Is there any way to use the index for order by even if the table with the order by fields is not the one processed first by the join? Or alternatively, is there a way to influence which table is processed first? -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop
Re: [h2] Performances of nested queries
Hi, I understand. However, I'm afraid I will not have time to work on this issues. I think those uses cases are not that common. Patches or ideas on how to fix this are welcome! Regards, Thomas On Sunday, May 3, 2015, Vitali vita...@gmail.com wrote: I would add also my case here. Seems a query like SELECT * FROM SOMETABLE WHERE SOMETABLE_ID IN (SELECT ID FROM SOMEFUNCTION( .. static input parameters.. )) also is not scalable very well. SOMETABLE_ID is a primary key with an index. SOMEFUNCTION is a deterministic function returning ResultSet, parameters are static Let's say main table contains 8 records, SOMEFUNCTION returns 3000 IDs. The query above works 4 seconds. If I rewrite it as SELECT * FROM SOMETABLE INNER JOIN (SELECT ID FROM SOMEFUNCTION( .. static input parameters.. )) ids ON ids.ID = SOMETABLE_ID then it works 10ms. More items are in set - performance drops exponentially. I think this case is much more widespread than the case that Quentine reported :) A variant of the case is a prepared statement query like: SELECT * FROM SOMETABLE WHERE SOMETABLE_ID IN (SELECT ID FROM TABLE(ID INT=?)) when SetInteger , for example, is passed to a prepared statement and time complexity should be O(n) iterating over IDs (that even passed a Java collection) and primary key index is asked to retrieve a record. Inner join also helps: SELECT * FROM SOMETABLE INNER JOIN (SELECT ID FROM TABLE(ID INT=?)) ids ON ids.ID = SOMETABLE_ID There is no problem to use inner joins, but for applications generating SQL dynamically an approach with SOMETABLE_ID IN (...) is preferable. Imagine UPDATE, DELETE queries where joins are not possible directly e.g. Vitali -- 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 javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.com javascript:_e(%7B%7D,'cvml','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] MVCC + Multithreaded
Ok, I will put the multithreaded back on making sure all clients are also using version 187. I will report if the corruption turns up again after that. - Rami On 6.5.2015 9:21, Thomas Mueller wrote: Hi, Yes this could explain the problem. See also the change log. Regards, Thomas On Monday, May 4, 2015, Rami Ojares rami.oja...@gmail.com mailto:rami.oja...@gmail.com wrote: It seems that the database was running 187 but my webserver (and dev client) was running 186. Could that explain the problem? - Rami On 4.5.2015 15:54, Noel Grandin wrote: You seem to be running 1.4.186. Do you know that 1.4.187 is out? -- 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 mailto:h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com mailto: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] NPE in at org.h2.store.LobStorageBackend.copyLob(LobStorageBackend.java:453) becuase of TRIGGER initialization..
Hi, Thomas. I think it's not a LOB-storage related problem. The problem as I specified in e-mail is that triggers during initialization are trying to read some CLOB data but LobStorageBackend is not yet initialized with a connection. Look to classes from stacktrace. It is because triggers are initialized earlier than LOB storage is initialized in Database class. Vitali. On Wednesday, May 6, 2015 at 9:21:48 AM UTC+3, Thomas Mueller wrote: Hi, There were some changes in this area, and an important bugfix in version 1.4.187. If the LOB data was already removed before that, then I'm afraid the data can not be restored easily. See also the change log for details. Regards, Thomas On Sunday, May 3, 2015, Vitali vit...@gmail.com javascript: wrote: Hi. H2 containts triggers that at initialization time are trying to read from some table from LOB column. Because it occurs inorg.h2.engine.Database.open(Database.java:735): org.h2.jdbc.JdbcSQLException: General error: java.lang.NullPointerException; SQL statement: select * from SMGIS.SYM_ON_U_FOR_TRG_TSS_HNK_FLD_CONFIG [5-187] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:168) at org.h2.message.DbException.convert(DbException.java:295) at org.h2.command.Command.executeQuery(Command.java:209) at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:79) at org.jumpmind.symmetric.db.AbstractEmbeddedTrigger.getTemplates(AbstractEmbeddedTrigger.java:265) at org.jumpmind.symmetric.db.AbstractEmbeddedTrigger.init(AbstractEmbeddedTrigger.java:78) at org.jumpmind.symmetric.db.h2.H2Trigger.init(H2Trigger.java:51) at org.h2.schema.TriggerObject.load(TriggerObject.java:81) at org.h2.schema.TriggerObject.setTriggerAction(TriggerObject.java:136) at org.h2.schema.TriggerObject.setTriggerClassName(TriggerObject.java:118) at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:115) at org.h2.engine.MetaRecord.execute(MetaRecord.java:58) at org.h2.engine.Database.open(Database.java:735) at org.h2.engine.Database.openDatabase(Database.java:266) at org.h2.engine.Database.init(Database.java:260) at org.h2.engine.Engine.openSession(Engine.java:60) at org.h2.engine.Engine.openSession(Engine.java:167) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:145) at org.h2.engine.Engine.createSession(Engine.java:128) at org.h2.engine.Engine.createSession(Engine.java:26) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:347) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:108) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:92) at org.h2.Driver.connect(Driver.java:72) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) We get NPE as: Caused by: java.lang.NullPointerException at org.h2.store.LobStorageBackend.copyLob(LobStorageBackend.java:453) at org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:495) at org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:38) at org.h2.result.LocalResult.cloneLobs(LocalResult.java:265) at org.h2.result.LocalResult.addRow(LocalResult.java:281) at org.h2.command.dml.Select.queryFlat(Select.java:585) at org.h2.command.dml.Select.queryWithoutCache(Select.java:685) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) because getLobStorage().init(); line is a bit later in Database class. Seems there was no problem in 1.4.181 that we have been using. Were there any changes in initialization logic of Database? Is loading from third-party tables in trigger a legal approach now? Vitali. -- 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] Moving from H2 1.3.176 to 1.4.187 results in sporadic ArrayIndexOutOfBoundsExceptions?
Today I released an update for my product, Poker Copilot. Until today, it used H2 1.3.176. Today's update uses H2 1.4.187, with MV_STORE=false appended to the database URL. After today's update, we are getting many H2 crash reports on both Windows and OS X. Poker Copilot is a mature product that has been using H2 for six years, and is used by many people each day; until this release we were getting infrequent crash reports. It does initially look like this is a sporadic H2 problem, although I am, of course, open to the idea that it is caused by my own programming mistakes. On OS X, the database URL is jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;MV_STORE=false;CACHE_SIZE=65536 The most common crash reports are variants of: java.lang.ArrayIndexOutOfBoundsException: 2048 at org.h2.store.Data.writeVarLong(Data.java:1254) at org.h2.store.Data.writeValue(Data.java:523) at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393) at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453) at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426) at org.h2.store.PageStore.writeBack(PageStore.java:1046) at org.h2.util.CacheLRU.removeOld(CacheLRU.java:215) at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:141) at org.h2.util.CacheLRU.put(CacheLRU.java:115) at org.h2.store.PageStore.getPage(PageStore.java:857) at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:233) at org.h2.index.PageDataNode.getRowWithKey(PageDataNode.java:279) at org.h2.index.PageDataIndex.getRowWithKey(PageDataIndex.java:426) at org.h2.index.PageDataIndex.getRow(PageDataIndex.java:415) at org.h2.table.RegularTable.getRow(RegularTable.java:106) at org.h2.index.PageBtreeIndex.getRow(PageBtreeIndex.java:301) at org.h2.index.PageBtreeCursor.get(PageBtreeCursor.java:45) at org.h2.index.IndexCursor.get(IndexCursor.java:260) at org.h2.table.TableFilter.getValue(TableFilter.java:913) at org.h2.expression.ExpressionColumn.getValue( ExpressionColumn.java:186) at org.h2.command.dml.Select.queryFlat(Select.java:580) at org.h2.command.dml.Select.queryWithoutCache(Select.java:685) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) ... 18 more and Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048 at org.h2.store.Data.writeStringWithoutLength(Data.java:263) at org.h2.store.Data.writeValue(Data.java:566) at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393) at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453) at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426) at org.h2.store.PageStore.writeBack(PageStore.java:1046) at org.h2.util.CacheLRU.removeOld(CacheLRU.java:215) at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:141) at org.h2.util.CacheLRU.put(CacheLRU.java:115) at org.h2.store.PageStore.getPage(PageStore.java:857) at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:233) at org.h2.index.PageDataIndex.find(PageDataIndex.java:283) at org.h2.index.BaseIndex.find(BaseIndex.java:127) at org.h2.index.IndexCursor.find(IndexCursor.java:159) at org.h2.table.TableFilter.next(TableFilter.java:329) at org.h2.command.dml.Select.queryFlat(Select.java:573) at org.h2.command.dml.Select.queryWithoutCache(Select.java:685) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) ... 18 more and org.h2.jdbc.JdbcSQLException: General error: java.lang. ArrayIndexOutOfBoundsException; SQL statement: SET CACHE_SIZE 65536 [5-187] at org.h2.message.DbException.getJdbcSQLException( DbException.java:345) at org.h2.message.DbException.get(DbException.java:168) at org.h2.message.DbException.convert(DbException.java:295) at org.h2.command.Command.executeUpdate(Command.java:262) at org.h2.engine.Engine.openSession(Engine.java:196) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:145) at org.h2.engine.Engine.createSession(Engine.java:128) at org.h2.engine.Engine.createSession(Engine.java:26) at
Re: [h2] JdbcPreparedStatement in Oracle mode don't honor treatEmptyStringsAsNull
Then you insert an empty string in Oracle, it will insert it as null. That works fine for jdbsStatment, because the parser handels it, by parsin the value of treateemptyStringAsNull into StringValue.get(). But for prepared statements that does not happen. Hope that explains it better. Peter -- 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] Bug in class org.h2.value.DataType (regarding type RESULT_SET)
Hi,
This should be fixed now, see
https://github.com/h2database/h2database/commit/cfb2cc373854f39fe8e0511fd22ca248c48f662f
Regards,
Thomas
On Wednesday, May 6, 2015, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
Hi,
Yes, I think this is a bug. I'm still working on a test case.
Regards,
Thomas
On Tuesday, April 28, 2015, christoff.schm...@finaris.de wrote:
Hi,
in my opinion, the code below contains a bug in the last line. Shouldn't
it be
*return ValueResultSet.get(x); *
instead of
*return ValueResultSet.get(rs);*
?
The current code causes the current result set to be returned (rs), not
the result set within the column (x). (Affects last stable 1.3. and last
1.4 beta version)
org.h2.value.DataType:
[...]
case Value.RESULT_SET: {
ResultSet x = (ResultSet) rs.getObject(columnIndex);
if (x == null) {
return ValueNull.INSTANCE;
}
*return ValueResultSet.get(rs);*
}
[...]
Kind regards,
Christoff Schmitz
F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main
Fon: +49 (0)69 / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax: +49 (0)69 / 254 98 - 50
eMail:mailto:christoff.schm...@finaris.de
www: http://www.finaris.de und http://www.rapidrep.com
Disclaimer
The information contained in this e - mail and any attachments ( together
the message) is intended for the addressee only and
may contain confidential and/or privileged information. If you have
received the message by mistake please delete it and notify
the sender and do not copy or distribute it or disclose its contents to
anyone.
FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322
Frankfurt/Main, Germany
Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf.
Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl
--
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.
