Re: [h2] Cannot parse TIMESTAMP constant aced000573720014...
Hi, What you could do is (all in H2): create linked table ora_events( 'oracle.jdbc.OracleDriver', 'jdbc:oracle:thin:@FOOBAR:1526:FOOBAR', 'FOOBAR', 'FOOBAR', '(select EV_ID, OPERATION_REFERENCE, CREATION_DATE_DB, ' || 'CLIENT_APPLICATION_NAME ' || 'from vedct_event where rownum 10)'); create table events(...) as select * from ora_events; drop table ora_events; Please note I didn't test it, there might be bugs. See also http://h2database.com/html/grammar.html#create_linked_table Regards, Thomas On Thu, Apr 17, 2014 at 5:01 AM, Joel Byrnes fatal.exception.occur...@gmail.com wrote: Of course, if you have a better way to copy rows from one database to another, being that they have virtually the same definition, I'm all ears :) -- 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] Does h2 support a clause like WITH temptab( ) AS ( select ... )
if not will it be supported in future releases? 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 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] Row disappears within the same transaction after a failed update (constraint violation)
Hi, When running an update that fails with an unique constraint violation, the row that I tried to update disappears for the remainder of the transaction. I cannot select it using its primary key or using the column that gave the constraint violation. Pseudo-SQL (all run within the same transaction): CREATE TABLE foo (row_id INTEGER PRIMARY KEY, unique_val INTEGER UNIQUE, some_other_val INTEGER); INSERT INTO foo VALUES (1, 1, 1); INSERT INTO foo VALUES (2, 2, 2); -- Gives a constraint violation UPDATE foo SET unique_val = 1 WHERE row_id = 2; -- The row that failed to update is no longer found by its old values SELECT unique_val FROM foo WHERE row_id = 2; SELECT unique_val FROM foo WHERE unique_val = 2; -- both SELECTs above fail to find anything... -- ...but this works SELECT unique_val FROM foo WHERE some_other_val = 2; I have a PasteBin with Groovy code in it: http://pastebin.com/d6sXAcPP Run it using [Groovy installation]/bin/groovy [path to script] I'm using H2 version 1.4.177. Regards /Karl -- 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] Row disappears within the same transaction after a failed update (constraint violation)
This is my initial analysis - I don't know how to solve it yet, but either someone else will, or I will get back to it at a later date. This problem appears to be specific to the combination of an in-memory database and MVCC=true, which is on by default in version 1.4.x The problem is occurring somewhere in the stacktrace below. I suspect it has to do with one of MultiVersionIndex.add(Session, Row) RegularTable.addRow(Session, Row) both which are attempting to do an insert/remove combination. I suspect that one of them is incorrectly restoring state when a unique constraint violation occurs. Thread [H2 Console thread] (Suspended (breakpoint at line 103 in BaseIndex)) owns: Database (id=103) owns: Session (id=104) TreeIndex(BaseIndex).getDuplicateKeyException(String) line: 103 TreeIndex.add(Session, Row) line: 69 MultiVersionIndex.add(Session, Row) line: 59 RegularTable.addRow(Session, Row) line: 120 RegularTable(Table).updateRows(Prepared, Session, RowList) line: 467 Update.update() line: 146 CommandContainer.update() line: 79 CommandContainer(Command).executeUpdate() line: 254 JdbcStatement.executeInternal(String) line: 186 JdbcStatement.execute(String) line: 160 WebThread(WebApp).getResult(Connection, int, String, boolean, boolean) line: 1391 WebThread(WebApp).query(Connection, String, int, int, StringBuilder) line: 1064 WebApp$1.next() line: 1026 WebApp$1.next() line: 1 WebThread.process() line: 168 WebThread.run() line: 94 Thread.run() line: 662 -- 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] 1.4 beta creates much bigger database file
Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- 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] Row disappears within the same transaction after a failed update (constraint violation)
Hi, Thanks a lot for the test case! I can reproduce it using an in-memory database, with MVCC enabled. I think for the next version, I will disable the whole RegularTable / MultiVersionIndex / TreeIndex code, and use an in-memory MVStore instead. In the long run, this will allow us to reduce the code complexity quite a bit. I hope it doesn't slow down in-memory databases too much. 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.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- 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] Why not implements serializable about MVMap
Hi, You mean, the MVMap should be serializable? It's possible to do that. It's possible to make almost every class serializable. But I don't see the value in this case. Why do you want it? What are you trying to do? What problem are you trying to solve? Regards, Thomas On Thu, Apr 17, 2014 at 4:41 AM, Frank Liu liusheng112...@gmail.com wrote: -- 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] Row disappears within the same transaction after a failed update (constraint violation)
Hi, By the way, a shorter SQL script test case: drop table test; create table test(id int primary key, a int unique, b int); set autocommit false; insert into test values (1, 1, 1); insert into test values (2, 2, 2); -- gives a constraint violation update test set a = 1 where id = 2; -- the row that failed to update is no longer found by its old values select * from test where id = 2; select * from test where a = 2; -- both selects above fail to find anything... -- ...but this works select * from test where b = 2; Regards, Thomas On Thursday, April 17, 2014, Noel Grandin noelgran...@gmail.com wrote: This is my initial analysis - I don't know how to solve it yet, but either someone else will, or I will get back to it at a later date. This problem appears to be specific to the combination of an in-memory database and MVCC=true, which is on by default in version 1.4.x The problem is occurring somewhere in the stacktrace below. I suspect it has to do with one of MultiVersionIndex.add(Session, Row) RegularTable.addRow(Session, Row) both which are attempting to do an insert/remove combination. I suspect that one of them is incorrectly restoring state when a unique constraint violation occurs. Thread [H2 Console thread] (Suspended (breakpoint at line 103 in BaseIndex)) owns: Database (id=103) owns: Session (id=104) TreeIndex(BaseIndex).getDuplicateKeyException(String) line: 103 TreeIndex.add(Session, Row) line: 69 MultiVersionIndex.add(Session, Row) line: 59 RegularTable.addRow(Session, Row) line: 120 RegularTable(Table).updateRows(Prepared, Session, RowList) line: 467 Update.update() line: 146 CommandContainer.update() line: 79 CommandContainer(Command).executeUpdate() line: 254 JdbcStatement.executeInternal(String) line: 186 JdbcStatement.execute(String) line: 160 WebThread(WebApp).getResult(Connection, int, String, boolean, boolean) line: 1391 WebThread(WebApp).query(Connection, String, int, int, StringBuilder) line: 1064 WebApp$1.next() line: 1026 WebApp$1.next() line: 1 WebThread.process() line: 168 WebThread.run() line: 94 Thread.run() line: 662 -- 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] Why not implements serializable about MVMap
I'm also interested in this feature, or I'd like to take a snapshot of an in memory h2 database and have the ability to rollback or reload to a snapshot. My use case is this: - I am using H2 in embedded in memory mode. - I have a very large test database that takes minutes to seed and between test suites the database is reset by dropping and recreating. - I wanted to cache the seeded database and just replace the dirty database after a testsuite with a copy of the seeded database. I have tried the script command and combined it with fast importing(disable; log, referential integrity, auto-commit), but that isn't noticeably faster than the traditional method of resetting the database. I was attempting to use reflection to access to the databases hash-map but obtaining a deep copy was proving troublesome, that caused me to stumble onto this thread. On Thursday, 17 April 2014 11:16:52 UTC-4, Thomas Mueller wrote: Hi, You mean, the MVMap should be serializable? It's possible to do that. It's possible to make almost every class serializable. But I don't see the value in this case. Why do you want it? What are you trying to do? What problem are you trying to solve? Regards, Thomas On Thu, Apr 17, 2014 at 4:41 AM, Frank Liu liushen...@gmail.comjavascript: wrote: -- 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.comjavascript: . 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.
