Re: [h2] H2 Database Engine: New version 2.3.230 released
Thank you so much, H2 team! We have done our part and integrated it into the H2 Migration Tool already: https://manticore-projects.com/H2MigrationTool/index.html All the best Andreas On Sun, 2024-07-14 at 18:57 -0400, Andrei Tokar wrote: > Hello, > > A new version 2.3.230 of H2 is available at http://www.h2database.com > (you may have to click 'Refresh') and > https://github.com/h2database/h2database/releases/tag/version-2.3.230 > > > For details, see the 'Change Log' at > http://www.h2database.com/html/changelog.html > > New artefacts will be uploaded to maven repository shortly. > > > Have fun, > Andrei Tokar > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/894e788a69757a46d3a3cfba89a2c7a5bed85b39.camel%40manticore-projects.com.
Re: [h2] Re: Duplicate records when order by date?
On Tue, 2024-04-16 at 09:54 +, 'Peter Borissow' via H2 Database wrote: > Hmm... I kinda have a row id already via the unique primary key. What > advantage would a temp table give in my case? The advantage is, that special column _rowid_ is the physical row of the data frame and it would not change unless you shuffle the data. It's comparable to Oracle's rownum. Complete example: drop table test if exists cascade; create table test(a int, b int, constraint a_key primary key (a)); insert into test values (1, 2), (2, 3), (3, 3), (4, 4), (5, 4), (6, 4), (7, 4); create table tmp_test as select * from test order by b; select * from tmp_test order by _rowid_ offset 0 rows fetch next 3 row only; select * from tmp_test order by _rowid_ offset 3 rows fetch next 3 row only; select * from tmp_test order by _rowid_ offset 6 rows fetch next 3 row only; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6b8f9eefa7632155c94b059db9fc1a170ff61473.camel%40manticore-projects.com.
Re: [h2] Re: Duplicate records when order by date?
In my understanding, you could create a temporary table from your query (without pagination) and then use the special column `_row_id_` for the pagination. On Tue, 2024-04-16 at 16:42 +0700, Andreas Reichel wrote: > On Tue, 2024-04-16 at 09:14 +, 'Peter Borissow' via H2 Database > wrote: > > I don't care if 218 appears before 217 or if 217 appears before 218 > > as long as they both appear. > > > Peter, > > please consider that > > 1) the second query call knows nothing about the first query call ( > and this fact alone makes pagination unreliable unless you can > guarantee that there is a distinct order and no change to the data in > between the calls). > > 2) both query calls grab ANY records out of the duplicates > > The observed behaviour is perfectly expected and explainable, unless > you order by a distinct criteria -- which was "ID" in your example > only. > > Cheers > Andreas > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/1fda6aa6c870c759abf48010bec19b56a643a2b6.camel%40manticore-projects.com > [1]. [1] https://groups.google.com/d/msgid/h2-database/1fda6aa6c870c759abf48010bec19b56a643a2b6.camel%40manticore-projects.com https://groups.google.com/d/msgid/h2-database/1fda6aa6c870c759abf48010bec19b56a643a2b6.camel%40manticore-projects.com?utm_medium=email_source=footer -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2ce8778f14356adb3f901fbd83935d8e748d0e17.camel%40manticore-projects.com.
Re: [h2] Re: Duplicate records when order by date?
On Tue, 2024-04-16 at 09:14 +, 'Peter Borissow' via H2 Database wrote: > I don't care if 218 appears before 217 or if 217 appears before 218 > as long as they both appear. Peter, please consider that 1) the second query call knows nothing about the first query call ( and this fact alone makes pagination unreliable unless you can guarantee that there is a distinct order and no change to the data in between the calls). 2) both query calls grab ANY records out of the duplicates The observed behaviour is perfectly expected and explainable, unless you order by a distinct criteria -- which was "ID" in your example only. Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1fda6aa6c870c759abf48010bec19b56a643a2b6.camel%40manticore-projects.com.
Re: [h2] Re: Duplicate records when order by date?
Peter, from what I can see, your records are NOT DISTINCT regarding the ORDER criteria? So what exactly do you expect to happen when there is no formal contract on the order and sorting? Thought experiment: insert all your records but with exactly the same Date/Timestamp -- in this case, your queries don't order anything but imply grab any 50 records first. Cheers -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/434297be11814d73c0efabe7edaa7e74d51f1f5c.camel%40manticore-projects.com.
Re: [h2] working around differences with h2 PSQL compatibilty
Greetings! You could use JSQLParser to parse your query and then transpile/rewrite it into the specific dialect. If you would like to give me a Query sample then I will happily assist you with a template. Cheers Andreas On Sun, 2024-04-07 at 08:27 -0700, broccolai wrote: > hi! > > I'm offering psql and h2 options in my software, as well as using h2 > in my tests. But I'm having an issue when trying to store json data. > H2 requires "FORMAT JSON" in the value for json but this syntax isn't > allowed in PSQL. I'm trying to use the same queries between both > which has been fine for almost everything until this. > > Anyone have any ideas? > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/790f0e5f-1900-40b9-a886-a5aeeb85657bn%40googlegroups.com > [1]. [1] https://groups.google.com/d/msgid/h2-database/790f0e5f-1900-40b9-a886-a5aeeb85657bn%40googlegroups.com https://groups.google.com/d/msgid/h2-database/790f0e5f-1900-40b9-a886-a5aeeb85657bn%40googlegroups.com?utm_medium=email_source=footer -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d75e69e60c940a1263c8b4a6fe8786ed19730cfc.camel%40manticore-projects.com.
Re: [h2] A single update can 10x the filesize of the database
Greetings! Please try to either a) partitioning you update into smaller chunks/commits or b) use a CTAS instead (although I am not a big fan of this). Good luck Andreas On Sat, 2024-03-16 at 22:11 -0700, Alexander Kainz wrote: > Hi, > > I wanted to mention this issue that took me a while to identify. > > Here's the output from my test code (https://github.com/akainz/h2db) > > Table created successfully.12 KB > Rows inserted successfully. File 986 MB actual data 983 MB > Rows updated successfully. 9 GB > > the code sets up a table, then inserts 100K rows and the does an > UPDATE sample_table SET COUNT = 0 > and the db file just 10x's in size. > > That seems to be excessive and forced me to stop updating all rows, > maybe you want to look into it. > > 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/91a34196-107b-4fc7-860b-0cc9c3ce523dn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/519379422a74235fd7b5d92897fe55c14cee59ce.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
Please discuss with the H2 developers first. My understanding is: the current trend goes to SQL:2016 compliance and the Compatibility modes are more or less seen as a tolerated legacy. So any extension of those modes may not be welcome unconditionally, when it introduced complexity or maintenance issues. I may be wrong of course. Cheers Andreas On Sat, 2024-02-17 at 02:46 +, 'Peter Borissow' via H2 Database wrote: > Thank you. I get it. Compatibility mode with PostgreSQL is incomplete > and certain regressions are to be expected when jumping major > versions. > > I would be willing to help patch the sw. Whether I would succeed is > another matter :-) > > I'll pull the source and start a new thread as needed. > > Many thanks to you and the team. Love H2! > > Best, > 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1da41bb4d651bf53e448314e9becd705a52d8b4b.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
On Sat, 2024-02-17 at 08:09 +0700, Andreas Reichel wrote: > Quoting transaction didn't have any effect. Try again on MS SQL Server -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b2d47526b97a12b3ce697ad8a752bdee32c6e0a5.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
Greetings!
On Sat, 2024-02-17 at 00:47 +, 'Peter Borissow' via H2 Database
wrote:
>
> Hi Andreas,
> Thanks for the quick reply! As you suggest, quoting "year" as the
> alias works. Quoting transaction didn't have any effect. Several
> questions:
>
> (1) What doesn't this work?
> properties.setProperty("NON_KEYWORDS", "YEAR");
Because it really is just a "work around" for uncommon keywords related
to more exotic features.
The problem is that the parser based on the Grammar needs to be able to
distinguish the tokens. Example:
-- value can work
SELECT Year( date ) value FROM ...
vs.
-- value can work
INSERT INTO table_name VALUE ..
Further illustration (unrelated to H2
though):
https://manticore-projects.com/JSQLParser/contribution.html#manage-reserved-keywords
In short, `YEAR` seems to be a keyword that can not be worked around
(for good).
>
> (2) I am in PostgreSQL mode. On a PostgreSQL server, I don't have to
> quote year as an alias in PostgreSQL in a statement like this:
> select date as year from transaction
The Compatibility modes are "certain syntax and functions are emulated"
modes. The Developers don't aim or claim full compliance.
Example: NEXT VALUE for a sequence is supported for all 3 syntax
(Postgres, Oracle and MS SQL Server) as a courtesy (which I find just
awesome). But non of those dialect is fully implemented (and will never
be).
>
> Why do I have to quote year? Why has the behavior changed from 1.x to
> 2.x?
Because H2 has massively evolved since and supports now a more complex
Grammar.
More Grammar, more restricted keywords.
Example: When you don't support `Exclusive` lock modes, then
`Exclusive` is not needed as a keyword. But when support is added, then
suddenly `Exclusive` becomes a keyword.
Thus it is best practise to avoid all SQL:2016 reserved keywords (long
list!) and/or to quote identifiers always.
(If you have a massive library of existing statements, then you could
engage JSQLParser and a) identify all such colliding identifiers and/or
b) rewrite your statements quoting the identifiers.)
Good luck
Andreas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/d08ac7158a006eb2e514c5d5c54fcdaadcbd47e3.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
Good Morning.
`YEAR` is a SQL:2016 reserved
keyword:
http://www.h2database.com/html/advanced.html?highlight=keyword=keyword#keywords
You will need to quote your alias:
SELECT Year( date ) AS "YEAR"
FROM "transaction"
;
Similar thing for `TRANSACTION`.
It is always advisable to avoid such keywords are object identifiers.
Cheers
Andreas
On Sat, 2024-02-17 at 00:25 +, 'Peter Borissow' via H2 Database
wrote:
> Dear H2 Community,
> I ran into an unexpected error today migrating from 1.x to 2.x.
> I'm using H2 2.2.224 in PostgreSQL mode using the following
> parameters
>
> properties.setProperty("MODE", "PostgreSQL");
> properties.setProperty("DATABASE_TO_LOWER", "TRUE");
> properties.setProperty("DEFAULT_NULL_ORDERING", "HIGH");
>
> The following query is failing:
>
> select year(date) as year from transaction
>
> Error:
>
> SELECT year(date) AS [*]year FROM transaction"; expected "identifier"
>
> Looks like it doesn't like the year alias in the select statement.
> The following query works:
>
> select year(date) as y from transaction
>
> I tried the following but it didn't seem to help:
>
> properties.setProperty("NON_KEYWORDS", "YEAR");
>
> Any suggestions?
>
> Thanks,
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/1040186991.2923444.1708129516544%40mail.yahoo.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/83c98a852da9a0ef3683051984c612304744c66c.camel%40manticore-projects.com.
Re: [h2] Syntax error with ON CONFLICT
Greetings! The `ON CONFLICT ...` clause is Postgres specific and certainly not supported in H2. Please read Postgres' "MODE" as: some specific syntax is supported, but there is no guarantee of full compliance. Best regards Andreas On Wed, 2024-01-17 at 15:04 -0800, 'Drew Dimanlig' via H2 Database wrote: > I'm getting this error trying to execute a query that does ON > CONFLICT DO UPDATE: > > org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL > statement "INSERT INTO tasks (assignee_id, item_key, title, status, > due_date, updated_at, state, company_id) VALUES (?,?,?,?,?,?,?,?) > [*]ON CONFLICT (assignee_id, item_key, status) DO UPDATE SET > assignee_id = excluded.assignee_id, title = excluded.title, status = > excluded.status, item_key = excluded.item_key, status = > excluded.status, due_date = excluded.due_date, updated_at = > excluded.updated_at, state = excluded.state, company = > excluded.company"; SQL statement: > INSERT INTO tasks (assignee_id, item_key, title, status, due_date, > updated_at, state, company_id) VALUES (?,?,?,?,?,?,?,?) ON CONFLICT > (assignee_id, item_key, status) DO UPDATE SET assignee_id = > excluded.assignee_id, title = excluded.title, status = > excluded.status, item_key = excluded.item_key, status = > excluded.status, due_date = excluded.due_date, updated_at = > excluded.updated_at, state = excluded.state, company = > excluded.company [42000-214] > > Here is my connection string: > "jdbc:h2:mem:default;DB_CLOSE_DELAY=- > 1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH;I > NIT=RUNSCRIPT FROM 'schema.sql';" > > Is there another setting I'm missing? > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/54ebacf8-dd4f-4132-b332-0d97224596e6n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/68f5f539bd6c0e7174dbb50851682748ead5f04a.camel%40manticore-projects.com.
Re: [h2] hex
On Tue, 2024-01-16 at 05:24 -0800, mche...@gmail.com wrote: > Hi > possible to add a function to convert dec to hex for display? > > select hex(col) from table > > thanks > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/079089e4-82f9-4828-9087-b61678e845ean%40googlegroups.com > . this one: https://www.h2database.com/html/functions.html#hextoraw ? cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f37581f6792dbae0428740efea9527b0356fe747.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
This seems to be a useful and interesting link: https://commons.apache.org/proper/commons-jcs/JCSvsEHCache.html I suggest you take it up and add MVStore and/or NitroCache to it for establishing a benchmark. When you find MVStore competitive (enough) and find the achievable speed matches your needs, you/we could write a kind of "H2 Loader" pumping data directly into the MVStore. I am actually interested in that too because I face similar challenges of reading LARGE datasets into H2 databases. One more thought: by CACHE design, you will need to decide about your priorities: a) FAST reading vs. b) FAST fetching. The fastest data-pump can easily result in the slowest data read and somehow you will may want to balance your expectations. (For me, reading is always more important than writing.) Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4c369243c7e21456d8229604c1dc38a5b86ce778.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
Greetings! How about by-passing JDBC/H2 and pushing the data into the MV Store directly? This way you eliminate any possible bottleneck. Next was to compare MV Store performance vs. other implementation (e .g. EHCache). Next next was comparing against Postgres LOAD and or DuckDB read from Parquet. You will need to establish a kind of benchmark first before you can really say what is possible or shall be expected. I don't know how well MVStore itself performs and where exactly any limitation may come from. For very most of us, H2/MVStore is just good enough and I assume the most relevant use-case is to have a minimum deploy/maintenance all batteries included Jar DB -- not so much the raw performance. Cheers Andreas On Sun, 2024-01-14 at 00:48 -0800, mche...@gmail.com wrote: > 1) Ensure that all Indexes and constraints are turned off > > yes, faster > > 2) Reduce the commit size. As far as I can see you create one very > large commit over all records. Instead, commit as per 1k or 4k > records or so. > > i tried 1k and 10k per commit, not much different, sometimes 1k is > slower than 10k. > > thanks Andreas > On Sunday 14 January 2024 at 01:21:36 UTC+8 mche...@gmail.com wrote: > > 1xk mean i can insert 10-15 thousand records to h2 per second, > > thanks > > > > On Friday 12 January 2024 at 16:41:03 UTC+8 Andreas Reichel wrote: > > > Forgot one: > > > > > > try multi threading, e. g. populating one prepared statement > > > while another is executed/written. > > > Not guaranteed if this really will be faster though. > > > > > > On Fri, 2024-01-12 at 15:38 +0700, Andreas Reichel wrote: > > > > Greetings. > > > > > > > > On Fri, 2024-01-12 at 00:17 -0800, mche...@gmail.com wrote: > > > > > hi. I am running AMD 3900x with 128GB ram and a nvme ssd. Now > > > > > i can insert 1xk record per seconds, which is very fast. But > > > > > how can I make is 10 times more? what hardware can do that? > > > > > > > > 1) Ensure that all Indexes and constraints are turned off > > > > 2) Reduce the commit size. As far as I can see you create one > > > > very large commit over all records. Instead, commit as per 1k > > > > or 4k records or so. > > > > 3) Ensure that your filesystem does not do COW or compression. > > > > 4) use RAID and ensure that there is ZERO waitIO or swapping > > > > > > > > What exactly does "1xk" mean? > > > > > > > > If you are really serious about loading speed you will end up > > > > with Oracle Loader. Not that I am promoting this shit, but in > > > > reality it is the fastest way for pumping data into a DB. > > > > > > > > Best regards > > > > Andreas > > > > > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/0d855987-55f3-45ea-bca3-4cf3390f9a08n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2230b650284fee800808d051b6a2ea817ac523ba.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
Forgot one: try multi threading, e. g. populating one prepared statement while another is executed/written. Not guaranteed if this really will be faster though. On Fri, 2024-01-12 at 15:38 +0700, Andreas Reichel wrote: > Greetings. > > On Fri, 2024-01-12 at 00:17 -0800, mche...@gmail.com wrote: > > hi. I am running AMD 3900x with 128GB ram and a nvme ssd. Now i can > > insert 1xk record per seconds, which is very fast. But how can I > > make is 10 times more? what hardware can do that? > > 1) Ensure that all Indexes and constraints are turned off > 2) Reduce the commit size. As far as I can see you create one very > large commit over all records. Instead, commit as per 1k or 4k > records or so. > 3) Ensure that your filesystem does not do COW or compression. > 4) use RAID and ensure that there is ZERO waitIO or swapping > > What exactly does "1xk" mean? > > If you are really serious about loading speed you will end up with > Oracle Loader. Not that I am promoting this shit, but in reality it > is the fastest way for pumping data into a DB. > > Best regards > Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/079a787ee98902bf5847d46b5fc0824546173212.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
Greetings. On Fri, 2024-01-12 at 00:17 -0800, mche...@gmail.com wrote: > hi. I am running AMD 3900x with 128GB ram and a nvme ssd. Now i can > insert 1xk record per seconds, which is very fast. But how can I make > is 10 times more? what hardware can do that? 1) Ensure that all Indexes and constraints are turned off 2) Reduce the commit size. As far as I can see you create one very large commit over all records. Instead, commit as per 1k or 4k records or so. 3) Ensure that your filesystem does not do COW or compression. 4) use RAID and ensure that there is ZERO waitIO or swapping What exactly does "1xk" mean? If you are really serious about loading speed you will end up with Oracle Loader. Not that I am promoting this shit, but in reality it is the fastest way for pumping data into a DB. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6a9dc4688445b554696767c00c68f1f98672ed1c.camel%40manticore-projects.com.
Re: [h2] why insert many record to in-memory database is same speed as embedded-mode
Because its cached and data are written to disk only eventually when
the cache is full?
On Mon, 2024-01-08 at 09:20 -0800, mche...@gmail.com wrote:
> hi.
> why insert many record to in-memory database is same speed as
> embedded-mode. In-mem should be much faster, right?
>
>
> import java.io.FileNotFoundException;
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.SQLException;
> import java.sql.Statement;
> import me.tongfei.progressbar.ProgressBar;
> import org.junit.Test;
>
> /**
> *
> * @author peter
> */
> public class TestH2InsertSpeed {
>
> @Test
> public void test() throws FileNotFoundException, IOException,
> SQLException {
>
> Connection conn =
> DriverManager.getConnection("jdbc:h2:./test;CACHE_SIZE=13107200;PAGE_
> SIZE=10240;CACHE_TYPE=SOFT_LRU;", "sa", "");
> // Connection conn =
> DriverManager.getConnection("jdbc:h2:mem:test;CACHE_SIZE=13107200;PAG
> E_SIZE=10240;CACHE_TYPE=SOFT_LRU;");
> Statement stmt2 = conn.createStatement();
> stmt2.execute("drop table if exists qemu;");
> stmt2.execute("""
> CREATE TABLE
> "PUBLIC"."QEMU"
> (
> "ID" integer
> auto_increment PRIMARY KEY NOT NULL,
> "SEQUENCE" bigint,
> "DATE" timestamp,
> "COMPUTER" varchar(50),
> "INTERRUPT" boolean,
> "INTERRUPT_CAUSE"
> bigint,
> "INTERRUPT_DESC"
> varchar(30),
> "PC" bigint NOT NULL,
> "MHARTID" bigint NOT
> NULL,
> "MSTATUS" bigint NOT
> NULL,
> "HSTATUS" bigint NOT
> NULL,
> "VSSTATUS" bigint NOT
> NULL,
> "MIP" bigint NOT NULL,
> "MIE" bigint NOT NULL,
> "MIDELEG" bigint NOT
> NULL,
> "HIDELEG" bigint NOT
> NULL,
> "MEDELEG" bigint NOT
> NULL,
> "HEDELEG" bigint NOT
> NULL,
> "MTVEC" bigint NOT NULL,
> "STVEC" bigint NOT NULL,
> "VSTVEC" bigint NOT
> NULL,
> "MEPC" bigint NOT NULL,
> "SEPC" bigint NOT NULL,
> "VSEPC" bigint NOT NULL,
> "MCAUSE" bigint NOT
> NULL,
> "SCAUSE" bigint NOT
> NULL,
> "VSCAUSE" bigint NOT
> NULL,
> "MTVAL" bigint NOT NULL,
> "STVAL" bigint NOT NULL,
> "HTVAL" bigint NOT NULL,
> "MTVAL2" bigint NOT
> NULL,
> "MSCRATCH" bigint NOT
> NULL,
> "SSCRATCH" bigint NOT
> NULL,
> "SATP" bigint NOT NULL,
> "X0_ZERO" bigint NOT
> NULL,
> "X1_RA" bigint NOT NULL,
> "X2_SP" bigint NOT NULL,
> "X3_GP" bigint NOT NULL,
> "X4_TP" bigint NOT NULL,
> "X5_T0" bigint NOT NULL,
> "X6_T1" bigint NOT NULL,
> "X7_T2" bigint NOT NULL,
> "X8_S0" bigint NOT NULL,
> "X9_S1" bigint NOT NULL,
> "X10_A0" bigint NOT
> NULL,
> "X11_A1" bigint NOT
> NULL,
> "X12_A2" bigint NOT
> NULL,
> "X13_A3" bigint NOT
> NULL,
> "X14_A4" bigint NOT
> NULL,
> "X15_A5" bigint NOT
> NULL,
> "X16_A6" bigint NOT
> NULL,
> "X17_A7" bigint NOT
> NULL,
> "X18_S2" bigint NOT
> NULL,
> "X19_S3" bigint NOT
> NULL,
> "X20_S4" bigint NOT
> NULL,
> "X21_S5" bigint NOT
> NULL,
> "X22_S6" bigint NOT
> NULL,
> "X23_S7" bigint NOT
> NULL,
> "X24_S8" bigint NOT
> NULL,
> "X25_S9" bigint NOT
> NULL,
> "X26_S10" bigint NOT
> NULL,
> "X27_S11" bigint NOT
> NULL,
> "X28_T3" bigint NOT
> NULL,
> "X29_T4" bigint NOT
> NULL,
> "X30_T5" bigint NOT
> NULL,
> "X31_T6" bigint NOT
> NULL,
> "LINENO" bigint,
> "CODE" varchar(200),
> "MEM" boolean,
> "MEMOPERATION"
> varchar(200),
> "MEMREAD" boolean,
> "MEMADDR" bigint,
> "MEMVALUE" bigint,
> "MEMSIZE" integer,
> "CCODE" varchar(100),
> "PRIV" integer,
> "IRQREQUEST" boolean,
> "IRQREQUESTNO" integer,
> "IRQREQUESTLEVEL"
> integer
> );""");
>
> String sql = "INSERT INTO `qemu` VALUES (default, ?,
> CURRENT_TIMESTAMP(), 'quantr-ubuntu', ?, ?, ?";
> sql += ",?".repeat(59);
> sql += ",?,?,?,?,?,?,?,?,?,?,?,?,?)";
> PreparedStatement stmt = conn.prepareStatement(sql);
>
> ProgressBar pb = new ProgressBar("Insert H2", 1000);
> for (int x1 = 0; x1 < 1000; x1++) {
> // System.out.println(x1);
> for (int x2 = 0; x2 < 1; x2++) {
> int x = 1;
> stmt.setLong(x++, 12345678l);
> stmt.setBoolean(x++, true);
> stmt.setLong(x++, 12345678l);
> stmt.setString(x++, "aslkdjads
> alksdja ldj");
> for (int z = 0; z < 59; z++) {
> //
> System.out.println(me.getKey() + "\t= " + me.getValue());
> //
> System.out.println(">" + me.getKey() + "=" + me.getValue());
> stmt.setLong(x++, 12345678l);
> }
> stmt.setLong(x++, 12345678l);
> stmt.setString(x++, "askdjasd
> aljdlasjdlkaj sd");
> stmt.setBoolean(x++, true);
> stmt.setString(x++, "peter cheung");
> stmt.setBoolean(x++, false);
> stmt.setLong(x++, 12345678l);
> stmt.setLong(x++, 12345678l);
> stmt.setInt(x++, 12345678);
> stmt.setString(x++, "asdasd as daasd
> asda sds
Re: [h2] open a db file named xxxxx.h2.db
Greetings! "*.h2.db" is a very old, page store based version of H2 (I believe 1.3.175 or so). You will need to download one of those old libraries or convert the H2 file into a recent version via export to script and import script. You can give the H2 Migration Tool a try: https://manticore-projects.com/H2MigrationTool/index.html (But I have not done many tests with those ancients versions. I am in interested in feedback though.) Good luck and cheers Andreas On Tue, 2023-12-26 at 04:13 -0800, Ali Atav wrote: > Hello everyone. > > First of all; merry xmas and happy new year for everyone. > > I'm new to H2; i have a running race result file as xxx.h2.db. > how can i open it with H2?? > > Thanks in advance. > > Ali > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/dd8e36fe-2a41-4599-82f9-da2edd9efbf1n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/41212462ccd151a97975afb42f687c525c28fade.camel%40manticore-projects.com.
Re: [h2] H2 db migration tool
On Fri, 2023-12-15 at 04:14 -0800, prayag shete wrote: > How to use https://github.com/manticore-projects/H2MigrationTool > on linux server without UI, want to upgrade db from 1.4 jar to 2.2 Please see: https://manticore-projects.com/H2MigrationTool/usage.html Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a6dc38cd5bf10c73befd27ba4e094de3713ed403.camel%40manticore-projects.com.
[h2] Building Pivot Tables with Example
Greetings!
Since H2 does not support the `PIVOT` clause yet, we have had to
develop a work around, which we would like to share.
Our solution is RDBMS agnostic and uses a plain JDBC `ResultSet` with a
Category Column for pivoting and a Values Column for aggregation. It
will return an Object[][] array suitable to fill a JTable or a spread
sheet.
The code is available: https://github.com/manticore-projects/MJdbcUtils
Please see below an accounting driven example for deriving the
Comprehensive Income View from a Table of Debit/Credit entries below.
Restrictions:
- currently only the SUM aggregate is supported, we plan to amend this
eventually
- currently only BigDecimal values are supported, we are going to amend
this for any other scalar data type
Plans:
- write a CREATE TABLE statement based on the data
- write a CREATE MATERIALISED VIEW statement
Please do let me know what you think of it and which additional
features may be useful. Your PRs and suggestions will be most welcome
and appreciated.
Thank you for your time and cheers
Andreas
0) Maven Artifact
implementation('com.manticore-projects.jdbc:MJdbcUtils:+') {changing=true}
1) Source ResultSet, with VALUE_DATE as category and AMOUNT as agregate
data (78 records in total, to be aggregated over 5 months)
GL_LINECODE_FROMDESCRIPTION_FROMCODE_TODESCRIPTION_TOVALUE_DATEAMOUNTID
_CURRENCY
01.0x.0x.99Expected Claims0x.0x.99Settlement Account01/01/2023-
40,000.00USD
01.0x.0x.99Insurance Fees Expense0x.0x.99Settlement Account01/0
1/202310,000.00USD
01.0x.0x.99Pending Claims0x.0x.99Settlement Account31/03/202317
,000.00USD
02.02.06.01Insurance Fees Expense02.06.01Previous year Profit a
nd Loss31/12/2023-27,000.00USD
02.02.06.01Premium Income Insurance02.06.01Previous year Profit
and Loss31/12/202340,000.00USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts01/01/2023-10,000.00USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts31/01/20232,076.97USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts28/02/20231,903.10USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts31/03/20232,067.89USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts30/04/20231,962.77USD
02.02.11.01Insurance Fees Expense02.11.01Prepaid Acquisition Co
sts31/05/20231,989.27USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims01/
01/2023-13,556.09USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims31/
01/2023-3,270.02USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims28/
02/2023-3,270.62USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims31/
03/202313,577.29USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims30/
04/2023-3,219.17USD
02.02.11.02Contractual Service Margin02.11.02Expected Claims31/
05/2023-3,261.39USD
02.02.11.02Insurance Loss02.11.02Expected Claims31/03/20233,310
.78USD
02.02.11.02Insurance Loss02.11.02Expected Claims31/05/2023-
3,310.78USD
02.02.11.02Premium Income Insurance02.11.02Expected Claims01/01
/2023-10,000.00USD
02.02.11.02Premium Income Insurance02.11.02Expected Claims31/03
/2023-17,000.00USD
02.02.11.02Risk Margin02.11.02Expected Claims01/01/2023-
438.90USD
02.02.11.02Risk Margin02.11.02Expected Claims31/01/2023110.67US
D
02.02.11.02Risk Margin02.11.02Expected Claims28/02/202379.46USD
02.02.11.02Risk Margin02.11.02Expected Claims31/03/2023224.28US
D
02.02.11.02Risk Margin02.11.02Expected Claims30/04/20230.42USD
02.02.11.02Risk Margin02.11.02Expected Claims31/05/202324.07USD
02.02.11.02Settlement Account02.11.02Expected Claims01/01/20234
0,000.00USD
02.02.11.03Expected Claims02.11.03Risk Margin01/01/2023438.90US
D
02.02.11.03Expected Claims02.11.03Risk Margin31/01/2023-
110.67USD
02.02.11.03Expected Claims02.11.03Risk Margin28/02/2023-
79.46USD
02.02.11.03Expected Claims02.11.03Risk Margin31/03/2023-
224.28USD
02.02.11.03Expected Claims02.11.03Risk Margin30/04/2023-0.42USD
02.02.11.03Expected Claims02.11.03Risk Margin31/05/2023-
24.07USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin01/
01/202313,556.09USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin31/
01/20233,270.02USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin28/
02/20233,270.62USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin31/
03/2023-13,577.29USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin30/
04/20233,219.17USD
02.02.11.04Expected Claims02.11.04Contractual Service Margin31/
05/20233,261.39USD
02.02.11.04Premium Income Insurance02.11.04Contractual Service
Margin31/01/2023-2,267.71USD
02.02.11.04Premium Income Insurance02.11.04Contractual
Re: [h2] Comparing NULL able columns --> IS DISTINCT FROM
On Mon, 2023-09-04 at 20:41 -0700, Evgenij Ryazanov wrote: > IS DISTINCT FROM is a null-safe equivalent of <> Thank you much for the explanation. I was not aware of this clause (after so many years of using SQL on many RDBMS). It is exactly what I was looking for and now I only need to check out the support on the different RDBMS. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d5f019469aafd7cb39c15dced28e08dc5a93d168.camel%40manticore-projects.com.
Re: [h2] Comparing NULL able columns --> IS DISTINCT FROM
Sorry for the noise. Chat GPT is smarter than me, I should have tried that first: In SQL, you can compare two nullable columns efficiently to find out if they hold different values using the `IS DISTINCT FROM` or `IS NOT DISTINCT FROM` operators. These operators are typically supported in databases like PostgreSQL, but may not be available in all database systems. Here's how you can use them: 1. Using `IS DISTINCT FROM`: This operator returns true if the two expressions are not equal, even if one or both of them are NULL. ```sql SELECT * FROM your_table WHERE column1 IS DISTINCT FROM column2; ``` In this query, it will return rows where `column1` and `column2` have different values, including cases where one or both columns are NULL. 2. Using `IS NOT DISTINCT FROM`: This operator returns true if the two expressions are equal, including cases where both are NULL. ```sql SELECT * FROM your_table WHERE column1 IS NOT DISTINCT FROM column2; ``` In this query, it will return rows where `column1` and `column2` have the same values, including cases where both columns are NULL. Choose the operator that best fits your specific requirements for handling NULL values. If you want to consider NULL values as different values, use `IS DISTINCT FROM`. If you want to treat NULL values as equivalent, use `IS NOT DISTINCT FROM`. Keep in mind that the availability of these operators may vary depending on the SQL database system you're using, so check your database's documentation for compatibility. On Mon, 2023-09-04 at 22:25 +0700, Andreas Reichel wrote: > Greetings. > > please let me ask for some brain storming: > > I have a table COUNTER_PARTY and a matching staging table > IMP_COUNTERPARTY. > Now I want to find any records of COUNTER_PARTY which will need to be > updated according to IMP_COUNTERPARTY by comparing all the columns. > If one of the columns has a different value, I would need to update > the record in COUNTER_PARTY. > > So far, so simple -- unless NULLs enter the stage, because always: > NULL != NULL. > > What would be the most efficient way to check, if two values are > equal or both are NULL without writing this out verbosely: > > select * > from COUNTER_PARTY a > left join IMP_COUNTERPARTY b > on a.id = b.id > where > ( a.name != b.name or (a.name is null and b.name is null) ) > or ( a.first_name != b.first_name or (a.first_name is null and > b.first_name is null) ) > > I know, that for Strings I can write it as > NVL( a.name, '') != NVL( b.name, '') > > However, this fails for DECIMALS, e.g. > NVL( a.age, 0) != NVL( b.age, 0) wont work when age is defined as > DECIMAL(3,0) > > Can't we have a simplified Compare() or Equal() function? > > Thank you for any input, best regards > Andreas > > > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/9d5b77d808f333256e648a894c6d8fc60bd63bf3.camel%40manticore-projects.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/216681b6bf9efa0be31bef3588395d00fc01626e.camel%40manticore-projects.com.
[h2] Comparing NULL able columns
Greetings. please let me ask for some brain storming: I have a table COUNTER_PARTY and a matching staging table IMP_COUNTERPARTY. Now I want to find any records of COUNTER_PARTY which will need to be updated according to IMP_COUNTERPARTY by comparing all the columns. If one of the columns has a different value, I would need to update the record in COUNTER_PARTY. So far, so simple -- unless NULLs enter the stage, because always: NULL != NULL. What would be the most efficient way to check, if two values are equal or both are NULL without writing this out verbosely: select * from COUNTER_PARTY a left join IMP_COUNTERPARTY b on a.id = b.id where ( a.name != b.name or (a.name is null and b.name is null) ) or ( a.first_name != b.first_name or (a.first_name is null and b.first_name is null) ) I know, that for Strings I can write it as NVL( a.name, '') != NVL( b.name, '') However, this fails for DECIMALS, e.g. NVL( a.age, 0) != NVL( b.age, 0) wont work when age is defined as DECIMAL(3,0) Can't we have a simplified Compare() or Equal() function? Thank you for any input, best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9d5b77d808f333256e648a894c6d8fc60bd63bf3.camel%40manticore-projects.com.
Re: [h2] H2 Database Engine: New version released
Greetings!
I have run your test through all H2 versions and have not been able to
see any material performance deviation:
INFO: 1.3.176: 101681 ms
INFO: 1.4.196: 104554 ms
INFO: 1.4.197: 103474 ms
INFO: 1.4.198: 101421 ms
INFO: 1.4.199: 99655 ms
INFO: 1.4.200: 100125 ms
INFO: 2.0.201: 100645 ms
INFO: 2.0.202: 101761 ms
INFO: 2.0.204: 101122 ms
INFO: 2.0.206: 100877 ms
INFO: 2.1.210: 100627 ms
INFO: 2.1.212: 99316 ms
INFO: 2.1.214: 98967 ms
INFO: 2.2.220: 98540 ms
INFO: 2.2.222: 103886 ms
INFO: 2.2.229: 103005 ms
Those are very rough estimates based
on System.currentTimeMillis() without engaging the JMH benachmarking
tool.
I am still working on a generic Performance Test Framework, which can
read simply Test Case Scripts like the one attached.
In the meantime, please elaborate on your concern since I can't really
confirm it so far.
Best regards
Andreas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/0b297f12b1eb881db8d30910d60742c79dec69cd.camel%40manticore-projects.com.
def String[] sqlStr = [
"CREATE TABLE IF NOT EXISTS A (number VARCHAR(128) not NULL, intent INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL, PRIMARY KEY (number,intent,objID,objType));",
"CREATE INDEX IF NOT EXISTS A_IDX ON A(objType,objID,intent);",
"CREATE INDEX IF NOT EXISTS A_type_IDX ON A (objType);",
"DROP TABLE IF EXISTS B; ",
"CREATE TABLE IF NOT EXISTS B (number VARCHAR(128) not NULL, intent INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL, PRIMARY KEY (number,intent,objID,objType));",
"CREATE INDEX IF NOT EXISTS B_IDX ON B(objType,objID,intent);",
"DELETE FROM A T WHERE EXISTS (SELECT NULL from A S WHERE T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND T.number<>S.number);",
"MERGE INTO A T USING (SELECT * FROM B) AS S ON T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND T.number=S.number WHEN NOT MATCHED THEN INSERT (objID, objType, number, intent) VALUES (S.objID, S.objType, S.number, S.intent);",
"DROP TABLE B CASCADE"
]
statement.execute(sqlStr[0]);
statement.execute(sqlStr[1]);
statement.execute(sqlStr[2]);
for (int loop = 0, number = 0; loop < 300; ++loop) {
statement.execute(sqlStr[3]);
statement.execute(sqlStr[4]);
statement.execute(sqlStr[5]);
for (int i = 0; i < 100; ++i) {
++number;
statement.execute("MERGE INTO B (number,intent,objID,objType) VALUES ('"
+ number
+ "',1, '"
+ number
+ "', 1);");
}
statement.execute(sqlStr[6]);
statement.execute(sqlStr[7]);
statement.execute(sqlStr[8]);
logger.info "loop = $loop"
}
Re: [h2] H2 Database Engine: New version released
H2 Team, do you have any facility in place for running such performance tests across various versions of H2 and comparing the outcome? Something like: 1) load driver 2) run various test scripts 3) write performance measures for this driver version as per script 4) unload driver and repeat over all provided drivers. if not, would you like to accept such a basic test performance test suite? Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/eda4e270718a328c3b1dbfb0fd207cc5add7441f.camel%40manticore-projects.com.
Re: [h2] H2 Database Engine: New version released
Greetings Juergen.
Unfortunately I can't reproduce your concern after implementing your
test in the quickest and dirtiest way, with lots of overhead.
It ran through in 1 minute and I did not see any slowdown while
iterating:
> Task :ETLBox:H2PerformanceTest.main()
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 0
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 1
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 2
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 296
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 297
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 298
Aug 29, 2023 6:14:08 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 299
BUILD SUCCESSFUL in 1m 13s
What exactly means "after some seconds" vs. "after some minutes"?
Can you elaborate on the details please?
Best regards
Andreas
On Tue, 2023-08-29 at 03:53 -0700, Jürgen Pingel wrote:
> Thanks for the fix version 2.2.222.
> Don't know if it will work for me, because I run with that revision
> into a performance problem.
> One of my unit tests runs now extremely long and it seems that much
> memory get allocated.
> Can't send you the complete code but I isolate it to the SQL
> statements and looks like:
>
> CREATE TABLE IF NOT EXISTS A (number VARCHAR(128) not NULL, intent
> INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL,
> PRIMARY KEY (number,intent,objID,objType));
> CREATE INDEX IF NOT EXISTS A_IDX ON A(objType,objID,intent);
> CREATE INDEX IF NOT EXISTS A_type_IDX ON A (objType);
>
> for (int loop = 0, number = 0; loop < 300; ++loop){
> DROP TABLE IF EXISTS B;
> CREATE TABLE IF NOT EXISTS B (number VARCHAR(128) not NULL, intent
> INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL,
> PRIMARY KEY (number,intent,objID,objType));
> CREATE INDEX IF NOT EXISTS B_IDX ON B(objType,objID,intent);
>
> for (int i = 0; i < 100; ++i) {
> ++number;
> MERGE INTO B (number,intent,objID,objType) VALUES
> (Integer.toString(number),1, Integer.toString(number), 1)
> }
>
> DELETE FROM A T WHERE EXISTS (SELECT NULL from A S WHERE
> T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND
> T.number<>S.number)
> MERGE INTO A T USING (SELECT * FROM B) AS S ON T.objID=S.objID AND
> T.objType=S.objType AND T.intent=S.intent AND T.number=S.number WHEN
> NOT MATCHED THEN INSERT (objID, objType, number, intent) VALUES
> (S.objID, S.objType, S.number, S.intent);
> DROP TABLE B CASCADE
> }
>
> If run with the previous versions 1.4, 2.1 and 2.2.220 it's finished
> after some seconds.
> With the 2.2.222 it takes longer with each loop; don't finish after
> some minutes.
>
> Please take a look; thanks.
>
> Thanks,
> Jürgen
> Andreas Reichel schrieb am Mittwoch, 23. August 2023 um 14:12:42
> UTC+2:
> > On Wed, 2023-08-23 at 08:09 -0400, Andrei Tokar wrote:
> > > This is a patch release, and AFAIK there are no incompatibilities
> > > with
> > > on-disk format of v.2.2.220, so hopefully it can be used as drop
> > > in
> > > replacement for 2.2.220 (and only 2.2.220!).
> >
> >
> >
> > Thank you very much.
> > Just in case: We have amended H2MigrationTool for support of H2
> > 2.2.222 already.
> >
> > http://h2migrationtool.manticore-projects.com/
> >
> > Best regards
> > Andreas
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/a9dde386-ec94-4beb-b13a-a2913f6e4311n%40googlegroups.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/a7aba634cf7821a96bb648b63281a7043d3a7556.camel%40manticore-projects.com.
package com.manticore.etl.custom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class H2PerformanceTest {
private final static Logger LOGGER = Logger.getLogger(H2PerformanceTest.class.getName());
public static void main(String[] a
Re: [h2] H2 Database Engine: New version released
On Wed, 2023-08-23 at 08:09 -0400, Andrei Tokar wrote: > This is a patch release, and AFAIK there are no incompatibilities > with > on-disk format of v.2.2.220, so hopefully it can be used as drop in > replacement for 2.2.220 (and only 2.2.220!). Thank you very much. Just in case: We have amended H2MigrationTool for support of H2 2.2.222 already. http://h2migrationtool.manticore-projects.com/ Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e8a3da3143988c31b65accf1ada090f57150d457.camel%40manticore-projects.com.
[h2] Fastest way to load MASSIVE data into H2
Greetings! Please what would be the equivalent to Oracle SQL Loader for H2? I know that H2 can read CSV directly, but I consider that (too) slow for large data. Is there any prepared facility for avoid parsing cell by cell and also skipping indices until the end? Anything like Disable Index --> PARQUET File --> PreparedStatement --> Rebuild Index? Thank you in advance and cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4628253f195cba0e622ba38d4a2852a349db801c.camel%40manticore-projects.com.
Re: [h2] H2 DB - Issues with the migration need from 2.1 to 2.2. And likely again to 2.3?
On Thu, 2023-08-03 at 04:27 -0700, Thomas Hurley wrote: > I am a Product Manager with 3 commercial products using H2 and > needing to upgrade. 1 upgraded to 2.1 and we had to go through a > migration process to make this seamless to our customers (mostly > heavily regulated companies). This upgrade went ok and no major > issues but we invested a lot of engineering time to make sure that > worked well for all upgrading customers. > > It now looks like upgrading from 2.1 to 2.2 will require the same > effort. Is that the case? Are you planning to make updates backward > compatible or will every upgrade require scripting to migrate data - > this is not a feasible solution for me and ask that you make upgrades > backward compatible to avoid your customers (and mine) having to > customise migration every version upgrade of H2. > > Look forward to your response. Greetings! We are in a similar situation, having multiple H2 Databases on our servers and need to upgrade all of those smoothly when we deploy newer versions of our software. Also, NOT upgrading H2 is never really an option since all the provided fixes are valuable and needed. That's why we wrote the Migration Tool. It can convert whole (recursive) directories with all DBs in it and we integrated it directly into our software. Upgrading from 2.1 to 2.2 was a minor task for us. We did not notice any incompatible features. Biggest issue (for us) is that CTEs are completely broken since 2.1.212 and return no rows (without throwing errors). As long as you don't use CTEs, you should be good. If you use CTEs you may need to refactor your queries (We use JSQLParser for this.) Good luck and cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f87230f862e9a93456bae210c8c8bab7a68a2086.camel%40manticore-projects.com.
Re: [h2] Problem upgrading from h2 version 2.1.214 to 2.2.220
Greetings.
You will need to Export to SQL Script and Create new DB from SQL
Script.
I wrote an UI that can help you with
that: https://manticore-projects.com/H2MigrationTool/index.html
There is an online version for testing small
databases: http://h2migrationtool.manticore-projects.com/
Cheers
Andreas
On Tue, 2023-08-01 at 06:35 -0700, Fredrik Sjögren wrote:
> When we have upgraded the h2 library we get following error trying to
> open the existing databases:
>
> Unsupported database file version or invalid file header in file
> "/path/to/database.h2.mv.db" [90048-220] 90048/90048
>
> We use a file based database ("jdbc:h2:file:...")
>
> Creating new databases works well.
>
> Has something changed so the files are not compatible?
> Any way to get around this in an easy way? The upgrade from version 1
> was a hassle for us earlier and we really wish not to do that again.
> Is there any way to get more information about why it gets this
> error?
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/619938c2-25f5-47cd-909c-c2cb1796464fn%40googlegroups.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/b5d0656eae4b9ea262d51caa64c55c1878e7e87c.camel%40manticore-projects.com.
Re: [h2] Slow Performance of Update / merge into sStatements
Greetings! 1) your write "embedded" mode, but your URL shows File access (not Memory) 2) you don't state the H2 version you are using 3) maybe provide a simplified, self containing test for people to look at the details In general, large DML on indexed columns/tables can lead to "write amplification". I filed a similar issue 2 years back. The received advice was: 1) remove indexes, update, create indexes (effective, but not nice) 2) update smaller portions (is this what you mean with "split" mode?) Best regards Andreas On Wed, 2023-07-19 at 07:24 -0700, Andrii Odnoviunenko wrote: > Hello, > > We face the following performance issue of the H2 Database V2 in > the embedded mode is not good > (UPDATE statement for 1 column in a table with 1 Million dataset > with a unique index column ) takes about 40 seconds > > jdbc:h2:file:XX:CACHE_SIZE=600;QUERY_CACHE_SIZE=16; > > also "merge into" statements with a join between 2 indexed columns > are comparably slow. > In general the performance is times worse than in a test with Oracle > database > > various settings have been tried (increasing cache size helped a bit, > but not much > I have also tried the "split" mode and changing page size to other > values (there have ben only marginally small performance gains) > > Please advice what the reason can be? Which other settings may cause > this performance issue? > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/d5484788-daed-48e8-9ca1-3369e11f3350n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b12c2d76f3610a24e287bae00dc9c024c4a19317.camel%40manticore-projects.com.
Re: [h2] H2 Database Engine: New version released
Greetings! On Tue, 2023-07-11 at 10:20 -0700, Vali Maties wrote: > How do I upgrade it to be able to use the latest driver, as long as > this one seems to be no more warnings in it! You could use the H2 Migration Tool https://manticore-projects.com/H2MigrationTool/index.html Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8c9f31bcb928154c4954a7de9631fae8a410ac8f.camel%40manticore-projects.com.
Re: [h2] H2 Database Engine: New version released
Thank you so much H2 Team! We have updated the H2 Migration Tool to reflect the new H2 Library. <https://manticore-projects.com/H2MigrationTool/index.html> All the best Andreas On Tue, Jul 4 2023 at 01:07:51 PM -04:00:00, Andrei Tokar wrote: Hello, A new version 2.2.220 of H2 is available at http://www.h2database.com <http://www.h2database.com/> (you may have to click 'Refresh') and <https://github.com/h2database/h2database/releases/tag/version-2.2.220> For details, see the 'Change Log' at <http://www.h2database.com/html/changelog.html> New artefacts will be uploaded to maven repository shortly. Have fun, Andrei Tokar -- 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 view this discussion on the web visit <https://groups.google.com/d/msgid/h2-database/baa7598e652f5e0f469b2706f8e990dd4b32ac31.camel%40gmail.com>. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/HFTAXR.6Q6DG8CIKJ5G2%40manticore-projects.com.
[h2] H2 Migration Tool 1.3.4 with Online Version and H2 2.2.219 Snapshot
Greetings! We have updated the H2 Migration Tool and made an Online Version available (for the quick migration or recovery in between). Please see: http://h2migrationtool.manticore-projects.com Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f1a7eb3acf82d1a8f74164e9f6fc781e81a843b8.camel%40manticore-projects.com.
Re: [h2] Create an SQL to copy all from H2 database in geonetork
Greetings! First, you NEED to find the location of the Database file in your file- system. It should be stated clearly in the connection URL. When having located this file, you can use the H2 Command Line Tools for Recover/Script or the provided UI Tool to extract the DB into a SQL file. The UI works on any OS including MacOS since it is a Java based application which depends on a JRE 11 only. Good luck! Andreas On Tue, 2023-05-30 at 02:15 -0700, xavi rayo wrote: > My main problem is that I have de h2.db file but I can't find how to > acces to it > > El dia dimarts, 30 de maig de 2023 a les 11:14:31 UTC+2, xavi rayo va > escriure: > > Hi Andreas, > > thanks a lot for your help! > > I was wondering if there is the possibility to use the user > > interface in a macOs computer or should I use the command line. > > > > Thanks a lot! > > > > El dia dilluns, 29 de maig de 2023 a les 16:02:10 UTC+2, Andreas > > Reichel va escriure: > > > Greetings, > > > > > > you can try "Export to SQL" and/or "Recovery". Both will give you > > > an SQL script which can be used to create a DB afresh. > > > There is command line and I also provide an (unoffical) UI: > > > https://github.com/manticore-projects/H2MigrationTool > > > > > > Good luck! > > > Andreas > > > > > > On Mon, 2023-05-29 at 05:46 -0700, xavi rayo wrote: > > > > Hi all, > > > > I have a geonetwork installation which uses H2 as storage > > > > database. My geonetwork have crashed and can't manage to make > > > > it work again, so before doing a fresh install, I would like > > > > (really need it!) to make a copy of the data of the database. > > > > Any suggestion on how to do it will be really appreciated. > > > > > > > > Thanks a lot! > > > > > > > > > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/894a39861b872988fb09c0928a67ebc3ba7fe90c.camel%40manticore-projects.com.
Re: [h2] Create an SQL to copy all from H2 database in geonetork
Greetings, you can try "Export to SQL" and/or "Recovery". Both will give you an SQL script which can be used to create a DB afresh. There is command line and I also provide an (unoffical) UI: https://github.com/manticore-projects/H2MigrationTool Good luck! Andreas On Mon, 2023-05-29 at 05:46 -0700, xavi rayo wrote: > Hi all, > I have a geonetwork installation which uses H2 as storage database. > My geonetwork have crashed and can't manage to make it work again, so > before doing a fresh install, I would like (really need it!) to make > a copy of the data of the database. Any suggestion on how to do it > will be really appreciated. > > Thanks a lot! > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/f392abc9-f1e2-4c41-a02a-058982219e8bn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/037cc646a5e5a8483c2109c5f2e905c293d7ad47.camel%40manticore-projects.com.
Re: [h2] SpringBoot multi Thread. Query extremely slow
Greetings once again. One thing you could do for confirming the performance: Export your tables into PARQUET files and load those into a Column-based database (e.g. duckdb), which may have an advantage for your particular aggregation. Then run your query against that Column-based DB. IF the performance is the same (more or less), then the issue is with your table/index design or with Spring/Hibernate. (I assume this is the case here.) Only if the performance was much better, then this may be a H2 issue worth to report. Good luck Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1133665b671f4ec896cc1cea85d3520c5aa1793c.camel%40manticore-projects.com.
Re: [h2] SpringBoot multi Thread. Query extremely slow
Greetings. Without knowing the Table definition and the indexes, nobody will be able to help. However, you may look for: 1) the JOINERS are all repetitive, filtering for a particular value of "key1". Instead repeating for each value, just use "key1" for the aggregation. 2) ensure, that "key1" and "val" are indexed and that those indices are used. Beware losing the index access when introducing the sub-queries. Depending on your data volume, it may be more efficient, to write the sub-queries into a temporary table, then index that and run the main query on the indexed temporary table. 3) avoid the "val != 'NaN'" as it will likely result in a full table scan. Instead, write something like " val IN (...)" which can used indexes (at least with latest Git Snapshot, if I understood latest commits correctly) 4) run you query against H2 directly and eliminate the Spring/Hibernate Layer. Get a proper execution plan and check the reads and used indices. Avoid Full table Scans at all cost. Good Luck! Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/663582052f72fb884c24968584482125142eadc7.camel%40manticore-projects.com.
Re: [h2] Problem updating from 1.4.200 to 2.1.214
Greetings,
maybe compile H2 from source by yourself with a JDK 8, just to prove
any doubts.
Best regards
Andreas
On Tue, 2023-05-23 at 14:51 +0530, Vardhan Belide wrote:
> Hi,
> I am trying to update from 1.4.200 to 2.1.214. I am using Java 8 in
> my project. I have the following piece of code
>
> private final Connection decorated;
> if(decorated instanceOf JdbcDataSource)
> {
> }
>
> I am getting the error message incompatible types:
> java.sql.Connection cannot be converted to
> org.h2.jdbcx.JdbcDataSource. I was not getting the compilation error
> with the previous version of h2.
>
> Any suggestions?
>
> Thanks
> Vardhan
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/CAF11HREa73g0AQ_oEMYB7%3DqSfOpuKrowMZW5khTgRiTyOYC4Bg%40mail.gmail.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/746dfa3928a83d6ac73a3abc4014cc14af1d7573.camel%40manticore-projects.com.
Re: [h2] In-Memory H2 deleted rows not garbage collected.
Greetings! On Sun, 2023-05-21 at 23:42 -0700, Arjun Sahoo wrote: > Reproducing the test case is easy, keep on ingesting and deleting > records, you will see the memory usage increasing over time. If if > query the table simultaneously , you will reach the trend faster. You will need to write a self containing unit test simulating this, Only this way people will help ypu. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cb335290abd9d8e0c5d10d9c0180c6c74d62a89d.camel%40manticore-projects.com.
Re: [h2] In-Memory H2 deleted rows not garbage collected.
Noel, question for better understanding please: Would a MEM database not grow exactly as a File backed database keeps growing forever? Or in other words, is there any difference between a MEMORY database and File backed at TEMP FS? I ask because all my file backed H2 databases keep growing and never release all filespace until I export to file, create new and compress. So I would expect the same (mis-) behavior for any memory DB. Setting up a reliable test should be easy: Create a DB, populate with an indexed table and data and run queries continuously for a longer time. Check if size peaks or grows forever. Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/725d35848838abedb93e986fe512b580d4415a7b.camel%40manticore-projects.com.
Re: [h2] Latest version H2 available via tycho
Greetings. You may consider BNDTOOLS https://bndtools.org/ over TYCHO in order to manage dependencies via Maven coordinates. Best regards Andreas On Fri, 2023-05-12 at 00:38 -0700, mrbr...@gmail.com wrote: > I have inherited an old project that is based on Eclipse 2019-09 and > uses Tycho to build it. It contains h2 but it is version 1.3.168. > Is there a later version that is available via Tycho? I have looked > at the various repositories and could not find a later version. > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/751adaf5-69c0-4ddb-8f56-6b87c1763c60n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a8646cb4fa9fe9eb73a36035ce0e4ee18a1fe587.camel%40manticore-projects.com.
Re: [h2] Request for advice
Greetings. As far as I understand it, Evgenji just committed a change regarding index use in `IN()` clause. Maybe try the very lastest GIT Master first. Also I wonder, why you would not use a JOIN instead of a IN() when you have a list of FK_A from C: select * from a inner join c on a.pk = c.fk_a Why use (uncorrelated?) sub-queries? Best regards Andreas On Wed, 2023-04-26 at 02:03 -0700, Silvio wrote: > We have some heavy queries that involve selecting records from a base > cached table A (~100K records) that satisfy a quite a number of > conditions expressed as > > A.PK [NOT] IN (...) > > on a secondary cached table B (~10M) records. Although the subqueries > use indexed columns the overall query is very slow. > > We are thinking of using a temporary memory table C that holds > primary keys of table A, evaluating the subqueries on B seperately > inserting or removing keys into table C as needed and finally having > a single subquery > > A.PK NOT IN (SELECT FK_A FROM C) > > Has anoyone ever tried such an approach in H2? Is there any reason to > expect an improvement in performance in comparison to the single > large query we have now? > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/798e67aa1384cc368d8af4fdcdae95b57e87435c.camel%40manticore-projects.com.
[h2] PR #3786 CSV empty String to Number conversion
Greetings! To whom it may concern: I wrote the code, it's mine, and I'm contributing it to H2 for distribution multiple-licensed under the MPL 2.0, and the EPL 1.0 (https://h2database.com/html/license.html). Warm regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a6c4dce66e7339ffe5ea4231394cf5059081.camel%40manticore-projects.com.
Re: [h2] DB file size grows in an unexpected size (H2 version 1.4)
Just to cheer you up: I just turned a 2 GByte database into a 15 GByte Database -- by running a "DELETE" (!) statement on a heavily indexed table. The good news is: the latest 2.2.214 does not break/corrupt as easily as the 1.2.xxx line did when interrupting such statements hard (killing the VM). Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4af749e28d328fa31f4301dd34a79000f75af61c.camel%40manticore-projects.com.
Re: [h2] DB file size grows in an unexpected size (H2 version 1.4)
Hi Juergen, while I am just a regular user, but not a developer, I have had a similar discussion a few months ago and I understood that there may be a problem with "Write Amplification" for large transactions on indexed tables (any DML affecting many records). The advice given was to break such large DMLs into smaller chunks, which I did not find easy since there is no "RETURNING" facility in H2. Alternatively you would have to remove indexes, execute your DML and create indexes afresh. Good luck and cheers Andreas On Tue, 2023-04-18 at 03:59 -0700, Jürgen Pingel wrote: > Hello, > we detect that the DB file size grows in an unexpected size. > Following scenario: > we have one DB file with ~30 tables where only ~10 tables regulary > get changed (add, update and delete) > Before open the DB we log out the file size of the DB - mostly it was > ~ 1GB. > But at some point the open DB grows up to ~5 GB - and for sure we > don’t add so much data. > Does anybody know why that could happen? > > Also we notice the following exception in the trace log file if we > close the database: > org.h2.jdbc.JdbcSQLNonTransientException: Allgemeiner Fehler: > "Allgemeiner Fehler: ""java.lang.IllegalStateException: File corrupt > reading chunk at position 236937216 [1.4.200/6]"" > General error: ""java.lang.IllegalStateException: File corrupt > reading chunk at position 236937216 [1.4.200/6]"" [5-200]" > General error: "Allgemeiner Fehler: > ""java.lang.IllegalStateException: File corrupt reading chunk at > position 236937216 [1.4.200/6]"" > General error: ""java.lang.IllegalStateException: File corrupt > reading chunk at position 236937216 [1.4.200/6]"" [5-200]"; SQL > statement: > shutdown [5-200] > at > org.h2.engine.Database.throwLastBackgroundException(Database.java:222 > 1) > at org.h2.engine.Session.close(Session.java:945) > at org.h2.engine.Session.suspend(Session.java:935) > at > org.h2.engine.Database.closeAllSessionsExcept(Database.java:1325) > at > org.h2.engine.Database.setExclusiveSession(Database.java:2571) > at > org.h2.command.dml.TransactionCommand.update(TransactionCommand.java: > 89) > at > org.h2.command.CommandContainer.update(CommandContainer.java:198) > at org.h2.command.Command.executeUpdate(Command.java:251) > at > org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:16 > 8) > at > org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126) > > What happen there in H2 if such happen? Could this explain why the > file grows up in that way? > > Thanks for any help, > Jürgen > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/4d99b80e-10ee-4f21-92b9-aaca08934a57n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/522e59676b1ddc154db685cdc6f0ec6493d40ee0.camel%40manticore-projects.com.
Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)
Greetings.
To my best knowledger, you can use Parameters only for QUERIES and DML,
but not for DDL statements.
Although you can have a look
at https://github.com/manticore-projects/MJdbcUtils which I wrote
exactly for this kind of challenges.
It rewrites your parameterised SQL Statement and I used it for ORACLE's
CREATE TABLE ... AS SELECT ... FROM (where the SELECT must not contain
parameters).
Good luck and cheers
Andreas
On Fri, 2023-04-14 at 04:44 -0700, airjairj wrote:
> The following code is what i'm using for the alter table, nothing
> special but it results in an exception:
> Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in
> SQL statement "ALTER TABLE TableName ADD [*]? BOOLEAN"; expected
> "identifier"; SQL statement:
> ALTER TABLE TableName ADD ? BOOLEAN [42001-214]
>
> The code:
> PreparedStatement preparedStatement = null;
> try {
> final String QUERY_SQL = "ALTER TABLE TableName ADD ? BOOLEAN";
> preparedStatement = connection.prepareStatement(QUERY_SQL);
> preparedStatement.setString(1, User.getUsername());
> preparedStatement.executeUpdate();
> preparedStatement.close();
> } catch (SQLException e) {
> ...
>
>
> For context:
> I'm trying to add a colum (of boolean) to the table named like the
> user and it works if i copy and paste the instruction on the browser
> and run it, am i missing something?
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/c7651d96-15fb-42e2-b4c1-bdee0ac1f2dan%40googlegroups.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/7190f68f2ec4abe866f54d833003b416d6195d11.camel%40manticore-projects.com.
Re: [h2] Re: Need help in diagnosing the db file
Unfortunately you can't count on it since there are no resources available for such an analysis. Only when you have a self contained repeatable test case there was a realistic chance to get help. On 12 Apr 2023 15:47, Moleesh A wrote:Anyone got a chnace to look into this ?On Tuesday, March 28, 2023 at 7:16:04 PM UTC+5:30 Moleesh A wrote:Driver Class: org.h2.DriverJDBC URL: jdbc:h2:./weighdataUser Name: rootPassword: toorh2 version : h2-2.1.214if we open and close the db file in backup it with crash the db file -- 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+unsubscribe@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/47a564f8-ce56-4403-b85c-3f1a81dba6den%40googlegroups.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1648e7fb-865b-46be-bb73-bed0dcc61270%40email.android.com.
Re: [h2] Re: show hex value
Greetings. On Sat, 2023-01-21 at 09:21 -0800, mche...@gmail.com wrote: > hi , possible to show an integer in hex format? As far as I understand you can define your own Functions in H2, using Java language. This should do the trick. Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a31de4029ae59bc816058a7a63793e5eab542902.camel%40manticore-projects.com.
Re: [h2] index doesn't help
Greetings. On Sat, 2023-01-21 at 09:12 -0800, mche...@gmail.com wrote: > where mem or irqRequest <-- very slow I'd try WHERE mem=true OR irqRequest=true It is possible that mem=irqRequest is not detected or considered (even when it resolved to the same meaning). It is also possible the OR expressions are not considered at all. It's not a Oracle DB after all. Good luck Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0112432d3a8e069fec3ba9fc7ca02084d880f138.camel%40manticore-projects.com.
Re: [h2] Re: questions about case sensitivity
On Sun, 2023-01-15 at 22:15 -0800, AndyGo wrote: > If I'm writing all SQL by hand then personally, I'm going to always > use unquoted values for table/view names. Working with Oracle, H2, MS SQL Server and DB2, I think best practise was to write SQL Keywords "lower case" and Identifiers "upper case" (maybe with Functions spelled "camel case", if you are adventurous). select COLUMN1, MY_COLUMN_2 from CFE.TABLE_NAME where Trim(COLUMN1)='something'; This will always work, with out without quoting. And its easy to read even when most SQL syntax highlighting is poor. Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/826eceea5c9d6124655a2ec2a9728c2c14ba0a6c.camel%40manticore-projects.com.
Re: [h2] Re: questions about case sensitivity
Greetings Andy.
On Sun, 2023-01-15 at 22:15 -0800, AndyGo wrote:
> I'm going to always use unquoted values for table/view names.
This is not RDBMS agnostic and will get you into big trouble when
switching from Oracle to Sybase or MS SQL Server or vice versa.
> I've used H2 successfully in different projects for over a decade.
> It's an awesome database with incredible breadth of use cases.
I full agree on that, however: who runs H2 in production should know
exactly what he is doing.
Your issue is a rather simple one: at least you got an error!
At the same time, between releases there is a residual risk that
perfect SQL:2016 compliant queries return wrong/different results
("WITH statement with parameters" since 210) -- and this is really
dangerous because you will suddenly get wrong information without a
chance of noticing.
Don't run software in production under scenarios, which you have not
well tested and understood in the lab before.
> And yet, just the other day when creating a client demo, I received a
> series of error messages that didn't appear to make sense -- even
> accounting for case-sensitivity, the table name of the SQL statement
> matched the table name, yet H2 couldn't find the table?
>
> Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException:
> Table "MYFIRSTTABLE" not found (candidates are: "myFirstTable"); SQL
> statement: SELECT id, name FROM myFirstTable
I do not understand your example: if your table was created as
"myFirstTable" (with explicit quotes), then of course myFirstTable ==
MYFIRSTTABLE == "MYFIRSTTABLE" won't match. Same for Oracle and MS SQL
Server or any other DB I know.
I do not see any H2 issue here, just the usual challenge of writing
platform agnostic SQL.
My advice: stick to the SQL:2016 standard as much as possible and
document well founded deviations (e.g. Oracle .nextval) when forced to
use it.
Cheers
Andreas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/b4fedab64616f1e2067534c3b2a2c12db278a828.camel%40manticore-projects.com.
Re: [h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why
Greetings. regarding the Migration to MS SQL Server/T-SQL, what has worked for me in the past was: 1) Either export to SQL Script and then apply a couple of Regex/Beanshell Replacements using JEdit: Simple Replace: NUMBER( DECIMAL( TIMESTAMP DATETIME2 SYSDATE current_timestamp CLOB VARCHAR(max) BLOB VARBINARY(max) Jedit Regex + BeanShell: TABLE (\w*)\.\"?(\w*)\"? "TABLE [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]" index (\w*)\.(\w*) "INDEX " + _2.toLowerCase() ON (\w*)\.\"?(\w*)\"? "ON [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]" (\w*)\.(\w*).NEXTVAL "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase() /\*TRANSACT: ([\w\s]*)\*/ _1.toUpperCase() 2) Alternatively, parse your exported SQL with JSQLParser and the use a customized De-Parser to rewrite it T-SQL compliant. Illustration is here: https://www.manticore-projects.com/JSQLParser/usage.html#parse-a-sql-statements Good luck and cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f32c5a72d4738c11ead6505a2d89f9ac972549be.camel%40manticore-projects.com.
Re: [h2] index doesn't speed up
From https://www.h2database.com/html/performance.html: This database uses indexes to improve the performance ofSELECT, UPDATE, DELETE. If a column is used in the WHERE clause of a query, and if an index exists on this column, then the index can be used. Multi-column indexes are used if all or the first columns of the index are used.Both equality lookup and range scans are supported.Indexes are used to order result sets, but only if the condition uses the same index or no index at all.The results are sorted in memory if required. Indexes are created automatically for primary key and unique constraints. Indexes are also created for foreign key constraints, if required. For other columns, indexes need to be created manually using the CREATE INDEX statement. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d7ea70cbb05ec219754b0d1353b2847c26e1e1f6.camel%40manticore-projects.com.
Re: [h2] index doesn't speed up
Greetings! As far as I remember, H2 considers composite indices only in certain situations -- but not for all possible optimisations. Please EXPLAIN your query to check, if the index has been considered (I guess, it has not). Maybe try again with 2 different indices, one for MEM and one for SEQUENCE (replacing you composite index). Good luck Andreas On Sun, 2022-12-18 at 02:17 -0800, mche...@gmail.com wrote: > hi > i have 20 millions rows , and this index doesn't speed up the > query, please help: > > create index mem on data(mem, sequence); > > select * from data where mem is not null order by sequence > > mem is varchar(200); and sequence is bigint > > thanks > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/d23b4ca9-2f9b-4bbe-84dd-b7d8469e129dn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cd57a7444f5009c7883fad2d61ce5fb9ad39df57.camel%40manticore-projects.com.
Re: [h2] How are "multiple rows" represented in MVStore?
Well, it depends what you want. But your MAP key should be corresponding with your Table PRIMARY KEY. If you have a compound Primary Key, then your Map also must have a compound Key (not just an Integer) and you would likely need to build a Key Class implementing Comparable, Equals and Hash. Cheers Andreas On Sat, 2022-10-29 at 10:50 +, 'Mark Raynsford' via H2 Database wrote: > > > Imagine I had the following rows: > > (1, 1) // Group 1 contains member 1 > (1, 23) // Group 1 also contains member 23 > (1, 24) // Group 1 also contains member 24 > (2, 4) // Group 2 contains member 4 > (2, 5) // Group 2 contains member 5 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3b3a93f4cf6dc756c95928be661b7d2e1651437d.camel%40manticore-projects.com.
Re: [h2] How are "multiple rows" represented in MVStore?
On Sat, 2022-10-29 at 09:46 +, 'Mark Raynsford' via H2 Database wrote: > I'm slightly confused as to how to represent a particular data > structure efficiently. It's pretty simple, so I'll describe it in > terms > of SQL: > > create table t ( > group integer not null, > member integer not null, > primary key (group, member) > ); > > I think the natural way this would be expressed as a plain Java data > structure would be: > > Map> Greetings, based on your PRIMARY KEY it would be MAP with a Class Key ( int group, int member ) and a class T (int group, int member). If group represents the key, and member represents the object, then PRIMARY KEY(group) and MAP. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0f147504216fdd15e90ded31cabaad56a98cecce.camel%40manticore-projects.com.
Re: [h2] ALTER VIEW PUBLIC."View1" AS SELECT ....
Sorry, I seem to have misread your e-mail. Now it would be: DROP VIEW .. IF EXISTS CREATE OR REPLACE VIEW .. IF NOT EXISTS Best regards Andreas On Tue, 2022-07-05 at 02:50 -0700, prrvchr wrote: > Hi Andreas, > > The UNO API also provides an interface com.sun.star.sdbcx.XRename to > change the name of a view, but apparently this interface is not > implemented in Base because it is never called. > > But I don't want to rename the view but change its command, and I > can't find a command in H2 to do so. > Please advise me the best alternative. > > > Le mardi 5 juillet 2022 à 11:38:52 UTC+2, and...@manticore- > projects.com a écrit : > > Greetings! > > > > On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote: > > > What is the best alternative with H2? > > > > > > H2 has a really excellent > > documentation: https://www.h2database.com/html/commands.html > > > > Your are looking > > for: https://www.h2database.com/html/commands.html#alter_view_rename > > > > Best regards > > Andreas > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/81a629f4-f62f-4f34-beba-d478f52ce688n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f5b765a084d7bb9add81e7cc78e7bf6ebdcc.camel%40manticore-projects.com.
Re: [h2] ALTER VIEW PUBLIC."View1" AS SELECT ....
Greetings! On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote: > What is the best alternative with H2? H2 has a really excellent documentation: https://www.h2database.com/html/commands.html Your are looking for: https://www.h2database.com/html/commands.html#alter_view_rename Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3af602f1c44aa76d90aa0614a281b6493550c0e1.camel%40manticore-projects.com.
Re: [h2] Export a MSSQL compatible script from h2?
On Wed, 2022-06-29 at 19:21 +0200, 'Christoph Läubrich' via H2 Database wrote: > I have a use-case where I collect some data in a local h2 database > and > later want to dump and import it e.g. in MSSQL. > > Is there an option for h2 SCRIPT TO command that takes care of > writing a > SQL Script that could be used in e.g. MSSQL? > > Or is there some way to transfer the data in a vendor independent way > from H2 to another JDBC compatible database connection? > Christoph, if I was in your shoes I would not use the SCRIPT TO, but instead: 1) generate the SQL Script by myself via JDBC Meta Data 2) optionally parse that SQL Script with JSQLParser and De-Parse it into a MSSQL specific dialect. In practise, I have done it differently though: the JEDIT editor supports Regex Search and BeanShell expression replacement. I have had to apply only a few search patterns to turn a H2/Oracle script into MSSQL compliant SQL: Normal Replace: NUMBER( DECIMAL( TIMESTAMP DATETIME2 SYSDATE current_timestamp CLOB VARCHAR(max) BLOB VARBINARY(max) Jedit Regex + BeanShell: TABLE (\w*)\.\"?(\w*)\"? "TABLE [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]" index (\w*)\.(\w*) "INDEX " + _2.toLowerCase() ON (\w*)\.\"?(\w*)\"? "ON [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]" (\w*)\.(\w*).NEXTVAL "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase() /\*TRANSACT: ([\w\s]*)\*/ _1.toUpperCase() More rules/search expressions may be needed, depending on your database. Good luck and best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8d0c3bd3ae9f07d6b8f6cc6db3f55c2779d99602.camel%40manticore-projects.com.
Re: [h2] Automatic COMPACT does not seem to work
Thank your for advising, Andrei. I have set RETENTION_TIME=0 and the DB behaves much more graceful now: Before a compact 2 GB datbase file grew to 15 GByte and never shrank even when idle during the day. Now, the same file grows to 2.5 GB only (increasing in relation to the actually added data daily). It is a very welcome improvement, which deserve more promotion in my opinion. All the best Andreas On Fri, 2022-06-24 at 12:47 -0700, Andrei Tokar wrote: > Yes, it makes sense now to make RETENTION_TIME=0 as a default. Use > case for a positive value is that it theoretically improve your > chances for recovery in case of abrupt shutdown (more history has > been kept), but that's about it. This setting does not change the way > how compaction works, it just delays marking chunks of storage as > available for reuse. Database might be little faster, because there > is less garbage to shuffle around, pretending it's still a data. > Current default (45 sec) seems a way too high. > > On Friday, June 24, 2022 at 4:23:54 AM UTC-4 Ulrich wrote: > > Using RETENTION_TIME=0, I see that a moderate compaction is applied > > :-) That's completely sufficient! > > I'll now check how it behaves in long term. > > > > RETENTION_TIME=0 also speeds up the database significantly. > > Is there any use case to set RETENTION_TIME>0? Should the default > > value be changed in H2? > > > > andrei...@gmail.com schrieb am Donnerstag, 23. Juni 2022 um > > 03:22:44 UTC+2: > > > Documentation is outdated. At the time, garbage determination was > > > inexact and this was used as additional safety. This is not the > > > case since 1.4.197 or 1.4.198, premature storage release is not > > > possible anymore. > > > > > > On Wednesday, June 22, 2022 at 9:17:39 AM UTC-4 Ulrich wrote: > > > > The documentation says: > > > > Using a lower value might be dangerous, unless the file system > > > > and hard disk flush the buffers earlier. > > > > > > > > Is it safe to use RETENTION_TIME=0? What are the consequences? > > > > Do I have to fear that the database file becomes corrupted or > > > > that transactions are not consistent? > > > > > > > > > I would rather try to add RETENTION_TIME=0 > > > > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/610b15c5-d5fc-4e6b-a8a7-31c1012c1a2an%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4892e8f8adc7de8c470147353b0c2860782ffdf0.camel%40manticore-projects.com.
Re: [h2] Automatic COMPACT does not seem to work
On Tue, 2022-06-14 at 08:11 +0200, Noel Grandin wrote: > In general, compacting should work while the db is running, but it's > fairly conservative Noel, not arguing, you know that we do love H2 and are grateful. Although I never saw any online defragmention doing anything. We have 300 MB defragmented content blown up to 20 GByte. The databases are idle during the night and most time the day. Heavy Delete/Write/Querying happens only in the early morning -- but it never shrinks, only grows, until "SHUTDOWN DEFRAG". Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/13bd3bcaa898278cb3bd4d4fb2228d855ca42a39.camel%40manticore-projects.com.
Re: [h2] Automatic COMPACT does not seem to work
Greetings. My understanding is, there is no "auto compact" while the database is running. Instead it will be compacted only during shutdown and only within the defined shutdown time period (longer will give more time to compact). And yes, H2 database consumes a lot of file space, especially when data are constantly written and deleted. I made exactly the same observation. Best regards Andreas On Mon, 2022-06-13 at 02:48 -0700, Ulrich wrote: > Hi all, > > I am running a H2 2.1.212 and I wonder why the automatic compact > feature does not shrink the database size. > > My application is continuously collecting data (around 100 inserts > per second). Most of the data is removed once a day. The data is kept > in 4300 tables. > > The database file size is around 5 GB. When running a manual SHUTDOWN > COMPACT it is compacted to 40 MB. > > I expected that the automatic compact algorithm of the MV_STORE is > able to keep the database smaller than it is. Are there any known > circumstances why the compact algorithm does not run or does not give > good results? > > I already tried to use the latest git code but the results are the > same. > > Here are some infos from the information_schema.settings table with > the relevant parameters: > > AUTO_COMPACT_FILL_RATE 90 > info.FILL_RATE 19 > info.CHUNKS_FILL_RATE 27 > info.CHUNKS_FILL_RATE_RW 27 > info.FILE_SIZE 4916121600 > info.CHUNK_COUNT 490 > info.PAGE_COUNT 1594967 > info.PAGE_COUNT_LIVE 440730 > info.PAGE_SIZE 4096 > info.CACHE_MAX_SIZE 16 > info.CACHE_SIZE 15 > info.CACHE_HIT_RATIO 85 > info.TOC_CACHE_HIT_RATIO 99 > info.LEAF_RATIO 44 > > Thanks! > Ulrich > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/ae5bf92d-f61a-4cac-981a-9e1b51d7d49fn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9c480453482e2f1b81ddf85bedb15af8d68333ea.camel%40manticore-projects.com.
Re: [h2] Column not found when SELECTing from a recursive Common Table Expression (CTE)
Greetings! While I am not a developer, but just an H2 user I would like to recommend to provide: 1) the DDL to create table_1 and table_2 and 2) some DML to fill it with data This way we can reproduce your samples easily, with different versions of H2. That said, the CTEs are still considered experimental although I never had problem using those with latest snapshots 2.2.212 - 2.2.219 Best of luck Andreas On Sat, 2022-06-11 at 10:34 -0700, Robert Faust wrote: > Hello all, > > I'm new to the group so I apologize if this question has already been > answered elsewhere. I'm just hoping someone has some ideas of what > could be happening, as I'm running out of ideas. > > Let's suppose I'm retrieving some data from a table that has a > parent/child reference. I'm using a common table expression to SELECT > the starting nodes from this table. Then, I have a second common > table expression to recursively SELECT the parents/ancestors of the > starting nodes (think starting at the leaf of a tree and working > backwards to the root). > > The basic structure looks like this: > /* Initial data retrieval */ > WITH cte AS ( > SELECT table_1.column_1, /* VARCHAR(255) */ > table_1.column_2, /* VARCHAR(255) */ > table_1.column_3, /* INTEGER */ > table_1.node_id, /* VARCHAR(255) */ > table_1.parent_node_id /* VARCHAR(255) */ > FROM table_1 > ), > > /* Recursive CTE */ > recursive_cte(column_1, column_2, node_id, parent_node_id) AS ( > SELECT cte.column_1, > cte.column_2, > cte.node_id, > cte.parent_node_id > FROM cte > UNION ALL > SELECT table_1.column_1, > table_1.column_2, > table_1.node_id, > table_1.parent_node_id > FROM table_1 > JOIN recursive_cte > ON table_1.node_id = recursive_cte.parent_node_id > ) > > SELECT recursive_cte.column_1, > recursive_cte.column_2 > FROM recursive_cte > > The query works great in both of my test databases. Let's call them > database 1 (1000 records in table_1) and database 2 (100,000 records > in table_1). They both use the same schema. > > Now, I'd like to add another column to the output of recursive_cte so > I can do some additional filtering, so I add column_3 (from table_1) > in the highlighted locations: > /* Initial data retrieval */ > WITH cte AS ( > SELECT table_1.column_1, /* VARCHAR(255) */ > table_1.column_2, /* VARCHAR(255) */ > table_1.column_3, /* INTEGER */ > table_1.node_id, /* VARCHAR(255) */ > table_1.parent_node_id /* VARCHAR(255) */ > FROM table_1 > ), > > /* Recursive CTE */ > recursive_cte(column_1, column_2, column_3, node_id, > parent_node_id) AS ( > SELECT cte.column_1, > cte.column_2, > cte.column_3, > cte.node_id, > cte.parent_node_id > FROM cte > UNION ALL > SELECT table_1.column_1, > table_1.column_2, > table_1.column_3, > table_1.node_id, > table_1.parent_node_id > FROM table_1 > JOIN recursive_cte > ON table_1.node_id = recursive_cte.parent_node_id > ) > > SELECT recursive_cte.column_1, > recursive_cte.column_2, > recursive_cte.column_3 > FROM recursive_cte > > This new query works fine in database 1, but errors out in database > 2. The error I get is Column "RECURSIVE_CTE.COLUMN_3" not found, > which leads me to believe it's coming from the last SELECT query at > the bottom. For some reason, that query can't find column_3 in the > recursive CTE, even though I made sure to include it in the > declaration. > > The weird part is that it works in one database but not the other, > even though they share the same schema. It makes me wonder if I'm > hitting some sort of memory limit in H2 Embedded. The weirdest part > is that its always the INTEGER column that seems to disappear, > considering an INTEGER should take up much less space in memory than > a VARCHAR(255). > > Does anyone have any ideas what might cause this? Any insights are > greatly appreciated. > - Rob > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/47c529f9-b624-437b-b4fd-e284577e1affn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d7b8151636959708ebf919bebf7871c9e58a6497.camel%40manticore-projects.com.
Re: [h2] Database corrupted after SHUTDOWN COMPACT
Agreed, I just provided alternatives and options. Cheers Andreas On Fri, 2022-06-10 at 03:54 -0700, Ulrich wrote: > Do you mean the migration tool as UI? > The database has already been migrated by exporting it to SQL and > importing it with the FROM_1X appendix. So I do not expect any > problems at this step. Importing from SQL should create a valid > database file. > > The current status is that I have one case where the BACKUP TO > statement created database file that corrupts when executing a > SHUTDOWN COMPACT on it. > I could not reproduce it but I try to repeat creating backups with > BACKUP TO and check if the database files are ok. > > and...@manticore-projects.com schrieb am Freitag, 10. Juni 2022 um > 12:26:08 UTC+2: > > Use the UI I sent to you. It takes care of those challenges. > > > > > > > > Sent from my Galaxy > > > > > > Original message > > From: Ulrich > > Date: 10/06/2022 12:21 (GMT+01:00) > > To: H2 Database > > Subject: Re: [h2] Database corrupted after SHUTDOWN COMPACT > > > > Stop, I am silly. It overlooked that the file created by the BACKUP > > command is a zip file. I tried to use it as database file... > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/80aafea58c3eece2fe6930cfc2914846f2f054f9.camel%40manticore-projects.com.
Re: [h2] Database corrupted after SHUTDOWN COMPACT
Use the UI I sent to you. It takes care of those challenges. Sent from my Galaxy Original message From: Ulrich Date: 10/06/2022 12:21 (GMT+01:00) To: H2 Database Subject: Re: [h2] Database corrupted after SHUTDOWN COMPACT Stop, I am silly. It overlooked that the file created by the BACKUP command is a zip file. I tried to use it as database file... -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6cc93937-85e4-490d-aba9-78daf40cfc85n%40googlegroups.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1N18MG-1nb5zk37vO-012XBJ%40mrelayeu.kundenserver.de.
Re: [h2] Database corrupted after SHUTDOWN COMPACT
Very strange, it must be a different problem then. Suggestions: 1) Extract the existing DB to SQL script 2) Create a new DB from that SQL script (using the 219 H2 Driver) 3) Open the new DB, close the new DB (without DEFRAG) 4) Open the new DB, SHUTDOWN DEFRAG it You can use the Migration Tool https://github.com/manticore-projects/H2MigrationTool I am not a H2 developer but do use H2 on many large databases. It should work. Can you share the DB SQL script with me for an independent test? Viel Glueck Andreas On Fri, 2022-06-10 at 01:28 -0700, Ulrich wrote: > I tried with the latest 219. The problem that the database corrupts > when running SHUTDOWN COMPACT still exist. > The compact is finished very fast but a .temp file remains in the > database folder. > > Here is the content of the trace file: > > 2022-06-10 10:24:30 database: close > org.h2.message.DbException: Eingabe/Ausgabe Fehler: "Closing" > IO Exception: "Closing" [90028-219] > at org.h2.message.DbException.get(DbException.java:212) > at org.h2.mvstore.db.Store.close(Store.java:401) > at > org.h2.engine.Database.closeOpenFilesAndUnlock(Database.java:1261) > at org.h2.engine.Database.closeImpl(Database.java:1225) > at org.h2.engine.Database.close(Database.java:1144) > at org.h2.engine.Database.removeSession(Database.java:1072) > at org.h2.engine.SessionLocal.close(SessionLocal.java:899) > at > org.h2.command.dml.TransactionCommand.update(TransactionCommand.java: > 91) > at > org.h2.command.CommandContainer.update(CommandContainer.java:174) > at org.h2.command.Command.executeUpdate(Command.java:252) > at > org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252) > at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) > at org.h2.server.web.WebApp.getResult(WebApp.java:1339) > at org.h2.server.web.WebApp.query(WebApp.java:1137) > at org.h2.server.web.WebApp$1.next(WebApp.java:1103) > at org.h2.server.web.WebApp$1.next(WebApp.java:1) > at org.h2.server.web.WebThread.process(WebThread.java:188) > at org.h2.server.web.WebThread.run(WebThread.java:101) > at java.base/java.lang.Thread.run(Thread.java:834) > Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Eingabe/Ausgabe > Fehler: "Closing" > IO Exception: "Closing" [90028-219] > at > org.h2.message.DbException.getJdbcSQLException(DbException.java:554) > at > org.h2.message.DbException.getJdbcSQLException(DbException.java:477) > ... 19 more > Caused by: org.h2.mvstore.MVStoreException: Double mark: 1b8/1a [1d7- > 1d9, 22a-235, 2ac-2b1, 312-326, 32d-336, 346-352, 419-428, 485-4cb, > 4f0-4f0, 56a-572, 59b-5a2, 621-621, 64f-67b, 682-69d, 6ef-6f4, 71b- > 726, 751-78b, 8a6-8d4, 910-913, 960-975, 99e-9b1, 9ce-a02, a2b-a6d, > a78-aac, ad4-af7, c03-c15, c2d-c49, c5b-c76, ce7-ce8, d50-d62, d7a- > d96, e0b-e15, e80-ec5, f27-f5a, ff1-1013, 10e2-10f5, 12c0-12c9, 1321- > 1331, 139c-1413, 1620-16b1, 17d1-180c, 18b4-18cb, 1952-196f, 19f9- > 1ae3, 1aeb-1b68, 1b6f-1bfb, 1c04-1c7a, 1c82-1e52, 1e5e-1f98, 1f9f- > 2002, 2015-218f, 2197-24a1, 24a9-24e5, 24ec-2534, 253e-25cc, 25db- > 29c5, 29f2-2d8b, 2d93-2f4a, 2f51-3400, 3413-3447, 344f-3579, 35a2- > 3635, 364e-36cc, 36df-3865, 3871-3872, 387d-398f, 399a-3a5c, 3a64- > 3a8c, 3a94-3ac2, 3ad7-3b7c, 3b88-3b9c, 3bc7-3e41, 3e4d-3e92, 3eaf- > 3fd0, 3fd8-4164, 4183-41a2, 41b8-41ee, 41f6-4214, 421c-42f7, 431a- > 4344, 436d-44a1, 44d5-44f2, 44fb-4511, 4520-4520, 452a-455d, 456b- > 4592, 45aa-464b, 4664-4742, 4766-481d, 4829-4840, 4859-48ea, 4912- > 498e, 4995-4a15, 4a1e-4a1e, 4a3d-4b55, 4b5c-4b72, 4b8b-4c0f, 4c24- > 4c7b, 4c85-4d0f, 4d19-4e3f, 4e81-4ea6, 4f13-50e9, 50f2-511c, 5191- > 5363, 5393-53c2, 53ce-550a, 558e-55f7, 5600-5695, 56a1-56b7, 56c1- > 5791, 57c5-580f, 5820-5870, 587f-58ac, 58ce-5919, 592c-5946, 5955- > 5955, 5961-5962, 5993-59d9, 5a0c-5a81, 5a92-5aa1, 5ad7-5bb4, 5bc3- > 5bc3, 5bd2-5c98, 5cb4-5d2f, 5d40-5d41, 5d4f-5de3, 5dee-5ea7, 5eb4- > 5f97, 5fd6-6123, 614e-6150, 618e-618e, 61a7-6286, 62a7-6351, 635e- > 635e, 6390-63e6, 63f7-6419, 6481-6592, 659e-65f3, 6623-6627, 663c- > 6659, 6664-66c5, 66d2-66d4, 6741-675a, 6763-67a5, 680d-696e, 6998- > 69c1, 69d9-69db, 6a2c-6a4d, 6a75-6aa0, 6ada-6af1, 6b04-6b04, 6b1c- > 6b91, 6b9a-6bcc, 6be2-6cf0, 6d07-6d27, 6d31-6d31, 6d80-6dce, 6de3- > 6e35, 6e3e-6e95, 6eaa-6ec4, 6ef9-6f71, 6f9f-7084, 70a9-7154, 7160- > 71cb, 71e4-71fa, 721f-7488, 74e9-74fe, 752c-7542, 75a2-75e4, 75f9- > 7611, 7667-7680, 76a3-770a, 7721-775e, 776a-7783, 77b4-77b4, 77da- > 77f2, 7806-7808, 783c-7852, 7876-78ca, 790c-7969, 79eb-7a35, 7a87- > 7a8c, 7ae1-7b68, 7b8d-7b8f, 7bae-7bc7, 7be3-7c40, 7c5a-7d44, 7d59- > 7d5d, 7e35-7e9b, 7eb5-7ecc, 7f07-7f07, 7f44-7fe7, 7ff6-7ff8, 8012- > 802b, 8
RE: [h2] Database corrupted after SHUTDOWN COMPACT
Greetings. Please try the latest snapshot. It has been fixed in 219.Sent from my Galaxy Original message From: Ulrich Date: 10/06/2022 09:34 (GMT+01:00) To: H2 Database Subject: [h2] Database corrupted after SHUTDOWN COMPACT Hi all,I am migrating my application from H2 1.4.199 to H2 2.1.212. It's working fine but the database grows and the automatic compact only has a minor effect (separate mail will follow about that). So I copied the database using the "BACKUP TO" command to inspect it.I opened the copy with the H2 console and entered a "SHUTDOWN COMPACT".Reopening the database with the H2 console shows the error " File corrupted while reading record: null. Possible solution: use the recovery tool [90030-212] 90030/90030".The trace file shows the problem:2022-06-10 09:23:55 database: closeorg.h2.message.DbException: Eingabe/Ausgabe Fehler: "Closing"IO Exception: "Closing" [90028-212] at org.h2.message.DbException.get(DbException.java:212) at org.h2.mvstore.db.Store.close(Store.java:391) at org.h2.engine.Database.closeOpenFilesAndUnlock(Database.java:1281) at org.h2.engine.Database.closeImpl(Database.java:1234) at org.h2.engine.Database.close(Database.java:1144) at org.h2.engine.Database.removeSession(Database.java:1072) at org.h2.engine.SessionLocal.close(SessionLocal.java:899) at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:91) at org.h2.command.CommandContainer.update(CommandContainer.java:174) at org.h2.command.Command.executeUpdate(Command.java:252) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) at org.h2.server.web.WebApp.getResult(WebApp.java:1339) at org.h2.server.web.WebApp.query(WebApp.java:1137) at org.h2.server.web.WebApp$1.next(WebApp.java:1103) at org.h2.server.web.WebApp$1.next(WebApp.java:1090) at org.h2.server.web.WebThread.process(WebThread.java:189) at org.h2.server.web.WebThread.run(WebThread.java:102) at java.base/java.lang.Thread.run(Thread.java:834)Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Eingabe/Ausgabe Fehler: "Closing"IO Exception: "Closing" [90028-212] at org.h2.message.DbException.getJdbcSQLException(DbException.java:554) at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ... 19 moreCaused by: org.h2.mvstore.MVStoreException: Double mark: 1b8/1a [1d7-1d9, 22a-235, 2ac-2b1, 312-326, 32d-336, 346-352, 419-428, 485-4cb, 4f0-4f0, 56a-572, 59b-5a2, 621-621, 64f-67b, 682-69d, 6ef-6f4, 71b-726, 751-78b, 8a6-8d4, 910-913, 960-975, 99e-9b1, 9ce-a02, a2b-a6d, a78-aac, ad4-af7, c03-c15, c2d-c49, c5b-c76, ce7-ce8, d50-d62, d7a-d96, e0b-e15, e80-ec5, f27-f5a, ff1-1013, 10e2-10f5, 12c0-12c9, 1321-1331, 139c-1413, 1620-16b1, 17d1-180c, 18b4-18cb, 1952-196f, 19f9-1ae3, 1aeb-1b68, 1b6f-1bfb, 1c04-1c7a, 1c82-1e52, 1e5e-1f98, 1f9f-2002, 2015-218f, 2197-24a1, 24a9-24e5, 24ec-2534, 253e-25cc, 25db-29c5, 29f2-2d8b, 2d93-2f4a, 2f51-3400, 3413-3447, 344f-3579, 35a2-3635, 364e-36cc, 36df-3865, 3871-3872, 387d-398f, 399a-3a5c, 3a64-3a8c, 3a94-3ac2, 3ad7-3b7c, 3b88-3b9c, 3bc7-3e41, 3e4d-3e92, 3eaf-3fd0, 3fd8-4164, 4183-41a2, 41b8-41ee, 41f6-4214, 421c-42f7, 431a-4344, 436d-44a1, 44d5-44f2, 44fb-4511, 4520-4520, 452a-455d, 456b-4592, 45aa-464b, 4664-4742, 4766-481d, 4829-4840, 4859-48ea, 4912-498e, 4995-4a15, 4a1e-4a1e, 4a3d-4b55, 4b5c-4b72, 4b8b-4c0f, 4c24-4c7b, 4c85-4d0f, 4d19-4e3f, 4e81-4ea6, 4f13-50e9, 50f2-511c, 5191-5363, 5393-53c2, 53ce-550a, 558e-55f7, 5600-5695, 56a1-56b7, 56c1-5791, 57c5-580f, 5820-5870, 587f-58ac, 58ce-5919, 592c-5946, 5955-5955, 5961-5962, 5993-59d9, 5a0c-5a81, 5a92-5aa1, 5ad7-5bb4, 5bc3-5bc3, 5bd2-5c98, 5cb4-5d2f, 5d40-5d41, 5d4f-5de3, 5dee-5ea7, 5eb4-5f97, 5fd6-6123, 614e-6150, 618e-618e, 61a7-6286, 62a7-6351, 635e-635e, 6390-63e6, 63f7-6419, 6481-6592, 659e-65f3, 6623-6627, 663c-6659, 6664-66c5, 66d2-66d4, 6741-675a, 6763-67a5, 680d-696e, 6998-69c1, 69d9-69db, 6a2c-6a4d, 6a75-6aa0, 6ada-6af1, 6b04-6b04, 6b1c-6b91, 6b9a-6bcc, 6be2-6cf0, 6d07-6d27, 6d31-6d31, 6d80-6dce, 6de3-6e35, 6e3e-6e95, 6eaa-6ec4, 6ef9-6f71, 6f9f-7084, 70a9-7154, 7160-71cb, 71e4-71fa, 721f-7488, 74e9-74fe, 752c-7542, 75a2-75e4, 75f9-7611, 7667-7680, 76a3-770a, 7721-775e, 776a-7783, 77b4-77b4, 77da-77f2, 7806-7808, 783c-7852, 7876-78ca, 790c-7969, 79eb-7a35, 7a87-7a8c, 7ae1-7b68, 7b8d-7b8f, 7bae-7bc7, 7be3-7c40, 7c5a-7d44, 7d59-7d5d, 7e35-7e9b, 7eb5-7ecc, 7f07-7f07, 7f44-7fe7, 7ff6-7ff8, 8012-802b, 8041-805a, 8108-8182, 8194-81ae, 8206-8221, 8240-831e, 8360-837a, 8394-83af, 83dc-83df, 846b-8486, 84d7-84f4, 8516-8536, 8589-85aa, 868d-868f, 86c3-86c3, 8b16-8b16, 8c81-8c84, 8e73-8e75, 8e9d-8e9f, 904b-904b, 9067-906a, 9107-9107, 912a-912a, 9275-9276, 9287-9289, 9309-930a, 939a-939d, 93af-93b1, 9485-9488, 94c0-94c0, 94f8-94f8, 957a-957b, 95ea-95ef, 96ff-96ff, 971b-971c, 975c-9762, 9805-9807, 9a11-9a11, 9a71-9a72,
Re: [h2] How to create table programming in version 2 ?
Greetings. As Noel pointed out, for the H2 project this might be internal API subject to change without notification. However, https://github.com/JSQLParser/JSqlParser and https://www.jooq.org/ provide such an API, which will De-Parse into a SQL, which can be executed in H2 (or any other RDBMS). This way you would be independent from the particular SQL Flavour and/or RDBMS. Cheers and good luck. Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1f5e2bc7acc67be0cae418ebec0d613ec30dce00.camel%40manticore-projects.com.
Re: [h2] How to run a sql script with duplicate insert queries on h2db
On Wed, 2022-05-25 at 22:25 -0700, 'Balamurali Krishna Ippili' via H2 Database wrote: > Hi Team, > > We have a requirement where we are executing an sql script on h2 db > and there is a primary key constraint on the table and if there is > any single failure in the script is stop executing the rest of the > script. How can we allow the java program to ignore the failed query > and continue with rest of the script execution? Greetings, while I am not a H2 developer, but just a user I would like to advise the following approach: 1) Do not rely on ignoring duplicates, but filter for distinct by using GROUP BY and Min(_rowid_) or Max(_rowid) 2) Example: DELETE FROM cfe.instrument_attribute WHERE ( id_instrument, id_attribute, _rowid_ ) IN ( SELECT id_instrument , id_attribute , Min( _rowid_ ) FROM cfe.instrument_attribute GROUP BYid_instrument , id_attribute HAVING Count( * ) > 1 ) ; The example above would ensure a UNBIQUE KEY (id_instrument, id_attribute) in table cfe.instrument_attribute (although you would need to repeat that delete until no row is returned). Good luck Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/23f46631af09f2415f879d6aab19b5a639107ef7.camel%40manticore-projects.com.
Re: [h2] H2 Running problem
On Thu, 2022-04-28 at 09:49 -0700, Kristóf Sándor Vad wrote: > The problem is that after installing neither the console or the > command line doesn't seem to work. Greetings, that's a bit generic and you will need to provide many more details please: 1) How to you start the H2 server (e.g. java -jar libs/h2.jar from the console) 2) What is the output on the console after starting it 3) How do you connect to the H2 server (e.g. URL connection settings) 4) What network ports are in use and does your firewall allow for them Good luck and cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/32a177a26fa2fda2490d170220f873ae2f60e387.camel%40manticore-projects.com.
Re: [h2] UNLINK_SCHEMA as opposition of LINK_SCHEMA, LINK_SCHEMA RFE and questions
Greetings. On Mon, 2022-04-25 at 23:19 -0700, t603 wrote: > Hello, may I ask You few questions about LINK_SCHEMA function? > > 1) Is LINK_SCHEMA on 10+ or 100+ tables and views far more faster > that 10+ or 100+ times CREATE LINKED TABLE? In other words is there > time improvement due connection establishment? Or does LINK_SCHEMA > internally call of 10+ or 100+ CREATE LINKED TABLE and there is no > performance improvement? I think, this will perform very similar, but may depend on the exact circumstances. As far as I understand it, the "LINKED" command only provide a framework for querying via a different JDBC connection and fetching records. Its an ETL alike mechanism. So LINKED SCHEMA would only provide the JDBC connection URL for accessing the schema's table -- same way as LINKED TABLE would do,. > > 2) When I do not need linked schema anymore, is there something like > UNLINK_SCHEMA? Would not that be a simple DROP SCHEMA? Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/304e641f868cb944cd660f6cb09fdd8dca9cd7b6.camel%40manticore-projects.com.
RE: [h2] Is it possible to flatten arrays when doing nested array aggregation?
https://www.h2database.com/html/functions.html#unnestThis one.Sent from my Galaxy Original message From: Adam R Date: 26/04/2022 05:59 (GMT+01:00) To: H2 Database Subject: [h2] Is it possible to flatten arrays when doing nested array aggregation? Here's a toy example:CREATE TABLE MY_TABLE (ID INT, NAME VARCHAR);INSERT INTO MY_TABLE VALUES(1, 'a');INSERT INTO MY_TABLE VALUES(2, 'a');INSERT INTO MY_TABLE VALUES(3, 'b');INSERT INTO MY_TABLE VALUES(4, 'b');SELECT ARRAY_AGG(IDS) AS IDS FROM (SELECT ARRAY_AGG(ID) AS IDS FROM MY_TABLE GROUP BY NAME)This will return [[1, 2], [3, 4]]. What I'd like is a way to flatten the inner arrays so that I end up with a single array, like [1, 2, 3, 4]. Is this possible in H2? (In a way that would would work for an arbitrary number of array elements).Thank you. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1MdwRi-1oGdF51uRa-00b4U4%40mrelayeu.kundenserver.de.
RE: [h2] Is it possible to flatten arrays when doing nested array aggregation?
Greetings. Why would you not aggregate on the whole table without the subquery?Anyway. If the subquery must be involved then you needed to unroll its arrays first before aggregating it again.Look for the unnest syntax in h2. I know its supported.Best regards AndreasSent from my Galaxy Original message From: Adam R Date: 26/04/2022 05:59 (GMT+01:00) To: H2 Database Subject: [h2] Is it possible to flatten arrays when doing nested array aggregation? Here's a toy example:CREATE TABLE MY_TABLE (ID INT, NAME VARCHAR);INSERT INTO MY_TABLE VALUES(1, 'a');INSERT INTO MY_TABLE VALUES(2, 'a');INSERT INTO MY_TABLE VALUES(3, 'b');INSERT INTO MY_TABLE VALUES(4, 'b');SELECT ARRAY_AGG(IDS) AS IDS FROM (SELECT ARRAY_AGG(ID) AS IDS FROM MY_TABLE GROUP BY NAME)This will return [[1, 2], [3, 4]]. What I'd like is a way to flatten the inner arrays so that I end up with a single array, like [1, 2, 3, 4]. Is this possible in H2? (In a way that would would work for an arbitrary number of array elements).Thank you. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1N17pC-1nteEr2ggc-012Xti%40mrelayeu.kundenserver.de.
[h2] Big Thank You!
Shout-out to the team and big thank you for the corrections and the frequent releases! Cheers Andreas On Sat, 2022-04-09 at 09:15 -0400, Andrei Tokar wrote: > Hello, > > A new version 2.1.212 of H2 is available at http://www.h2database.com > (you may have to click 'Refresh'). > > It will be available in Maven repository shortly. > > For details, see the 'Change Log' at > http://www.h2database.com/html/changelog.html > > P.S. If you reply to this message please use a different subject. > > Have fun, > Andrei Tokar > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8c818754883240f07a525f724bdba65b6c875c78.camel%40manticore-projects.com.
Re: [h2] insert with on conflict do nothing not working
Tom,
from the docs:
PostgreSQL Compatibility ModeTo use the PostgreSQL mode, use the database
URLjdbc:h2:~/test;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_O
RDERING=HIGH. Do not change value of DATABASE_TO_LOWER after creation
of database.
* For aliased columns, ResultSetMetaData.getColumnName()
returns the alias name and getTableName() returns
null.
* When converting a floating point number to an integer, the
fractional
digits are not be truncated, but the value is rounded.
* The system columns ctid and
oid are supported.
* LOG(x) is base 10 in this mode.
* REGEXP_REPLACE():
uses \ for back-references;does not throw an exception when the
flagsString parameter contains a 'g';replaces only the first matched
substring in the absence of the 'g' flag in the flagsString
parameter.
* LIMIT / OFFSET clauses are supported.
* Legacy SERIAL and BIGSERIAL data types are supported.
* ON CONFLICT DO NOTHING is supported in INSERT statements.
* Spaces are trimmed from the right side of CHAR values, but CHAR
values in result sets are right-padded with
spaces to the declared length.
* MONEY data type is treated like NUMERIC(19, 2) data type.
* Datetime value functions return the same value within a transaction.
* ARRAY_SLICE() out of bounds parameters are silently corrected.
* EXTRACT function with DOW field returns (0-6), Sunday is 0.
* UPDATE with FROM is supported.
* GROUP BY clause can contain 1-based positions of expressions from
the SELECT list.
It should work. What is your connection setting please?
Best regards
Andreas
On Tue, 2022-04-05 at 16:28 -0700, Tom wrote:
> hi All,
>
> Is there anything special that I need to do to get ON CONFLICT DO
> NOTHING working with Postgresql? I'm using H2 2.1.210 (and I know it
> has been added since H2 1.4.200 in 2019) with PostgreSQL10Dialect
> (H2Dialect doesn't work either) but what I get is
>
> on statement:
>
> insert into transaction_type (transaction_type, description) values
> ('sometx', 'xx') ON CONFLICT DO NOTHING;
>
> I get:
>
> org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL
> statement "insert into transaction_type (transaction_type,
> description) values ('sometx', 'xx') [*]ON CONFLICT DO NOTHING";
> SQL statement:
> insert into transaction_type (transaction_type, description) values
> ('sometx', 'xx') ON CONFLICT DO NOTHING [42000-210]
>
> help?
>
> Tom
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/03b164cf-e394-4109-9bf5-ebb7d7dcb6edn%40googlegroups.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/dbb2bdf904e60e57bd2330d957b6ee29feb955fe.camel%40manticore-projects.com.
[h2] H2 Database Migration Tool 1.2 released
Greetings. Release 1.2 fixes the Connection Strings on Windows and provides an Ueber-Jar. https://github.com/manticore-projects/H2MigrationTool Any issues, please do let me know. Cheers -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/24fc2ed1a380755fa97bb5642beef58ee6c9faf3.camel%40manticore-projects.com.
Re: [h2] Query in latest version is returning empty results
On Fri, 2022-02-11 at 11:57 -0800, Ajay Bhide wrote: > Hi , > > The exact same query which returns the expected result-set in the > version 1.4.187, is returning empty result set with both the versions > 2.0.206 and 2.1.210. Can someone be able to help on this? Based on your example, you may better ask the Perl obfuscation contest for help. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/daa58fd064780d91ad1dcdece15d37b23bf918ef.camel%40manticore-projects.com.
[h2] Library for Support of Named Parameter Statements
Greetings. Apologies, I posted it into the wrong thread originall.
To my best knowledge, H2 database does not support Named Parameters.
Furthermore, Oracle does not support Parameters in DDL Statements (not
even in CTAS).
I have started writing a small
library https://github.com/manticore-projects/MJdbcUtils in order to
handle Named Parameter Statements nicely. Summary and examples are
shown below.
It is in its very early stages and I will appreciate feedback and tests
and are more than willing to incorporate interesting use cases.
I do use H2 as basis for the development, although I want to be RDBMS
agnostic as possible.
Warm regards
Andreas
--
MJdbcUtils
Library supporting Named Parameters (e.g. :Customer_Id ) in Queries or
DML/DDL statements.
Use case
When the RDBMS does not support Named Parameters directly, it will:
1) find any Named Parameter,
2) replace it with an ordinary Positional Parameter ?,
3) maintain a map between the Position and the Named Parameter
4) provide methods for applying the Parameter Values and retrieving
Parameter Type information.
It supports PreparedStatements with parameters and also
rewriting/injecting SQL Statements for execution without parameters.
Rewriting/injecting is useful for Oracle Databases, which do not allow
parameters for DDL Statements (not even for the query block of CTAS).
Furthermore, MJdbcUtils makes it easy to build an UI Parameter Dialog
based on the used Parameters and the Type Information.
Examples
Based on a Table Definition
CREATE TABLE test (
a DECIMAL(3) PRIMARY KEY
, b VARCHAR(128) NOT NULL
, c DATE NOT NULL
, d TIMESTAMP NOT NULL
, e DECIMAL(23,5) NOT NULL
)
1) We can fill the table with a simple update
// DML statement with Named Parameters
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
// Helper function will fill our parameter map with values
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
// Create a Prepared Statement, which holds our paramater mapping
MPreparedStatement st = new MPreparedStatement(conn, dmlStr);
// Execute our statement with the provided parameter values
Assertions.assertFalse( st.execute(parameters) );
2) We can fill table using Batch Updates
int maxRecords = 100;
int batchSize = 4;
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
MPreparedStatement st = new MPreparedStatement(conn, dmlStr, batchSize);
for (int i=0; i < maxRecords; i++) {
parameters.put("a", i);
parameters.put("b", "Test String " + i);
// submit a new set of parameter values and execute automatically after 4
records
int[] results = st.addAndExecuteBatch(parameters);
}
// submit any outstanding records
st.executeBatch();
3) We can query our table
String qryStr = "SELECT Count(*) FROM test WHERE a = :a or b = :b";
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
ResultSet rs = st.executeQuery(parameters);
4) We can rewrite our statement and inject the parameter values
directly (useful for Oracle DDLs)
Date dateParameterValue = new Date();
HashMap parameters = new HashMap<>();
parameters.put("param1", "Test String");
parameters.put("param2", 2);
parameters.put("param3", dateParameterValue);
String sqlStr = "select :param1, :param2, :param3;";
String rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr,
parameters);
Assertions.assertEquals("SELECT 'Test String', 2, " +
getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);
sqlStr = "UPDATE tableName SET a = :param1, b = :param2, c = :param3;";
rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr,
parameters);
Assertions.assertEquals("UPDATE tableName SET a = 'Test String', b = 2, c = " +
getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);
5) We can retrieve the information about the used parameters for
building a UI Dialog
String qryStr = "SELECT * FROM test WHERE d = :d and c = :c and b = :b and a =
:a and e = :e";
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
List parameters = st.getNamedParametersByAppearance();
Output of the List:
INFO: Found Named Parameters:
D java.sql.Timestamp
C java.sql.Date
B java.lang.String
A java.math.BigDecimal
E java.math.BigDecimal
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/6765ca9511cd40edff971c9858b0e96081bbde9d.camel%40manticore-projects.com.
[h2] https://github.com/manticore-projects/MJdbcUtils for support of Named Parameter Statements
Greetings.
To my best knowledge, H2 database does not support Named Parameters.
Furthermore, Oracle does not support Parameters in DDL Statements (not
even in CTAS).
I have started writing a small
library https://github.com/manticore-projects/MJdbcUtils in order to
handle Named Parameter Statements nicely. Summary and examples are
shown below.
It is in its very early stages and I will appreciate feedback and tests
and are more than willing to incorporate interesting use cases.
I do use H2 as basis for the development, although I want to be RDBMS
agnostic as possible.
Warm regards
Andreas
--
MJdbcUtils
Library supporting Named Parameters (e.g. :Customer_Id ) in Queries or
DML/DDL statements.
Use case
When the RDBMS does not support Named Parameters directly, it will:
1) find any Named Parameter,
2) replace it with an ordinary Positional Parameter ?,
3) maintain a map between the Position and the Named Parameter
4) provide methods for applying the Parameter Values and retrieving
Parameter Type information.
It supports PreparedStatements with parameters and also
rewriting/injecting SQL Statements for execution without parameters.
Rewriting/injecting is useful for Oracle Databases, which do not allow
parameters for DDL Statements (not even for the query block of CTAS).
Furthermore, MJdbcUtils makes it easy to build an UI Parameter Dialog
based on the used Parameters and the Type Information.
Examples
Based on a Table Definition
CREATE TABLE test (
a DECIMAL(3) PRIMARY KEY
, b VARCHAR(128) NOT NULL
, c DATE NOT NULL
, d TIMESTAMP NOT NULL
, e DECIMAL(23,5) NOT NULL
)
1) We can fill the table with a simple update
// DML statement with Named Parameters
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
// Helper function will fill our parameter map with values
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
// Create a Prepared Statement, which holds our paramater mapping
MPreparedStatement st = new MPreparedStatement(conn, dmlStr);
// Execute our statement with the provided parameter values
Assertions.assertFalse( st.execute(parameters) );
2) We can fill table using Batch Updates
int maxRecords = 100;
int batchSize = 4;
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
MPreparedStatement st = new MPreparedStatement(conn, dmlStr, batchSize);
for (int i=0; i < maxRecords; i++) {
parameters.put("a", i);
parameters.put("b", "Test String " + i);
// submit a new set of parameter values and execute automatically after 4
records
int[] results = st.addAndExecuteBatch(parameters);
}
// submit any outstanding records
st.executeBatch();
3) We can query our table
String qryStr = "SELECT Count(*) FROM test WHERE a = :a or b = :b";
Map parameters = toMap("a", 1, "b", "Test String", "c", new
Date(), "d", new Date(), "e", "0.12345");
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
ResultSet rs = st.executeQuery(parameters);
4) We can rewrite our statement and inject the parameter values
directly (useful for Oracle DDLs)
Date dateParameterValue = new Date();
HashMap parameters = new HashMap<>();
parameters.put("param1", "Test String");
parameters.put("param2", 2);
parameters.put("param3", dateParameterValue);
String sqlStr = "select :param1, :param2, :param3;";
String rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr,
parameters);
Assertions.assertEquals("SELECT 'Test String', 2, " +
getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);
sqlStr = "UPDATE tableName SET a = :param1, b = :param2, c = :param3;";
rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr,
parameters);
Assertions.assertEquals("UPDATE tableName SET a = 'Test String', b = 2, c = " +
getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);
5) We can retrieve the information about the used parameters for
building a UI Dialog
String qryStr = "SELECT * FROM test WHERE d = :d and c = :c and b = :b and a =
:a and e = :e";
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
List parameters = st.getNamedParametersByAppearance();
Output of the List:
INFO: Found Named Parameters:
D java.sql.Timestamp
C java.sql.Date
B java.lang.String
A java.math.BigDecimal
E java.math.BigDecimal
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/863843e79b79d2290503fafa94b49ef58125f25f.camel%40manticore-projects.com.
[h2] H2 Performance Windows vs Linux
Greetings, incidentally I stumbled over a performance comparison related to H2 on Windows vs Linux: https://www.phoronix.com/scan.php?page=article=ryzen7pro-windows-linux=3 The difference looks relevant to me and I am curios to ask: Any idea why there is such a difference? I would actually have expected a very similar performance -- given that it runs within the JVM. Any thoughts on this? Warm regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/18e458764a6d810175c894090e53b181541cfebd.camel%40manticore-projects.com.
Re: [h2] DB regression when exiting with SHUTDOWN COMPACT?
Greetings! Unfortunately, this seems to be a kind of normal: As long as the DB is running, it will keep "leaking" file space . Our virgin 2 GByte DB grew to 400 GB eventually, after running for months. "Shutdown defrag" frees a lot of the claimed file space, but not all and it depends on a restart. Best solution I found so far is to Extract the DB to script and rebuild the DB from script. This brings the DB reliable down to the actual size and also provides nice backup snapshots in case of corruption. (SHUTDOWN DEFRAG corrupted our databases last year, but seems to have gotten much more stable recently -- just a "gut feeling" though.) Best regards Andreas On Thu, 2021-12-09 at 04:20 -0800, torlpedo wrote: > Hi all, > > Creating a medium sized database (300MB with 800MB disk usage) and > then exiting with SHUTDOWN COMPACT makes the database read-only, > making impossible to modify the data via DML commands. > > I tried opening the DB in read-write mode in the connection string > using ACCESS_MODE_DATA without success. > > Has everybody else encountered the same issue? > > BR, > Ivan > > > > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/89d53406-be8e-4a15-9f9e-bf4f24e67092n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/64f82decc38efbee02f754694c02506f2e899581.camel%40manticore-projects.com.
Re: [h2] Release date of next version of H2? Maybe version 2.x?
Greetings. The situation seems to be difficult: H2 is written by volunteers in their precious spare time and at the same time is a software which depends on a lot of knowledge and expertise. Right now, the main developers do not seem to be able to invest much time. Even sponsoring offers did not catch -- end we can only respect that (and so I do!). At the same time, 1.4.200 is quite old now and a lot of corrections and improvements have been provided since. There are a lot of reports about corruption with 1.4.200 (and I have observed those myself) and based on the age, nobody will want to dig into that anymore because the current development tree has sorted very most of such issues and seems to run very stable (at least to me). So, with all respect to the developers, from a practical perspective of an end-user , oversimplified it looks like that: A release does not seem to happen soon and you have the choice between an unsupported "stable" 1.4.200 release, which has a lot of short comings (compared to the development version) and known corruption problems. Or you could use the latest GIT Snapshot, which has countless improvements, works much more stable (at least for me) and you will get help/answers when you experience any problem. The caveat is: you will constantly migrate the H2 Databases whenever using a newer H2 Snapshot. Cheers On Thu, 2021-09-30 at 06:57 -0700, t603 wrote: > Hello, > > may I ask You, if there are any plans to release next version of H2 > anytime soon? There should be a couple of interesting new features > and bug fixes, so I am eager to know, if we can expect release at > least this year. The last version is almost two years old, if I am > right. > > Thank You, Stepan > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/63b2bf71-c4f3-4d10-83b7-a9314bcfc7e4n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/fed3c4842738fd404e26cbce4b786e8018016841.camel%40manticore-projects.com.
Re: [h2] H2 Database not support materialized?
Greetings: I do not think H2 supports MATERIALIZED within WITH CTEs: https://www.h2database.com/html/commands.html#with Best regards Andreas On Fri, 2021-06-04 at 00:36 -0700, JUN LUO wrote: > When my SQL contains materialized, some exception appeared > > java.util.concurrent.ExecutionException: > org.springframework.jdbc.BadSqlGrammarException: > ### Error querying database. Cause: > org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL > statement "with temp_table as materialized[*] > > It successed when I remove the key word "materialized" > > JDBC_URL = jdbc:h2:mem:test;DB_CLOSE_DELAY=- > 1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE > > How to fix 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 view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/b4e48d4e-3df1-41a7-90c7-8d899f5124f5n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/375142efa299c91c1910b56552cc5ce6ed2ef12f.camel%40manticore-projects.com.
Re: [h2] Re: Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o
Greetings. On Sat, 2021-05-22 at 21:26 -0700, Evgenij Ryazanov wrote: > It may be surprising, but validity of some queries depends on the > data. > H2 and some other DBMS support optional feature T301, “Functional > dependencies” from the SQL Standard. Indeed! > SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS > without the mentioned feature @OP: You can rewrite your query like below in order to make it work in general (without depending on T301). You can also use that form to identify any duplicates easily (using a HAVING COUNT(*)>1), which would break you original SQL Statement. Good luck. WITH t AS ( SELECT DISTINCT iwbatches.id , iwbatches.name , iwbatches.company_id , iwbatches.opener_id o_id , iwbatches.opened_when , Coalesce( Concat( openers.firstname, ' ', openers.lastname ), '' ) o_by , Coalesce( To_Char( iwbatches.opened_when, '-MM-DD hh24:mi' ), '' ) o_when , companies.name company_name , companies.code company_code , companies.streetaddress company_streetaddress , companies.settlement company_settlement , companies.regcode company_regcode , companies.vatcode company_vatcode , companies.contact company_contact , companies.email company_email , companies.phone company_phone , iwbatchrows.id rows_id FROM iwbatches LEFT JOIN iwbatchrows ON iwbatches.id = iwbatchrows.iwbatch_id AND iwbatchrows.needed IS NOT NULL LEFT JOIN persons openers ON iwbatches.opener_id = openers.id LEFT JOIN companies ON iwbatches.company_id = companies.id WHERE iwbatches.finished_when IS NULL AND ( iwbatches.opened_when >= Dateadd( 'DAY', (SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.oldest.days' ), CURRENT_TIMESTAMP ) ) AND ( iwbatches.opened_when <= Dateadd( 'DAY', (SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.youngest.days' ), CURRENT_TIMESTAMP ) ) ) , aggregate AS ( SELECT id , Count( rows_id ) rows FROM t GROUP BY id ) SELECT aggregate.id , aggregate.rows , t.id , t.name , t.company_id , t.o_id , t.opened_when , t.o_by , t.o_when , t.company_name , t.company_code , t.company_streetaddress , t.company_settlement , t.company_regcode , t.company_vatcode , t.company_contact , t.company_email , t.company_phone FROM aggregate LEFT JOIN t ON aggregate.id = t.id ORDER BYaggregate , id ; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e4200bf8dc55e63680b1c02f181e938dc5a8542a.camel%40manticore-projects.com.
Re: [h2] using external(cloud) key-value db as backing store
Hi Alex, thank you for the explanation. On Sat, 2021-04-24 at 11:59 -0700, Alex Ramos wrote: > - But I don't like database servers. > - So my idea is to move to embedded H2 with cloud backing store and > get rid of the database servers, while keeping SQL, JDBC, > JdbcTemplate, and Hibernate. This is were you lose me (pardon me, I am a Unix dinosaur): Would you not still need a single H2 "server" or service instance somewhere to dispatch messages between the client and the "cloud-storage"? Beacause you stated: > This configuration would still retain an undesirable (to me) > characteristic of a traditional RDBMS, mainly, that you have one node > (running H2) that acts as a bottleneck for all data access. > Here I have an unstated requirement: I want the ability for some > consumers to "backdoor" and read the data in the cloud backing store > directly through native APIs without going through H2. Pardon my ignorance, but to me this looks like contradicting requirements. Either you will use "Embedded" mode not sharing, then you can use any file (wether local or mounted on a network does not matter). Or you will use "Shared" mode and will rely on a kind of Service Dispatcher, which is usually the H2 SQL Server over a TCP connection. Anyway, maybe I am just not understanding it well and it is certainly not on me to evangelize you. Good luck and cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/72b9bf99fa2264d862343710e8e826b2ea405a78.camel%40manticore-projects.com.
Re: [h2] using external(cloud) key-value db as backing store
Greetings. On Fri, 2021-04-23 at 21:43 -0700, Alex Ramos wrote: > The end-goal is the ability to instantiate an "embedded" h2 that uses > "dumb" (NoSQL) cloud storage to persist the data and index B-Trees, > while retaining the full RDBMS SQL capability of the upper layers. Pardon my dumb question, I just want to learn something: Why would you want to do that? Why not: a) mount the cloud storage per SSHFS and access the normal DB file b) or start the H2 server in the cloud and access it via TCP What are the Pros of your ideas and what are the Cons of the "traditional" approach (despite having no buzzwords in the description). Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d01fe173f4f9ef8bdaa14e0fd8536861a9bd0a32.camel%40manticore-projects.com.
Re: [h2] Re: Best Practice using H2
Mike.For our own software we test if the h2 port is open. If not we start the h2 server per java api and stop it when the software exits.When the port is found open we assume the h2 servers has been started externally and act as a client strictly.This may not been best practise but works like a charm and reliable for us.Best regards Sent from my Galaxy Original message From: Mike Bray Date: 23/03/2021 18:47 (GMT+07:00) To: h2-database@googlegroups.com Subject: Re: [h2] Re: Best Practice using H2 Thanks, I will do it as a file, the console is really only for debugging purposes. The user will not know about h2 and so shouldn’t need to access the db.RegardsMikeOn Tue, 23 Mar 2021 at 10:17, Evgenij Ryazanov wrote:If you need the H2 Console only, you can embed it as a servlet into your application:https://h2database.com/html/tutorial.html#usingH2ConsoleServletDon't forget to configure the security constraints to prevent access to it from untrusted and unauthorized users.If you need to accept connections from other applications, you need to use the separate H2 server process, possibly running as a system service. Your web application and other applications need to use remote connections to it in that case. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ff81ab83-dbe9-4c99-a977-b7f1518a7b3dn%40googlegroups.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAM-i_afw0XMMR1LczT4Aq%2Byo9rZsvWarJVyMuHcyNtsvHqVFNw%40mail.gmail.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1MuVOM-1lgcuW1w3e-00rUGI%40mrelayeu.kundenserver.de.
Re: [h2] Don't get AVG(DareDiff) field in java and H2
Vitali, while I am not a H2 developer, it appears from your e-mail that your challenge is related to your persistence framework but not to the H2 database itself. You wrote that you can run your query against the JDBC data source and would get the correct result for AVG(). If the persistence frame work returns AVG() == NULL then I would double check first for the actual selected rows before aggregation. Aggregate functions will return NULL when no rows have been selected. Also it might worth replacing H2 with another RDBMS (Derby, Postgres) temporarily just for the sake of narrowing down the issue. Right now I can only advise: 1) send your questions to the persistence framework as long as your query works correctly (or unless you come up with a DDL and a query which fails directly on H2) 2) send (short, simplified, reproducible) code examples instead of screen shots, because nobody will be able to walk through the code using screenshots only Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3831e0978e92cd00cc7df32151e58efec25253fc.camel%40manticore-projects.com.
Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]
Good Morning. On Tue, 2021-02-23 at 05:33 -0800, Wojciech Marciniak wrote: > Don't hesitate to use H2 not only for < 100k records. I wrote "accounts" (not records) , and each account with average 1'000 balance records and maybe 10'000 postings will give quite some data volume. I have experimented with H2 databases up to 20 GByte large and they perform surprisingly well as long as you avoid large DML with indexes or constraints. There is an open ticket on "INSERT INTO ... SELECT .. FROM ...". Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c21193be4f101038e58e21cd2335312089453614.camel%40manticore-projects.com.
Re: [h2] How can I repair database with error "#Row {1} not found in primary index "PUBLIC.SYS_DATA: 1570" [90143-196]"?
On Tue, 2021-02-23 at 10:09 -0800, Thomas Frick wrote: > Can anyone here please give me a hint on how to repair the DB and > rescue all the data in it (if it's possible)? Good Morning Thomas. http://www.h2database.com/javadoc/org/h2/tools/Recover.html (Although this has never worked for me.) Please note: You should avoid opening a H2 database (e.g. 1.4.199) with any different H2 driver (e.g. 1.4.200). Instead you are supposed to extract to SQL script and create the DB afresh from script. http://www.h2database.com/javadoc/org/h2/tools/Script.html http://www.h2database.com/javadoc/org/h2/tools/RunScript.html Good luck and best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0a640cc6b199a40dabc5431027b2501bca868da1.camel%40manticore-projects.com.
Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]
"Oracle compatibility" in H2. While our product is RDBMS agnostic and works with all major DBs, each product will need its own handling or SQL statement where not compliant with the standard. In case of H2 compared to Oracle, we do not need any particular adjustments any more (since 2.0.201), while MS SQL Server has basically its own DDL/query definition file. Running the same software and SQL on Oracle (on large servers) and H2 (on a 2 core 4GB virtual machine) is unparalleled and priceless. All the best Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6d1b924151a6f80962900eea85f184fba335a1cc.camel%40manticore-projects.com.
Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]
Silvio, we are in a very similar situation. Banking software, financial accounting, not really the place where you want to experiment a lot. Our large customers all run Oracle (which deserves its own place in hell) but for smaller banks less than 100k accounts H2 can provide a nice alternative. I am actually happy to hear from you because sometimes it looks like not many other people/companies run H2 in a similar scenario. We also have to put a lot of effort in versioning and upgrading our own VBox software accross various customers and so are aware of the burden, which any kind of versioning, backward compatibility and migration actually is. So with all understanding for the developpers and the scarce resources, from an end-user's point of view the current versioning schema is not optimal and we were close to abandon H2 once simply because the maintenance and upgrading procedure became too cumbersome. Eventually we have decided for ourselves, that there is no actual reliable version schema: We have experienced the current 2.0.201+ development branch as much more robust and stable and correct than 1.4.200 and also there were many "breaking changes" in between (e. g. new reserved keywords, NEXTVAL vs. NEXT VALUE FOR etc.) that for us it works better to be as close as possible to the upstream development. We see H2 now as an very agile, rolling realise software. I believe, the H2 developpers doe a fantastic job regarding the software itself but seem to lack experience or interest in practically running/using H2 in a corporate environment as a replacement for Postgres or Oracle. Maybe this is just out of scope. My advise: If you stick with 1.4.200 longer, you will likely face a big migration effort in your own software/Schema Definition when switching to 2.0.201 eventually. Consider using 2.0.201 and establish a weekly procedure of exporting to SQL script and re-importing into the next version, followed by a large batch processing test including reports. This works for us at least and has reduce the headache. Best regards Andreas On Mon, 2021-02-22 at 16:24 -0800, Silvio wrote: > We use H2 in production heavily and would love to upgrade but as long > as there is no actual version released we can only use 2.0.x for > testing purposes. If only we could get some ball park estimate about > when 2.0.201 will be released with some highlights of improvements we > can expect and preferably some information about compatibility (or > lack thereof) with 1.4.200 that would be great. We are planning the > roll-out of a major new version of our software which will involve > major customer data migrations. If a production release of 2.0.201 is > imminent we could adjust our release schedule to include the upgrade. > If it will take another 6-12 months we cannot. But as it is we can > only guess which, to be honest, really sucks. > > On Monday, 22 February 2021 at 06:37:13 UTC+1 and...@manticore- > projects.com wrote: > > Good Morning. > > > > On Sun, 2021-02-21 at 21:32 -0800, ciphe...@gmail.com wrote: > > > rying a development version of H2 from Github? > > > > > > > > While I am not a H2 Developper and can only speak for my own > > experience: > > > > Running several large H2 databases we have had similar corruption > > issues with 1.4.200+ and never again since we switched to 2.0.201+ > > (until Feb/March 2020). > > In my opinion, the current 2.0.201+ snapshot is the most robust and > > correct H2 database and I prefer this "unstable" develepmoent > > snapshot over the actually released versions. > > > > Best regards > > Andreas > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/00a5d74e-6a53-4ebd-a9a5-0b9af8fecfc3n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b2f7cd83d284abaffc87378afbc02666111dfd41.camel%40manticore-projects.com.
Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]
Good Morning. On Sun, 2021-02-21 at 21:32 -0800, ciphe...@gmail.com wrote: > rying a development version of H2 from Github? While I am not a H2 Developper and can only speak for my own experience: Running several large H2 databases we have had similar corruption issues with 1.4.200+ and never again since we switched to 2.0.201+ (until Feb/March 2020). In my opinion, the current 2.0.201+ snapshot is the most robust and correct H2 database and I prefer this "unstable" develepmoent snapshot over the actually released versions. Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e7b48915f98ac997c21bda91dbf7e8ec11348401.camel%40manticore-projects.com.
Re: [h2] For everyone who use snapshot builds of upcoming H2 2.0
Dear All,
we have implemented support for H2 Driver Version Snapshots, based on
GIT IDs.
This way, one can easily migrate many H2 databases from one H2 snapshot
to another one.
You can now also add H2 Drivers to the program (e. g. for adding more
snapshots or adding older H2 versions).
There are two caveats though:
1) the GIT ID is parsed from the H2 driver file name only. You will
need to create correct filenames when compiling the H2 snapshots, e. g.
using this patch attached to this e-mail.
2) the GIT ID itself does not say anything about a particular order.
Instead, we maintain the order in a Textfile as part of the Migration
Tool.
I am looking for a better and more robust solution to this and any idea
was very welcome.
@H2 Developers: Some help or feedback on this snapshot version thing
was much appreciated. I understand that you can't maintain backward
compatibility during the developing cycle and why export to SQL script
is needed.
But at the same time, the lack of a reliable versioning makes it
difficult to maintain many databases. Please consider adding the GIT ID
to the H2 driver filename and also to put it into the Meta Data
somewhere. So we have a chance to know which driver version to use.
Testing many H2 databases against the recent development snapshots has
merrits. We found a few issues this way, which static test cases have
not found before.
Thank you and best regards!
https://github.com/manticore-projects/H2MigrationTool
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/c0335526a4430ce8057401cc399884f5d11a37bd.camel%40manticore-projects.com.
diff --git a/h2/pom.xml b/h2/pom.xml
index 027897f..ca10b31 100644
--- a/h2/pom.xml
+++ b/h2/pom.xml
@@ -1,10 +1,10 @@
http://maven.apache.org/POM/4.0.0; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd;>
+ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd;>
4.0.0
com.h2database
h2
- 2.0.201-SNAPSHOT
+ 2.0.201
jar
H2 Database Engine
https://h2database.com
@@ -69,9 +69,9 @@
${lucene.version}
-org.apache.lucene
-lucene-queryparser
-${lucene.version}
+ org.apache.lucene
+ lucene-queryparser
+ ${lucene.version}
org.slf4j
@@ -125,7 +125,7 @@
+ where to possibly find tools.jar and annoyingly its called classes.jar on OSX -->
jigsaw-jdk
@@ -172,6 +172,7 @@
src/main
+${project.artifactId}-${project.version}-${git.commit.id.describe-short}
src/test
@@ -200,19 +201,19 @@
-
-src/test
-
-org/h2/test/bench/test.properties
-org/h2/test/script/testScrip.sql
-org/h2/test/scripts/**/*.sql
-org/h2/samples/newsfeed.sql
-org/h2/samples/optimizations.sql
-
-
+
+src/test
+
+ org/h2/test/bench/test.properties
+ org/h2/test/script/testScrip.sql
+ org/h2/test/scripts/**/*.sql
+ org/h2/samples/newsfeed.sql
+ org/h2/samples/optimizations.sql
+
+
-
+
org.apache.maven.plugins
maven-enforcer-plugin
3.0.0-M3
@@ -226,7 +227,7 @@
- src/main/org/h2/res/help.csv
+src/main/org/h2/res/help.csv
Re: [h2] For everyone who use snapshot builds of upcoming H2 2.0
On Mon, 2021-02-15 at 08:11 +0700, Andreas Reichel wrote:
> this is where a "Build ID" or "Commit ID" would become handy, packing
> into "h2-2.0.201_888e228.jar" instead of "h2-2.0.201" only.
Patch attached.
Best regards
Andreas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/c7a0927f6ce11a2d0ba23b954e3877d5143d399a.camel%40manticore-projects.com.
diff --git a/h2/pom.xml b/h2/pom.xml
index 027897f..130efe8 100644
--- a/h2/pom.xml
+++ b/h2/pom.xml
@@ -1,5 +1,5 @@
http://maven.apache.org/POM/4.0.0; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd;>
+ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd;>
4.0.0
com.h2database
@@ -69,9 +69,9 @@
${lucene.version}
-org.apache.lucene
-lucene-queryparser
-${lucene.version}
+ org.apache.lucene
+ lucene-queryparser
+ ${lucene.version}
org.slf4j
@@ -125,7 +125,7 @@
+ where to possibly find tools.jar and annoyingly its called classes.jar on OSX -->
jigsaw-jdk
@@ -172,6 +172,7 @@
src/main
+${project.artifactId}-${project.version}-${git.commit.id.describe-short}
src/test
@@ -200,19 +201,19 @@
-
-src/test
-
-org/h2/test/bench/test.properties
-org/h2/test/script/testScrip.sql
-org/h2/test/scripts/**/*.sql
-org/h2/samples/newsfeed.sql
-org/h2/samples/optimizations.sql
-
-
+
+src/test
+
+ org/h2/test/bench/test.properties
+ org/h2/test/script/testScrip.sql
+ org/h2/test/scripts/**/*.sql
+ org/h2/samples/newsfeed.sql
+ org/h2/samples/optimizations.sql
+
+
-
+
org.apache.maven.plugins
maven-enforcer-plugin
3.0.0-M3
@@ -226,7 +227,7 @@
- src/main/org/h2/res/help.csv
+src/main/org/h2/res/help.csv
Re: [h2] For everyone who use snapshot builds of upcoming H2 2.0
Thank you Evgenij, this is where a "Build ID" or "Commit ID" would become handy, packing into "h2-2.0.201_888e228.jar" instead of "h2-2.0.201" only. Also it wouuld be nice to be able to read that "Build ID" from the data base meta data or schema information (without opening the DB in full at the risk of corrupting it). >From an end-users point of view, the H2 releases take very long and are massive. 201 has so many beautiful improvments (which fixed a lot of real-life production issues for us) that we rather follow the development as closely as possible even when 1.4.200 is declared stable. (At least in our experience, 1.4.200 is far less robust and correct than 2.0.201.) I will also have to figure out, how to support "Build IDs" in the Migration Tool, because following your advise we will have to export/create 20 H2 databases weekly. Best regards Andreas On Sun, 2021-02-14 at 16:51 -0800, Evgenij Ryazanov wrote: > Please note that database files created by one snapshot build should > not be opened with another snapshot build to avoid data loss or > corruption. Always export your data to SQL with your current build > and import it into new empty database with a new build if you want to > use a more recent (or older) build for a some reason. > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/29ca67ff-216c-4991-9da4-04271e79f964n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d15c71206174c3c74d528aa38571100e2e65cb09.camel%40manticore-projects.com.
[h2] Re: Automatic H2 Migration tool, first release for testing --> UI is functional
Dear All, we have a functional UI. H2 DB files can be added by pointing on folders, which will be searched recursively. Give it a try and let me know what you think. Source: https://github.com/manticore-projects/H2MigrationTool Binary: https://github.com/manticore-projects/H2MigrationTool/releases/download/v1.0/H2MigrationTool-1.0.zip Best regards Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/07230ca89c84e1123d54b0bf72fbadeebfa9db71.camel%40manticore-projects.com.
Re: [h2] Debugging large database file
Good Morning Quy, unfortunately I have observed similar problems in the past: https://github.com/h2database/h2database/issues/2904 Two things: 1) when inserting large data, then you will need to remove any indices and constraints on the target table and create them only after the data are written 2) "pagination" helps, but is not very robust or reliable Also, beyond the problem above, H2 in general is meant to take a lot of filespace -- trading of speed vs. file space efficiency. Best regards Andreas On Thu, 2021-02-11 at 10:11 -0800, Quy Nguyen wrote: > Hello, > > I have an issue with the size of the database file. It stores roughly > 500mb of data normally, but during use it can balloon to something > like 25GB. If I restart the application, it can spends hours reading > and writing the database, and then the file will be back to 500mb. > > I'm using the following dependencies, along with Ktorm as my ORM, but > I did not have this issue when I was using sqlite. > > ``` > implementation group: 'com.zaxxer', name: 'HikariCP', version: > '4.0.1' > implementation group: "com.h2database", name: "h2", version: > "1.4.200" > ``` > > Thanks, > Quy > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/6359beae-5ab3-4fd2-9693-7d19fdfe48d7n%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1a99ff0d33ec187cd2d80c138d80f7e2741458c7.camel%40manticore-projects.com.
