Re: Passing additional arguments to a TableEngine instantiation
Personally, I'd say the easiest solution is to copy the CREATE ALIAS syntax, so that we could say CREATE TABLE TEST(ID int) ENGINE $$new MyEngine(val1, val2)$$ But Thomas might have a better idea. On 2013-05-29 22:08, Andrew Franklin wrote: I have written a custom TableEngine implementation that requires additional arguments (eg. a path on disk to the underlying file). I could possible provide some of these arguments via the session, however, in some cases I envisage I will have multiple tables making use of differently configured instances of the same TableEngine. I was thinking that it may be useful to add arbitrary key/value pairs to the CreateTableData object, passed with something like the WITH syntax in Postgres (http://www.postgresql.org/docs/9.1/static/sql-createtable.html). If this is something that is useful to others, and the syntax is approved, then I can write up a patch. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
On 2013-05-30 00:34, Jason Pell wrote: We finally got sick of this issue and migrated to HSQLDB for MVCC and no more missing lob issues. We still use H2, but only for storing configuration stuff that does not need to be updated concurrently. It's a shame more attention has not been shown to this issue, because it was certainly frustrating having to change over to HSQLDB. It's an open-source project, so I guess the problem would be yours for failing to provide a fix. (Nobody on this project, to my knowledge, is being paid to work on it). Now, if you want to provide a test-case for your non-MVCC table lock timeout problem, I could maybe help you there, but the MVCC code is still experimental, so I'm not touching that, especially since we're going to drop it when Thomas' new MVStore engine reaches maturity. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: H2, LibreOffice and Java
Network problems are always a pain. You could try using Wireshark or Microsoft Network Monitor to capture the connection, that might provide some more information. You aren't by any chance using some kind of Connection Pool are you? Some of those have timeout logic in them to kill off stale connections, and that might be accidentally triggering. On 2013-05-30 06:36, Jorge Alberch wrote: As a temporary measure I'm thinking of running a little robot to simple go up and down the database records to maintain the session open (I know: very crude solution, but it should work as a temporary measure). -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Passing additional arguments to a TableEngine instantiation
Hi, Let's concentrate on current use cases. I wouldn't want to implement a feature that _might_ be useful in the future (do you know the term YAGNI?). The path on disk, isn't it just the path of the database? Is the path different for each table in a database? What are the other arguments you need? What kind of table engine is it? Regards, Thomas On Wednesday, May 29, 2013, Andrew Franklin wrote: I have written a custom TableEngine implementation that requires additional arguments (eg. a path on disk to the underlying file). I could possible provide some of these arguments via the session, however, in some cases I envisage I will have multiple tables making use of differently configured instances of the same TableEngine. I was thinking that it may be useful to add arbitrary key/value pairs to the CreateTableData object, passed with something like the WITH syntax in Postgres (http://www.postgresql.org/docs/9.1/static/sql-createtable.html). If this is something that is useful to others, and the syntax is approved, then I can write up a patch. -- 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({}, 'cvml', 'h2-database%2bunsubscr...@googlegroups.com');. To post to this group, send email to h2-database@googlegroups.comjavascript:_e({}, 'cvml', 'h2-database@googlegroups.com'); . Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
I can't argue with you there, and I can understand that this one is a difficult problem, but from what I have read it's been around a while and still is an open problem. I find it strange that more attention has not been paid to it is all. On Thursday, May 30, 2013 4:30:41 PM UTC+10, Noel Grandin wrote: On 2013-05-30 00:34, Jason Pell wrote: We finally got sick of this issue and migrated to HSQLDB for MVCC and no more missing lob issues. We still use H2, but only for storing configuration stuff that does not need to be updated concurrently. It's a shame more attention has not been shown to this issue, because it was certainly frustrating having to change over to HSQLDB. It's an open-source project, so I guess the problem would be yours for failing to provide a fix. (Nobody on this project, to my knowledge, is being paid to work on it). Now, if you want to provide a test-case for your non-MVCC table lock timeout problem, I could maybe help you there, but the MVCC code is still experimental, so I'm not touching that, especially since we're going to drop it when Thomas' new MVStore engine reaches maturity. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
I'm using MVCC and the only problems I've come across appear to be with LOBS (that's ok for me, don't really need them for my use case). What I mean is that it may have been less work for you to try and fix the H2 source (Seeing the issues seem to all be with LOBS) rather than change to hsqldb (And there would have been other people benefit from that also). Isn't that meant to be one of the good things about open source? I know, I get it from my programmers all the time (They say there is an issue with the library, so they make their own or work around it, which usually doesn't work very well anyway), I tell them to fix it upstream! that is the whole point! And it is getting lots of attention, the entire file store is being re-written to accommodate for MVCC right from the start!, that is why they haven't been giving attention to maintaining the current experimental MVCC mode (Please correct me if I am wrong) On 30/05/2013 2:30 PM, Noel Grandin wrote: On 2013-05-30 00:34, Jason Pell wrote: We finally got sick of this issue and migrated to HSQLDB for MVCC and no more missing lob issues. We still use H2, but only for storing configuration stuff that does not need to be updated concurrently. It's a shame more attention has not been shown to this issue, because it was certainly frustrating having to change over to HSQLDB. It's an open-source project, so I guess the problem would be yours for failing to provide a fix. (Nobody on this project, to my knowledge, is being paid to work on it). Now, if you want to provide a test-case for your non-MVCC table lock timeout problem, I could maybe help you there, but the MVCC code is still experimental, so I'm not touching that, especially since we're going to drop it when Thomas' new MVStore engine reaches maturity. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
On 2013-05-30 08:48, Ryan How wrote: And it is getting lots of attention, the entire file store is being re-written to accommodate for MVCC right from the start!, that is why they haven't been giving attention to maintaining the current experimental MVCC mode (Please correct me if I am wrong) That is correct. The existing MVCC mode was very much an experiment. Thomas is busy writing a new engine that is MVCC from the ground up, instead of being bolted onto the top. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
On 2013-05-30 08:42, Jason Pell wrote: I find it strange that more attention has not been paid to it is all. Mostly because most of us don't use it, we find the normal mode to be pretty peachy. Now, I have noticed that over the last year various people have bumped into locking issues, some of which manifest as LOCK TIMEOUT, and we have fixed various of those. So might make sense for you to re-examine your workload on H2 (without using the MVCC mode). And if you bump into a LOCK TIMEOUT bug there, I'm willing to look into it. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: H2, LibreOffice and Java
Hi Noel, No, I don't think so. I'm checking the LibreOffice Base - Connections settings and I have the Connection pooling enabled unchecked... however, since you mention it... would it be a good idea to have checked instead? But then which driver would I select? the JDBCDriver? Note: regarding H2 itself, on the Windows OS, cmd screen I initiate the H2 Server by running a script with the following: java -cp h2*.jar org.h2.tools.Server -tcpAllowOthers -webAllowOthers Thanks again, Jorge On Thursday, May 30, 2013 10:33:12 AM UTC+4, Noel Grandin wrote: Network problems are always a pain. You could try using Wireshark or Microsoft Network Monitor to capture the connection, that might provide some more information. You aren't by any chance using some kind of Connection Pool are you? Some of those have timeout logic in them to kill off stale connections, and that might be accidentally triggering. On 2013-05-30 06:36, Jorge Alberch wrote: As a temporary measure I'm thinking of running a little robot to simple go up and down the database records to maintain the session open (I know: very crude solution, but it should work as a temporary measure). -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: H2, LibreOffice and Java
On 2013-05-30 09:28, Jorge Alberch wrote: No, I don't think so. I'm checking the LibreOffice Base - Connections settings and I have the Connection pooling enabled unchecked... however, since you mention it... would it be a good idea to have checked instead? But then which driver would I select? the JDBCDriver? Sorry, I have no idea how the LibreOffice Base stuff works. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: H2, LibreOffice and Java
I'd try the connection pooling :). I imagine that means it keeps a pool of connections open to the database underneath, then when it needs a connection in the GUI there is always one available. I'd imagine the connection string would be the same, connection pooling would be handled by LibreOffice? On 30/05/2013 3:36 PM, Noel Grandin wrote: On 2013-05-30 09:28, Jorge Alberch wrote: No, I don't think so. I'm checking the LibreOffice Base - Connections settings and I have the Connection pooling enabled unchecked... however, since you mention it... would it be a good idea to have checked instead? But then which driver would I select? the JDBCDriver? Sorry, I have no idea how the LibreOffice Base stuff works. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
Hi, I have a sample of h2 failing when used with spring integration jdbc message channel store that times out on lock. Adding mvcc fixes that particular problem. I will get a minimum test case that demonstrates this. The real issue is by default h2 locks entire table so only one thread can perform dml stuff this makes it unsuitable for our situation. And we actually only use h2 in test we use Oracle or sql server in production On 30/05/2013 5:05 PM, Noel Grandin noelgran...@gmail.com wrote: On 2013-05-30 08:42, Jason Pell wrote: I find it strange that more attention has not been paid to it is all. Mostly because most of us don't use it, we find the normal mode to be pretty peachy. Now, I have noticed that over the last year various people have bumped into locking issues, some of which manifest as LOCK TIMEOUT, and we have fixed various of those. So might make sense for you to re-examine your workload on H2 (without using the MVCC mode). And if you bump into a LOCK TIMEOUT bug there, I'm willing to look into it. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
H2 table creation fails
create table Company (id bigint generated by default as identity,location bigint not null, addressId bigint, email varchar(255), name varchar(255), phone1 varchar(255), phone2 varchar(255), primary key (id, location)); The above fails on H2 but runs on mysql if i rewrite it as create table Company (id bigint,location bigint not null, addressId bigint, email varchar(255), name varchar(255), phone1 varchar(255), phone2 varchar(255), primary key (id, location)); it works,but then i'll have to manage creating company id Can this be fixed -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
On 2013-05-30 10:56, jasonmp...@gmail.com wrote: The real issue is by default h2 locks entire table so only one thread can perform dml stuff this makes it unsuitable for our situation. And we actually only use h2 in test we use Oracle or sql server in production H2 is pretty fast, even with it's simple locking strategy. What are you doing that requires so much speed that single-threaded access to a table is insufficient? Especially since you're only using it in test. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
Fixing the H2 source is easier said than done, changing to HSQLDB was actually a few hours of work, granted they were a painful few hours, but when you use JPA and Hibernate, switching DB's is relatively painless. We are still using H2 for a lot of stuff, even in production, we just use HSQLDB to replace Oracle in test and dev as it behaves concurrently much better. We use H2 as a configuration store for lots of stuff that would be overkill to use with Oracle. The row level locking is what we really needed and thus because MVCC is experimental with H2, we had to go with another DB where the MVCC support works well with CLOB. I would love to go back to H2 for everything and will certainly keep an eye on MVSTORE. I do contribute to open source myself - being a committer on Apache CXF, but hacking on H2 to fix this particular issue is way beyond my capability especially considering what little time I have. On Thursday, May 30, 2013 4:48:31 PM UTC+10, Kartweel wrote: I'm using MVCC and the only problems I've come across appear to be with LOBS (that's ok for me, don't really need them for my use case). What I mean is that it may have been less work for you to try and fix the H2 source (Seeing the issues seem to all be with LOBS) rather than change to hsqldb (And there would have been other people benefit from that also). Isn't that meant to be one of the good things about open source? I know, I get it from my programmers all the time (They say there is an issue with the library, so they make their own or work around it, which usually doesn't work very well anyway), I tell them to fix it upstream! that is the whole point! And it is getting lots of attention, the entire file store is being re-written to accommodate for MVCC right from the start!, that is why they haven't been giving attention to maintaining the current experimental MVCC mode (Please correct me if I am wrong) On 30/05/2013 2:30 PM, Noel Grandin wrote: On 2013-05-30 00:34, Jason Pell wrote: We finally got sick of this issue and migrated to HSQLDB for MVCC and no more missing lob issues. We still use H2, but only for storing configuration stuff that does not need to be updated concurrently. It's a shame more attention has not been shown to this issue, because it was certainly frustrating having to change over to HSQLDB. It's an open-source project, so I guess the problem would be yours for failing to provide a fix. (Nobody on this project, to my knowledge, is being paid to work on it). Now, if you want to provide a test-case for your non-MVCC table lock timeout problem, I could maybe help you there, but the MVCC code is still experimental, so I'm not touching that, especially since we're going to drop it when Thomas' new MVStore engine reaches maturity. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
We are doing integration testing using HSQLDB, we still do full blown integration test with oracle, but on developers PC's Oracle XE is way too heavy, so we have a local build which uses HSQLDB. This does do stuff that requires reasonable concurrency from the DB and tends to cause timeouts otherwise. We always suffered either from timeouts or when we went to MVCC, the missing lobs issue. On Thursday, May 30, 2013 7:40:57 PM UTC+10, Noel Grandin wrote: On 2013-05-30 10:56, jason...@gmail.com javascript: wrote: The real issue is by default h2 locks entire table so only one thread can perform dml stuff this makes it unsuitable for our situation. And we actually only use h2 in test we use Oracle or sql server in production H2 is pretty fast, even with it's simple locking strategy. What are you doing that requires so much speed that single-threaded access to a table is insufficient? Especially since you're only using it in test. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Missing LOB issue
On 2013-05-30 12:58, Jason Pell wrote: The row level locking is what we really needed and thus because MVCC is experimental with H2, we had to go with another DB where the MVCC support works well with CLOB. I doubt that. I suspect that you were just hitting some unfortunate deadlocks in our locking stuff which went away with MVCC because our locking is different in that mode. But hey, you're free to do what floats your boat, this is open-source :-) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index with Version 1.3.162
We have tried this with the latest build but that did not solve the problem for us. Any other advice on this one? On Tuesday, 14 May 2013 10:51:06 UTC+5:30, Sanjeev Gour wrote: Sorry, I mentioned the incorrect version number, I am using *1.3.168*. Earlier we also tried without MVCC but that didn't help. Just for your information that the index which is creating the problem is on a timestamp column if that can give you some clue about the problem. We are very close to go into production and upgrading H2 does not look a preferred option. Also I read through the change log from 1.3.168 to 1.3.171 and did not see any mention for this defect being fixed so I am not quite sure if upgrading would help. I will try providing more information on this as and when I hit it again. On Monday, 13 May 2013 18:37:16 UTC+5:30, Noel Grandin wrote: You're also running quite an old version of H2, updating to the latest version would probably help. I would also suggest running without MVCC, it's still a little rough around the edges. On 2013-05-13 14:47, Sanjeev Gour wrote: I am getting the following error when running a clean up routine on some of the tables. I am using version 1.3.162. The JDBC url is configured with the following options- CACHE_TYPE=LRU;PAGE_SIZE=16384;MVCC=TRUE;DB_CLOSE_DELAY=-1 Also tried with ;optimize_update=false to no avail. This typically happens when my applications runs for long hours so I don't really have an easy test case to reproduce this problem. Is there anything else to try out on this one? *org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index TIMESERIES.IDX_METRIC_DATA_START_TIME*: ( /* key:18158 */ 43848, TIMESTAMP '2013-03-27 18:30:00.0', TIMESTAMP '2013-03-27 23:10:00.0', X'aced000573720020636f6d2e63612e63686f7275732e74696d657365726965732e54534172726179018c63d06105fca80200054a0007656e6454696d654900066c656e6774684a0009737461727454696d654c000c636c6f636b4d656d656e746f74002a4c636f6d2f63612f63686f7275732f74696d657365726965732f6e756d657269632f4d656d656e746f3b4c000b646174614d656d656e746f71007e00017870013dae1d79400039013dad1d20407372003a636f6d2e63612e63686f7275732e74696d657365726965732e6e756d657269632e4e756d62657244656c74614f7574707574244d656d656e746f7390f51c1bf5d3fc0200014c00076d656d656e746f71007e0001787073720038636f6d2e63612e63686f7275732e74696d657365726965732e6e756d657269632e4e756d626572524c454f7574707574244d656d656e746ff49d0987111300c102000549000a63757272656e74496e744a000b63757272656e744c6f6e674900066c656e67746849000473697a654c00076d656d656e746f71007e0001787493e0003800437372003c636f6d2e63612e63686f7275732e74696d657365726965732e6e756d657269632e4e756d6265725061636b696e674f7574707574244d656d656e746f40a3b5b30 dd1f4770c787077040003737200106a6176612e7574696c2e4269745365746efd887e3934ab210200015b0004626974737400025b4a7870757200025b4a782004b512b1759302787413c39ed68e902053000124f8787371007e000777047371007e00097571007e000c00080202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202000278'); SQL statement: DELETE FROM timeseries.metric_data WHERE end_time ? LIMIT 1000 [90112-168] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:225) at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241) at org.h2.index.MultiVersionIndex.remove(MultiVersionIndex.java:170) at org.h2.table.RegularTable.removeRow(RegularTable.java:361) at org.h2.command.dml.Delete.update(Delete.java:93) at org.h2.command.CommandContainer.update(CommandContainer.java:75) at org.h2.command.Command.executeUpdate(Command.java:230) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493) at com.ca.chorus.db.DbExecutor$9.call(DbExecutor.java:829) ... 21 more -- 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. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at
LocalResult hold by PreparedStatement consumes a lot of Memory
Hi all, we are using a H2 database (1.3.161) embedded in jboss 7.1.1.Final. We wanted to cache the prepared queries but without caching the result of it. Therefore we set OPTIMIZE_REUSE_RESULTS=0. But unfortunately there is another reference-chain from the PreparedStatement to its result. This yields in keeping all the big results in memory. My questions are: - Is this a (known) bug (which might be fixed in a newer version)? - Is 1.3.172 running in jboss 7.1.1.Final? So may I replace the jar with the newer one? Thank you for your help, Flo *Detailed information:* The jboss-configuration is: datasources datasource jndi-name=java:/TestDS pool-name=TestDS enabled=true use-java-context=true connection-urljdbc:h2:mem:test;REFERENTIAL_INTEGRITY=0;LOG=0;MULTI_THREADED=1;OPTIMIZE_REUSE_RESULTS=0;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0;DEFAULT_LOCK_TIMEOUT=1000/connection-url driverh2/driver transaction-isolationTRANSACTION_READ_COMMITTED/transaction-isolation pool min-pool-size1/min-pool-size max-pool-size12/max-pool-size prefilltrue/prefill /pool timeout blocking-timeout-millis3/blocking-timeout-millis idle-timeout-minutes1/idle-timeout-minutes /timeout statement prepared-statement-cache-size100/prepared-statement-cache-size share-prepared-statementstrue/share-prepared-statements /statement /datasource drivers driver name=h2 module=com.h2database.h2 xa-datasource-classorg.h2.jdbcx.JdbcDataSource/xa-datasource-class /driver /drivers /datasources The reference-chain is the following: org.h2.jdbc.JdbcPreparedStatement.command - org.h2.command.CommandContainer.prepared - org.h2.command.dml.Select.topTableFilter - org.h2.table.TableFilter.cursor - org.h2.index.IndexCursor.cursor - org.h2.index.ViewCursor.result - org.h2.result.LocalResult containig all the data of the result Maybe result in ViewCursor should be set to null after usage? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Passing additional arguments to a TableEngine instantiation
The way I see it, the TableEngine interface has the potential to enable traditional SQL access to many NoSQL style stores, while removing the burden of implementing a JDBC driver (and SQL parser) and enabling the developer to concentrate on the much smaller task of the data to table adapter portion. In my specific use case, each table would have a different path; but there may be other optional config options that are specific to the engine that could be passed in. Examples might include map fill factors, debugging options? I'm wary of YAGNI too; so if there is an alternative to my suggestion then I'm happy to look into it instead. On Thursday, May 30, 2013 2:42:23 AM UTC-4, Thomas Mueller wrote: Hi, Let's concentrate on current use cases. I wouldn't want to implement a feature that _might_ be useful in the future (do you know the term YAGNI?). The path on disk, isn't it just the path of the database? Is the path different for each table in a database? What are the other arguments you need? What kind of table engine is it? Regards, Thomas On Wednesday, May 29, 2013, Andrew Franklin wrote: I have written a custom TableEngine implementation that requires additional arguments (eg. a path on disk to the underlying file). I could possible provide some of these arguments via the session, however, in some cases I envisage I will have multiple tables making use of differently configured instances of the same TableEngine. I was thinking that it may be useful to add arbitrary key/value pairs to the CreateTableData object, passed with something like the WITH syntax in Postgres (http://www.postgresql.org/docs/9.1/static/sql-createtable.html ). If this is something that is useful to others, and the syntax is approved, then I can write up a patch. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index with Version 1.3.162
Hi, Did you re-create the problematic index, or (even better) re-create the database? To re-create the database, first create a SQL script using the script to command, then create a new database, and run runscript. Regards, Thomas On Thursday, May 30, 2013, Sanjeev Gour wrote: We have tried this with the latest build but that did not solve the problem for us. Any other advice on this one? On Tuesday, 14 May 2013 10:51:06 UTC+5:30, Sanjeev Gour wrote: Sorry, I mentioned the incorrect version number, I am using *1.3.168*. Earlier we also tried without MVCC but that didn't help. Just for your information that the index which is creating the problem is on a timestamp column if that can give you some clue about the problem. We are very close to go into production and upgrading H2 does not look a preferred option. Also I read through the change log from 1.3.168 to 1.3.171 and did not see any mention for this defect being fixed so I am not quite sure if upgrading would help. I will try providing more information on this as and when I hit it again. On Monday, 13 May 2013 18:37:16 UTC+5:30, Noel Grandin wrote: You're also running quite an old version of H2, updating to the latest version would probably help. I would also suggest running without MVCC, it's still a little rough around the edges. On 2013-05-13 14:47, Sanjeev Gour wrote: I am getting the following error when running a clean up routine on some of the tables. I am using version 1.3.162. The JDBC url is configured with the following options- CACHE_TYPE=LRU;PAGE_SIZE=**16384;MVCC=TRUE;DB_CLOSE_**DELAY=-1 Also tried with ;optimize_update=false to no avail. This typically happens when my applications runs for long hours so I don't really have an easy test case to reproduce this problem. Is there anything else to try out on this one? *org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index TIMESERIES.IDX_METRIC_DATA_START_TIME*: ( /* key:18158 */ 43848, TIMESTAMP '2013-03-27 18:30:00.0', TIMESTAMP '2013-03-27 23:10:00.0', X'** aced000573720020636f6d2e63612e**63686f7275732e74696d6573657269** 65732e54534172726179018c63d061**05fca80200054a0007656e6454696d** 654900066c656e6774684a00097374**61727454696d654c000c636c6f636b** 4d656d656e746f74002a4c636f6d2f**63612f63686f7275732f74696d6573** 65726965732f6e756d657269632f4d**656d656e746f3b4c000b646174614d** 656d656e746f71007e00017870**013dae1d79400039013dad** 1d20407372003a636f6d2e63612e63**686f7275732e74696d657365726965** 732e6e756d657269632e4e756d6265**7244656c74614f7574707574244d65** 6d656e746f7390f51c1bf5d3fc0200**014c00076d656d656e746f71007e00** 01787073720038636f6d2e63612e63**686f7275732e74696d657365726965** 732e6e756d657269632e4e756d6265**72524c454f7574707574244d656d65** 6e746ff49d0987111300c102000549**000a63757272656e74496e744a000b** 63757272656e744c6f6e674900066c**656e67746849000473697a654c0007** 6d656d656e746f71007e0001787000**0493e00038** 00437372003c636f6d2e63612e**63686f7275732e74696d6573657269** 65732e6e756d657269632e4e756d62**65725061636b696e674f7574707574**244d656d656e746f40a3b5b30 dd1f4770c7870770400037**37200106a6176612e7574696c2e426** 9745365746efd887e3934ab2102000**15b0004626974737400025b4a78707** 57200025b4a782004b512b17593020**000787413c39ed68e90205** 3000124f80**000787371007e0** 00777047371007e0009757**1007e000c00080202020202020** 202020202020202020202020202020**202020202020202020202020202020** 202020202020202020202020202020**202020202000278')**; SQL statement: DELETE FROM timeseries.metric_data WHERE end_time ? LIMIT 1000 [90112-168] at org.h2.message.DbException.**getJdbcSQLException(** DbException.java:329) at org.h2.message.DbException.**get(DbException.java:169) at org.h2.message.DbException.**get -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Using H2 safely on Android
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/05/13 08:09, Noel Grandin wrote: You could try running a CHECKPOINT SYNC command from onPause(). At the very least, that would dramatically reduce the amount of work that H2 has to do during recovery. To improve durability I've been experimenting with WRITE_DELAY and CHECKPOINT SYNC, as recommended here: http://h2database.com/html/advanced.html#durability_problems Can you tell me whether it's useful to call CHECKPOINT SYNC after each commit if WRITE_DELAY is set to 0? Or conversely, is it useful to set WRITE_DELAY to 0 if I'm going to call CHECKPOINT SYNC after each commit? Thanks, Michael -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iQEcBAEBAgAGBQJRp6lBAAoJEBEET9GfxSfMK1oH/3EnIuZmGi8lKCcBNfKsI42X F48OuSgQc8UDkbj0Ez7VtH+zZccceBC5NR2XIWhFdW1O3RW69uOT/rmYDeFSaFt6 7cX041FKHgT7SUIcVYOy4r+k8ZACi9WZBYibskv4Q1g8l5cTPbQzDlCETpzxpn6b nEm3EUyhKWWKsul/pmHUbonRfME4B0oE6ULqBQX6qz8iATAL5VOWWyFxEhId+Zgi dEOI0bSX5JK/PjocTM3lOFUmKphTZ0Od4+Lrn+o7ZFmH5zVyOqw6FkUf8ew4vlUl ZGl3WsUPhhCLCcQRMfNCp4mjZFaas9g1kwdPb5XIkEiq7ZMUivHgIDNkRieLTGo= =zUsF -END PGP SIGNATURE- -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Reuse of the connection passed to a trigger
I have a table for witch after data is inserted, I need to do long running proceeding from the data. I plan to do the proceeding is a separate thread fired stared form the trigger. My question is, is it safe to send the connection sent to the trigger (ether the init or the fire method) to the other thread to do the work or should i create a new connection to send to the other thread. If I need a new connection what is the best way to produce it from the passed connection, (I might not know the connection string in advance). Would it make a difference if I plan to use tractions in the second thread? Thank You Ben Sagal -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Using H2 safely on Android
I opted to manage H2 from a service. All my activities bind themselves to this service in activity.onResume(), unbind themselves in onPause(). When no activity have been bound to the service for 5 seconds, the service stop itself, stop h2 and do some other cleanup. So when my app goes in the background without beeing destroyed, h2 is stopped after 5 seconds. If the user restore my app in the foreground, h2 is resumed (i restart the service if needed). My app has been published, and It seems to be working fine. But I still think i may have missed a simpler solution. It would be nice if h2 had an official recipe for android. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.