Re: [h2] What version to pick for production

[Ryan How]

Stability seems good to me. I sometimes get MS SQL and MySQL database 
corruptions either from power outages or OOM. I think you've done an 
insanely awesome job putting H2 together Thomas. Other more "Mainstream" 
products seem to get more hype and attention, but I don't think that 
makes them better. They probably just are marketed better.


Ryan

On 9/06/2016 1:59 AM, Thomas Mueller Graf wrote:

Hi,

What I could do is make H2 version 1.4.192 "non-beta" now. I didn't 
want to do that because for some use cases (according to Steve 
McLeod), performance was not great with versions up to and including 
1.4.191, and I didn't know about 1.4.192. But now I have confirmation 
from Steve McLeod that performance improved almost to the PageStore 
level. Not quite as good, but relatively close. As for stability, I 
didn't see any major problems with version 1.4.191 and 1.4.192.


What is a bit unexpected is that the jar file of 1.4.192 is compiled 
with Java 7, which doesn't match the documentation. This is my mistake.


Or, probably easier, is to release version 1.4.193 soon, and mark that 
as stable.


Regards,
Thomas

On Tuesday, June 7, 2016, Dennis Wagelaar 
> wrote:


It's the same for us: we use H2 in production as well, as a
client-side database that caches part of the server-side database
for offline use. If there's ever an inquiry into the cause of a
particularly painful data loss of offline recorded data, using a
beta version of a database engine is enough to conclude the
inquiry. We are currently looking at the following timeline:

- 2011: choice of database engine: HSQLDB is inactive since 2010,
H2 is actively developed: we chose H2
- 2014: last stable release of H2
- 2015: HSQLDB development restarted; 2.x release series
- 2016: still no stable H2 release

We're currently using H2 1.3.168: Upgrading to 1.3.176 has
resulted in several cases of database file corruption, so we never
pushed that to production. As a result, we're currently stuck with
1.3.168.

Op maandag 6 juni 2016 10:26:57 UTC+2 schreef Silvio:

Thank you for the info. I am still very uncomfortable using
something that is marked as beta in production. But this beta
status has lasted for a long time already and it is unclear
when an actual release version will be available. And in the
meantime I have seen multiple remarks about things that have
been improved in 1.4 wrt. the PageStore.

Personally I would have preferred a release status for 1.4 as
soon as the software was reliable enough to warrant use in
production, regardless of a possible format change. Why not
call that 1.5, 1.6 aso. to mark such backward incompatibility?


On Sunday, June 5, 2016 at 9:15:19 AM UTC+2, Noel Grandin wrote:

We use 1.4 in production with both PageStore and MVStore,
both are pretty stable.

We currently find PageStore to be slightly more stable
when running on some systems in the field where power
outages are extremely common (i.e. more than once per
day), but even there we are thinking of moving to MVStore
with the latest 1.4 builds.

The only downside with MVStore is that there might be a
binary format change at some point in the future, which
will need need a full dump and restore to upgrade across.
​

-- 
You received this message because you are subscribed to the Google

Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com

.
To post to this group, send email to h2-database@googlegroups.com
.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at 

Re: [h2] Semi-Unique String column

[Ryan How]

Hi,


Can you try a unique constraint instead?

See here

http://osdir.com/ml/h2-database/2012-03/msg00141.html

Ryan

On 3/05/2016 10:36 PM, sd23gl via H2 Database wrote:

Hello,

I do save usernames in a VARCHAR_IGNORECASE column. However the user 
can stay anonymous until a username is picked.
On mysql a unique index is fine, as it allows multiple null-entries 
not violating the index. In the application layer I just map null to

whatever the default username is. (i.e. Guest, etc.).

Now H2 seems to not support multiple nulls in a unique index, even in 
mysql compat mode. I don't consider this a bug, or mising
feature, this seems to be undefined in the standard for some time now. 
And people already argue about null enough. ;)


But I would like to emulate the behavior without autogenerating 
usernames for various reasons. But i would like to solve uniqueness
on the database layer. I have looked into constraints, which seems to 
be what I am looking for, but currently struggle with the syntax:


ALTER TABLE users ADD CONSTRAINT username_unique CHECK (??? LIKE 
'guest' || FALSE=(SELECT username FROM users WHERE username like ??? 
LIMIT 1))


Now my plan-B is to just create another table with userID, username 
and unique Index and just join all the time, but I would like to

avoid that, if possible/reasonable.
Another alternative would be to use a foreign key here, and unique 
seems to allow exactly one null entry.


Thanks for any input!

--


--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] mail flow analytics for a company

[Ryan How]

Hi,

You could make it work with that, but it would depend massively on how 
you architect your system. You won't just be able to put the records in 
a database and then query it with any kind of scalability and 
performance expectations.


Ryan



On 20/04/2016 10:12 PM, Valentin Popov wrote:

Hello everyone.

I need write analytics of mail flow in a company for domains and 
users. Analytics should contains traffic size/count, attachments 
size/count, attachments type, with directions (in/out/internal) with 
granularity threshold of 30 minutes. Company with 50 000 employers 50 
messages day each ~ 2 500 000 records day minus some granularity of 30 
minutes. Each of 50 000 employers has average 100 external contacts, 
so it will be 5 000 000 users table. Analytics for such thing should 
be years... Like zabbix or nagios.


Is H2 can handle this?

Thanks for help.

Regards,
Valentin.
--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] h2 Database backup and restore while database is opned using hibernate ORM

[Ryan How]

You aren't executing the query, only creating it?

|session.createSQLQuery("BACKUP TO 
'"+file.getCanonicalPath()+"'").executeUpdate();|






On 4/04/2016 4:55 AM, abdou amer wrote:
|I want to perform backup and restore using hibernate ORM, but i seems 
that the below code not do any thing.|
|So, what the propre way to perform backup and restore without 
corrupting the database.|

||
|Filefile 
=fileChooser.showSaveDialog(tbTabPaneHome.getScene().getWindow());if(file 
!=null){// Save filetry{Sessionsession 
=DatabaseUtil.getSessionFactory().openSession();session.beginTransaction();session.createSQLQuery("BACKUP 
TO 
'"+file.getCanonicalPath()+"'");session.getTransaction().commit();session.close();}catch(IOExceptione){e.printStackTrace();}}|

--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Unique index or primary key violation in version 1.4.191

[Ryan How]

I've been using MVCC with PageStore for years. LOBS don't work very well 
(but I didn't want them in the db anyway, so we don't use LOBS). Haven't 
had any issues with anything else though.


Doesn't mean bugs don't exist though.

You only get the issue with MVCC on though hey?

On 17/02/2016 1:18 PM, Noel Grandin wrote:

At some point in the future is all I can say, not anytime soon. My
point was more that MVCC on PageStore is a dead end for us, so we're
not going to expend any effort debugging problems with it.

So if you want reliability, use PageStore without MVCC.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] how can i delete all .db files when i uniinstall my java application

[Ryan How]

That would be the job of the uninstaller. eg. If they are all in a 
single directory then delete that directory.


So I'd look up the documentation for the installer / uninstaller.


On 19/01/2016 11:15 PM, AKHIL KOTTAKKAL AKHIL wrote:

how can i delete all .db files when i uniinstall my java application
--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] HAVING without GROUP BY

[Ryan How]

That's so good I just wanted to see it by itself and repeat it to everyone!

:D

On 27/10/2015 4:28 PM, Lukas Eder wrote:
As far as I'm concerned, nothing beats a good glass of red wine whilst 
reading the SQL standard documents in front of the fireplace. Somehow, 
few people are with me on that ;)



--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: SQL synthax error

[Ryan How]

You need to create the Kunde table before the |RESERVIERUNG table. 
Otherwise it can't create the foreign key (because the table doesn't exist).



|
On 15/10/2015 6:04 PM, Steve McLeod wrote:

Two things I can suggest:

1) You should ask this question on stackoverflow.com, not in this 
mailing list.

2) You should post the full error you get (on stackoverflow.com)


On Thursday, 15 October 2015 05:42:59 UTC+2, DBDude wrote:

Hi!

I have a problem with creating tables:
|CREATE TABLE BOX
(BID int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Groesse int,
Einstreu varchar(50),
Ort varchar(50),
Fenster boolean,
Preis int,
Bild varchar(50));

CREATE TABLE RESERVIERUNG
(RID int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
KID int FOREIGN KEY REFERENCES KUNDE(KID) ,
BID int FOREIGN KEY REFERENCES BOX(BID),
Anfang date,
Ende date,
Pferd varchar(50),
);


CREATE TABLE Kunde
(KID int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 Name varchar(50),
 HatKundenKarte boolean,
);|

When I execute this sql statement I cant create the table
'reservierung' . There seems to be a problem with the 'foreign key
references' command.
I used this code with HSQLDB a while ago and it worked. Does
anyone know what is wrong with the code?

--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
.
To post to this group, send email to h2-database@googlegroups.com 
.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Multiple independent H2 databases within one JVM

[Ryan How]

I'd be inclined to have a connection pool per session. Then create and 
destroy the connection pool. That way it will manage connections however 
you want to use them and you don't have to keep a control connection 
open on the database.


But there are so many ways to do it!. It always confuses me which is the 
best one :)


Ryan

On 3/07/2015 3:27 PM, Basil Bourque wrote:
Regarding Vaadin, the Application class/idea was one of the major 
changes in Vaadin 7 from Vaadin 6. That class/idea are gone. Now we 
have the much more flexible VaadinSession that wraps a Servlet 
session, and owns one or more instances of one or more UI classes. 
Each UI instance is the entire content present in a web browser's 
window/tab (or a Portlet https://en.wikipedia.org/wiki/Portlet). In 
other words, your Vaadin app can have multiple open browser 
window/tabs running simultaneously. I made a diagram 
http://i.stack.imgur.com/Qp1XB.png of this architecture. This 
Question http://stackoverflow.com/q/21777067/642706 and this 
Question 
http://stackoverflow.com/questions/15439761/how-to-put-data-in-session-variable-and-get-the-data-in-different-page-in-vaadin 
on StackOverflow may be useful, as well as the /Application Lifecycle/ 
chapter https://vaadin.com/book/-/page/application.lifecycle.html of 
The Book Of Vaadin.


You could hold one reference/connection to H2 in the VaadinSession for 
all the UI instances (all the open windows). Or each UI instance 
could have its own H2 reference/connection. I don’t know enough about 
H2 yet to know which is appropriate.


There are hooks for the creation  destruction 
https://vaadin.com/book/-/page/application.lifecycle.html#application.lifecycle.session.init 
of the VaadinSession, where I would be starting/stopping that user's 
own H2 database.


On Wednesday, July 1, 2015 at 10:11:49 PM UTC-7, Kartweel wrote:

Yep no probs at all. You can have multiple databases open at the
same time and open and close them on different threads as you like.

You'll just need to do the appropriate connection handling linked
to the session open / close.

Scaling is going to depend on your application architecture too.
If you are horizontally scaling it isn't going to work with
embedded databases?. Or are you using TCP and having a h2 server
on a different server?

The downside I can think of is that you can't query all databases
for any non-user tasks. Have to update all schemas individually,
etc. So on each session start it is going to almost be like
starting a separate application.

Probably fits quite nicely with vaadin architecture. Just link it
to the Application start and end (Don't they call a session an
application?)


Ryan


On 2/07/2015 6:06 AM, Basil Bourque wrote:


Is it possible to start up and shut down multiple H2
http://h2database.com/html/main.html databases within a JVM?

My goal is to support multi-tenancy
https://en.m.wikipedia.org/wiki/Multitenancy by giving each
user/account their own database. Each account has very little
data. Data between the accounts is never accessed together,
compared, or grouped; each account is entirely separate from the
others. Each account is only accessed briefly once a day or a few
times a month. So there are few upsides to housing the data
together in a single database, and some serious downsides.

So my idea is that when a user logs in for a particular account,
that account’s database is loaded. When that user logs out, or
their web app session (Vaadin https://www.vaadin.com/ app)
times out, that account’s database is closed, it's data flushed
to storage, and possibly a backup performed. This opening and
closing would be happening for any number of databases in parallel.

Benefits include minimizing the amount of memory in use at any
one time for caching data and indexes, minimizing locking and
other contention, and allowing for smooth scaling.

I'm new to H2, so I'm not sure if its architecture can support
this. I'm asking for a denial or confirmation of this capability,
along with any tips or caveats.



Taken from my Question
http://stackoverflow.com/q/31150193/642706 on StackOverflow.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...@googlegroups.com javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving 

Re: [h2] Multiple independent H2 databases within one JVM

[Ryan How]

Yep no probs at all. You can have multiple databases open at the same 
time and open and close them on different threads as you like.


You'll just need to do the appropriate connection handling linked to the 
session open / close.


Scaling is going to depend on your application architecture too. If you 
are horizontally scaling it isn't going to work with embedded 
databases?. Or are you using TCP and having a h2 server on a different 
server?


The downside I can think of is that you can't query all databases for 
any non-user tasks. Have to update all schemas individually, etc. So on 
each session start it is going to almost be like starting a separate 
application.


Probably fits quite nicely with vaadin architecture. Just link it to the 
Application start and end (Don't they call a session an application?)



Ryan


On 2/07/2015 6:06 AM, Basil Bourque wrote:


Is it possible to start up and shut down multiple H2 
http://h2database.com/html/main.html databases within a JVM?


My goal is to support multi-tenancy 
https://en.m.wikipedia.org/wiki/Multitenancy by giving each 
user/account their own database. Each account has very little data. 
Data between the accounts is never accessed together, compared, or 
grouped; each account is entirely separate from the others. Each 
account is only accessed briefly once a day or a few times a month. So 
there are few upsides to housing the data together in a single 
database, and some serious downsides.


So my idea is that when a user logs in for a particular account, that 
account’s database is loaded. When that user logs out, or their web 
app session (Vaadin https://www.vaadin.com/ app) times out, that 
account’s database is closed, it's data flushed to storage, and 
possibly a backup performed. This opening and closing would be 
happening for any number of databases in parallel.


Benefits include minimizing the amount of memory in use at any one 
time for caching data and indexes, minimizing locking and other 
contention, and allowing for smooth scaling.


I'm new to H2, so I'm not sure if its architecture can support this. 
I'm asking for a denial or confirmation of this capability, along with 
any tips or caveats.




Taken from my Question http://stackoverflow.com/q/31150193/642706 on 
StackOverflow.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 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] out of memory with big db

[Ryan How]

Maybe you are better off with a different database for each book, and 
open and close them 1 at a time?


It really depends on your application...


On 1/06/2015 9:23 PM, naseh wrote:

version 1.4.187

i have designed the DB to have a table for every book. maybe it is not 
a good design but it is easier for me for now at least.



On Monday, June 1, 2015 at 11:30:33 AM UTC+4, Noel Grandin wrote:

Which version are you using?

If you have 6000 tables, you have something wrong with your
database design.

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] MVCC + Multithreaded

[Ryan How]

Isn't a bit unnerving if any client can actually corrupt the database?. 
I thought it is the servers job to keep the database safe?




On 8/05/2015 7:10 PM, Rami Ojares wrote:

Hold your horses!
I found one more client that accesses the database with version 186.
I had forgotten all about it.
It is a very difficult situation that just one client reading a little 
bit from the database will corrupt it.

So beware of version 186 (and maybe others?).

I will regenerate the database this evening make sure all clients are 
187 and report if I see any more corruptions.


- Rami

On 8.5.2015 10:36, Rami Ojares wrote:


But my import process as follows:
- Create a new db from scratch
- Create new tables
- import data from the old version using jdbc+sql
- create materilized views (calculated tables), set rights etc.
- turn h2 off in production, copy there the newly created file and 
start again.


So I don't think that my current db has ever touched version 186. Is 
there a way to see from the db file if it has at some point been 
touched by version 186?


- Rami

On 8 May 2015 08:42, Thomas Mueller thomas.tom.muel...@gmail.com 
mailto:thomas.tom.muel...@gmail.com wrote:


 Hi,

 I'm afraid if the database was opened with version 1.4.186 (when 
using the MVStore), then the data is gone, even if you open it with a 
newer version afterwards.




--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Backup mvcc+multithreaded

[Ryan How]

I think you have to turn off the space re-using or something like that 
while running the backup?


Maybe that should happen automatically as part of the script tool?


On 8/05/2015 7:48 AM, Rami Ojares wrote:
I tested it out and it seems that the backup becomes completely 
corrupted.
So one should reserve the db exclusively for himself and only then try 
the backup.


- rami

On 7.5.2015 21:24, Rami Ojares wrote:

Hi,

Now when I have moved to mvcc+multithreaded and do my backup using 
Script tool the database stays responsive.

I think before every object was locked during backup.

But I started wondering about the consistency of the backup.
Consider the following scenario.
- Table T1 that has FK referring to T2.
- Backup has already processed T1
- Delete on T2 happens that with cascade mechanism removes a row from T1
- Backup now processes T2

The backup is now inconsistent because T1 has a row that refers to a 
non-existent row in T2.


Is this possible?
Should I still take my database offline when doing a backup?

- Rami




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Backup mvcc+multithreaded

[Ryan How]

I misread initially. You are using the script command to produce an SQL 
script?


I'm not sure if that would work ? Wouldn't the MVCC isolate so the 
changes don't propagate through to the other session?



On 8/05/2015 1:42 PM, Thomas Mueller wrote:

Hi,

 Should I still take my database offline when doing a backup?

Either this, or use the backup SQL statement. With the MVStore 
storage, this should work very well. With the PageStore, it should 
also work, but I didn't test as much.


Regards,
Thomas


On Friday, May 8, 2015, Ryan How r...@bitworks.com.au 
mailto:r...@bitworks.com.au wrote:


I think you have to turn off the space re-using or something like
that while running the backup?

Maybe that should happen automatically as part of the script tool?


On 8/05/2015 7:48 AM, Rami Ojares wrote:

I tested it out and it seems that the backup becomes completely
corrupted.
So one should reserve the db exclusively for himself and only
then try the backup.

- rami

On 7.5.2015 21:24, Rami Ojares wrote:

Hi,

Now when I have moved to mvcc+multithreaded and do my backup
using Script tool the database stays responsive.
I think before every object was locked during backup.

But I started wondering about the consistency of the backup.
Consider the following scenario.
- Table T1 that has FK referring to T2.
- Backup has already processed T1
- Delete on T2 happens that with cascade mechanism removes a row
from T1
- Backup now processes T2

The backup is now inconsistent because T1 has a row that refers
to a non-existent row in T2.

Is this possible?
Should I still take my database offline when doing a backup?

- Rami




-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com

javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com');.
To post to this group, send email to h2-database@googlegroups.com
javascript:_e(%7B%7D,'cvml','h2-database@googlegroups.com');.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] file corrupted

[Ryan How]

Heya,

What's your connection string?


On 5/03/2015 8:16 AM, Bashorun Olajide wrote:

i am new to H2, i just started 2 days ago after reading about javafx. i got an 
error while trying to retrieve record in a database. i dont even know maybe 
successfully connection established.
i got this error
org.h2.jdbcSQLException: File corrupted while reading record:[6] stream data key:1 
pos:11 remaining:0. possible solution: use the recovery tool [90030-186].
thanks in advance.
Also if you could get me useful resources on h2 connection to javafx i will 
appreciate.
thanks once again.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Is it safe and reasonable to open a read-only database in embedded mode from two processes without locking?

[Ryan How]

I'll let someone more qualified give you a better answer.

But, yes I can't see any reason why it wouldn't work (The same as if it 
was on a read only filesystem?)


I find embedded mode has better performance, but it is more profound in 
lots of smaller queries. If it is larger less frequent queries there 
isn't as much difference.


You might have to test your specific case if you are trying to squeeze 
every bit of performance possible.


Not sure if the cache might be better with the server though. Otherwise 
each process will have it's own cache.


On 28/02/2015 8:49 PM, Johann Petrak wrote:

Is it possible, safe and reasonable to add
;FILE_LOCK=NO;ACCESS_MODE_DATA=r
to the URL from two processes which use the same database in embedded mode?
If yes, can I expect better performance from doing it that way than from
using a local server?

thanks
  johann
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Is it safe and reasonable to open a read-only database in embedded mode from two processes without locking?

[Ryan How]

My Email doesn't appear have come through.. sending again.

Sounds like trouble there!. A would still be updating metadata, and if B 
is reading from metadata then it might be in a corrupted state. That 
would be my guess. Have no idea really. But I wouldn't do it if you were 
counting it on it work consistently. Might work 99.9% of the time then 
do something strange. I might be completely off though.



On 1/03/2015 2:00 AM, Johann Petrak wrote:
Thanks -- this is what I had been thinking, but I thought it would be 
better to bring it up since

this option is not explicitly mentioned in the documentation.

Another interesting situation is where two processes access the same 
database, again in embedded
mode, and one of the processes updates just table A while the second 
process just reads from table B.
Would it be safe for the second process to open the database in 
read-only mode and without locking, while
the first process opens it r/w and creates a lock file and would they 
interfere with each other?



On 28 February 2015 at 17:43, Ryan How r...@bitworks.com.au 
mailto:r...@bitworks.com.au wrote:


I'll let someone more qualified give you a better answer.

But, yes I can't see any reason why it wouldn't work (The same as
if it was on a read only filesystem?)

I find embedded mode has better performance, but it is more
profound in lots of smaller queries. If it is larger less frequent
queries there isn't as much difference.

You might have to test your specific case if you are trying to
squeeze every bit of performance possible.

Not sure if the cache might be better with the server though.
Otherwise each process will have it's own cache.

On 28/02/2015 8:49 PM, Johann Petrak wrote:

Is it possible, safe and reasonable to add
;FILE_LOCK=NO;ACCESS_MODE_DATA=r
to the URL from two processes which use the same database in embedded mode?
If yes, can I expect better performance from doing it that way than from
using a local server?

thanks
  johann
-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to a topic in

the Google Groups H2 Database group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/h2-database/JtWn2CjvIp0/unsubscribe.
To unsubscribe from this group and all its topics, send an email
to h2-database+unsubscr...@googlegroups.com
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] TRANSACTION_READ_UNCOMMITTED Transaction isolation in H2

[Ryan How]

I think with the MVStore it only works with READ_COMMITTED. Other 
isolation levels don't work.


If you use the page store it should work (or the latest stable version).



On 28/02/2015 10:25 AM, Sachin Patil wrote:



Hello there,

 I am trying use H2 database(1.4.185) in junit environment  and found 
out that TRANSACTION_READ_UNCOMMITTED is not working as expected for 
me.  I have created junit test case gist which reproduces this,


https://gist.github.com/sacdroid/c6e29a4e4f84916fafec

   Can you someone look into this and point me whats wrong with my 
test case? Looks like as per documentation, H2 supports 
TRANSACTION_READ_UNCOMMITTED isolation.


Thanks,
Sachin.
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] 1.4.x stability

[Ryan How]

I use it on the 1.3 branch in production. It was considered experimental 
in 1.3.


There are issues with blobs, I can't remember what it is, but we ended 
up not using them. Apart from that we've had no issues in production 
over the past 2 years with MVCC.


It may have been blobs and FOR UPDATE, I don't remember exactly, but 
you need to be careful around them.





On 18/02/2015 8:25 AM, Alex Courtis wrote:
Thanks for the very clear answer Thomas - looks like we'll be going 
with 1.3 until we have 1.4 MVStore stable.


Next question: is the MVCC feature on 1.3 considered stable i.e. 
usable in a production system?




On Wednesday, 18 February 2015 06:01:03 UTC+11, Thomas Mueller wrote:

Hi,

I'm afraid the MVStore is not yet stable, and it's hard to say
when it will be the case. The main issue is, the file format might
still change. I'm quite sure it will be stable later this year,
but when exactly that will be I don't know, sorry.

Regards,
Thomas



On Monday, February 16, 2015, Alex Courtis acou...@atlassian.com
javascript: wrote:

Any update would be appreciated!

We have extensively tested with the 1.4 MVStore engine,
however would prefer to release with a stable 1.4.

An update of the roadmap would be fantastic:
http://www.h2database.com/html/roadmap.html
http://www.h2database.com/html/roadmap.html



On Tuesday, 3 February 2015 00:17:24 UTC+11, Sander Sõnajalg
wrote:

Hi,

we're using H2 1.3.176 to back up a 24/7 Play!-based
application, average database size ~100 Mb, persisted to
file system. We have some performance problems.

I thus have a very general question - when do you think
1.4.x branch would be stable enough to end the beta, and
how would you assess the stability right now? Would you
recommend to update to the current latest beta, or rather not?

Thanks a lot!

--
Sander Sõnajalg
ZeroTurnaround

-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from
it, send an email to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Could data corruption being caused by non-accurate .lock.db information ?

[Ryan How]

Just out of interest, something like an Access database works out of the 
box on windows (I've never seen any glaring corruption issues, they 
corrupt easily, but not as easily as a bug like this would indicate).


I wonder if it works around it? I'm sure older versions like Access 2003 
are unaware of SMBv2 (I have a lot of clients running Access 2003 on 
network file systems running SMB2). Plus it has been standard since 
Vista, so in 6 years I would have thought the issue would come up much 
more regularly? I've had major performance issues on some systems with 
SMB2 (But not across the board, I believe that was another issue that 
SMB2 triggered, not an issue with SMB2 itself), but never corruption issues.


Ryan

On 23/01/2015 9:34 PM, Rinse Lemstra wrote:
Ok, certain versions of windows do have this behaviour, see: 
https://groups.google.com/forum/?utm_source=digestutm_medium=email#!topic/h2-database/w65UQETmo7U 



Rinse

Op donderdag 22 januari 2015 21:36:09 UTC+1 schreef Thomas Mueller:

Hi,

 on some windows-version the  .lock.db file is not visible for
remote connections for up to 10 seconds

OK, if you have a file system where a new file is not visible for
up to 10 seconds, then this is not something that can be supported
by the auto-server mode (by this file locking method). In that
case, the auto-server mode can't be used. If you do use it, then
probably the database gets corrupt.

Regards,
Thomas

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] does LIMIT improve performane

[Ryan How]

Hi,

I don't know how it is actually implemented, but conceptually It depends 
on your query.


If you do an order by and limit, it'll need to sort it all first before 
pulling the first record. So it isn't going to shortcut the query at all.


The limit will improve performance because it only has to return 1 
result instead of an entire result set (and then pulling out only the 
first result in code), but again depending on your query, that may only 
be a tiny improvement.


The best thing to do is run explain and see what your query is doing 
with and without the limit and see if it improves performance.


Cheers, Ryan

On 4/01/2015 11:14 PM, Adam McMahon wrote:

Hi,

I have a large table in which I need to find only one matching record 
from the table.  If I put in a LIMIT 1, could that improve performance?


 If there is a limit, does the engine stop searching once the LIMIT 
has been found?  Or is the LIMIT applied as a filter after the search 
has been performed?


Thanks,

-Adam
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Backup.zip not immediately available

[Ryan How]

I've had similar issues with anti-viruses locking the file while it 
unzips it and scans it...



On 10/12/2014 1:03 AM, Thomas Mueller wrote:

Hi,

Could you describe what you do exactly (how you create the backup, how 
you copy it)? How do you know the file is not available?


Regards,
Thomas



On Sunday, December 7, 2014, Rinse Lemstra r.lems...@xso.nl 
mailto:r.lems...@xso.nl wrote:


We found that a backup.zip is not immediately ready for copying
after creating the backup, it can take up to 5 seconds before the
backup.zip is available.

This behavior does not occur in all situations, it only occurs if
the database is located on a share on a server,  it may be related
to the OS of the server. In particular Wndows small business
server is one of them.

Did any of you see this behavior, and if so, what could be done to
eliminate this delay.

regards,

Rinse



-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com

javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com');.
To post to this group, send email to h2-database@googlegroups.com
javascript:_e(%7B%7D,'cvml','h2-database@googlegroups.com');.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] H2DB select performance issue :(

[Ryan How]

Is it CPU bound or disk bound during that 90 seconds when the query is 
running?


You can try increasing JVM memory?

The the slow query the same as all the others?. perhaps it isn't using 
an index for that query?


You really need to try and find out what is going on, or get it running 
in a test environment where you can reproduce the issue and find out 
what it is, or profile it.




On 19/09/2014 7:27 PM, Arun wrote:



On Wednesday, September 17, 2014 12:14:32 AM UTC+5:30, Thomas Mueller 
wrote:


Hi,


and jar version is h2-1.1.114.jar.


Hm, that's very very old. Could you upgrade to a more recent version?


jdbc:h2:/data/Testbed/h2DB/Arun;DB_CLOSE_ON_EXIT=FALSE;FILE_LOCK=NO;LOG=2


I hope you understand that FILE_LOCK=NO is very dangerous.

Regards,
Thomas






Hi Sir,

As its a production server.running from past few years.its impossible 
to change jar version.

if jar version is same then anything which we can try..?

we can  recreate DB.
we can change DBURL. any appropriate DB parameter which we can use 
with same version jar.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Is 60 write operations/sec really the limit for H2?

[Ryan How]

I think it means that is why it doesn't sync for every commit, so it can 
get higher performance than that.


If it syncs for every commit, then if you are committing each row and 
doing a lot of updates for example, then you get bad performance. 
Probably wouldn't matter much for an SSD, but on a rotational HDD it 
makes a huge difference.


Anyway, yes H2 will get a lot more than 60 write operations per second.

Ryan



On 11/09/2014 4:21 PM, Anders wrote:

Hi,

/According to documentation, the system doesn't call 
FileDescriptor.sync () nor FileChannel.force () nor fsync 
http://en.wikipedia.org/wiki/Sync_%28Unix%29 for every commit 
because they significantly degrade system performance: *only 60 write 
operations per second http://en.wikipedia.org/wiki/IOPS are 
achievable*./ -- http://en.wikipedia.org/wiki/H2_%28DBMS%29


Could someone please confirm if this really is correct?

I will remember that H2 can perform so much better than this.

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 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] H2 db getting corrupt on mac

[Ryan How]

Pretty sure he is using the backup command.



 Here's the code:

 String sqlStatement = BACKUP TO ?;
try (Connection connection = getConnection())
{
try (PreparedStatement preparedStatement =
   connection.prepareStatement(sqlStatement))
{
preparedStatement.setString(1, fileName);
   preparedStatement.executeUpdate();
if (isDebugEnabled)
{
logger.debug(BACKUP TO result completed);
}
}
}




On 5/09/2014 8:46 PM, Thomas Mueller wrote:

Hi,

 to do a test after the backup and copy to make sure the database can 
be opened.


Testing a backup is a good idea in any case (even for regular backup). 
However, just opening the database will not detect some types of 
errors. You would need to do a complete read (for example using 
script). But if you do that, wouldn't it be simpler and faster to 
use the built-in online backup feature (the backup SQL statement)?


Regards,
Thomas



On Fri, Sep 5, 2014 at 9:03 AM, witerat johndhutche...@gmail.com 
mailto:johndhutche...@gmail.com wrote:


Copying an active data file creates and inconsistent image. This
is writes by the data  engine occur randomly throughout the file,
while reading from the file is done serially. Essently later
writes nearer the beginning of the file that the reading point of
the copy operation are not reflected in the copied image.


On Friday, 5 September 2014 05:02:25 UTC+12, Kam wrote:

Testing the backup sounds like a good idea. thanks. Still not
sure what would cause a corrupt db because if it happens while
its in use, then this is not a solution but it's a start.

-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] H2 db getting corrupt on mac

[Ryan How]

I have an application that uses BACKUP to copy the database to tablets 
for offline use. It fails and produces a corrupt database about once a 
year. It probably does 3000 or so copies a year. But like you, I'm not 
sure if it is in the backup code or the copy code and I can't reproduce 
it in a test.


My only suggestion (which I am meaning to implement myself one day), is 
to do a test after the backup and copy to make sure the database can be 
opened. Basically a verification stage of the backup. After then you can 
be guaranteed that you have a valid backup.





On 4/09/2014 1:34 AM, Kam wrote:

Here's the code:

 String sqlStatement = BACKUP TO ?;
try (Connection connection = getConnection())
{
try (PreparedStatement preparedStatement =
connection.prepareStatement(sqlStatement))
{
preparedStatement.setString(1, fileName);
preparedStatement.executeUpdate();
if (isDebugEnabled)
{
logger.debug(BACKUP TO result
completed);
}
}
}

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] H2 db getting corrupt on mac

[Ryan How]

It isn't safe to backup the file that way. You need to use the inbuilt 
backup command if the database is in use.


http://h2database.com/html/grammar.html#backup

http://h2database.com/javadoc/org/h2/tools/Backup.html





On 3/09/2014 6:27 AM, Kam wrote:
I don't know what's causing it. We have a feature that backs up the H2 
DB file and zips it via java. the DB is in use when we back it up (all 
it does is copy the h2 file) When we tried to use it, it's corrupted. 
This is the stack trace when I try to open it
ZipOutputStream append = new ZipOutputStream(new 
FileOutputStream(fileFullPath));


java version 1.7.0_55 on mac and file size is 1.4 MBs and we have a 
lot of / (slashes) stored. I am not sure where to begin to find it. Is 
it H2 ? is it our code writing/copying the file? (tests can't 
reproduce it) What is this error ?




java -cp lib/h2*.jar org.h2.tools.Shell -url 
jdbc:h2:./data/path;CIPHER=AES;TRACE_LEVEL_SYSTEM_OUT=0;ALLOW_LITERALS=NUMBERS 
-user sa -password pass1 pass1


Exception in thread main org.h2.jdbc.JdbcSQLException: General 
error: java.lang.ArrayIndexOutOfBoundsException: 4 [5-173]


at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)

at org.h2.message.DbException.get(DbException.java:160)

at org.h2.message.DbException.convert(DbException.java:283)

at org.h2.table.RegularTable.removeRow(RegularTable.java:397)

at org.h2.store.PageStore.redo(PageStore.java:1537)

at org.h2.store.PageStore.redoDelete(PageStore.java:1511)

at org.h2.store.PageLog.recover(PageLog.java:334)

at org.h2.store.PageStore.recover(PageStore.java:1371)

at org.h2.store.PageStore.openExisting(PageStore.java:361)

at org.h2.store.PageStore.open(PageStore.java:285)

at org.h2.engine.Database.getPageStore(Database.java:2210)

at org.h2.engine.Database.open(Database.java:603)

at org.h2.engine.Database.openDatabase(Database.java:226)

at org.h2.engine.Database.init(Database.java:221)

at org.h2.engine.Engine.openSession(Engine.java:56)

at org.h2.engine.Engine.openSession(Engine.java:160)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:139)

at org.h2.engine.Engine.createSession(Engine.java:122)

at org.h2.engine.Engine.createSession(Engine.java:28)

at 
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:313)


at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:105)

at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:90)

at org.h2.Driver.connect(Driver.java:73)

at java.sql.DriverManager.getConnection(DriverManager.java:571)

at java.sql.DriverManager.getConnection(DriverManager.java:215)

at org.h2.tools.Shell.runTool(Shell.java:147)

at org.h2.tools.Shell.main(Shell.java:80)

Caused by: java.lang.ArrayIndexOutOfBoundsException: 4

at org.h2.index.PageBtree.getRow(PageBtree.java:172)

at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:228)

at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:238)

at org.h2.table.RegularTable.removeRow(RegularTable.java:379)

... 23 more



--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Reduce space usage with repeated strings column values

[Ryan How]

I thought it did this already?

It just saves the string once and uses the hash or something?

I can't find it, but I thought I read such a thing in my years or forum 
browsing on here... It was like an internal de-duplication that happened 
by default. Maybe I was dreaming...


Ryan


On 8/07/2014 2:34 PM, Noel Grandin wrote:

No, we don't have such a feature.

You can enable compression on a database-wide basis, but not per-table.

On 2014-07-08 01:14 AM, gaurav wrote:
Is there any option to reduce space usage for varchar type columns 
when the values for some of the columns may be

repeated, below is an example dataset:





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] mvcc, long-running insert from csv

[Ryan How]

H2 is not multi-threaded in this version. So a single long running 
statement will block everything else. You'll either need to split it up 
into smaller inserts (can still be a single transaction, just smaller 
queries), or you could try turning on MULTI_THREADED=1.


There is multi-threaded mode, but in this version it doesn't work with 
MVCC. I think in the latest beta version it might work, but if you are 
after reliability I'd go with breaking up your long running statement 
into smaller statements.


On 28/05/2014 1:45 AM, Brian Craft wrote:
Version 1.3.171. The only option I'm setting is MVCC, like 
somefile;MVCC=TRUE. I'll try to get a thread dump in a few minutes.


Does this mean readers should not be blocked during the insert?

On Tuesday, May 27, 2014 4:18:01 AM UTC-7, Noel Grandin wrote:

Can you post a thread dump, so we can see where it is blocked?
Also which version is this, and what does your DB URL look like?

On 2014-05-25 20:24, Brian Craft wrote:
 I'm doing a large insert into .. select * from CSVREAD ...,
which is in a transaction. It seems to be blocking
 readers, regardless of whether MVCC is enabled. Is this
expected? If I understand the threads on MVCC, it will allow

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Release 1.4.178 Beta

[Ryan How]

The 2nd digit incremented aswell from 1.3 to 1.4


On 4/05/2014 1:52 PM, Lukas Eder wrote:


Am Samstag, 3. Mai 2014 14:10:11 UTC+2 schrieb Noel Grandin:

On Sat, May 3, 2014 at 12:46 PM, Lukas Eder lukas...@gmail.com
javascript: wrote:

 What does Beta mean when upgrading to any of these versions?
Is this
 because of the new MVCC store being active by default?


Yes.


This may have been suggested before. I take this question of mine as 
an occasion to point out the usefulness of semantic versioning 
(http://semver.org). For some reason, H2 increments the third digit 
for what are really minor releases, not patch releases. As a user, I 
would *not* expect the whole storage engine algorithms to change in a 
third-digit-upgrade release. I would only expect riskless bugfixes and 
very minor feature increments.


Specifically, since these two releases seem to be intended as beta, 
why not name them beta as well in Maven Centra to clearly 
communicate this intent?

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] FILE_WRITE function needed to export BLOB and CLOB from H2 to filesystem (opposite to FILE_READ function)

[Ryan How]

Yeah hash comparison on writes is a bit of overkill. If you can't rely 
on what is written to disk to actually be written to disk we are all in 
major trouble!


Also, java handles exceptions for permissions, out of disk space, etc, 
pretty well. just try, catch with appropriate handling as per your 
application needs...


Java 7 brings a lot of enhancements in nio2 also, so even less code to 
write, or you could use commons IO like you suggested...


I think clob encoding is also up to the user application. If it was me I 
would just do a binary copy and leave it in the source encoding, but it 
really depends on what you are trying to do...


On 25/04/2014 3:09 AM, Thomas Mueller wrote:

Hi,

Sorry I don't understand comparison of SHA-1 (MD4. MD5)

So writing it *once and well* for all

Well, so far it's not a popular feature request.

Regards,
Thomas


On Wed, Apr 23, 2014 at 9:20 AM, t603 stepan.rybar.1...@gmail.com 
mailto:stepan.rybar.1...@gmail.com wrote:



On Wednesday, April 23, 2014 9:00:40 AM UTC+2, Noel Grandin wrote:

Why not just retrieve the data as a ResultSet, call
getInputStream() on the ResultSet and write the InputStream to
disk.
Seems easy enough.


Writing such *really well coded* function in Java could be a job
for experienced Java programmer - to handle all Exceptions like
target directory permissions, free space size, encoding of CLOB,
check of correctness of writing using comparison of SHA-1 (MD4.
MD5) hash between file content and database LOB etc. Or to use
some File IO like one from Apache. So writing it *once and well*
for all in H2 as SQL function should be more effective and
trustworthy, moreover in case, than FILE_READ already exists. That
is my humble opinion.
-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: ODBC Error

[Ryan How]

Hi,

I'd try using the latest 1.3 version. I've had it working from Access 
and Excel for quite some time, but I don't use it regularly.


Ryan

On 23/04/2014 3:56 AM, Sylvain Nault wrote:
I can see the Support for the PG_CATALOG is part of the version 1.4.x 
roadmap. Will it work with the MS Office applications like Access or 
Excel? Any time frame for this to be available? I am trying to access 
than 2 database via ODBC using the PostgreSQL driver and am trying to 
link tables and keep getting errors. Will the support for the 
PG_CATALOG wix those issues?



Le lundi 22 février 2010 09:50:07 UTC-5, fd a écrit :

When i connect a table from Access i see this error in the trace log
of H2,
and the operation fails

Error: Table PG_ATTRDEF not found; SQL statement:

I'm using

 h2-1.2.129.jar
 psqlodbc_08_04_0200.zip
Access 2007

thanks
F.D.

Trace details ***

Connect
Init
StartupMessage
 version 196608 (3.0)
 param database=mimweb
 param user=MIMWEB
 param DateStyle=ISO
 param extra_float_digits=2
 param geqo=on
 param client_encoding=UTF8
PasswordMessage
Query
select oid, typbasetype from pg_type where typname = 'lo';
Query
SELECT Config, nValue FROM MSysConf;
org.h2.jdbc.JdbcSQLException: Table MSYSCONF not found; SQL
statement:
SELECT Config, nValue FROM MSysConf [42102-129]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:
317)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.get(DbException.java:145)
at org.h2.command.Parser.readTableOrView(Parser.java:4334)
at org.h2.command.Parser.readTableFilter(Parser.java:1002)
at
org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1530)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1627)
at org.h2.command.Parser.parseSelectSub(Parser.java:1524)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1369)
at org.h2.command.Parser.parseSelect(Parser.java:1357)
at org.h2.command.Parser.parsePrepared(Parser.java:396)
at org.h2.command.Parser.parse(Parser.java:279)
at org.h2.command.Parser.parse(Parser.java:251)
at org.h2.command.Parser.prepareCommand(Parser.java:223)
at org.h2.engine.Session.prepareLocal(Session.java:422)
at org.h2.engine.Session.prepareCommand(Session.java:383)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1071)
at
org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:163)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at
org.h2.server.pg.PgServerThread.process(PgServerThread.java:340)
at org.h2.server.pg.PgServerThread.run(PgServerThread.java:76)
at java.lang.Thread.run(Thread.java:619)
Query
select relname, nspname, relkind from pg_catalog.pg_class c,
pg_catalog.pg_namespace n where relkind in ('r', 'v') and n.oid =
relnamespace order by nspname, relname;
Query
select c.relhasrules, c.relkind, c.relhasoids from
pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid =
c.relnamespace and c.relname = E'TMIMAMB' and u.nspname = E'MIMWEB';
Query
select n.nspname, c.relname, a.attname, a.atttypid, t.typname,
a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules,
c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then t.typbasetype
else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'TMIMAMB' and n.nspname like E'MIMWEB') inner join
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum  0 and
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid
= a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname,
attnum;
org.h2.jdbc.JdbcSQLException: Column T.TYPTYPMOD not found; SQL
statement:
select n.nspname, c.relname, a.attname, a.atttypid, t.typname,
a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules,
c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then t.typbasetype
else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'TMIMAMB' and n.nspname like E'MIMWEB') inner join
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum  0 and
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid
= a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname,
attnum [42122-129]
at

Re: [h2] Re: insertion after drop is much slower than insertion in a new Table

[Ryan How]

Hi,

I was just reading your previous posts. It may be possible you are 
filling up your disk buffers after so many writes, so it is quite fast, 
then slows down while the OS flushes to disk. So you probably find it is 
fast up to a point, then slows right down after then. So if you try with 
more records / less records and varying the size of the records you may 
change when this point occurs.


Just my 2 cents.

Thanks, Ryan


On 8/04/2014 4:09 PM, Kais Haddadin wrote:
with a separate example (attached) I can not reproduce the problem. 
From what I saw from the trace file, the attached test should be very 
similar. I will further investigate from my application.


Regards,

Kais


On Tue, Apr 8, 2014 at 8:58 AM, Kais Haddadin kaishadda...@gmail.com 
mailto:kaishadda...@gmail.com wrote:


It will take some time to do that I will try to do that as soon as
possible and post it here.

Cheers,

Kais


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: H2 Database Corruption Issues

[Ryan How]

I'm really not sure sorry. I wouldn't think it would cause an issue if 
it doesn't OOM. Even in the event of OOM or power outage, process 
killed, etc, it still should be fairly safe.


Can you post about your environment, JVM version, OS, JVM startup params 
etc? Do you access files on a network file system? I probably can't 
help, but someone else here probably can :)


Ryan

On 4/04/2014 5:08 PM, David Anstee wrote:

Hi,

We have not experienced any Out of Memory errors so far. We don't tend 
to receive any errors until the database is re-opened and the 
connection fails. I think this might be worth looking into further 
though as our application can sometimes require large amounts of 
memory for some processes. Do you think it is possible that this could 
be affecting the memory usage of the h2 database, as this will be 
included in the JVM, and though not causing an out of memory 
exception, it could be making it 'grind to a halt'?


Thanks, Steve. We don't use many long running transactions either, but 
i will look into this where we do and see if restricting these can 
solve the issue.


David
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: H2 Database Corruption Issues

[Ryan How]

Just another note, you aren't getting Out of Memory Errors are you?. The 
docs say the Soft LRU cache is experimental and has been known to cause OOM?



On 3/04/2014 10:29 PM, Steve McLeod wrote:

Hi David,

I use H2 as an embedded database in desktop software with a 
substantial user base. I also occasionally get crash reports with 
similar errors. A few years ago this was frequent, but year after year 
these have been been becoming infrequent.


As far as I can tell, it happens sporadically when someone suddenly 
loses power while H2 is writing.


I minimised this problem by reducing my use of long-running 
transactions. As much as possible I use auto-commit on every write.







On Wednesday, 2 April 2014 12:47:57 UTC+2, David Anstee wrote:

Hi,

We have been using H2 as an embedded database in one of our java
applications and have been experiencing some database corruption
issues with some of our users. We do not disable the transaction
log, or the file locking as I know these can potentially cause
corruption of the database but we still occasionally receive
complaints of corrupted databases. As the issues have been
occurring on the client’s machines it has been hard for us to
track down what is causing them. The exception messages we receive
when opening the H2 database also appear to vary. The most recent
exception, which we had this morning, was:

org.h2.jdbc.JdbcSQLException: General error:
java.lang.RuntimeException: rowCount expected 51427 got 51390
T154.I178 [5-175]org.h2.jdbc.JdbcSQLException: General error:
java.lang.RuntimeException: rowCount expected 51427 got 51390
T154.I178 [5-175]

In the past we have attempted to use the recovery tool to restore
a database but so far we have been unable to recover a single one.
I'm not sure if this is due to the process that we go through or
the corruption of the database but the recovery tool never appears
to work successfully.

The connection string we use to the database is:
jdbc:h2:split:31: + DBFILE +

;PAGE_SIZE=8192;CACHE_SIZE=524288;CACHE_TYPE=SOFT_LRU;database_event_listener='
+ H2EventListener.class.getName() + ';USER= + dbusername +
;PASSWORD= + dbpassword;

We have, up until last week, been using version 1-3-169.jar but
have now moved to 1.3.175.jar

Thanks,
David

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] H2 Web Client problem

[Ryan How]

You need to enter a jdbc url, not the file path.

eg. jdbc:h2:file:c:\path\to\my\db

Check the docs for valid URLs for h2.

On 29/03/2014 1:42 PM, Axel wrote:

Hiya my name is Alex.

I am attempting to connect to a local DB but I get a error No suitable 
driver found and a java error.


I have windows 7 Pro 64 bit and both 32 and 64 Java installed. I am 
including a screenshot of the error along with the database.


Thanks for your help and time.
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] strange state db

[Ryan How]

Hi,

I'm not sure multithreaded is 100% safe yet. I wonder if it was caused 
by an issue with that.



On 7/03/2014 2:06 PM, Alex wrote:

hi

  db 1.3.172

FILE_LOCK=FS;PAGE_SIZE=1024;CACHE_SIZE=400072;MULTI_THREADED=TRUE;DATABASE_TO_UPPER=FALSE

i guess, some unclear way unique index has been updated with duplicate 
value. thus, when i try to **connect**
to database error message Unique index or primary key violation: 
SYS_ID ON PUBLIC.SYS(ID) [23505-172] 
http://127.0.1.1:8082/login.do?jsessionid=ce7b028e906119f96bd4a7d225166a1e# 
23505/23505 is thrown.


db file has been used by single app.

any comments to avoid such issue?

btw, is it safe to use two instances of DriverManager in one app?
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com 
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com 
mailto:h2-database@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Console or Shell: Run a query from a script file and capture output

[Ryan How]

If it is a small script you can copy paste the script in I think.

If you want to run from a file then issue the RUNSCRIPT sql command. If 
it is on a server, the script needs to be accessible on the server and 
use a path as it would be on the server.


http://h2database.com/html/grammar.html#runscript



On 18/02/2014 2:57 AM, John11 wrote:

Update:
I found how to run Runscript from a terminal with suitable arguments 
to run the query from a file and capture the results. Good.


I still cannot see whether there is a way to do this from the 
Console.  It appears not, is that right?


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Missing lob entry

[Ryan How]

What connection string are you using? LOBs and MVCC are a bit stuffed...


On 15/02/2014 2:08 AM, Darren S wrote:
I seemed to hit the error Missing lob entry quite frequently for my 
particular application.  I was wondering if there are some settings I 
should enable, or some information I can capture to help debug why 
this is happening.  I'm using 1.3.175 across various JVM versions and 
configurations.  Since I use H2 mainly for testing, when I this 
situation I usually just wipe out the database, so I've never 
attempted to narrow it down to a specific configuration or scenario.


Darren
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Hardware recommendations for H2 database ?

[Ryan How]

Yeah it doesn't actually affect my use cases, as long as there are no 
corruptions I'm happy. If I wanted to make sure everything is written to 
disk straight away I'd probably manually make a kernel call to flush 
system and disk caches and need to test it on each platform.


Some RAID controllers and filesystems like ZFS can handle synchronous 
writes very efficiently, so you wouldn't experience the slow down.


On 29/01/2014 2:04 AM, Thomas Mueller wrote:

Hi,

On a side note, it's probably been discussed to death, but
wouldn't sync after every transaction be advisable? Then you know
once a transaction is committed it can't be lost...


Yes, this has been discussed before. Everything would be terribly slow 
(sometimes nearly unusable), and the advantage is questionable. You 
would probably lose less transactions when there is a power failure, 
but what would that gain you? And you might still lose some 
transactions, as fsync doesn't always work as expected (see the docs). 
I think the current behavior is acceptable for most use cases (you can 
lose up to about 1 seconds of transactions).


Regards,
Thomas

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 startup error

[Ryan How]

Hi,

What args are you actually putting into svrArgs? Perhaps on that machine 
svrArgs is incorrect?



On 30/01/2014 7:18 AM, Subhash Agrawal wrote:

Hi All,

We are using H2 database in embedded mode and use 
Server.createTcpServer(svrArgs).start() to get instance of h2 server. 
It works fine for us but for some reason, on one particular windows 
machine, we see following error.


[2014-01-02 13:17:10,081] ERROR IO Exception: java.io.IOException: 
org.h2.message.DbException: Error while creating file // 
[90062-170]; port: 8382 ssl: true 
[90031-170]org.h2.jdbc.JdbcSQLException: IO Exception: 
java.io.IOException: org.h2.message.DbException: Error while creating 
file // [90062-170]; port: 8382 ssl: true [90031-170]



Is there particular reason, we can see this error while getting h2 
instance? I can take a look in error manual if you can point me there.


--
Regards,

Subhash Agrawal


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 startup error

[Ryan How]

That sounds like it might be possible. Or the users home dir is not set 
correctly? I'm not familiar enough with H2 codebase to know what it 
does, you'd probably have to look into the source or someone else can 
probably help you :).


If you try it as a different user on the same machine I guess it works?

Only other thing I can think of is some JVM version or settings or 
something?, but I imagine the best bet is to find the line in the source 
where the error is from to see exactly what it is trying to create and 
why it is failing.



On 30/01/2014 9:51 AM, Subhash Agrawal wrote:

Hi,

I am using following svrArgs. This is used in all the machines, 
including this.


-tcpSSL, -tcpPort, 8392 -tcpAllowOthers

I also noticed that h2 creates .h2.keystore file in user home 
directory. It could be some user permission issue on the file system. 
Any pointer or confirmation on this?



On Wed, Jan 29, 2014 at 4:55 PM, Ryan How r...@exemail.com.au 
mailto:r...@exemail.com.au wrote:


Hi,

What args are you actually putting into svrArgs? Perhaps on that
machine svrArgs is incorrect?



On 30/01/2014 7:18 AM, Subhash Agrawal wrote:

Hi All,

We are using H2 database in embedded mode and use
Server.createTcpServer(svrArgs).start() to get instance of h2
server. It works fine for us but for some reason, on one
particular windows machine, we see following error.

[2014-01-02 13:17:10,081] ERROR IO Exception:
java.io.IOException: org.h2.message.DbException: Error while
creating file // [90062-170]; port: 8382 ssl: true
[90031-170]org.h2.jdbc.JdbcSQLException: IO Exception:
java.io.IOException: org.h2.message.DbException: Error while
creating file // [90062-170]; port: 8382 ssl: true
[90031-170]


Is there particular reason, we can see this error while getting
h2 instance? I can take a look in error manual if you can point
me there.

-- 
Regards,


Subhash Agrawal


-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.




--
Regards,

Subhash Agrawal

Blog: http://investingbytrend.blogspot.com/
gmail: subas11us
Yahoo: subas11us
Skype: subas11
msn: subas11
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Clarification on h2.lobInDatabase deprecation

[Ryan How]

On 28/01/2014 3:41 AM, forkj...@gmail.com wrote:
# Storing the content yourself on the filesystem and link the content to 
a path column on a database table?


I do the latter in my project. But you lose any transactional 
consistency unless you create a complex mechanism. So I'm sure I have a 
few lingering BLOBS that aren't referenced anywhere :)


Works well for me. It makes backups a million times quicker...

--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Hardware recommendations for H2 database ?

[Ryan How]

On 17/01/2014 3:29 AM, Thomas Mueller wrote:

Hi,

 from my experience I can propose to use SSDs for the storage.

Yes, that's a very good point. SSDs are much faster, specially for 
reading, and for writing larger blocks.


SSD has much better random read and write, which is awesome for 
databases. If you can afford SSDs and you want performance, it's a definite.




I think the RAID system does not give a large perfomance improvement 
although it is good to protect against hardware failures.


I agree. A RAID system doesn't help H2 a lot (for the current version 
of H2 - with the MVStore it could speed up reads I think).


RAID mirror will give better read speed as it can read from 2 drives at 
once, but H2 won't do concurrent reads anyway, so probably won't give 
you much improvement for random reads.


RAID 5/6 isn't good for performance.



As far as I have understood H2 performs a file.sync() after each 
transaction.


Actually, not for each transaction, but once in a while, yes.



A raid controller with battery backup will ignore disk flushes as it can 
safely flush the cache on power failure. So will give better performance.


Or with ZFS you can use a SSD to handle synchronous writes, so they are 
effectively as fast as asynchronous writes meaning the sync won't slow 
it down.


On a side note, it's probably been discussed to death, but wouldn't sync 
after every transaction be advisable? Then you know once a transaction 
is committed it can't be lost...



Regards,
Thomas


On Thu, Jan 16, 2014 at 10:46 AM, Uli u.wiel...@gematronik.com 
mailto:u.wiel...@gematronik.com wrote:


Hi,

from my experience I can propose to use SSDs for the storage. They
have gained a great performance improvement for my application.

uneducated infoI think the RAID system does not give a large
perfomance improvement although it is good to protect against
hardware failures. As far as I have understood H2 performs a
file.sync() after each transaction. This requires that the RAID
controller empties its write cache after each transaction and
therefore the performance is not better as using the disk without
RAID./uneducated info But beware: maybe this is totally wrong
and I have observed a different effect.

Uli
-- 
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%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Putting the H2 database file on a Java NIO2 based, custom filesystem.

[Ryan How]

I think Thomas would know this.

But my gut feeling is no. I think it uses Java 6, so I can't imagine it 
using the Java 7 APIs.


But it does have pluggable filesystems, so you could probably write your 
own Java 7 NIO2 plugin, then use the java 7 filesystems under that. But 
I have no experience in that area.



On 14/01/2014 12:09 PM, Harry wrote:

Hello... anybody there?!

Would greatly appreciate if someone in the community or in the H2 
development team could respond at their earliest. There's no way I can 
read and understand the H2 codebase and give myself an authoritative, 
100% correct answer.


Thanks.

On Monday, January 13, 2014 11:21:47 AM UTC+5:30, Harry wrote:

Hi,

Does the latest H2 driver use the Java 7 NIO2 API which supports
custom file-systems?

For example, if I have a custom file-system identified by
'MyScheme:/', and which allows access to regular files and folders
sitting on it using the NIO2 API, then would H2 support a
connection URL of the form jdbc:h2:MyScheme:/path/to/my.h2.db,
where  '/path/to/my.h2.db' is the H2 database sitting on the
custom file-system?

Just in case H2 doesn't support this right now, my next question is:
  1. Is there any plan to supporting this in near future?
  2. Is there any workaround available in the meantime?

Thanks in advance,
/HS

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Only show changes in GROUP BY

[Ryan How]

If the timestamps are unique you could try including a subquery which 
gets the next lowest date


eg. (not sure if this is valid SQL syntax, I'm not meant to be on the PC 
:) Select data, datetimefield, (select top 1 datetimefield from table1 
where datetimefield  A.datetimefield order by datetimefield desc) as 
prevdatetime from table1 as A order by datetimefield


then you can use the previous date/time to calc whether to show the 
date/time for not






On 12/01/2014 1:06 PM, Atul Chowdhury wrote:
No, I'm afraid not.  What's your final output? If its a report, you 
may want to consider a reporting tool ( Jasper/BIRT/etc ) to satisfy 
this requirement.


On Sat, Jan 11, 2014 at 8:49 AM, Cecil Westerhof 
cldwester...@gmail.com mailto:cldwester...@gmail.com wrote:


2014/1/11 Atul Chowdhury atulsmail...@gmail.com
mailto:atulsmail...@gmail.com

You're probably after something like the BREAK directive found
in SQL*Plus.
See:
http://docs.oracle.com/cd/A84870_01/doc/sqlplus.816/a75664/ch42.htm
SQL BREAK ON DEPTNO
SQL SELECT DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE SAL  2500
  4  ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL
-- --- -
10 CLARK2450
   MILLER   1300
20 SMITH 800
   ADAMS1100
30 ALLEN1600
   JAMES 950
   TURNER   1500
   WARD 1250
   MARTIN   1250


Yes, that is exactly what I want. This is not possible in H2?

-- 
Cecil Westerhof
-- 
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%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Only show changes in GROUP BY

[Ryan How]

Wouldn't you usually do that in your reporting software? Or on any layer 
on top of the database?


In MS Access I've done it using a custom function which stores the last 
value and then only outputs the next value if it is different. Not sure 
how to do that in H2 ? Probably via a custom function again?


I'm not sure of other ways to make 1 row aware of the previous rows 
value so you can run a calculation off it.


Can you put it into a temp table with row numbers, then join the temp 
table to itself in a query to get the previous rows date value, then run 
a calculation off that whether to display the date or not?



On 10/01/2014 5:43 AM, Cecil Westerhof wrote:

I have the following SQL:
SELECT   date  AS Date
,SUBSTRING(time, 1, 2) AS Hour
,MIN(idleTime) AS `Min Idle`
,MAX(idleTime) AS `Max Idle`
,COUNT(*)  AS SUM
FROM vmstatDefault
GROUP BY Date
,Hour
ORDER BY Date
,Hour
;

This works fine, but I would prefer to only have Date shown when it is 
different as the previous Date. Is this possible?


--
Cecil Westerhof
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Exception when using MULTI_THREADED=1

[Ryan How]

I've been using it in a production environment for several years without 
issue. But it isn't very high concurrency, so I've probably just been 
lucky that it hasn't had issues.


On 2/01/2014 4:39 PM, Uli wrote:

Hi Noel,

what a pity! Thanks for digging into the synching problems!

It sounds to me as if MULTI_THREADED=1 should never be used and its 
usage is strongly inadvisable. Can you add some strong warnings to the 
documentation so that other users do not fall into the same trap?


Thanks!
Uli


Unfortunately, it proves to me that our MULTI_THREADED=1 support
is not at all safe.

For example, in the stacktrace below:

...

The layers up to org.h2.command.CommandContainer are synchronized
around the Session object.
And the layers from org.h2.store.PageStore down are synchronized
around the PageStore object.

But the layers in between have no synchronization, but they are
shared between multiple sessions.

I am afraid I have no idea how to fix this, and it looks like
quite a big job.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 to create, serialize, distribute, and deserialize a sub-database?

[Ryan How]

On 2/01/2014 6:40 PM, Thomas Mueller wrote:

 Would be nice if there was some java in-memory file abstraction

I don't understand, you can already use the file system abstraction of 
H2. You can also use databases that persist to an in-memory file 
system, using the database URL jdbc:h2:memFS:test or 
jdbc:h2:memLZF:test. See also 
http://h2database.com/html/advanced.html#file_system




I meant that because RUNSCRIPT reads from a file or a URL, it would be 
good to be able to pass it file object that just points to an in memory 
string. Nothing H2 specific, just a way to use the java file API for 
accessing custom data structures.


So he could do something like SCRIPT TO mem:myobject COMPRESSION GZIP, 
which would put the compressed data into some byte[] and then transfer 
it, and then RUNSCRIPT mem:myobject COMPRESSION GZIP which reads from 
the byte array. Would save all the custom compressing / uncompressing 
and manually executing the SQL statements. Not that it would be hard...


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 to create, serialize, distribute, and deserialize a sub-database?

[Ryan How]

Wow, I didn't realise you could use it out of the context of a H2 
connection URL. That is pretty awesome :)


Thanks, Ryan

On 2/01/2014 7:39 PM, Thomas Mueller wrote:

Hi,

 SCRIPT TO mem:myobject COMPRESSION GZIP

You can do that already, using the H2 file system abstraction:

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import org.h2.store.fs.FilePath;
import org.h2.util.IOUtils;
public class TestSimpleDb {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection(
jdbc:h2:mem:test);
conn.createStatement().execute(
create table test(id int) as select 1);
conn.createStatement().execute(
script to 'memFS:script.sql');
InputStream in = FilePath.get(
memFS:script.sql).newInputStream();
byte[] script = IOUtils.readBytesAndClose(in, -1);
System.out.println(new String(script, UTF-8));
}
}

Or you can just run script and process the result set.

Regards,
Thomas





On Thu, Jan 2, 2014 at 12:14 PM, Ryan How r...@exemail.com.au 
mailto:r...@exemail.com.au wrote:


On 2/01/2014 6:40 PM, Thomas Mueller wrote:

 Would be nice if there was some java in-memory file abstraction

I don't understand, you can already use the file system
abstraction of H2. You can also use databases that persist to an
in-memory file system, using the database URL jdbc:h2:memFS:test
or jdbc:h2:memLZF:test. See also
http://h2database.com/html/advanced.html#file_system



I meant that because RUNSCRIPT reads from a file or a URL, it
would be good to be able to pass it file object that just points
to an in memory string. Nothing H2 specific, just a way to use the
java file API for accessing custom data structures.

So he could do something like SCRIPT TO mem:myobject COMPRESSION
GZIP, which would put the compressed data into some byte[] and
then transfer it, and then RUNSCRIPT mem:myobject COMPRESSION GZIP
which reads from the byte array. Would save all the custom
compressing / uncompressing and manually executing the SQL
statements. Not that it would be hard...

-- 
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%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 to create, serialize, distribute, and deserialize a sub-database?

[Ryan How]

Sorry, I'm probably confusing you by getting off topic.

I think memLZF just stores it compressed in memory. To compress it for 
transfer over the wire you want to alter this line to say


conn.createStatement().execute(
script to 'memFS:script.sql COMPRESSION GZIP');

To read it back you need to do the opposite process, I assume you write 
to the file in-memory with something like


os = FilePath.get(memFS:script.sql).newOutputStream();

then write to the output stream to populate the in-memory file.

then

conn.createStatement().execute(runscript memFS:script.sql COMPRESSION 
GZIP);


I'm not sure how to clear the in-memory file to get that memory back or 
how long it lasts in memory?


You might be safer just using SCRIPT TO, read the script from the result 
set, then transfer, then execute the script manually without using 
RUNSCRIPT... This in-memory file stuff might just be complicating it, 
but I think it's pretty nifty! :)





On 2/01/2014 10:40 PM, Rob Oaks wrote:


Just to be clear (and keeping in mind that I'm new to H2!), to run the 
in-memory script would I use:


conn.createStatement().execute(runscript 
FilePath.get('memFS:script.sql'));


Can I also substitute memLZF for memFS so that the compressed version 
of the string is being sent over the wire?




On Thursday, January 2, 2014 6:39:21 AM UTC-5, Thomas Mueller wrote:

Hi,

 SCRIPT TO mem:myobject COMPRESSION GZIP

You can do that already, using the H2 file system abstraction:

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import org.h2.store.fs.FilePath;
import org.h2.util.IOUtils;
public class TestSimpleDb {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection(
jdbc:h2:mem:test);
conn.createStatement().execute(
create table test(id int) as select 1);
conn.createStatement().execute(
script to 'memFS:script.sql');
InputStream in = FilePath.get(
memFS:script.sql).newInputStream();
byte[] script = IOUtils.readBytesAndClose(in, -1);
System.out.println(new String(script, UTF-8));
}
}

Or you can just run script and process the result set.

Regards,
Thomas





On Thu, Jan 2, 2014 at 12:14 PM, Ryan How rh...@exemail.com.au
javascript: wrote:

On 2/01/2014 6:40 PM, Thomas Mueller wrote:

 Would be nice if there was some java in-memory file abstraction

I don't understand, you can already use the file system
abstraction of H2. You can also use databases that persist to
an in-memory file system, using the database URL
jdbc:h2:memFS:test or jdbc:h2:memLZF:test. See also
http://h2database.com/html/advanced.html#file_system
http://h2database.com/html/advanced.html#file_system



I meant that because RUNSCRIPT reads from a file or a URL, it
would be good to be able to pass it file object that just
points to an in memory string. Nothing H2 specific, just a way
to use the java file API for accessing custom data structures.

So he could do something like SCRIPT TO mem:myobject
COMPRESSION GZIP, which would put the compressed data into
some byte[] and then transfer it, and then RUNSCRIPT
mem:myobject COMPRESSION GZIP which reads from the byte array.
Would save all the custom compressing / uncompressing and
manually executing the SQL statements. Not that it would be
hard...

-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from
it, send an email to h2-database...@googlegroups.com
javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit
https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database

Re: [h2] H2 to create, serialize, distribute, and deserialize a sub-database?

[Ryan How]

On 2/01/2014 12:43 PM, Rob Oaks wrote:


Thank you both for your assistance.

Ryan, it’s comforting to know that someone has accomplished something 
similar, which inclines me to go ahead and build a prototype. I have 
just a couple more questions:


1.

I would prefer to avoid generating/transmitting a file, unless
there is a compelling architectural rationale, as that will
complicated the Web Service/queueing architecture. My plan would
be to SCRIPT the database to a string and compress the string
(e.g. using GZIPOutputStream). The string would then be sent via
RESTful service, queued, dequed, decompressed, and sent to
RUNSCRIPT to recreate the H2 database in-memory. Does this seem
reasonable (again remembering that the string is typically 10-30K
and will really never exceed 100K)?

If you run SCRIPT without a file, it returns the script in a result set. 
You can then get the string, zip it, BASE64 encode if you need, and do 
what you like with it.


1.

Rembering that I’m competely new to H2, I want to make sure H2
supports dynamic SCRIPT/RUNSCRIPT calls, not just command line
usage, and that your experience doing that has been positive.


Yes, you just issue the SQL command.

http://www.h2database.com/html/grammar.html#script
http://www.h2database.com/html/grammar.html#runscript

RUNSCRIPT accepts a file, so you probably just want to read your script 
in and execute each SQL command manually rather than using RUNSCRIPT. It 
says it can also accept a URL, but I'm not quite sure if you could 
utilise that in any way.


Script (and runscript) have built in support for compression, but I 
think that only works when writing to a file, not in-memory.


Would be nice if there was some java in-memory file abstraction, so you 
could just use the file support already in H2 and not have to worry 
about compressing and manually running the sql commands.



Anything else I should watch out for?




I think it is pretty straight forward :) and at a conceptual level seems 
pretty sound.  All the best.




--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] jdbc:h2:mem: threadsafe?

[Ryan How]

It will be 2. and the second create table will fail because the table 
already exists. But I don't see how that has anything to do with being 
thread safe? The in memory database is per process, so all threads 
access the same database. If you want a separate database per thread 
then give the databases different names.





On 31/12/2013 7:50 AM, Laird Nelson wrote:

On Tuesday, October 8, 2013 10:24:12 AM UTC-7, Thomas Mueller wrote:

I think the documentation is quite clear.


Let me rephrase the question (and sorry for just now noticing the 
timely reply):


Suppose I have two threads, t1 and t2.  Suppose each of them opens a 
connection to jdbc:h2:mem:  Suppose that each of them now issues a 
CREATE TABLE Fred statement.  Suppose further that each of them now 
inserts one row into the Fred table.


If t1 then issues a SELECT COUNT(*) FROM Fred, and if no other 
database activity takes place, will the result be /guaranteed/ to be 
1?  Or will it be 2?


Thanks,
Best,
Laird
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Very slow / read-only exception when connecting to a database the first time after moving it

[Ryan How]

Are you possibly copying the database while it is still open on system A?

On 11/12/2013 12:47 AM, Johann Petrak wrote:
I created a database (two files) on system A, then moved the files to 
system B and when I want to connect to that DB on system B, the first 
time just establishing the connection takes many minutes. But after 
that, every subsequent time the connection is established in less than 
a second. Also, if I attempt to connect to the database with 
;ACCESS_MODE_DATA=r appended to the URL the first time I get an 
exception complaining that the DB is read-only, but it is no problem 
the subsequent times.
So it seems as if the DB needs to write and re-organize something on 
the disk after I move it ... is there some explanation of what goes on 
here somewhere?

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] using H2 with JOOQ

[Ryan How]

I use it with H2 every day. Works great.

You are quite possibly pointing to an empty database as suggested...

Here is my jooq.xml file I use to generate the classes. Note that 
because the h2 database path is relative, I need to have the working 
directory set correctly.


?xml version=1.0 encoding=UTF-8 standalone=yes?
configuration xmlns=http://www.jooq.org/xsd/jooq-codegen-2.0.4.xsd;
  !-- Configure the database connection here --
  jdbc
driverorg.h2.Driver/driver
urljdbc:h2:glhbuildflowtemp/glhbuildflow/url
usersa/user
passwordsa/password
  /jdbc

  generator
!-- The default code generator. You can override this one, to 
generate your own code style

 Defaults to org.jooq.util.DefaultGenerator --
nameorg.jooq.util.DefaultGenerator/name

database
  !-- The database type. The format here is:
   org.util.[database].[database]Database --
  nameorg.jooq.util.h2.H2Database/name

  !-- The database schema (or in the absence of schema support, in 
your RDBMS this

   can be the owner, user, database name) to be generated --
  inputSchemaPUBLIC/inputSchema

  !-- All elements that are generated from your schema (several 
Java regular expressions, separated by comma)
   Watch out for case-sensitivity. Depending on your database, 
this might be important! --

  includes.*/includes

  !-- All elements that are excluded from your schema (several 
Java regular expressions, separated by comma).

  Excludes match before includes --
  excludes/excludes
/database

generate
  !-- Primary key / foreign key relations should be generated and 
used.

   This will be a prerequisite for various advanced features
   Defaults to false --
  relationstrue/relations

  pojostrue/pojos
  daostrue/daos

/generate

target
  !-- The destination package of your generated classes (within 
the destination directory) --

packageNameau.com.glhbuildflow.data.jooq/packageName

  !-- The destination directory of your generated classes --
directory./GLHBuildFlow/src/main/java/directory
/target
  /generator


/configuration



On 8/12/2013 3:30 PM, Christoph Läubrich wrote:
I used JOOQ with h2 in the past with succes, you really should post 
your configurationfile.
Take in mind, if you specify a non exiting (e.g. different path) h2 DB 
it is normally automatically created (but empty).


Am 07.12.2013 22:48, schrieb bruce buchanan:
ok I have my various files and jars and classpaths all in the right 
places... invocation of the codegenerator seems to be working...


but it doesnt really seem to find my schema--note below how it finds 
PUBLIC but PUBLIC seems to be empty, since it says tables fetched = 0?


the JOOQ website doesnt really claim they support H2, but if you say 
it does, that's good enough for me


meanwhile... thoughts?

thanks



Dec 7, 2013 1:38:07 PM org.jooq.tools.JooqLogger info
INFO: Initialising properties  : /ceo_codegen_cfg.xml
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: License parameters
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: --
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   Thank you for using jOOQ and jOOQ's code generator
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: Database parameters
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: --
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   dialect: H2
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   target dir : .
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   target package : db.jooq
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   includes   : [.*]
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   excludes   : []
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   includeExcludeColumns  : false
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: --
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: DefaultGenerator parameters
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO: --
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   strategy   : class 
org.jooq.util.DefaultGeneratorStrategy

Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   deprecated : true
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   generated annotation   : true
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info
INFO:   JPA annotations: false
Dec 7, 2013 1:38:08 PM org.jooq.tools.JooqLogger info

Re: [h2] remove database??help me

[Ryan How]

Are you using an in memory database? They don't persist so will be blank 
each time you open it.



On 3/12/2013 11:29 AM, ??? wrote:

Do not know why..
11:17:41,719 INFO  ~ Connected to 
jdbc:h2:tcp://10.1.1.222/~/test;CACHE_SIZE=131

072;CACHE_TYPE=SOFT_LRU;WRITE_DELAY=2000
11:17:41,747 INFO  ~ GAE environment detected
11:17:41,749 INFO  ~ Siena DB Type: sql:h2:mysql
11:17:42,131 INFO  ~ Table FT_INDEXES needs to be removed
11:17:42,137 INFO  ~ Table IGNORELIST needs to be removed
11:17:42,137 INFO  ~ Table MAP needs to be removed
11:17:42,137 INFO  ~ Table ROWS needs to be removed
11:17:42,137 INFO  ~ Table TEST needs to be removed
11:17:42,138 INFO  ~ Table WORDS needs to be removed
11:17:42,168 INFO  ~ Executed 6 SQL command(s) with 0 error(s)
11:17:42,197 INFO  ~ Application 'Yi' is now started !

msg: select * from test where id = '1'
Exception: Table TEST not found; SQL statement:
select * from test where id = '1' [42102-166]

application.conf

# Database configuration
db.default.driver=org.h2.Driver
# db.default.url=jdbc:h2:fs:play
db.default.url=jdbc:h2:mem:play
db.url=jdbc:h2:tcp://10.1.1.222/~/test;

 db.driver=org.h2.Driver
#db.driver=com.mysql.jdbc.Driver
 db.user=sa
 db.pass=





#source code

package controllers;

import play.*;
import play.mvc.*;

import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import play.db.DB;


//@With(Secure.class)
public class Application extends Controller {
//@Check(User)
public static void index() throws ClassNotFoundException {
System.out.println(start);
String SQLString;
Connection Con = DB.getConnection();
SQLString =  select *;
SQLString +=  from test;
SQLString +=  where id = ' + 1' ;
//SQLString = SHOW TABLES;;
System.out.println(msg:  + SQLString);
try{
Statement Stmt = Con.createStatement();
if(Stmt == null) {
System.out.println(Stmt is null);
}
ResultSet Result = Stmt.executeQuery(SQLString);
if(Result == null) {
System.out.println(Res is null);
}
Result.next();
System.out.println(Result.getString(ID));
if(Con != null) {
Con.close();
} else {
System.out.println(Con is null);
}
}catch(Exception e) {
System.out.println(Exception:  + e.getMessage());
}
render();
}
public static void index1(String name,String hidden) {
render(name,hidden);
}

}

Who can help meA connection will remove database??
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] A big file or many small files?

[Ryan How]

Not sure if I would choose H2 for a 2TB database. I don't think it has 
been tested too well with databases of that size. It would probably need 
a lot of RAM.


I'd be inclined to try smaller files rather than a single 2TB file. For 
instance backing up, or recovery of corrupt databases will be much 
better handled.


But it really depends on your use case. If it is just to do some number 
crunching, then a single file might be fine.




On 3/12/2013 1:14 PM, James Chan wrote:


I'm curious about the performance, wish to have your comments, thanks.

If my database will grow to 2TB, should I keep all data in only one 
file? or split the file into smaller pieces? (if 1GB per file, than 
there will be 2048 files). Or there's no big difference between these 
two types?



--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] remove database??help me

[Ryan How]

No idea. Must be an issue in your config. Try opening another connection
to your DB from the H2 console and see what happens to the tables.

On 3/12/2013 1:32 PM, 董藝興 wrote:
 NO,i setting file application.conf is connection
 http://www.h2database.com/html/features.html#embedded_databases
 database db.url=jdbc:h2:tcp://10.1.1.222/~/test
 http://www.google.com/url?q=http%3A%2F%2F10.1.1.222%2F%7E%2Ftestsa=Dsntz=1usg=AFQjCNFDzEbF338PEfjfJJ9EitJ9GD6lJQ;

 Kartweel於 2013年12月3日星期二UTC+8下午12時48分59秒寫道：

 Are you using an in memory database? They don't persist so will be
 blank each time you open it.


 On 3/12/2013 11:29 AM, 董藝興 wrote:
 Do not know why..
 11:17:41,719 INFO ~ Connected to
 jdbc:h2:tcp://10.1.1.222/~/test;CACHE_SIZE=131
 http://10.1.1.222/%7E/test;CACHE_SIZE=131
 072;CACHE_TYPE=SOFT_LRU;WRITE_DELAY=2000
 11:17:41,747 INFO ~ GAE environment detected
 11:17:41,749 INFO ~ Siena DB Type: sql:h2:mysql
 11:17:42,131 INFO ~ Table FT_INDEXES needs to be removed
 11:17:42,137 INFO ~ Table IGNORELIST needs to be removed
 11:17:42,137 INFO ~ Table MAP needs to be removed
 11:17:42,137 INFO ~ Table ROWS needs to be removed
 11:17:42,137 INFO ~ Table TEST needs to be removed
 11:17:42,138 INFO ~ Table WORDS needs to be removed
 11:17:42,168 INFO ~ Executed 6 SQL command(s) with 0 error(s)
 11:17:42,197 INFO ~ Application 'Yi' is now started !

 msg: select * from test where id = '1'
 Exception: Table TEST not found; SQL statement:
 select * from test where id = '1' [42102-166]

 application.conf

 # Database configuration
 db.default.driver=org.h2.Driver
 # db.default.url=jdbc:h2:fs:play
 db.default.url=jdbc:h2:mem:play
 db.url=jdbc:h2:tcp://10.1.1.222/~/test http://10.1.1.222/%7E/test;

 db.driver=org.h2.Driver
 #db.driver=com.mysql.jdbc.Driver
 db.user=sa
 db.pass=





 #source code

 package controllers;

 import play.*;
 import play.mvc.*;

 import java.sql.Connection;
 //import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.*;

 import play.db.DB;


 //@With(Secure.class)
 public class Application extends Controller {
 //@Check(User)
 public static void index() throws ClassNotFoundException {
 System.out.println(start);
 String SQLString;
 Connection Con = DB.getConnection();
 SQLString = select *;
 SQLString +=  from test;
 SQLString +=  where id = ' + 1' ;
 //SQLString = SHOW TABLES;;
 System.out.println(msg:  + SQLString);
 try{
 Statement Stmt = Con.createStatement();
 if(Stmt == null) {
 System.out.println(Stmt is null);
 }
 ResultSet Result = Stmt.executeQuery(SQLString);
 if(Result == null) {
 System.out.println(Res is null);
 }
 Result.next();
 System.out.println(Result.getString(ID));
 if(Con != null) {
 Con.close();
 } else {
 System.out.println(Con is null);
 }
 }catch(Exception e) {
 System.out.println(Exception:  + e.getMessage());
 }
 render();
 }
 public static void index1(String name,String hidden) {
 render(name,hidden);
 }

 }

 Who can help meA connection will remove database??
 -- 
 You received this message because you are subscribed to the
 Google Groups H2 Database group.
 To unsubscribe from this group and stop receiving emails from it,
 send an email to h2-database...@googlegroups.com javascript:.
 To post to this group, send email to h2-da...@googlegroups.com
 javascript:.
 Visit this group at http://groups.google.com/group/h2-database
 http://groups.google.com/group/h2-database.
 For more options, visit https://groups.google.com/groups/opt_out
 https://groups.google.com/groups/opt_out.

 -- 
 You received this message because you are subscribed to the Google
 Groups H2 Database group.
 To unsubscribe from this group and stop receiving emails from it, send
 an email to h2-database+unsubscr...@googlegroups.com.
 To post to this group, send email to h2-database@googlegroups.com.
 Visit this group at http://groups.google.com/group/h2-database.
 For more options, visit https://groups.google.com/groups/opt_out.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Questions about MVCC

[Ryan How]

This is interesting :)

Does MVCC even support different isolation modes?. I thought it was it's 
own isolation mode? The different isolation modes don't seem to make 
sense in an MVCC context?


Thanks, Ryan


On 24/11/2013 11:50 PM, cowwoc wrote:

Hi Thomas,

It was my understanding that SERIALIZABLE isolation in MVCC mode is 
supposed to behave like SERIALIZABLE_SNAPSHOT. At least, that's the 
case for Postgres. If SERIALIZABLE behaves the way you mention in MVCC 
mode, what is the difference if MVCC mode is on or off? It doesn't 
sound like there is a difference.


Gili

On 24/11/2013 7:49 AM, Thomas Mueller wrote:

Hi,

 How does SERIALIZABLE isolation in MVCC mode compare to 
SERIALIZABLE_SNAPSHOT? Aren't they the same thing?


I can only refer to Google, which found this: 
http://blogs.msdn.com/b/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx


Regards,
Thomas




On Sun, Nov 24, 2013 at 1:10 PM, cowwoc cow...@bbs.darktech.org 
mailto:cow...@bbs.darktech.org wrote:


Hi Thomas,

Thanks for the reply.


On 21/11/2013 6:43 AM, Thomas Mueller wrote:

Does H2 implement Serializable Snapshot

Currently not, and I didn't plan to support it. In theory, it
could be supported with the MVStore, but I don't currently think
it's very important.


How does SERIALIZABLE isolation in MVCC mode compare to
SERIALIZABLE_SNAPSHOT? Aren't they the same thing?

Thanks,
Gili




Regards,
Thomas



On Sat, Aug 24, 2013 at 8:10 PM, Gili cow...@bbs.darktech.org
mailto:cow...@bbs.darktech.org wrote:

Thomas,

Can you please clarify how H2's implementation of MVCC
interacts with Transaction Isolation?

 1. Is there a practical difference between READ_COMMITTED
and REPEATABLE_READ in MVCC mode? Doesn't MVCC prevent
READ_COMMITTED from seeing updates committed by other
transactions (after the current transaction has already
read the row once)? If so, aren't they identical?
 2. Same question for REPEATABLE_READ and SERIALIZABLE
isolation in MVCC mode.
 3. Does H2 implement

http://en.wikipedia.org/wiki/Serializable_Snapshot_Isolation#Serializable_Snapshot_Isolation
 for
MVCC? If not, do you plan on doing so in the future?

Thank you,
Gili


On Sunday, June 23, 2013 11:23:16 AM UTC-4, Gili wrote:


Thanks Thomas. So you're saying I can use other
isolation modes with MVCC as I normally would?

Gili

On 23/06/2013 7:39 AM, Thomas Mueller wrote:

Hi,

H2 only supports row level locks when using the MVCC
mode. By the way, the plan is that in the future (with
the MVStore), the MVCC mode will be the default mode.

Insert/delete operations don't wait for other
operations that do not conflict. They wait for
conflicting operations (changes on the same rows).

 What happens in the following scenario?

H2 uses read committed by default, so T1 will see the
value from T2.

Regards,
Thomas




On Thu, Jun 20, 2013 at 8:18 PM, Gili
cow...@bbs.darktech.org
mailto:cow...@bbs.darktech.org wrote:

Hi,

Looking at
http://www.h2database.com/html/advanced.html#mvcc,

 1. Does H2 only support table locks or MVCC? Or is
there a way to get row locks without MVCC?
 2. Using MVCC, do insert/delete operations wait
until all open transactions complete?
 3. Does this imply that open transactions will
never experience insert/delete rows by other
threads in mid-transaction?
 4. What happens in the following scenario?

Database contains a single row: count[value=1]
T1: Open transaction
T2: Open transaction
T2: update count set value=2 where value=1
T2: commit
T1: select value from count

Will T1 see a value of 1 or 2?

Thanks,
Gili
-- 
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%2bunsubscr...@googlegroups.com.
To post to this group, send email to
h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at
http://groups.google.com/group/h2-database.
   

Re: [h2] Move database files after shutdown

[Ryan How]

Do you have any backup software running? On occasion I get backup 
software locking files while it backs them up and it stops me deleting 
the file. And I've had anti-virus do the same, but that was mainly with 
zip files...



On 11/11/2013 8:21 PM, Mike Goodwin wrote:

Hi Ryan,

Thanks for the answer. It is helpful to have confirmation that it can 
be done normally. Pretty sure there are no open connections as I have 
been counting them in and out (as they open/close), and I don't have 
the close delay set (and call shutdown in any case).


I guess I need to start from the simplest possible case and get that 
working first. I have had other problems with deleting files from java 
on windows 7.


- mike





On Mon, Nov 11, 2013 at 5:16 AM, Ryan How r...@exemail.com.au 
mailto:r...@exemail.com.au wrote:


The database will close when all connections to it are closed. So
you must still have some connections open (Are you using a
connection pool??)

Also see http://h2database.com/html/grammar.html#set_db_close_delay

Is there still the lock file present?. If that is still present
then there is an open connection somewhere

I close and open my databases all the time from the same JVM and
copy them around, so it does work.


On 11/11/2013 8:13 AM, Mike Goodwin wrote:

Hi,

I have found I am unable to move the database directory after
a shutdown. I have tried waiting, gc(), calling for a shutdown
(using the SHUTDOWN sql command). I have made sure there are
no more connections open.

Is there any expectation that it should be possible to close a
database after shutting it down? Has anyone succeeded in doing so?

- mike
-- 
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%2bunsubscr...@googlegroups.com.
To post to this group, send email to
h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



-- 
You received this message because you are subscribed to the Google

Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database+unsubscr...@googlegroups.com
mailto:h2-database%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Move database files after shutdown

[Ryan How]

The database will close when all connections to it are closed. So you 
must still have some connections open (Are you using a connection pool??)


Also see http://h2database.com/html/grammar.html#set_db_close_delay

Is there still the lock file present?. If that is still present then 
there is an open connection somewhere


I close and open my databases all the time from the same JVM and copy 
them around, so it does work.


On 11/11/2013 8:13 AM, Mike Goodwin wrote:

Hi,

I have found I am unable to move the database directory after a 
shutdown. I have tried waiting, gc(), calling for a shutdown (using 
the SHUTDOWN sql command). I have made sure there are no more 
connections open.


Is there any expectation that it should be possible to close a 
database after shutting it down? Has anyone succeeded in doing so?


- mike
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: updates much slower than inserts

[Ryan How]

No Worries :). If you run Explain Analyze update tre_combinedtestday set 
compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416, 
dollaramount=0.9453345900094259 where id=230827 what do you get?




On 29/10/2013 2:12 PM, TrendTimer.com wrote:
Kartweel, I forgot to say thanks for your reply.  Ormlite has an 
option to perform rawQueries. So I changed it to use this. Here's the 
query and the execution time:



query=update tre_combinedtestday set 
compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416, 
dollaramount=0.9453345900094259 where id=230827

updateTime2=86



On Monday, October 28, 2013 10:50:56 PM UTC-7, TrendTimer.com wrote:


Oh right, I forgot to mention that I was using Ormlite for the
updates.  Maybe Ormlite is at fault?  I'm pretty certain it's just
doing an update xxx set a=1, b=2 where id=12345 kind of update.


On Monday, October 28, 2013 10:20:47 PM UTC-7, Kartweel wrote:

Is that hibernate? I haven't used it for so long I forget the
method names... You probably need to run it through a profiler
(
http://h2database.com/html/performance.html#built_in_profiler
http://h2database.com/html/performance.html#built_in_profiler )
and see what it is doing. It could be doing optimistic locking
checks or multiple queries or anything. At the minimum you
need to find out what SQL it is executing so you can run
explain on it (
http://h2database.com/html/grammar.html#explain
http://h2database.com/html/grammar.html#explain ) to see if
it is using the index or doing a table scan or anything.

Hope that helps a little bit :)

Ryan

On 29/10/2013 1:00 PM, TrendTimer.com wrote:

adding CACHE_SIZE=32768 to the jdbc url helped speed up the
updates to 6 ms.  So this is better, but still a lot slower
that the inserts.  I'm timing the inserts like this:


long startTime=System.currentTimeMillis();
try {
getDao().createOrUpdate(combinedTestDay);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
long endTime=System.currentTimeMillis();
long totalTime=endTime-startTime;
System.out.println(createOrUpdateTime=+totalTime);


and the output is always:

createOrUpdateTime=0


On Monday, October 28, 2013 9:32:23 PM UTC-7, TrendTimer.com
wrote:

When I use createOfUpdate, I can insert 1 rows in
the table almost immediately.

getDao().createOrUpdate(combinedTestDay);


but then when I try to update these same items, it's
taking around 70 ms each:

getDao().update(saveObject);

So for 10,000 updates  that's 70,000ms or around 70 seconds.

Can someone explain what might be happening?  Does anyone
know how to work around this issue?  I'd appreciate your
help! thanks,

Stephen Gower




-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from
it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at
http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit
https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: updates much slower than inserts

[Ryan How]

Have you got an index on the ID column?, it doesn't appear to be using 
an index. Usually you would have ID as the primary key which would 
create an index...



On 29/10/2013 3:17 PM, TrendTimer.com wrote:
Explain Analyze update tre_combinedtestday set 
compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416, 
dollaramount=0.9453345900094259 where id=230827


returns this:

UPDATE PUBLIC.TRE_COMBINEDTESTDAY
/* PUBLIC.TRE_COMBINEDTESTDAY.tableScan */
/* scanCount: 74005 */
SET
COMPOUNDPERCENT = -13.373487168551002,
TOTALPERCENT = -5.466540999057416,
DOLLARAMOUNT = 0.9453345900094259
WHERE ID = 230827
/*
total: 5087
TRE_COMBINEDTESTDAY.TRE_COMBINEDTESTDAY_DATA read: 5087 (100%)
*/






I switched to jdbc to test this:

PreparedStatement prep = con.prepareStatement(update 
tre_combinedtestday set compoundpercent=?, totalpercent=?, 
dollaramount=? where id=?);

//insert 10 row data
long startTime=System.currentTimeMillis();
for (int i = 0; isaveObjects.size(); i++){
System.out.println(ii=+i);
CombinedTestDay saveObject=saveObjects.get(i);
prep.setDouble(1, saveObject.getCompoundpercent());
prep.setDouble(2, saveObject.getTotalpercent());
prep.setDouble(3, saveObject.getTotalpercent());
prep.setInt(4, saveObject.getId());
//batch insert
//prep.addBatch();
prep.executeUpdate();
}
long endTime=System.currentTimeMillis();
long totalTime=endTime-startTime;
System.out.println(totalTime=+totalTime);


The above performed around 5000 updates. I wasn't able to capture the 
totalTime because the console buffer wasn't large enough, but I was 
able to count along while this line incremented:


System.out.println(ii=+i);

At the rate I was observing, i estimate it took around 4-5 minutes for 
these update to complete











On Monday, October 28, 2013 11:34:57 PM UTC-7, Kartweel wrote:

No Worries :). If you run Explain Analyze update
tre_combinedtestday set compoundpercent=-13.373487168551002,
totalpercent=-5.466540999057416, dollaramount=0.9453345900094259
where id=230827 what do you get?



On 29/10/2013 2:12 PM, TrendTimer.com wrote:

Kartweel, I forgot to say thanks for your reply.  Ormlite has
an option to perform rawQueries. So I changed it to use this.
Here's the query and the execution time:


query=update tre_combinedtestday set
compoundpercent=-13.373487168551002,
totalpercent=-5.466540999057416, dollaramount=0.9453345900094259
where id=230827
updateTime2=86



On Monday, October 28, 2013 10:50:56 PM UTC-7, TrendTimer.com wrote:


Oh right, I forgot to mention that I was using Ormlite for
the updates.  Maybe Ormlite is at fault?  I'm pretty certain
it's just doing an update xxx set a=1, b=2 where id=12345
kind of update.


On Monday, October 28, 2013 10:20:47 PM UTC-7, Kartweel wrote:

Is that hibernate? I haven't used it for so long I forget
the method names... You probably need to run it through a
profiler (
http://h2database.com/html/performance.html#built_in_profiler
http://h2database.com/html/performance.html#built_in_profiler
) and see what it is doing. It could be doing optimistic
locking checks or multiple queries or anything. At the
minimum you need to find out what SQL it is executing so
you can run explain on it (
http://h2database.com/html/grammar.html#explain
http://h2database.com/html/grammar.html#explain ) to
see if it is using the index or doing a table scan or
anything.

Hope that helps a little bit :)

Ryan

On 29/10/2013 1:00 PM, TrendTimer.com wrote:

adding CACHE_SIZE=32768 to the jdbc url helped speed up
the updates to 6 ms.  So this is better, but still a lot
slower that the inserts.  I'm timing the inserts like this:


long startTime=System.currentTimeMillis();
try {
getDao().createOrUpdate(combinedTestDay);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
long endTime=System.currentTimeMillis();
long totalTime=endTime-startTime;
System.out.println(createOrUpdateTime=+totalTime);


and the output is always:

createOrUpdateTime=0


On Monday, October 28, 2013 9:32:23 PM UTC-7,
TrendTimer.com wrote:

When I use createOfUpdate, I can insert 1 rows
in the table almost immediately.

getDao().createOrUpdate(combinedTestDay);


but then when I try to update these same items, it's
taking around 70 ms each:

getDao().update(saveObject);

So for 10,000 updates  that's 70,000ms or around 70
seconds.

 

Re: [h2] org.h2.tools.XXXX NOT FOUND!

[Ryan How]

Can you type in the dir command and show the directory listing, then 
the java -cp h2*.jar etc command and copy paste the output here?.


On 24/10/2013 5:58 PM, sheing3003 wrote:

Hello All,

  When I tried to do jave -cp h2*.jar org.h2.tools.Server  (or any
others like Shell), windows command prompt returns cannot find
org.h2.tools.. Please help. 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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] org.h2.tools.XXXX NOT FOUND!

[Ryan How]

I you can paste the entire output and directory listing, I'm not sure 
what you are doing. It seems like a basic java classpath issue. Nothing 
H2 related.


On 25/10/2013 12:17 AM, sheing3003 wrote:

  Could not find the main class: org.h2.tools.Server in 
C:\user\shiang\project\activiti-5.8\apps\h2\h2.start.bat
I originally was using activiti demo, but it didn't work
But even if I run the h2 directly with java using java h2*.jar -cp 
org.h2.tools.server it won't work




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] threads and where .. in queries

[Ryan How]

I quite often use a huge IN clause as a quick way to combine lots of 
small queries. I'm fairly sure it uses the index (although I must admit 
I haven't checked). But it usually speeds up the queries considerably. 
Much faster than lots of small queries. Even in embedded mode for me.



On 8/10/2013 5:20 AM, Brian Craft wrote:
Sorry, the 120 sec is including some application code that is 
apparently slower than I expected. The h2 trace log shows 9 sec for 
the query. There is an index on the column, but it doesn't appear to 
be used for the IN clause with 500 values. explain analyze shows it 
scanning 50k rows or so.


I'll try the TABLE() thing instead of the big IN clause.

On Monday, October 7, 2013 12:33:36 PM UTC-7, Thomas Mueller wrote:

Hi,

 a where ... in clause with 500 values (like where x in [v0,
v1, v2,  v499]), which ran with average time 120 seconds.

120 seconds for 500 entries sounds like a problem with the data or
the query. Do you have an index on that column? See also
http://h2database.com/html/performance.html#explain_plan
http://h2database.com/html/performance.html#explain_plan

Regards,
Thomas


On Sun, Oct 6, 2013 at 6:29 AM, Brian Craft craft...@gmail.com
javascript: wrote:

Reading over the archive on the subject of threads has left me
mostly still confused about how h2 handles concurrency, so
I've been doing some tests, instead.

I started with largish queries that all have a where ... in
clause with 500 values (like where x in [v0, v1, v2, 
v499]), which ran with average time 120 seconds.

I then re-ran the same queries as sets of 500 queries (each
with a single value, like where x in [v0]), splitting the
queries across different threads. These sets of 500 queries
ran much faster, average 30 seconds. It also made much more
use of the disk: io stats where much higher, which is
consistent with the queries running concurrently.

The docs seem to indicate that a query in one thread blocks
queries in other threads, so I'm not sure how to interpret
these numbers. Maybe I misunderstood the docs, and queries do
run concurrently? I'm not using MULTI_THREADED.

-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from
it, send an email to h2-database...@googlegroups.com
javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit
https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Known issues with large databases

[Ryan How]

The larger the database the more RAM you need generally. It depends what 
you mean by large. I have databases of a few GB that run without very 
high memory usage, but if you start running large transactions it 
requires more memory.


So it is probably more how you use the database rather than the size of 
it that will cause performance / stability (mainly due to OOM) issues.


On 18/09/2013 4:08 PM, Federico Dal Castello wrote:
Should I suppose that there are no known performance issues on large 
databases? Has anyone experienced such kind of issues now or in the past?


Thank you
Federico
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] strategies for large transactions

[Ryan How]

Good job tracking it down.

Regarding the tools for debugging. No ideas from me sorry, I need to get 
better at using those kinds of things.


On 12/09/2013 12:03 PM, Brian Craft wrote:
Following up on this, after resolving the memory problems in my code, 
h2 handles my 1G transaction.


Tracking down the memory problems was enormously painful. jmap/jhat 
provided better information than visualvm, but they were unreliable: 
jhat would randomly refuse to read the heap dumps generated by jmap, 
and it would consistently freeze up when tracing particular objects. I 
suspect it was incorrectly handling circular dependencies in these cases.


Are there any better tools for jvm memory debugging?

On Tuesday, September 10, 2013 1:21:51 PM UTC-7, Brian Craft wrote:

Well, that was way more informative than visualvm. The top three
items, amounting to 2G:

 num #instances #bytes  class name
--
   1: 70466 1104237992  [C
   2:  45338856 1088132544  java.lang.Float
   3: 1544435  403100896  [Ljava.lang.Object;

I don't believe any of this is h2. There are no floats in the
commit, and the char arrays in the commit are less than 6M. So my
current guess is that I'm leaking something, though I don't
understand how splitting it into multiple transactions is allowing
it to work.

On Tuesday, September 10, 2013 12:51:00 PM UTC-7, Brian Craft wrote:

I do it with a bunch of inserts. The tables already exist. It
happens even if I use UNDO_LOG=0, which I think disables the
undo log.

I'll try jmap.

On Tuesday, September 10, 2013 11:50:43 AM UTC-7, Thomas
Mueller wrote:

Hi,

It depends on how you do the import. Could you get a heap
histogram for a large import (jmap -histo:live pid), and
could you tell us how you do the import exactly?

If you use create table ... as select then it can't run
out of memory (as far as I know), because no undo log is
needed.

If you execute many insert or update statements, then the
undo log is used, and at least a few bytes are needed per
record (the actual data is stored to disk at some point,
but the position of the data is not). So in theory it
could run out of memory, even thought the transaction
would need to be huge.

By the way, with the new MVStore storage (not production
ready yet), there should no longer be a limit on the size
of a transaction, as no undo log is needed.

Regards,
Thomas






On Tue, Sep 10, 2013 at 10:51 AM, Ryan How
rh...@exemail.com.au wrote:

Howcome it is getting OOM on import? It shouldn't do
that should it? I've imported several GB of data
before, I did get OOM, but increasing heap size to
around 1GB worked for me. I didn't need to go to crazy
sizes...


On 10/09/2013 4:32 PM, Noel Grandin wrote:


On 2013-09-06 20:15, Brian Craft wrote:

I need to load about 1G of data into an
existing db, while maintaining data coherence.
Wrapping the inserts in one transaction
results in out-of-memory problems in the jvm.
I increased the max heap size to 8g w/o
improvement. I can split it into a bunch of
smaller commits, which works fine, but then on
error I need a bunch of application code to
delete the transactions which succeeded. The
deletes will need their own transactions,
which could also fail.

Is there any better way to do this?


Not really.
One strategy would be to copy the DB, since with
H2 it's just a single file, and then run your
import process.
If it fails, just replace the modified DB with the
backup.



-- 
You received this message because you are subscribed

to the Google Groups H2 Database group.
To unsubscribe from this group and stop receiving
emails from it, send an email to
h2-database...@googlegroups.com.
To post to this group, send email to
h2-da...@googlegroups.com.
Visit this group at
http://groups.google.com/group/h2-database
http://groups.google.com/group/h2-database.
For more options, visit
https://groups.google.com/groups

Re: [h2] strategies for large transactions

[Ryan How]

Howcome it is getting OOM on import? It shouldn't do that should it? 
I've imported several GB of data before, I did get OOM, but increasing 
heap size to around 1GB worked for me. I didn't need to go to crazy sizes...


On 10/09/2013 4:32 PM, Noel Grandin wrote:


On 2013-09-06 20:15, Brian Craft wrote:
I need to load about 1G of data into an existing db, while 
maintaining data coherence. Wrapping the inserts in one transaction 
results in out-of-memory problems in the jvm. I increased the max 
heap size to 8g w/o improvement. I can split it into a bunch of 
smaller commits, which works fine, but then on error I need a bunch 
of application code to delete the transactions which succeeded. The 
deletes will need their own transactions, which could also fail.


Is there any better way to do this?



Not really.
One strategy would be to copy the DB, since with H2 it's just a single 
file, and then run your import process.

If it fails, just replace the modified DB with the backup.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Data loss with H2 HA / Clustered Mode

[Ryan How]

On 6/09/2013 11:48 PM, Daniel Stone wrote:


We did also experience many problems with a “missing lob” error which 
actually triggered the cluster issue in the first place.  We have 
managed to work around this but is this still a bug with H2 (I have 
seen this problem mentioned on the web)?




Are you using MVCC mode by any chance?

--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Corrupted H2 Database

[Ryan How]

It sounds like your configuration is corrupted. It cannot find the H2 
driver. Just make sure you still have the H2 driver on the classpath.


On 4/09/2013 9:47 PM, James Ball wrote:
I have been using Jubula to programme test cases and during the middle 
of one Jubula froze and would not respond. I had to forcibly close the 
application and once trying to connect to the Embedded H2 database I 
was greeted with an error message. Unfortunately, the error window 
disappeared quickly, so I was not able to paste the exact message but 
I was able to take a video of the and snapshot the message and then 
typed it below:

*'Database: Connecting...' has encountered a problem.*
*
*
*Exception [EclipseLink-4002] (Eclipse Persistence Services - 
2.3.2.v2025-r10461):*

*org.eclipse.persistence.exceptions.DatabaseException*
*Internal Exception: java.sql.SQLException: *
*No suitable driver found for jdbc:h2:~/.jubula/database/*
*embedded;*
*MVCC=TRUE;AUTO_SERVER=TRUE;DB_CLOSE_ON_EXIT=FALSE*
*Error Code= 0*

I am unsure of what to do, and I don't know if there is a way to fix 
it. However, if anyone can help to fix it it would be greatly 
appreciated!

Many Thanks,

James.
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] How to query whether a table exists?

[Ryan How]

works for me.

you want IS NOT NULL, IS NULL instead of = null

This is what I did

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FILE_RESOURCE'

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FILE_RESOURCE';
TABLE_CATALOGhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 
	TABLE_SCHEMAhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	TABLE_NAMEhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	TABLE_TYPEhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	STORAGE_TYPEhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	SQLhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	REMARKShttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	LAST_MODIFICATIONhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	IDhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	TYPE_NAMEhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	TABLE_CLASShttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc# 	ROW_COUNT_ESTIMATEhttp://192.168.2.61:55348/query.do?jsessionid=2c1cff9dfed2a757fca5c68b2d1ee6dc#
GLHBUILDFLOW 	PUBLIC 	FILE_RESOURCE 	TABLE 	CACHED 	CREATE CACHED TABLE 
PUBLIC.FILE_RESOURCE(
FILE_RESOURCE_ID LONG DEFAULT (NEXT VALUE FOR 
PUBLIC.SYSTEM_SEQUENCE_23C5B2D5_89BA_406C_935E_9CC776D79E73) NOT NULL 
NULL_TO_DEFAULT SEQUENCE 
PUBLIC.SYSTEM_SEQUENCE_23C5B2D5_89BA_406C_935E_9CC776D79E73 SELECTIVITY 100,

FILE_NAME VARCHAR_IGNORECASE(255) NOT NULL SELECTIVITY 66,
PARENT_FILE_RESOURCE_ID LONG SELECTIVITY 55,
FILE_RESOURCE_ROOT_ID LONG SELECTIVITY 11,
DIRECTORY BOOLEAN NOT NULL SELECTIVITY 22,
DATE_MODIFIED TIMESTAMP SELECTIVITY 77,
FILE_LENGTH LONG SELECTIVITY 88,
MD5_SUM VARCHAR_IGNORECASE(255) SELECTIVITY 11
)   
0   109 /null/  org.h2.table.RegularTable   10

(1 row, 1 ms)



On 22/08/2013 1:44 PM, sandstones wrote:
I found a post at 
http://h2-database.66688.n3.nabble.com/Best-practice-Test-for-existence-of-table-etc-td4024451.html


 which said that the correct method was:   select count(*) from 
information_schema.tables where table_name = ?


However, I cannot get any results in doing such a search if I have a 
where clause in the statement.


I have been testing this from the H2 web console and driving my self nuts

If I use SELECT * FROM INFORMATION_SCHEMA.TABLES  I get all the tables
If I use any kind of where clause on any of the columns I get zero 
rows. For example


where table_name='name'
where table_name = null
where table_name ! =null
where table_name like 'name'
where table_type != null

Is there any way to modify the query to get this to work?
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.


--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] H2 database and connection pool

[Ryan How]

Sounds like you're on the right path. It's usually good practice to have 
a connection per thread. A H2 connection is thread safe, but I don't 
think that is the case for all DBs. But anyway, a connection per thread 
is better, because then if you turn on multithreaded mode for H2 you 
will get better concurrency. If all your threads share a single 
connection then they will synchronise on the connection, so only 1 
thread will be able to use it at a time.


Just make sure your threads return the connection to the pool when they 
have finished with it.


Depending on your particular case, you might not get any performance 
improvement, sometimes a single thread is better. You really need to 
test and see what works better for your particular case.


HTH Ryan


On 14/08/2013 9:34 AM, Shiva wrote:
I am having multiple threads processing the data and querying against 
H2 database each thread might query h2 2,000,000 times.
I am thinking if each thread has it own H2 connection will it improve 
the performance Vs having single connection for all threads?

I am thinking of using the connection pool in the following way.
JdbcConnectionPool.create(memoryConnectionString, userName, password);

Any help is appreciated.
Thanks
Shiva
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Version for production use?

[Ryan How]

On 21/07/2013 1:03 AM, Thomas Mueller wrote:
But still, there is a risk. This was one reason for me to start 
working on the new storage engine MVStore: to reduce the risk of 
corruption (the MVStore is quite a bit simpler than the current, 
traditional storage engine). It is still work in progress however.


If it is simpler, are there are drawbacks to the new MVStore?

--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] mydb.trace.db despite TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0

[Ryan How]

If you look inside the zip file of your backup are there trace files in 
there?


On 18/07/2013 4:14 PM, EDV-Services wrote:

Hi Thomas,

I thought the same, I thought it could be a bug in my application.
Then I restored my latest backup file by using the web interface of H2 
by clicking on Tools - Restore - Start.
And the result was the same: Trace-output of error messages in 
trace.db-file even if I remove the trace file before starting to restore.
That's why I offered you to send you my backup file so you can try it 
with the web interface, too.


I'm 100% sure, it has nothing to do with my application.
The only mistake I could have done was when I created my database the 
first time and then accessed it without the trace = 0 parameters in my 
db url.
I guess H2 saved the trace parameters |TRACE_LEVEL_SYSTEM_OUT| = 1 and 
|TRACE_LEVEL_FILE| = 1 (default) inside the database files so changing 
the url parameters later won't affect anything.


Nevertheless, thank you for your reply again.

Best regards,
Michael



Am Mittwoch, 17. Juli 2013 18:35:14 UTC+2 schrieb Thomas Mueller:

Hi,

 I definitely use the h2-1.3.172.dll file.

Yes, I understand.

I understand that my trace-db file contains old dates but I don't
understand why

Well, the most simple explanation is that those files are old
files. Probably your application restored them from somewhere (I
think I wrote that already). You need to figure out why this
happens. This is your application, and I can't really help you
there. This is not a problem of H2.

I would like to send you my backup file, so you can try to restore
the file on your machine.

Sorry, I'm not quite sure how this would help. It seems you need
to figure out what's wrong with your application, why it restores
old .trace.db files. This is your application, and I'm not able to
help you with that.

Regards,
Thomas





On Wed, Jul 17, 2013 at 12:50 PM, EDV-Services
edvservi...@web.de wrote:

Hi Thomas,

thank you again for your reply.

I definitely use the h2-1.3.172.dll file. I've downloaded
the jar file some days ago and I used IKVM to transform the
jar file into a dll file to be able to use H2 in my .NET project.
I understand that my trace-db file contains old dates but I
don't understand why as I don't know anything about the
internal H2 db processes.
All I can say is that I delete the trace-file before doing a
restore and then that output is created.


 One explanation is that you restore old files over existing
files

Thats true. In my application you have a database running and
you can do backups and restores whenever you want.
When I do a restore, the database already exists. I don't
delete the database before I restore an old backup file.
Is that the problem?

Also true is, that before I haven't used the same URL as now.
As I told you, I've added the two parameters
TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0; later.
Maybe H2 saves the url parameters into the database files on
first database connection so if you add these two parameters
to your connection URL later, then they won't affect the trace
output anymore.

I would like to send you my backup file, so you can try to
restore the file on your machine.
Are you okay with that? If yes, then please tell me your email
address.

Best regards
Michael


Am Dienstag, 16. Juli 2013 08:42:16 UTC+2 schrieb Thomas Mueller:

Hi,

I understand that it doesn't work for your case, but I'm
afraid I can't help you much because it works for me, and
so far you didn't provide enough information to reproduce
the problem.

Just a few things I noticed:

* You wrote you use the latest version of H2, but in fact
the latest error message you got is from an older version
(1.3.170). Reason: the build number is included in the
error code, which is [42001-170]. So the build is 170.

* The .trace.db file contains old dates. You wrote you
reproduce the problem now, but the error message starts
with  07-09 12:51:43. So it clearly was written at
2013-07-09 and not July 15th. The same with older messages.

* You seem to backup and possibly restore files. One
explanation is that you restore old files over existing files.

* My guess is that you didn't always use the database URL
you provided.

That's all I can say right now. I don't think this is a
bug in H2.

Regards,
Thomas



On Monday, July 15, 2013, EDV-Services wrote:

Sorry again, now I deleted 

Re: [h2] Avoiding corruptions in frequent unclean-shutdown scenario

[Ryan How]

I shut it down about 50 times a day uncleanly during development and 
never had an issue. As the database gets larger startup time takes 
longer. I guess as it checks the database. It all happens automatically.


I can't vouch for ext4, I've had a lot of corruptions on that (not H2, 
but just the system in general). So in terms of the entire system going 
down and not just killing the H2 process, I'm not sure.


On 7/07/2013 5:40 PM, Germano Rizzo wrote:

Hi all,
 we're about to implement a project that uses H2 on board of some 
RFID readers, for a steel factory. The database will be used to store 
RFID readings, so we will write few datas relatively often (2 
records/second, maybe).


Problem is, being an embedded scenario, the readers will usually go 
down without a proper, clean shutdown, at a frequency of 3-4 times per 
day. There's really no way to avoid the possibility that the power-off 
happens during a write.


OS is Linux, with a proper journaled filesystem (EXT4) stored on a 
micro-sd card; CPU is an Atom processor. Supplier assures me that as 
far as the operating system is concerned, this scenario is O.K.


Which is the best way to avoid corruptions of H2, detect them and 
recover from them, in a programmatical way? No user interaction should 
be required.


In particular:

- Which url parameters should we use? is LOG=2 enough?
- Which version should we use? 172, nightly, wait for the next one? 
Project will be rolled out at 20 of july.
- Will it make any difference if I create indexes? Does them increase 
the exposed area for corruptions?
- Which is the worst-case scenario for this? I'm ok losing the last 
few writes.


Thanks a lot,

   Germano
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Missing lob, lob: null table, when selecting CLOB for update (MVCC setting)

[Ryan How]

Hi,

Yeah this doesn't work. And nobody is currently supporting MVCC until 
the new Storage engine is out. So options are: Don't use MVCC. Don't use 
For Update with LOBS. Try and fix H2 code.


Also, Other people have noted problems with lots of LOBS and MVCC when 
not using for update.


Ryan


On 24/06/2013 4:20 PM, Juan Miguel Cejuela wrote:

I'm getting the following error:

org.h2.jdbc.JdbcSQLException: IO Exception: java.io.IOException:
org.h2.message.DbException: IO Exception: java.io.IOException:
org.h2.jdbc.JdbcSQLException: IO Exception: Missing lob: 7
[90028-170] [90028-168]; lob: null table: -1 id: 7 [90031-168]


When I do a select for update of a row with a CLOB column.

Facts:

  * I added the CLOB column to an existing table.
  * The table and the database were created with h2 version 168.
  * The server is currently using h2 version 172.
  * I use MVCC
  * If I do a normal select (without for update), it works fine.


What is the cause of the error and how can I solve it?
Should I drop the MVCC setting? (although I want it...)
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Limiting database size on disk

[Ryan How]

But it does re-use space. So if you delete data, then insert new data, 
the database won't grow, it will overwrite the deleted data.


Please correct me if I am wrong.

On 14/06/2013 2:33 PM, Noel Grandin wrote:
You can use DISK_SPACE_USED to get the approx amount of space used by 
a table

http://h2database.com/html/functions.html#disk_space_used

But we don't do online shrinkage, so you're going to have to do a 
shutdown cycle to recover any space.


On 2013-06-13 17:38, krupti...@gmail.com wrote:
I'm using h2 1.3.170 as a cache in an application that runs on 
Windows and Linux. Some users have disk quotas and run into various 
issues when the database file gets too large. Ideally I'd like to be 
able to query the database to determine how much data it contains, 
and delete old records as necessary to get the database size below 
some threshold.


I see that Functions to calculate the memory and disk space usage of 
a table, a row, or a value. is on the Roadmap and thus is not 
supported yet. I know I can query for info.PAGE_SIZE and 
info.PAGE_COUNT to get how much space the database is using on disk, 
but that will not change as I delete data, so I can't tell how much 
I've cleaned up. I could guess at how much data to delete, shutdown 
compact, re-open the database, and check the size, but that could 
cause a noticeable delay for the user.


I'd like to avoid doing my own tracking of how much data I put into 
the database because it will slow down my operations and complicate 
my codebase.


What's the best way to deal with this problem?





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] why one query at a time

[Ryan How]

By Default H2 run single threaded, so when a statement is executing, it 
blocks everything else. See 
http://h2database.com/html/features.html#multiple_connections


You can turn on multithreaded mode if you really need the concurrency, 
or have single long running statements and don't want to block 
everything else. Append MULTI_THREADED=1 to the connect URL, but it is 
not as well tested as single threaded mode.


Generally you get better performance in single threaded mode where the 
disk IO is the limiting factor.






On 13/06/2013 12:51 AM, tsukasa wrote:
h2 database executes only 1 SELECT query at a time, even when 
autocommit is ON, blocking everything else, why is that? it's not 
going to change? to an outsider it looks like a simple feature. --
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: NPE when shutting H2 down after integration tests run

[Ryan How]

The stack trace indicates version 1.3.170 ?


On 3/06/2013 5:51 AM, Martin Lichtin wrote:

We are using 1.3.172 and have seen a very similar NPE.

2013-06-01 14:48:14,035 [Atomikos:1 ] WARN XAResourceTransaction - XA 
resource 'JDBC-H2RuleExecCamelITest': rollback for XID 
'3137322E32322E33302E37332E746D30303030313030303137:3137322E32322E33302E37332E746D31' 
raised 0: unknown
javax.transaction.xa.XAException: General error: 
java.lang.NullPointerException; SQL statement:

ROLLBACK [5-170]
at 
org.h2.jdbcx.JdbcXAConnection.convertException(JdbcXAConnection.java:397)
at 
org.h2.jdbcx.JdbcXAConnection.rollback(JdbcXAConnection.java:282)
at 
com.atomikos.datasource.xa.XAResourceTransaction.rollback(XAResourceTransaction.java:703)
at 
com.atomikos.icatch.imp.RollbackMessage.send(RollbackMessage.java:70)
at 
com.atomikos.icatch.imp.PropagationMessage.submit(PropagationMessage.java:110)
at 
com.atomikos.icatch.imp.Propagator$PropagatorThread.run(Propagator.java:87)
at 
com.atomikos.icatch.imp.Propagator.submitPropagationMessage(Propagator.java:66)
at 
com.atomikos.icatch.imp.CoordinatorStateHandler.rollback(CoordinatorStateHandler.java:700)
at 
com.atomikos.icatch.imp.ActiveStateHandler.onTimeout(ActiveStateHandler.java:102)
at 
com.atomikos.icatch.imp.CoordinatorImp.alarm(CoordinatorImp.java:930)
at 
com.atomikos.timing.PooledAlarmTimer.notifyListeners(PooledAlarmTimer.java:113)
at 
com.atomikos.timing.PooledAlarmTimer.run(PooledAlarmTimer.java:100)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:722)
Caused by: org.h2.jdbc.JdbcSQLException: General error: 
java.lang.NullPointerException; SQL statement:

ROLLBACK [5-170]
at 
org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

at org.h2.message.DbException.get(DbException.java:158)
at org.h2.message.DbException.convert(DbException.java:281)
at org.h2.command.Command.executeUpdate(Command.java:234)
at 
org.h2.jdbc.JdbcConnection.rollbackInternal(JdbcConnection.java:1423)

at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:464)
at 
org.h2.jdbcx.JdbcXAConnection.rollback(JdbcXAConnection.java:269)

... 13 more
Caused by: java.lang.NullPointerException
at org.h2.table.RegularTable.removeRow(RegularTable.java:345)
at org.h2.engine.UndoLogRecord.undo(UndoLogRecord.java:98)
at org.h2.engine.Session.rollbackTo(Session.java:529)
at org.h2.engine.Session.rollback(Session.java:506)
at 
org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:49)
at 
org.h2.command.CommandContainer.update(CommandContainer.java:75)

at org.h2.command.Command.executeUpdate(Command.java:230)
... 16 more

On Thursday, September 20, 2012 8:12:03 PM UTC+2, Thomas Mueller wrote:

Hi,

Does it happend with the latest version as well?

Regards,
Thomas

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: H2 database and Excel VBA

[Ryan How]

Have you got the pgserver running in H2 ? on Port 5432?

From memory the default Postgres port  default H2 pgserver port might 
be different...


On 3/06/2013 10:41 AM, Balaji M wrote:


Hi,
 When i try to connect through DSN less  connection  i am getting 
error pls help


/Could not Connect to the Server;
No connection could be made because the target machinne actively  
refused it .

[127.0.0.1:5432]
/
*Connection string*
/ODBC;DSN=;SERVER=localhost;DATABASE=GE2;UID=admin;PWD=Testing;DRIVER={PostgreSQL 
Unicode}/


Thanks  - Balaji

On Tuesday, March 23, 2010 2:25:24 AM UTC+8, Paluee wrote:

Hi there,

I know this is a Java based database.
But is there a way using Excel VBA using ADO to create and
use H2 database.

I would like to use H2 database as a backend for Excel app, because
Excel is a good data entry GUI for the work that I do.

Paluee

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: NPE when shutting H2 down after integration tests run

[Ryan How]

Sorry, so do you mean you are still getting a very similar stack in 
1.3.172?, You just don't have a record of it?


On 3/06/2013 3:12 PM, Martin Lichtin wrote:
Yeah, you're right. I already updated the version, but the stack 
indeed came from 1.3.170.


On Monday, June 3, 2013 8:58:13 AM UTC+2, Kartweel wrote:

The stack trace indicates version 1.3.170 ?

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: H2 database and Excel VBA

[Ryan How]

When you start the PGServer you can also set the port it is listening on.

[-pgPort port]The port (default: 5435)



On 3/06/2013 3:55 PM, Balaji M wrote:

Hi ,
I am new to the H2 ...

For ODBC DSN connection by default the port no is 5435 ..it is working 
fine 

For ODBC DSN less Connection by default the port no is 5432..

How to check server is running in specific port ?

Thanks  Regards
Balaji


On Monday, June 3, 2013 3:00:13 PM UTC+8, Kartweel wrote:

Have you got the pgserver running in H2 ? on Port 5432?

From memory the default Postgres port  default H2 pgserver port
might be different...

On 3/06/2013 10:41 AM, Balaji M wrote:


Hi,
 When i try to connect through DSN less  connection  i am getting
error pls help

/Could not Connect to the Server;
No connection could be made because the target machinne actively 
refused it .

[127.0.0.1:5432 http://127.0.0.1:5432]
/
*Connection string*

/ODBC;DSN=;SERVER=localhost;DATABASE=GE2;UID=admin;PWD=Testing;DRIVER={PostgreSQL
Unicode}/

Thanks  - Balaji

On Tuesday, March 23, 2010 2:25:24 AM UTC+8, Paluee wrote:

Hi there,

I know this is a Java based database.
But is there a way using Excel VBA using ADO to create and
use H2 database.

I would like to use H2 database as a backend for Excel app,
because
Excel is a good data entry GUI for the work that I do.

Paluee

-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database...@googlegroups.com javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at
http://groups.google.com/group/h2-database?hl=en-US
http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2]

[Ryan How]

I imagine you could probably change the source of the shell tool fairly 
easily to change the max length, or make it configurable as a command 
line option?


And also make header output optional.

Or you could probably strip the header by piping it through some awk 
command or something (I'm a bit rusty on my unix!)


You could probably just make your own version of the shell tool that way 
you could upgrade H2 and retain compatibility, or submit a patch :)




On 3/06/2013 8:58 PM, Cecil Westerhof wrote:

At the moment I use:
 java -cp /home/cecil/java/h2/bin/h2-1.3.171.jar org.h2.tools.Shell …

To get the result of a query in BASH. But the results of the query are
limited to 100 characters per column. Is there a way to get the
complete result?

Less important, but icing on the cake: is it possible to get rid of the header?




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Missing LOB issue

[Ryan How]

I'm using MVCC and the only problems I've come across appear to be with 
LOBS (that's ok for me, don't really need them for my use case).


What I mean is that it may have been less work for you to try and fix 
the H2 source (Seeing the issues seem to all be with LOBS) rather than 
change to hsqldb (And there would have been other people benefit from 
that also). Isn't that meant to be one of the good things about open source?


I know, I get it from my programmers all the time (They say there is an 
issue with the library, so they make their own or work around it, which 
usually doesn't work very well anyway), I tell them to fix it upstream! 
that is the whole point!


And it is getting lots of attention, the entire file store is being 
re-written to accommodate for MVCC right from the start!, that is why 
they haven't been giving attention to maintaining the current 
experimental MVCC mode (Please correct me if I am wrong)




On 30/05/2013 2:30 PM, Noel Grandin wrote:


On 2013-05-30 00:34, Jason Pell wrote:
We finally got sick of this issue and migrated to HSQLDB for MVCC and 
no more missing lob issues.  We still use H2, but only for storing 
configuration stuff that does not need to be updated concurrently.  
It's a shame more attention has not been shown to this issue, because 
it was certainly frustrating having to change over to HSQLDB.


It's an open-source project, so I guess the problem would be yours for 
failing to provide a fix.


(Nobody on this project, to my knowledge, is being paid to work on it).

Now, if you want to provide a test-case for your non-MVCC table lock 
timeout problem, I could maybe help you there, but the MVCC code is 
still experimental, so I'm not touching that, especially since we're 
going to drop it when Thomas' new MVStore engine reaches maturity.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: H2, LibreOffice and Java

[Ryan How]

I'd try the connection pooling :). I imagine that means it keeps a pool 
of connections open to the database underneath, then when it needs a 
connection in the GUI there is always one available.


I'd imagine the connection string would be the same, connection pooling 
would be handled by LibreOffice?


On 30/05/2013 3:36 PM, Noel Grandin wrote:


On 2013-05-30 09:28, Jorge Alberch wrote:


No, I don't think so. I'm checking the LibreOffice Base - Connections 
settings and I have the Connection pooling enabled unchecked... 
however, since you mention it... would it be a good idea to have 
checked instead? But then which driver would I select? the JDBCDriver?



Sorry, I have no idea how the LibreOffice Base stuff works.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: How do I allow TCP connections from specified IP addresses only?

[Ryan How]

Hi,

You can't currently do that in H2. You need to do it in the firewall on 
the machine.


Ryan


On 26/05/2013 12:33 PM, Jay Lee wrote:

Hi All,

I am using H2DB in TCP server mode.
The only thing missing or I can't find is that
how to block clinet connection based on IP address.

For example if I want to allow only several clients in my subnet
, 192.168.0.7 and 192.168.0.8 to connect H2DB.
How do I do that?

Currently I start the TCP server with -tcpAllowOthers option.

Server.createTcpServer(new String[]{
-baseDir, /h2db
, -tcpPort, 19083
, -tcpAllowOthers
}).start();

Thanks,

Jinhee
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Weird H2 Database error

[Ryan How]

I'm seeing 171 on the 2nd line and 169 on the 4th line?


On 24/05/2013 11:25 PM, Thomas Mueller wrote:

Hi,

The error message Caused by: java.lang.NoClassDefFoundError: 
org/h2/engine/Constants  doesn't say much about the state of the 
database file. All it says is that you have a classloading problem. 
For example you have multiple version of the H2 jar file in your 
classpath (at least H2 version 1.3.169 from 2012-09-09, but possibly 
another one as well).


Regards,
Thomas


On Fri, May 24, 2013 at 5:21 PM, Sammy Guergachi sguerga...@gmail.com 
mailto:sguerga...@gmail.com wrote:


So we have a database hosted on a networked drive.

One day I tried to connect to H2 and got this error via the
browser tool:

General error: java.lang.NoClassDefFoundError:
org/h2/engine/Constants; SQL statement:
SELECT UPPER(VALUE) FROM INFORMATION_SCHEMA.SETTINGS WHERE
NAME='MODE' [5-171]

http://192.168.30.113:8082/tables.do?jsessionid=dc43494160387c9488a2572beb0e0753#
 HY000/5
org.h2.jdbc.JdbcSQLException: General error:
java.lang.NoClassDefFoundError: org/h2/engine/Constants; SQL
statement:
SELECT UPPER(VALUE) FROM INFORMATION_SCHEMA.SETTINGS WHERE
NAME='MODE' [5-169]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:158)
at org.h2.message.DbException.convert(DbException.java:277)
at org.h2.command.Command.executeQuery(Command.java:195)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:308)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:149)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoClassDefFoundError: org/h2/engine/Constants
at org.h2.table.MetaTable.generateRows(MetaTable.java:859)
at org.h2.index.MetaIndex.find(MetaIndex.java:45)
at org.h2.index.BaseIndex.find(BaseIndex.java:102)
at org.h2.index.IndexCursor.find(IndexCursor.java:145)
at org.h2.table.TableFilter.next(TableFilter.java:322)
at org.h2.command.dml.Select.queryFlat(Select.java:513)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:618)
at org.h2.command.dml.Query.query(Query.java:307)
at org.h2.command.dml.Query.query(Query.java:277)
at org.h2.command.dml.Query.query(Query.java:36)
at org.h2.command.CommandContainer.query(CommandContainer.java:86)
at org.h2.command.Command.executeQuery(Command.java:191)
... 3 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:568)
at
org.h2.command.CommandRemote.executeQuery(CommandRemote.java:149)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:76)
at org.h2.server.web.DbContents.readContents(DbContents.java:111)
at org.h2.server.web.WebApp.tables(WebApp.java:662)
at org.h2.server.web.WebApp.process(WebApp.java:219)
at org.h2.server.web.WebApp.processRequest(WebApp.java:164)
at org.h2.server.web.WebThread.process(WebThread.java:137)
at org.h2.server.web.WebThread.run(WebThread.java:93)
at java.lang.Thread.run(Unknown Source)

I'm pretty new to using H2, and havent been able to find this sort
of error anywhere online, so any help as to what the issue might
be would be great.
We can't access any of the tables in the database, so it seems
corrupted, We have a backup but we would like to make sure this
sort of corruption doesn't occur again.

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
mailto:h2-database%2bunsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com
mailto:h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: H2 in-memory database with MVCC enabled - what are the risks?

[Ryan How]

I've been using MVCC for quite a while. It appears pretty stable, but 
there is a bug with FOR UPDATE and LOBS. (It doesn't work), so there are 
still bugs. Furthermore MVCC is not really supported by anyone, so if 
you come across bugs you'll probably need to fix them yourself.


I think once the new MVStore is operational then MVCC will be much 
better supported??


Ryan


On 23/05/2013 7:51 PM, Rami Ojares wrote:
I think everyone reading this list would like to hear a well-grounded 
answer to this question.

But I quess no one is capable of answering questions of this magnitude.

- Rami

On 23.5.2013 14:45, aditya wrote:

Hi,

I wanted to know if it is recommended to use the MVCC parameter with 
an in-memory h2 database and

are there any risks involved if it is used in production environment?

We have some scenarios wherein concurrent access to DB is required 
and if MVCC is not used,
it results in error 'timeout trying to lock table' (even on SELECT 
queries and in cases where we have nested transactions).


We are using H2 version 1.2.147 but if required we can switch to the 
latest version.
However, even the latest H2 documentation says that MVCC feature is 
still experimental.


And are there any other alternatives to MVCC, to achieve better 
concurrency in such situations? A quick response would be highly 
appreciated.


Thanks,
Aditya
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, 
send an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.







--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: How to remove Thousands separator from decimal

[Ryan How]

Hi,

Wouldn't that indicate each value is in a new column, being a CSV file?
I'd imagine in your export process to get the CSV you'd need to not 
export the commas ?


Ryan

On 23/05/2013 3:30 AM, pkod...@uic.edu wrote:

Hi,

I would like to know if there is any in built function to remove 
thousands separator from a decimal.I'm trying to dump a CSV file into 
a H2 database table. I have a number

7,960,800 which has to be inserted into column of type decimal. --
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: How to remove thousands separator from decimal type?

[Ryan How]

I don't know of anything inbuilt. You could dump it into a varchar 
column, remove all the commas (SET field = REPLACE(field, ' ')), then 
alter column type to decimal?


Ryan


On 23/05/2013 3:41 AM, Rnj wrote:
I'm trying to import a csv file into a h2 database table. I have a 
number 786,960,000 to be inserted into decimal column. Is there any 
inbuilt function in h2 database to remove thousand separator in H2? --
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: IDENTITY column increments even after error in previous SQL statement

[Ryan How]

Hi,

That is how the identity columns work. Also if a failed transaction then 
they will still increment. If the database is not closed correctly they 
may jump by a large number. There is no guarantee that they will be in 
increments of 1, just that they will be higher in value than the last.


If you need to guarantee that it increments by 1 you'll need to create 
your own mechanism for doing that.


I guess in the case of the failed statement below, internally H2 it 
still grabs the next value in the sequence to try the insert.


Hope that helps,

Ryan


On 22/05/2013 6:48 AM, Rustam Ismailov wrote:

Hi! I don't know is it a bug or feature, please explain.
Identity column is autoincremented, even previous statment completed 
with an error (so I expect ID column value will not incremented).


Example snippet:

drop table TEST_TABLE if exists;

create table TEST_TABLE (
ID IDENTITY NOT NULL PRIMARY KEY, -- this column value we will observe
REQ_FIELD VARCHAR2(32) NOT NULL,
NOT_REQ_FIELD VARCHAR2(32)
);

insert into TEST_TABLE (NOT_REQ_FIELD) values ('foo'); -- this will 
fail because of REQ_FIELD is still NULL - it is expected behavior, but 
identity value incremented inside H2!


insert into TEST_TABLE (REQ_FIELD) values ('test'); -- this will be 
executed fine, but ID value becomes 2 instead of (IMHO) expected 1, 
see select statament below

COMMIT;

select * from TEST_TABLE; -- we see 2 in ID column, but expected 1

H2 Database version 1.3.171

java -version:
java version 1.7.0_21
Java(TM) SE Runtime Environment (build 1.7.0_21-b11)
Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)

Windows 7 Ultimate x64 Russian.

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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Are deadlocks preventable?

[Ryan How]

Well you could get a global database lock with every transaction, it 
would kill concurrency, but you'd be ensured to never get a deadlock :P


Sorry, not very helpful :), but no-one else has replied on here :)


On 18/05/2013 7:58 AM, Gili wrote:

Hi,

I wanted to get your opinion 
regarding http://stackoverflow.com/a/112256/14731


Is it fair to say that database deadlocks are not preventable:

1. Using portable SQL, since the SQL standard does not specify lock 
ordering;
2. Using database-specific SQL code, because the execution plan of a 
database might change over time.


Thank you,
Gili
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Dropbox and H2

[Ryan How]

I'm not sure how dropbox handles open files, but regarding multiple 
files h2 should be fine as everything is in 1 file. If you have separate 
lobs or fulltext index that might be a problem. You could regenerate 
full text index, but if lobs are out of sync I don't know how it would 
handle it. So if you store lobs in the database it should be ok.


Ryan

On 10/05/2013 3:36 AM, JohanBerg wrote:

Thanks for the replies!

The possibility of file-conflicts is a problem for dropbox, not for 
H2, I think. If a file is changed from two computers at a time, 
dropbox will catch this, and H2 will still use the local copies. Or?


I'm more worried if the database-files would be corrupted if for 
example the database consists of more than one file and not all files 
are updated (due to lost internet connection), and another computer is 
accessing the half-updated database-files. Is there a way for H2 to 
handle this? I'm just trying to imagine what possible error situation 
there could be... The database shall not be accessed at the same time 
from different computers, but rather one at a time.


Thanks again,
Johan

On Wednesday, May 8, 2013 11:42:00 PM UTC+2, JohanBerg wrote:

Hi!

I wonder if anyone has experience with using H2 database-files on
a cloud-drive, for example on Dropbox? Is there known problems or
difficulties?

Best regards,
Johan

--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: H2 error when running.

[Ryan How]

Can you paste a stack trace of what error you are getting?

And what URL are you using to connect to the database?

Thanks, Ryan

On 8/05/2013 1:54 PM, khang phamngoc wrote:
I used H2DB for my project, well evrything seem good but 2 days ago 
when I'm using it, my computer shut down so when I connect to H2 
again,it throw error login...and I can't using it, some time I catch 
same action with H2 when my computer shut down while H2 is running.

who tell me what happen?
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Dropbox and H2

[Ryan How]

Dropbox just syncs a folder on the local PC.
So it depends on what you want to do with it?

If you want to use it on more than 1 PC at a time it won't work (You'll 
just get conflicts). Otherwise it should work ok I think. ? I'm not sure 
how dropbox accesses in use files. But it might only sync once you've 
closed the file (Which is what you'd want anyway probably?).






On 9/05/2013 5:42 AM, JohanBerg wrote:

Hi!

I wonder if anyone has experience with using H2 database-files on a 
cloud-drive, for example on Dropbox? Is there known problems or 
difficulties?


Best regards,
Johan
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: CPU utilization is 100% for huge records transaction

[Ryan How]

Sounds like you are running out of memory.

What is the memory usage of the java process? and what JVM parameters 
are you using?


You'd expect CPU to be maxed out for an in-memory database. Something 
would have to max out.



On 1/05/2013 9:40 PM, neelima kaliki wrote:

Below are my System details:
OS: Linux
Processors : 4
Memory: 8 GB
Below are my Application details:
Tables: In-Memory tables
Mode: Server/Embedded mode
Data: Arround 10 Million records
CPU utiliztion is more than 100% when a query is executing on huge 
records and the system just hangs on. After couple of hours, getting 
message as Database is closed and all my tables in Memory is getting 
dropped off. This keeps repeating every time. The problem is same in 
Server mode or Embedded mode.

Response is normal for less number records in the memory tables.
Please suggest to solve this issue.
Thanks,
Neelima.Kaliki
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: CPU utilization is 100% for huge records transaction

[Ryan How]

You can try it and see if it works.-Xmx7000m or something like that. But 
you may keep running out of memory. It might just be too much data to 
fit. You might be better off trying using a file based database on an SSD?


On 2/05/2013 12:03 AM, neelima kaliki wrote:
So, if i increase my memory from 8GB, would i be able to resolve this 
issue?

Thanks,
Neelima.Kaliki

On Wednesday, May 1, 2013 12:00:58 PM UTC-4, trh...@gmail.com wrote:

AFAIK you are limiting the memory your VM will ever know about to
4g with this:

JAVA_OPTS=-Xms512m -Xmx4076m

therefore this

Mem:   8174516k total,  3465712k used,  4708804k free,

does not mean that the VM could use the 4708804k you still have
free. In the eyes of the VM, you're out of memory:


On Wed, May 1, 2013 at 11:53 AM, neelima kaliki
neeli...@gmail.com javascript: wrote:

Thanks for your reply.
Below are the memory details of the linux box when the query
is executing.

Cpu(s): 25.0%us,  0.2%sy,  0.0%ni, 74.7%id,  0.0%wa, 0.0%hi, 
0.1%si,  0.0%st

Mem:   8174516k total,  3465712k used,  4708804k free, 14k
buffers
Swap:0k total,0k used,0k free,
1096344k cached
  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEM TIME+  COMMAND
 3478 kn40169   17   0 2409m 1.6g 9808 S 100.9 20.0 19:40.60 java
JVM parameters for the H2 is as below:

JAVA_OPTS=-Xms512m -Xmx4076m

I could not see any exceptions in the logs. There is almost
4GB free memory available in that box as well. Please guide me
further.

Thanks,

Neelima.Kaliki


On Wednesday, May 1, 2013 10:57:50 AM UTC-4, Kartweel wrote:

Sounds like you are running out of memory.

What is the memory usage of the java process? and what JVM
parameters
are you using?

You'd expect CPU to be maxed out for an in-memory
database. Something
would have to max out.


On 1/05/2013 9:40 PM, neelima kaliki wrote:
 Below are my System details:
 OS: Linux
 Processors : 4
 Memory: 8 GB
 Below are my Application details:
 Tables: In-Memory tables
 Mode: Server/Embedded mode
 Data: Arround 10 Million records
 CPU utiliztion is more than 100% when a query is
executing on huge
 records and the system just hangs on. After couple of
hours, getting
 message as Database is closed and all my tables in
Memory is getting
 dropped off. This keeps repeating every time. The
problem is same in
 Server mode or Embedded mode.
 Response is normal for less number records in the memory
tables.
 Please suggest to solve this issue.
 Thanks,
 Neelima.Kaliki
 --
 You received this message because you are subscribed to
the Google
 Groups H2 Database group.
 To unsubscribe from this group and stop receiving emails
from it, send
 an email to h2-database...@googlegroups.com.
 To post to this group, send email to
h2-da...@googlegroups.com.
 Visit this group at
http://groups.google.com/group/h2-database?hl=en
http://groups.google.com/group/h2-database?hl=en.
 For more options, visit
https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.




-- 
You received this message because you are subscribed to the

Google Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from
it, send an email to h2-database...@googlegroups.com
javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at
http://groups.google.com/group/h2-database?hl=en
http://groups.google.com/group/h2-database?hl=en.
For more options, visit
https://groups.google.com/groups/opt_out
https://groups.google.com/groups/opt_out.



--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
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 

Re: Is it possible to renew the execution plan for a view?

[Ryan How]

Strange that it took  10 minutes to reproduce it? That would almost be 
consistent with the sub-optimally executed view?


I'm wondering whether it might be a caching thing?

It's hard without a reproducible test case. I'm just guessing what it 
might be and trying to narrow down the circumstances.




On 2/05/2013 5:46 AM, Niko Paltzer wrote:


And if you re-create the view then you get the same execution plan
as running the select directly?


Yes.

Like, If you close and open the database does it speed it up?


No.

In the good select statement, it still has 2 huge table scans.
Does it really take  1 second??


Yes.

Also, you haven't got the scan count (Explain Analyze) for the view?


I once had it and it had a scanCount of 86 million for one or two 
joins. But when I tried to reproduce it, it took too long to wait at 
that time ( 10 min).

Best regards, Niko
--
You received this message because you are subscribed to the Google 
Groups H2 Database group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Is it possible to renew the execution plan for a view?

[Ryan How]

Wow, that isn't a simple query is it :)

Nothing seems obvious does it.

And if you re-create the view then you get the same execution plan as 
running the select directly?


I think you'll need someone much more knowledgeable than me helping. 
Hope you get it sorted! I'd only be trying different things to narrow 
down the issue.


Like, If you close and open the database does it speed it up?

In the good select statement, it still has 2 huge table scans. Does it 
really take  1 second??


/* scanCount: 130074 */
/* scanCount: 217116 */

Also, you haven't got the scan count (Explain Analyze) for the view?





On 23/04/2013 9:51 PM, Niko Paltzer wrote:

Hi Kartweel,

On Tuesday, April 23, 2013 10:37:25 AM UTC+2, Kartweel wrote:

If you can post the create view statement  running the select
manually, both with the execution plans, then it might provide
some insight?


you can find the statement and the two execution plans at the end of 
this post.


You also say if you drop and re-create the view then it is better?


Yes.

. Are you creating the view, then populating the tables, then
running the view?


Yes.

You could try analyze
http://h2database.com/html/grammar.html#analyze
http://h2database.com/html/grammar.html#analyze


I did and I observed changes in COLUMNS.SELECTIVITY but it did not 
affect the execution plan of the view.


Cheers, Niko


And here comes the code (due to company policies I had to do some 
obfuscation):


-- Statement

SELECT
TAB01.TAB03_ID AS ATT01
,TAB02.TAB03_ID AS ATT05
,TAB02.ATT02
,TAB02.ATT03
,TAG.ATT04 AS ATT06
,TAB02.ATT14
,GES.ATT04 AS ATT07
,VERP.ATT08
,VERP.ATT09
,VERP.ATT10
,VERP.ATT11
,VERP.ATT12
,TIT.ATT13
,U.ATT15
,VP.ATT16
,VP.ATT17
,VP.ATT18
,VP.ATT19
FROM TAB03 TAB01
JOIN TAB04 U ON TAB01.ATT20 = U.ATT21
JOIN TAB05 P ON U.ATT22 = P.ATT23
JOIN TAB06 PPON P.ATT23 = PP.ATT24
JOIN TAB05 VERP ON PP.ATT25 = VERP.ATT23
JOIN TAB07 V ON V.ATT26 = PP.ATT27
JOIN TAB03 TAB02 ON V.ATT28 = TAB02.ATT29
JOIN TAB08 VP on V.ATT28 = vp.ATT29
LEFT JOIN TAB09 TAG ON TAB02.ATT30 = TAG.ATT31
LEFT JOIN TAB09 GES ON TAB02.ATT32 = GES.ATT31
LEFT JOIN TAB10 TIT ON P.ATT33 = TIT.ATT34




-- EXPLAIN for the view (the bad one)


SELECT
TAB01.TAB03_ID AS ATT01,
TAB02.TAB03_ID AS ATT05,
TAB02.ATT02,
TAB02.ATT03,
TAG.ATT04 AS ATT06,
TAB02.ATT14,
GES.ATT04 AS ATT07,
VERP.ATT08,
VERP.ATT09,
VERP.ATT10,
VERP.ATT11,
VERP.ATT12,
TIT.ATT13,
U.ATT15,
VP.ATT16,
VP.ATT17,
VP.ATT18,
VP.ATT19
FROM PUBLIC.TAB06 PP
/++ PUBLIC.TAB06.tableScan ++/
INNER JOIN PUBLIC.TAB05 P
/++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT24 ++/
ON 1=1
/++ WHERE P.ATT23 = PP.ATT24
++/
INNER JOIN PUBLIC.TAB03 TAB02
/++ PUBLIC.TAB03.tableScan ++/
ON 1=1
INNER JOIN PUBLIC.TAB08 VP
/++ PUBLIC.TAB08.tableScan ++/
ON 1=1
LEFT OUTER JOIN PUBLIC.TAB09 TAG
/++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT30 ++/
ON TAB02.ATT30 = TAG.ATT31
LEFT OUTER JOIN PUBLIC.TAB09 GES
/++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT32 ++/
ON TAB02.ATT32 = GES.ATT31
LEFT OUTER JOIN PUBLIC.TAB10 TIT
/++ PUBLIC.PRIMARY_KEY_C8B: ATT34 = P.ATT33 ++/
ON P.ATT33 = TIT.ATT34
INNER JOIN PUBLIC.TAB03 TAB01
/++ PUBLIC.TAB03.tableScan ++/
ON TRUE
INNER JOIN PUBLIC.TAB04 U
/++ PUBLIC.PRIMARY_KEY_E9: ATT21 = TAB01.ATT20 ++/
ON TRUE
/++ WHERE (U.ATT22 = P.ATT23)
AND (TAB01.ATT20 = U.ATT21)
++/
INNER JOIN PUBLIC.TAB05 VERP
/++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT25 ++/
ON TRUE
/++ WHERE PP.ATT25 = VERP.ATT23
++/
INNER JOIN PUBLIC.TAB07 V
/++ PUBLIC.PRIMARY_KEY_CA: ATT28 = VP.ATT29
AND ATT28 = TAB02.ATT29
 ++/
ON TRUE
WHERE (V.ATT28 = VP.ATT29)
AND ((V.ATT28 = TAB02.ATT29)
AND ((V.ATT26 = PP.ATT27)
AND ((PP.ATT25 = VERP.ATT23)
AND ((P.ATT23 = PP.ATT24)
AND ((U.ATT22 = P.ATT23)
AND (TAB01.ATT20 = U.ATT21))



-- EXPLAIN ANALYZE for the select (the good one)


SELECT
TAB01.TAB03_ID AS ATT01,
TAB02.TAB03_ID AS ATT05,
TAB02.ATT02,
TAB02.ATT03,
TAG.ATT04 AS ATT06,
TAB02.ATT14,
GES.ATT04 AS ATT07,
VERP.ATT08,
VERP.ATT09,
VERP.ATT10,
VERP.ATT11,
VERP.ATT12,
TIT.ATT13,
U.ATT15,
VP.ATT16,
VP.ATT17,
VP.ATT18,
VP.ATT19
FROM PUBLIC.TAB03 TAB02
/* PUBLIC.TAB03.tableScan */
/* scanCount: 399 */
INNER JOIN PUBLIC.TAB07 V
/* PUBLIC.PRIMARY_KEY_CA: ATT28 = TAB02.ATT29 */
ON 1=1
/* WHERE V.ATT28 = TAB02.ATT29
*/
/* scanCount: 651 */
INNER JOIN PUBLIC.TAB06 PP
/* PUBLIC.PRIMARY_KEY_F8: ATT27 = V.ATT26 */
ON 1=1
/* WHERE V.ATT26 = PP.ATT27
*/
/*