[h2] Re: What's the recommended way to migrate from H2 1.3.175 to 1.4.195

[Steve McLeod]

Hendrik, I've answered your question in detail on StackOverflow.


On Friday, 5 May 2017 17:29:02 UTC+2, hendrik wrote:
>
> Hey there,
>
> asked this on Stackoverflow 
> 
>  
> the other day, but I guess this may be the better place (unless you're keen 
> on SO reputation...).
>
> Now that H2 1.4 is out of beta, I'd like to migrate my old 1.3.175 
> database to 1.4.195.
>
> Background info:
>
>- In the docs, database upgrade 
> does 
>not mention 1.4 yet.
>- The roadmap  still 
>lists "Automatic migration from 1.3 databases to 1.4." as "planned change".
>- The current state 
> of MVStore 
>is still labeled as "experimental".
>
> So, what's the recommended way to migrate?
>
> Additional aspects/bonus questions:
>
> Should I enable MVStore or stick with PageStore (pros/cons)? Which one 
> delivers better performance (multithreading is not important for me), which 
> one better stability, especially resilience against OutOfMemoryErrors?
>
> Thanks for your help!
>
> -hendrik
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: underscore in table name and czech collation

[Steve McLeod]

Hi Ivo,

This problem caught my eye, because I'm fascinated by code problems that 
occur with obscure causes.

However I've been unable to reproduce the problem. I've tried in the H2 web 
console and I've tried using Java code.

Can you please post a self-contained source code example that I reproduces 
the problem? 

(I'm using macOS, Java 1.8.0_111, and a slighter newer build of H2).

On Sunday, 26 February 2017 18:33:33 UTC+1, Ivo Panáček wrote:
>
> Hi,
>
> I have a problem. I am using recent H2 (via maven):
>
> 
>
> com.h2database
>
> h2
>
> 1.4.193
>
> 
>
>
> Following simple command did work as expected:
>
> CREATE TABLE "search_tree" (
>>
>> "code"  BIGINT NOT NULL
>>
>> )
>>
>
> now it creates table and the column is not visible ... tested also from 
> embedded web console.
>
> Similar command work normally:
>
> CREATE TABLE "searchtree" (
>>
>> "code"  BIGINT NOT NULL
>>
>> )
>>
>
> There is the only one difference, now I create the database with* Czech 
> collation*,
> so after creating database, before creating tables I run command:
>
> SET COLLATION CZECH STRENGTH SECONDARY
>>
>
> Without it everything works as before.
>
> *BUT* following command works normally even *with* Czech collation:
>
> CREATE TABLE "key_entries" (
>>
>> "code"  BIGINT NOT NULL
>>
>> )
>>
>
> *so it depends on the table name !*
>
> Nevertheless columns seem to exist:
>
> command:
>
>> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'PUBLIC'
>
>
> gives:
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'PUBLIC';
> TABLE_CATALOG ▼ 
> 
> TABLE_SCHEMA   
> 
> TABLE_NAME   
> 
> COLUMN_NAME   
> 
> ORDINAL_POSITION   
> 
> COLUMN_DEFAULT   
> 
> IS_NULLABLE   
> 
> DATA_TYPE   
> 
> CHARACTER_MAXIMUM_LENGTH   
> 
> CHARACTER_OCTET_LENGTH   
> 
> NUMERIC_PRECISION   
> 
> NUMERIC_PRECISION_RADIX   
> 
> NUMERIC_SCALE   
> 
> CHARACTER_SET_NAME   
> 
> COLLATION_NAME   
> 
> TYPE_NAME   
> 
> NULLABLE   
> 
> IS_COMPUTED   
> 
> SELECTIVITY   
> 
> CHECK_CONSTRAINT   
> 
> SEQUENCE_NAME   
> 
> REMARKS   
> 
> SOURCE_DATA_TYPE   
> 
> IDC PUBLIC key_entries code 1 *null* NO -5 19 19 19 10 0 Unicode CZECH 
> BIGINT 0 FALSE 50 *null* *null*
> IDC PUBLIC search_tree code 1 *null* NO -5 19 19 19 10 0 Unicode CZECH 
> BIGINT 0 FALSE 50 *null* *null*
>
>
> Just they are not shown in web console.
>
> My real problem is, that my java code cannot read table columns.
> Following call:
>
> ResultSet r = m.getColumns(null, null, table, null);
>>
>
> returns proper result set with normal metadata, but the result set *is 
> empty.*
> So my code cannot parse schema and create proper sql commands.
> I guess that the same reason causes that columns are not visible in 
> embedded web console.
>
> Java is:
>
> java version "1.8.0_121"
>>
>> Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
>>
>> Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)
>>
>
> on recent macOS, tomorrow I'll test it on Ubuntu.
>
> Sincerely,
> Ivo Panacek
>

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

[h2] Re: connecting to h2 from non-java languages

[Steve McLeod]

+1 for Spark. It is one of the few tech products that is truthful when it 
says, "you'll have your first example running in five minutes".

The solution you propose would involve would establish a connection for 
each query? I'd imagine that would not perform too well. 

I suggest establishing a connection once with one API call, which returns a 
token. Then use that token with future queries instead of supplying the 
credentials with each API call.



On Friday, 24 February 2017 07:34:53 UTC+1, Christian MICHON wrote:
>
> Jruby was used to write an proxy data API server using Sequel and Roda. 
> Since it's ruby inside java, connections to H2 are fully supported via 
> Sequel.
>
> For the container, I used Puma.
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Schema "XXXX" not found; SQL statement: GRANT SELECT ON SCHEMA XXXX TO USERX [90079-192]

[Steve McLeod]

Can you reproduce this in a small stand-alone Java program? If so, it does 
seem like a bug and you should report as an issue 
here: https://github.com/h2database/h2database/issues

Make sure to post the repro code.


On Monday, 20 February 2017 09:20:38 UTC+1, Ashok Rai wrote:
>
> Hi Kerry,
> Thanks for your reply. I could replicate this issue using SQL Workbench/J.
>
> On Saturday, February 18, 2017 at 9:15:56 PM UTC+5:30, Ashok Rai wrote:
>>
>> I am getting below error :
>>
>> java.util.concurrent.ExecutionException: java.lang.RuntimeException: 
>> org.h2.jdbc.JdbcSQLException: Schema "" not found; SQL statement:
>> GRANT SELECT ON SCHEMA  TO USERX[90079-192]
>>
>> Steps to reproduce:
>> 1) Connect to H2 db using Squirrel-sql client .
>> Connection url used: 
>>  jdbc:h2:file:MyDBFileLocation;CIPHER=AES;IFEXISTS=TRUE;AUTO_SERVER=FALSE
>> 2) Run below command 
>> alter schema  rename to ;
>> 3) Close all the connections
>> 4) Try connecting to H2 DB . I am unable to connect getting below error:
>>
>> java.util.concurrent.ExecutionException: java.lang.RuntimeException: 
>> org.h2.jdbc.JdbcSQLException: Schema "" not found; SQL statement:
>> GRANT SELECT ON SCHEMA  TO USERX[90079-192]
>>
>> Please help.
>>
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] I'm no longer able to offer H2 commercial support; can you?

[Steve McLeod]

Hi all,

For the last few years, I've offered H2 commercial support, as per the link 
here:
http://www.h2database.com/html/links.html#commercial_support

I'm now focusing on building a new product, so I don't have the time for 
this anymore. I've been turning down enquiries lately.

This is an opportunity for someone else who knows H2 well. 

I typically receive a few serious enquiries per year. They involve trying 
to fix corrupted databases, advising on how best to use H2, or offering an 
ongoing SLA. 

Is anyone interested in taking over?

Regards,

Steve McLeod

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Unhandled exception parsing date

[Steve McLeod]

Can you please post a reproducible test case? A complete SQL statement one 
could use to reproduce the problem would be great.


On Monday, 9 January 2017 19:45:44 UTC+1, Mike G wrote:
>
> Stack trace below. The sql itself is massive so I've edited it out. 
> Presumably the handling could just be improved.
>
>
> org.h2.jdbc.JdbcSQLException: Cannot parse "DATE" constant ; SQL statement:
> ...
> FROM (SELECT NULL) 
> WHERE (true)
> LIMIT 25000 [22007-193]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> at org.h2.message.DbException.get(DbException.java:168)
> at org.h2.value.ValueDate.parse(ValueDate.java:81)
> at org.h2.value.Value.convertTo(Value.java:928)
> at org.h2.expression.Function.getSimpleValue(Function.java:1084)
> at org.h2.expression.Function.getValueWithArgs(Function.java:1195)
> at org.h2.expression.Function.getValue(Function.java:590)
> at org.h2.expression.ExpressionList.getValue(ExpressionList.java:32)
> at 
> org.h2.expression.JavaAggregate.updateAggregate(JavaAggregate.java:192)
> at org.h2.expression.Function.updateAggregate(Function.java:2663)
> at org.h2.expression.Alias.updateAggregate(Alias.java:87)
> at org.h2.command.dml.Select.queryGroup(Select.java:365)
> at org.h2.command.dml.Select.queryWithoutCache(Select.java:650)
> at org.h2.command.dml.Query.query(Query.java:341)
> at org.h2.command.dml.Query.query(Query.java:309)
> at org.h2.expression.Subquery.getValue(Subquery.java:36)
> at org.h2.expression.ExpressionList.getValue(ExpressionList.java:32)
> at org.h2.expression.Alias.getValue(Alias.java:36)
> at org.h2.command.dml.Select.queryFlat(Select.java:549)
> at org.h2.command.dml.Select.queryWithoutCache(Select.java:655)
> at org.h2.command.dml.Query.query(Query.java:341)
> at org.h2.command.dml.Query.query(Query.java:309)
> at org.h2.command.dml.Query.query(Query.java:1)
> at org.h2.command.CommandContainer.query(CommandContainer.java:112)
> at org.h2.command.Command.executeQuery(Command.java:201)
> at 
> org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:110)
> ...
> Caused by: java.lang.StringIndexOutOfBoundsException: String index out of 
> range: 0
> at java.lang.String.charAt(String.java:646)
> at org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:305)
> at org.h2.value.ValueDate.parse(ValueDate.java:79)
> ... 51 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.
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] Re: Index hints

[Steve McLeod]

Pull request submitted.

Surprisingly, while doing this I discovered we already accept Sybase's 
format for index hints, although the hints are ignored:

// Sybase compatibility with
// "select * from test (index table1_index)"



On Thursday, 5 January 2017 10:30:03 UTC+1, Thomas Mueller Graf wrote:
>
> Hi,
>
> > With this enhancement I'm trying to solve one of my own performance 
> issues.
>
> Sure, it sounds good to me. The "fail query" is a different feature, the 
> links were just FYI.
>
> > It is possible that no index in the list is chosen, in which case a 
> full table scan will be used.
>
> It looks like this is what MySQL is doing, so that's fine.
>
> Regards,
> Thomas
>
>
> On Thu, Jan 5, 2017 at 9:48 AM, Steve McLeod <steve@gmail.com 
> > wrote:
>
>> MySQL syntax seems to be the popular option. I'll use that.
>>
>> Proposed Solution
>> ==
>>
>> I'll add this to the H2 SQL syntax:
>>
>> SELECT [TOP term] [DISTINCT | ALL] selectExpression FROM tableExpression 
>> [USE INDEX (indexList)] ...
>>
>> where indexList is defined as :
>> indexName [, indexName]*
>>
>> Each index in the list must exist, otherwise an INDEX_NOT_FOUND_1 (error 
>> code 42112) exception is thrown.
>>
>> Only indexes in the list will be used when choosing an index to use on 
>> the given tableExpression. There is no significance to order in this list.
>>
>> It is possible that no index in the list is chosen, in which case a full 
>> table scan will be used.
>>
>> To test this, I'll add test cases that check that the result of EXPLAIN 
>> ANALYZE indicates that a specified index is used (or is not used).
>>
>> > A related feature is to fail queries if no index is available.
>>
>> This sounds like a great idea, but I won't do this as part of this 
>> change. With this enhancement I'm trying to solve one of my own performance 
>> issues.
>>
>> Any feedback on this proposed solution?
>>
>>
>>
>> On Wednesday, 4 January 2017 13:13:15 UTC+1, Steve McLeod wrote:
>>>
>>> I'm considering attempting to add index hints to H2.
>>>
>>> Questions: 
>>> * Is this a good idea? Or is it  better to work on making the query 
>>> optimizer smarter at choosing the correct index?
>>> * Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better 
>>> syntax altogether?
>>>
>>> Oracle:
>>> SELECT /*+ INDEX (employees emp_department_ix)*/ 
>>>employee_id, department_id 
>>>   FROM employees 
>>>   WHERE department_id > 50;
>>>
>>> MySQL:
>>> SELECT * FROM table1 USE INDEX (col1_index,col2_index)
>>>   WHERE col1=1 AND col2=2 AND col3=3;
>>>
>>>
>>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to h2-database...@googlegroups.com .
>> To post to this group, send email to h2-da...@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.

[h2] Re: H2 - Throwing Syntax Error for Create Alias

[Steve McLeod]

Here's what happened:

1) You created a database with an older H2 jar and added a user-defined 
function (UDF)
2) You updated your H2 jar to a newer version that has a built-in function 
that has the same name as your user-defined function
3) Now H2 gets a bit confused at start up because it tries to recreate your 
user-defined function but finds the the name conflict.

I've experienced a similar problem. My (not ideal) solution was thus:

1) Use the older H2
2) Create a new UDF MY_TO_DATE with a name that doesn't conflict
3) Port my code to use the new UDF
4) Drop the old UDF TO_DATE
5) Upgrade to the new H2 jar
6) If the new TO_DATE built-in function achieves what you want, port your 
code again to use built-in function instead of the UDF.

You can simplify this depending on how much direct access you have to the 
end user environment





On Thursday, 5 January 2017 16:10:30 UTC+1, Bret Calvey wrote:
>
> Hi,
>
> What exactly is going on with this?
>
> We were using an older version of H2.
>
> We had a table that stored a day, month and year in separate fields and 
> had a query like this to turn the three fields into a Java date...
>
> SELECT TO_DATE(day || '-' || month || '-' || year, 'dd-MM-') FROM 
> WhateverTheTableIsCalled
>
> The TO_DATE we have is defined as...
>
> CREATE ALIAS IF NOT EXISTS TO_DATE AS $$ java.util.Date to_date(String 
> value, String format) throws java.text.ParseException { 
> java.text.DateFormat dateFormat = new  java.text.SimpleDateFormat(format); 
> return dateFormat.parse(value); }$$;
>
> When running this on the older version, we noticed that it would always 
> return the first date value from the first row in the result set for all of 
> the records. So say the first record had the date "01-01-2017", all of the 
> other records would return "01-01-2017" even if they had totally different 
> values.
>
> So I thought "this must be a bug" and went on to try and upgrade to the 
> latest version.
>
> After upgrading, when creating the alias (suing CREATE ALIAS *IF NOT 
> EXISTS*...) , we get this error...
>
> org.h2.jdbc.JdbcSQLException: *Function alias "TO_DATE" already exists;*
>
> If I try and drop the alias, I get...
>
> sql> DROP ALIAS TO_DATE;
> Error: org.h2.jdbc.JdbcSQLException: *Function alias "TO_DATE" not found; 
> SQL statement:*
> DROP ALIAS TO_DATE [90077-190]
> sql>
>
> So it looks like the latest version is in a bit of a mess...
>
> I'm starting to think that I probably should start looking for an 
> alternative to H2 :(
>
>
> On Wednesday, July 2, 2014 at 5:31:58 AM UTC+1, Kunal Kishan wrote:
>>
>> When running CREATE SQL Query as below from a ".sql" file , Syntax error 
>> is thrown,. However if i run the same sql on the console it runs fine.
>>
>> create alias TO_DATE as $$
>>  java.util.Date toDate(String s,String format) throws Exception {
>>   return new java.text.SimpleDateFormat(".MM.dd").parse(s);}
>> $$;
>>
>> create alias TO_DATE as [*]$$ java.util.Date toDate(String s,String 
>> format) throws Exception { return new 
>> java.text.SimpleDateFormat("".MM.dd"").parse(s)" [42000-177]
>> at org.h2.message.DbException.getSyntaxError(DbException.java:190)
>>
>>
>> if i change this query to below in .sql file : 
>>
>> create alias TO_DATE as '$$'
>>  java.util.Date toDate(String s,String format) throws Exception {
>>   return new java.text.SimpleDateFormat(".MM.dd").parse(s);}
>> '$$';
>>
>> It throws an error : 
>>
>> Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement 
>> "create alias TO_DATE as '$$' java.util.Date toDate(String s,String format) 
>> throws Exception { return new 
>> java.text.SimpleDateFormat("".MM.dd"").parse(s[*])" [42000-177]
>> at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
>> at org.h2.message.DbException.get(DbException.java:178)
>> at org.h2.message.DbException.get(DbException.java:154)
>>
>>
>> Whats the issue here and how to resolve 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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Index hints

[Steve McLeod]

MySQL syntax seems to be the popular option. I'll use that.

Proposed Solution
==

I'll add this to the H2 SQL syntax:

SELECT [TOP term] [DISTINCT | ALL] selectExpression FROM tableExpression 
[USE INDEX (indexList)] ...

where indexList is defined as :
indexName [, indexName]*

Each index in the list must exist, otherwise an INDEX_NOT_FOUND_1 (error 
code 42112) exception is thrown.

Only indexes in the list will be used when choosing an index to use on the 
given tableExpression. There is no significance to order in this list.

It is possible that no index in the list is chosen, in which case a full 
table scan will be used.

To test this, I'll add test cases that check that the result of EXPLAIN 
ANALYZE indicates that a specified index is used (or is not used).

> A related feature is to fail queries if no index is available.

This sounds like a great idea, but I won't do this as part of this change. 
With this enhancement I'm trying to solve one of my own performance issues.

Any feedback on this proposed solution?



On Wednesday, 4 January 2017 13:13:15 UTC+1, Steve McLeod wrote:
>
> I'm considering attempting to add index hints to H2.
>
> Questions: 
> * Is this a good idea? Or is it  better to work on making the query 
> optimizer smarter at choosing the correct index?
> * Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better 
> syntax altogether?
>
> Oracle:
> SELECT /*+ INDEX (employees emp_department_ix)*/ 
>employee_id, department_id 
>   FROM employees 
>   WHERE department_id > 50;
>
> MySQL:
> SELECT * FROM table1 USE INDEX (col1_index,col2_index)
>   WHERE col1=1 AND col2=2 AND col3=3;
>
>
>

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

[h2] Index hints

[Steve McLeod]

I'm considering attempting to add index hints to H2.

Questions: 
* Is this a good idea? Or is it  better to work on making the query 
optimizer smarter at choosing the correct index?
* Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better 
syntax altogether?

Oracle:
SELECT /*+ INDEX (employees emp_department_ix)*/ 
   employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;

MySQL:
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;


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

[h2] Use memory database and use file for persist and for next restart

[Steve McLeod]

I don't believe this is supported.

Instead you can use a file URL, with a large value specified for CACHE_SIZE. 
Read more about cache size here:
http://www.h2database.com/html/grammar.html#set_cache_size

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] H2 in memory impl - restart after ~300000 inserts with empty database. why????

[Steve McLeod]

Starting your JDBC URL with "jdbc:h2:mem:" tells H2 you want an in-memory 
non-persisted database. If you want a database persisted between sessions, use 
a URL that starts with jdbc:h2:file: 

The syntax of the H2 JDBC URL is explained here:
http://www.h2database.com/html/features.html#database_url

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: full join alternative for h2

[Steve McLeod]

I guess this is what you want:

SELECT ... FROM tableA a OUTER JOIN tableB b on a.date = b.date



On Saturday, 24 December 2016 13:11:22 UTC+1, Christian Master wrote:
>
> Hi,
> i have 
> tableA (date, moneyIn)
>
> tabelB (date, moneyOut)
>
> i need
> select a.date, a.moneyIn, b.moneyOut
> from
> tableA a
> full join
> tableB b
> a.date=b.date
>
> How to do it in H2, i think full join is not supported?
> thx
> chris
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: The last stable release Version 1.3.176 was published at 2014-04-05, when will 1.4.x stable version release?

[Steve McLeod]

Leon,

Although not marked stable, 1.4 with the page store (MV_STORE=false in JDBC 
URL) works better than 1.3.176 in many ways, and as good as 1.3.176 in all 
other ways.

I'm willing to state this because my product (Poker Copilot) has been using 
H2 since 2009 with thousands of users. As of January 2016 we switched to H2 
1.4 (with the page store only) and have had no H2 problems.




On Wednesday, 21 December 2016 03:48:35 UTC+1, Consu Leon wrote:
>
> I have this same question.
>
> Is H2 still a product you can base any level of 'production' on, 
> considering no stable release for years and still have to build with now 
> completely obsolete java 1.6 (I removed some lines from the pom and was 
> able to build with java7 but don't want to use such build till it's marked 
> stable).
>
> We run still somewhat happily with h2-1.3.176.jar, but after inserting a 
> few 10 records a drop of all tables takes literally forever. Only 
> stopping the server and removing the files then gives a quick solution. 
> This is probably the main reason we advise our customers not to run 
> production with H2.
>
> Once I attempted to switched to h2-1.4.187.jar but even the first tests 
> failed quickly: SQL's we execute on 1.3 version yield different query 
> results - some bugs in the query engine I guess - and back to 1.3.
>
> Then we decided to wait till a stable release of 1.4 -but it never came.
>
> Currently testing with h2-1.4.193.jar, initial impression is good; but 
> further testing is needed to confirm.
>
> Main point here is that it's obvious that h2 database is used in many 
> places and often in 'production' situations where installing one of the big 
> database players would be overkill (probably slower) or too expensive.
>
> Those users then have to consider that their only choice is to run a 
> 'stable' version with known problems or a beta version that's 'nearly 
> stable' but still built with completely obsolete java6.
>
> Looking at the great performance we achieve with H2 and the ease of use my 
> wish for 2017 is deliver a stable 1.4 version built with java8!
>
> Best season wishes,
>
> Leon
>
> On Wednesday, December 7, 2016 at 5:14:44 AM UTC+1, Felix Lee wrote:
>>
>> The last stable release Version 1.3.176 was published at 2014-04-05, when 
>> will 1.4.x stable version release? My company's internal policy is 
>> forbidden 'beta' version to be used in production...
>>
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: H2 Documentation - missing information

[Steve McLeod]

> It seems the H2 is lack of documentation.

I think this is somewhat unfair. H2 has comprehensive documentation 
describing every datatype, function, and SQL grammar component, all 
maintained and kept up to date. There's always an accurate change log and 
road map. 

This is an open source project, and if you do think the documentation could 
be improved, your contribution would be gratefully received.


On Monday, 12 December 2016 12:13:42 UTC+1, Elad Wertzberger wrote:
>
> Hi Guys,
>
> I 'm looking for more detailed sample code using h2 with JDBI.
> Do you have links?
>
> How about real life use cases?
> Recommendations for configuration?
>
> For example if I use more read queries than write - how to optimize them? 
> many updates?
> multiple indexes - what is the recommendation?
> list of limitation or known bugs?
>
> It seems the H2 is lack of documentation.
> I only familiar with: http://www.h2database.com/html/
>
> Am I miss something? Are there known links I can assist?
>
> Best regards
>
> Elad Wertzberger
>
> This message may contain confidential and/or privileged information. 
> If you are not the addressee or authorized to receive this on behalf of 
> the addressee you must not use, copy, disclose or take action based on this 
> message or any information herein. 
> If you have received this message in error, please advise the sender 
> immediately by reply email and delete this message. Thank you.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To 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] Re: Very slow calculateCost

[Steve McLeod]

Your query is extreme. Hundreds of joins, dozens of nested selects. Way too 
big, and way too complicated. You'll never get any decent performance with a 
query like that, nor will you ever be able to analyse and understand the 
reasons for the performance problems.

The solution to your problem is to redesign your database. Read up on database 
normalisation.

If you need ad hoc, complicated queries, first load your data from your normal 
schema into a star schema as described in database warehouse textbooks.



-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Is Java 7 the new H2 baseline Java version?

[Steve McLeod]

Hi all,

I might have missed this when announced or discussed, but I get the 
impression that we're no longer aiming for Java 6 compatibility as of H2 
1.4.193. Is that correct?

Regards,

Steve

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

[h2] Re: The date of next 1.4 release

[Steve McLeod]

While waiting for a release, it is surprisingly straightforward to make 
your own build, assuming you are familiar Git, GitHub, and running shell 
scripts.

1. Clone locally the H2 GitHub repo
2. In the local clone of the repo, do the following (assuming bash on Linux 
or macOS, on Windows, it is similar but I don't know the exact syntax):
cd h2
./build.sh download
./build.sh jar

You'll now have an up-to-date H2 jar file in the ~/bin folder with all the 
latest commits.








On Thursday, 20 October 2016 05:34:30 UTC+2, vraim...@gmail.com wrote:
>
> Branch 'master' has important bug fixes and improvements but they are not 
> released. What is the date of next 1.4 release?
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] help picking H2 version

[Steve McLeod]

You asked this in another thread and my answer was straightforward and clear, I 
thought. Use only the latest version of H2 1.4 if you want to use the MVStore. 
Currently that is 1.4.192. 

There is no version of H2 1.4 marked as stable but you did ask what version of 
1.4 you should use. 

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Main differeces between 1.3 and 1.4 versions

[Steve McLeod]

Only the latest (H2 1.4.192)

On Tuesday, 18 October 2016 23:44:43 UTC+2, Adam McMahon wrote:
>
> Good info here
>
> So, what version of 1.4x would you use in your production environment.   I 
> use pagestore for JDBC, but am also going to be integrating MVSTORE via the 
> map api (not JDBC) using its OffHeapStore.  What version would you 
> recommend.
>
> Thanks,
>
> -Adam
>
> On Tuesday, October 18, 2016 at 4:24:33 PM UTC-4, Petr Holík wrote:
>>
>> Hello,
>>
>> I've done some investigation of relation between 1.3(stable) and1.4(beta) 
>> - Hope it somebody helps to pick proper version for conrete suits.
>> Please feel free to correct me:
>>
>>  * Development of version 1.3 is discontinued - all bugs found are fixed 
>> only in 1.4 version
>>  * Git branch tagged version1.3 only contains snapshot of released 
>> sources of particular 1.3 versions and there is not and WILL NOT be any 
>> further development
>>  * 1.4 is Beta because of new MV_STORE which is default since 1.4.177 AND 
>> because of MVCC enabled
>>  * 1.4 can be used for production same as 1.3 when 
>> ;MV_STORE=FALSE;MVCC=FALSE  is appended to JDBC URL (MVCC is now 
>> disabled when not using MV_STORE - so potetionally can be omited)
>>  * MV_STORE has few data corruption issues  but since 2015-10-11 is there 
>> was no new one
>>  * MV_STORE should now have pretty similar performance as old one but 
>> delivers some new cool Map-Like  features, which can be used 
>> directly.
>>  * There are no _unfixed known bugs_ on MV_STORE but there still exists 
>> few open data corruption bugs related to this store(which may or may not be 
>> caused by it)
>>
>>
>> Sincerely Petr Holik
>>
>>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Main differeces between 1.3 and 1.4 versions

[Steve McLeod]

A good summary. Let me add some more info.

 * 1.4 can be used for production same as 1.3 when 
> ;MV_STORE=FALSE;MVCC=FALSE  is appended to JDBC URL (MVCC is now disabled 
> when not using MV_STORE - so potetionally can be omited)
>

Appending ;MV_STORE=FALSE to the JDBC URL is enough to use the PageStore 
(the solid, reliable. database storage mechanism used in H2 1.3. You do 
mention this, but let's make it explicit!
 

>  * MV_STORE should now have pretty similar performance as old one
>

I disagree. In my own performance tests, the MV_STORE (as of H2 1.4.192) 
becomes much slower on a large database (say, > 1 GB) with many SQL UPDATE 
statements.


-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Is there a way to create an UNIQUE INDEX in H2?

[Steve McLeod]

H2 does restrict inserts according to UNIQUE index constraints.

Can you post complete reproduction steps?



On Wednesday, 5 October 2016 18:44:38 UTC+2, Rodrigo wrote:
>
> I'm trying to add this statement in my h2 script:
>
> CREATE UNIQUE INDEX "SOME_NAME" ON "SOME_TABLE_NAME" (COLUMN1,COLUMN2)
>
> Looks like it is being bypassing, because I was able to insert two 
> identical rows in my h2, but not in my actual DB (Oracle).
>
> Is there a way to create that UNIQUE INDEX?
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Error while executing query "Subquery is not a single column query; SQL statement:"

[Steve McLeod]

Have you tried concatenating the two columns into one column?

I don't know if that would work, just an idea.

PS: I think you'd have more success finding a solution if you post this on 
stackoverflow.com


On Wednesday, 5 October 2016 09:14:06 UTC+2, Ramesh Elkaturi wrote:
>
> Hi all,
>
>
> I tried to execute below query then it is returning error saying 
> that "Subquery is not a single column query; SQL statement: 
> ".
>  
> but it is working on other databases like DB2, Datacom etc...I have googled 
> the issue some of the folks are saying this feature not supported by H2 and 
> Derby. Can any body suggest me the solution or work around for this.
>
>
>   * My Query: *
>
>SELECT * FROM REPORTINFO  WHERE (REPORTINFOID , VERSION ) IN 
> (SELECT REPORTINFOID , MAX(VERSION ) AS VERSION  FROM REPORTINFO  WHERE 
> DELETED =FALSE GROUP BY REPORTINFOID )
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Clound Server for H2

[Steve McLeod]

What is a Clound Server? 

Did you mean Cloud Server? If so, can you describe in more detail what you 
are looking for?

On Saturday, 1 October 2016 21:18:48 UTC+2, Pedro Gentil wrote:
>
> Hi.
>
> Are there a Clound Server solution for H2?
>
>
> 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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Maven build issues

[Steve McLeod]

Hi Hendrik,

If you have the time, please do fix any of these problems that you can.

The Maven configuration was added recently, and I don't think it is used 
actively by most contributors. Yet!



On Tuesday, 13 September 2016 09:47:29 UTC+2, hendrik wrote:
>
> Hey there,
>
> I was trying to build H2 with the provided mvnw script and stumbled over 
> a couple of things.
>
> 1) Encoding
>
> [WARNING] File encoding has not been set, using platform encoding 
> US-ASCII, i.e. build is platform dependent!
>
> This can easily be fixed with a build property (sets the encoding to 
> UTF-8). Interested in a patch?
>
> 2) Failing tests
>
> Then there are numerous test failures.
> I guess those are known?
>
> 3) Failing Sniffer/Failing Maven Wrapper
>
> When I skip the tests (./mvnw clean install -Dmaven.test.skip=true), and 
> run Maven using Java 8, the Java 6 Animal Sniffer reports issues in MVStore 
> and MVTable:
>
> [INFO] --- animal-sniffer-maven-plugin:1.15:check (check-java-api) @ h2 ---
> [INFO] Checking unresolved references to 
> org.codehaus.mojo.signature:java16:1.1
> [ERROR] 
> /Users/hendrik/Projects/h2database/h2/src/main/org/h2/mvstore/db/MVTable.java:358:
>  
> Undefined reference: java.util.concurrent.ConcurrentHashMap.KeySetView 
> java.util.concurrent.ConcurrentHashMap.keySet()
> [ERROR] 
> /Users/hendrik/Projects/h2database/h2/src/main/org/h2/mvstore/db/MVTable.java:358:
>  
> Undefined reference: java.util.Iterator 
> java.util.concurrent.ConcurrentHashMap.KeySetView.iterator()
> [ERROR] 
> /Users/hendrik/Projects/h2database/h2/src/main/org/h2/mvstore/MVStore.java:727:
>  
> Undefined reference: java.util.concurrent.ConcurrentHashMap.KeySetView 
> java.util.concurrent.ConcurrentHashMap.keySet()
> [ERROR] 
> /Users/hendrik/Projects/h2database/h2/src/main/org/h2/mvstore/MVStore.java:2362:
>  
> Undefined reference: java.util.concurrent.ConcurrentHashMap.KeySetView 
> java.util.concurrent.ConcurrentHashMap.keySet()
> [ERROR] 
> /Users/hendrik/Projects/h2database/h2/src/main/org/h2/mvstore/MVStore.java:2362:
>  
> Undefined reference: java.util.Iterator 
> java.util.concurrent.ConcurrentHashMap.KeySetView.iterator()
>
> When I switch to run Maven with Java 6, I get:
>
> Exception in thread "main" java.lang.UnsupportedClassVersionError: 
> org/apache/maven/wrapper/MavenWrapperMain : Unsupported major.minor version 
> 51.0
>
> Finally, running Maven with Java 7 seems to work.
>
> I have no idea how the maven-wrapper.jar was built, but it should 
> probably be recompiled with Java 6.
>
> 4) Are the docs not built?
>
> Are docs generated when I build with Maven? I'm under the impression they 
> aren't. Open issue?
>
> Cheers,
>
> -hendrik
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Quickly adding multiple columns at a certain position

[Steve McLeod]

Hi Hendrik,

I added the multiple column version due to a similar performance problem I 
was facing four years ago.

I think you have two options:

1) patch the code that implements the multiple column version of ALTER 
TABLE ADD COLUMN. It is in org.h2.command.ddl.AlterTableAlterColumn. If you 
haven't done something like this before, I think you'll be surprised at how 
straightforward it is, given that you have good knowledge of Java and SQL, 
and some understanding of how parsers work.

2) manually perform the SQL statements similar to those that ALTER TABLE 
ADD COLUMN would internally use. I forget the details, but effectively, it 
internally executes a series of SQL statements similar to the following: 

CREATE TABLE  (...) AS SELECT ... FROM 

for each index on original table {
  CREATE INDEX  ON  
}
DROP TABLE 
ALTER TABLE  RENAME TO 
for each index on original table {
  CREATE INDEX  ON  
  ALTER INDEX  RENAME TO 
}

Plus some manual modifying any AUTO_NUMBER column.






On Friday, 9 September 2016 12:24:44 UTC+2, hendrik wrote:
>
> Hey there,
>
> I'd like to add multiple columns to a table. Caveat: I'd like to add them 
> at a specified position and it should be quick.
>
> What I'm doing right now, is to add them one by one like this:
>
> ALTER TABLE Songs ADD custom1 varchar(255) AFTER someStartColumn;
> ALTER TABLE Songs ADD custom2 varchar(255) AFTER custom1;
> ALTER TABLE Songs ADD custom3 varchar(255) AFTER custom2;
> ...
>
> Unfortunately, due to a high number of indices, adding columns like this 
> is very slow. Around 10s each.
> This is using H2 1.3.175 on an SSD with a table size of roughly 7000.
> Without any indices on the table, this only takes a little more than 1s 
> each. I know, I could drop indices before altering the table and later 
> re-create them.
>
> Indices aside, adding the columns in one statement is a lot faster:
>
> ALTER TABLE Songs ADD (custom1 varchar(255), custom2 varchar(255), custom3 
> varchar(255), ...);
>
> This takes about as long as adding a single column.
> But unfortunately this syntax does not let me specify the position of the 
> added columns.
>
> Or does it?
>
> Is there a better way to add multiple columns at specific positions?
>
> Thanks for your advice,
>
> -hendrik
>
>

-- 
You received this message because you are subscribed to the Google Groups "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] Shall I add H2 to Travis CI?

[Steve McLeod]

I tried adding the H2 tests to Travis, but this turned out to be not easy. 
The tests never complete, and after some time, Travis kills the build. I 
suspect it is memory-related or thread-related, but it became a big time 
sink.

Next time I'll try with mosts tests disabled, and see if I can pinpoint the 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: H2 Corrupted database v1.4.189

[Steve McLeod]

I think it is clear that version 1.4 is not marked as stable. 

It is is impossible to tell the cause of a database corruption without a 
full investigation.

> what are the side impacts of disabling MV STORE? knowing that we are in a 
multi-threaded application. 

You'd need to assess that yourself, I'm afraid. 


On Monday, 22 August 2016 10:23:44 UTC+2, Rachid OURICH wrote:
>
> Thank you ,
> Does this mean that MV STORE is not stable enough ? and it is the cause 
> of the database corruption ?
> what are the side impacts of disabling MV STORE? knowing that we are in a 
> multi threaded application. 
>
> 2016-08-20 18:12 GMT+01:00 Steve McLeod <steve@gmail.com 
> >:
>
>> You are using a version of H2 (1.4.189) that was clearly marked as beta. 
>> The most recent stable release is 1.3.176.
>>
>> In my opinion, you should be using 1.3.176 for production.
>>
>> If you really insist on using the latest beta, then you should use the 
>> page store, and not the MV store.  Do this by appending MV_STORE=false to 
>> the JDBC URL.
>>
>>
>> On Friday, 19 August 2016 17:57:14 UTC+2, Rachid OURIHC wrote:
>>>
>>> Hi everyone,
>>>
>>> We are encountering recurring data corruption in one of our products 
>>> using H2 as database. Until now we got 2 corrupted databases; and our 
>>> application will be installed in more than 20 machines.
>>> - The version of H2 is 1.4.189 with hibernate+ JPA
>>> - The database schema is composed of 62 small tables without binaries.
>>>
>>> - Database url is the following 
>>>
>>>
>>> jdbc:h2:file:C:/Storage/MDBASE;MVCC=TRUE;MV_STORE=TRUE;AUTO_SERVER=TRUE;CACHE_SIZE=16384
>>>
>>> - Backuping the corrupted database gives the following error : 
>>>
>>>Exception in thread "main" java.lang.IllegalStateException: 
>>> File corrupted in chunk 13439, expected page length 4..32, got -486539227 
>>> [1.4.191/6]
>>>
>>> at 
>>> org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773)
>>>
>>> at org.h2.mvstore.Page.read(Page.java:649)
>>>
>>> at org.h2.mvstore.Page.read(Page.java:195)
>>>
>>> at org.h2.mvstore.MVStore.readPage(MVStore.java:1939)
>>>
>>> at org.h2.mvstore.MVMap.readPage(MVMap.java:736)
>>>
>>> at org.h2.mvstore.Page.getChildPage(Page.java:217)
>>>
>>> at org.h2.mvstore.Cursor.fetchNext(Cursor.java:150)
>>>
>>> at org.h2.mvstore.Cursor.next(Cursor.java:50)
>>>
>>> at org.h2.mvstore.MVMap$2$1.next(MVMap.java:879)
>>>
>>> at org.h2.mvstore.MVMap$2$1.next(MVMap.java:870)
>>>
>>> at org.h2.tools.Recover.dumpLobMaps(Recover.java:701)
>>>
>>> at org.h2.tools.Recover.dumpMVStoreFile(Recover.java:603)
>>>
>>> at org.h2.tools.Recover.process(Recover.java:345)
>>>
>>> at org.h2.tools.Recover.runTool(Recover.java:196)
>>>
>>> at org.h2.tools.Recover.main(Recover.java:159)
>>>
>>> Picked up JAVA_TOOL_OPTIONS: -Djava.vendor="Sun Microsystems Inc."
>>>
>>>  
>>>
>>> I cannot provide an example of a corrupted database, since I cannot 
>>> reproduce the issue, and the data we manage is really sensitive...
>>>
>>>  
>>>
>>> Do you have an idea what will be the cause of these crashes ? How it is 
>>> possible to recover these databases ?
>>>
>>> Any pointer or insight would be greatly appreciated,
>>> Thanks
>>>
>> -- 
>> 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/vytfkUIeWIs/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to 
>> h2-database...@googlegroups.com .
>> To post to this group, send email to h2-da...@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.

[h2] Re: H2 Corrupted database v1.4.189

[Steve McLeod]

You are using a version of H2 (1.4.189) that was clearly marked as beta. 
The most recent stable release is 1.3.176.

In my opinion, you should be using 1.3.176 for production.

If you really insist on using the latest beta, then you should use the page 
store, and not the MV store.  Do this by appending MV_STORE=false to the 
JDBC URL.


On Friday, 19 August 2016 17:57:14 UTC+2, Rachid OURIHC wrote:
>
> Hi everyone,
>
> We are encountering recurring data corruption in one of our products using 
> H2 as database. Until now we got 2 corrupted databases; and our application 
> will be installed in more than 20 machines.
> - The version of H2 is 1.4.189 with hibernate+ JPA
> - The database schema is composed of 62 small tables without binaries.
>
> - Database url is the following 
>
>
> jdbc:h2:file:C:/Storage/MDBASE;MVCC=TRUE;MV_STORE=TRUE;AUTO_SERVER=TRUE;CACHE_SIZE=16384
>
> - Backuping the corrupted database gives the following error : 
>
>Exception in thread "main" java.lang.IllegalStateException: 
> File corrupted in chunk 13439, expected page length 4..32, got -486539227 
> [1.4.191/6]
>
> at 
> org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773)
>
> at org.h2.mvstore.Page.read(Page.java:649)
>
> at org.h2.mvstore.Page.read(Page.java:195)
>
> at org.h2.mvstore.MVStore.readPage(MVStore.java:1939)
>
> at org.h2.mvstore.MVMap.readPage(MVMap.java:736)
>
> at org.h2.mvstore.Page.getChildPage(Page.java:217)
>
> at org.h2.mvstore.Cursor.fetchNext(Cursor.java:150)
>
> at org.h2.mvstore.Cursor.next(Cursor.java:50)
>
> at org.h2.mvstore.MVMap$2$1.next(MVMap.java:879)
>
> at org.h2.mvstore.MVMap$2$1.next(MVMap.java:870)
>
> at org.h2.tools.Recover.dumpLobMaps(Recover.java:701)
>
> at org.h2.tools.Recover.dumpMVStoreFile(Recover.java:603)
>
> at org.h2.tools.Recover.process(Recover.java:345)
>
> at org.h2.tools.Recover.runTool(Recover.java:196)
>
> at org.h2.tools.Recover.main(Recover.java:159)
>
> Picked up JAVA_TOOL_OPTIONS: -Djava.vendor="Sun Microsystems Inc."
>
>  
>
> I cannot provide an example of a corrupted database, since I cannot reproduce 
> the issue, and the data we manage is really sensitive...
>
>  
>
> Do you have an idea what will be the cause of these crashes ? How it is 
> possible to recover these databases ?
>
> Any pointer or insight would be greatly appreciated,
> 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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Shall I add H2 to Travis CI?

[Steve McLeod]

Great!

Starting from 14 August 2016, you'll see a green checkmark next to each 
commit on https://github.com/h2database/h2database/commits/master if the 
build passed. If it failed you'll see a red cross. Click on the 
checkmark/cross and you'll see more info about the build on travis-ci.org

I've made the build happen against both Java 6 and Java 7.

@Alex I'll add the tests soon. I wanted to get the simplest possible config 
to work first, to avoid project maintainers getting hit with lots of "build 
failed" messages due to my mistakes. 


On Monday, 15 August 2016 10:29:57 UTC+2, Noel Grandin wrote:
>
>
>
> On 2016/08/15 10:01 AM, Sergi Vladykin wrote: 
> > Ok, I enabled it and it seems to work. I also added build status icon on 
> README.md for convenience. 
>
>
> Nice work people! 
>

-- 
You received this message because you are subscribed to the Google Groups "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] Shall I add H2 to Travis CI?

[Steve McLeod]

PS: You can see Travis CI in action with my fork of H2database 
here: https://travis-ci.org/stevemcleod/h2database

On Saturday, 13 August 2016 15:44:28 UTC+2, Steve McLeod wrote:
>
> Done! Sort of. It's working with my own fork of H2.
>
> Thomas, I think you (or another member of GitHub organisation "h2database" 
> needs to connect Travis CI to the GitHub h2 repository. (I'd be happy to do 
> this, but I don't seem to have the necessary permissions.)
>  
> You can do it here: https://travis-ci.org/
>
> 1. Connect to https://travis-ci.org/
> 2. On your Travis CI profile page, "flick" the repository switch on. 
>
>
> That should be enough. Travis CI will automatically detect the config file 
> I added to the repository and do the necessary building.
>
> Note: Travis CI won't start for H2 until the first commit after doing all 
> the above.
>
> Cheers,
>
> Steve
>
>
> On Friday, 12 August 2016 09:48:00 UTC+2, Thomas Mueller Graf wrote:
>>
>> Hi,
>>
>> I agree, it would be great!
>>
>> Regards,
>> Thomas
>>
>> On Thu, Aug 11, 2016 at 12:40 PM, Sergi Vladykin <sergi.v...@gmail.com> 
>> wrote:
>>
>>> Very good idea! I wanted to do exactly that, but never got time. It will 
>>> be a great contribution!
>>>
>>> Sergi
>>>
>>> 2016-08-11 13:18 GMT+03:00 Steve McLeod <steve@gmail.com>:
>>>
>>>> Travis CI is web-based continuous integration that works well with 
>>>> GitHub and is free for open source.
>>>>
>>>> I propose adding H2 to Travis. This will make it clear almost 
>>>> immediately after a commit to master that the build is broken. I think it 
>>>> can also do the same to pull requests, before they get accepted.
>>>>
>>>> I volunteer to do the necessary work.
>>>>
>>>> A good idea? Or an unnecessary extra burden?
>>>>
>>>>
>>>> -- 
>>>> You received this message because you are subscribed 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 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...@googlegroups.com.
>>> To post to this group, send email to h2-da...@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] Shall I add H2 to Travis CI?

[Steve McLeod]

Done! Sort of. It's working with my own fork of H2.

Thomas, I think you (or another member of GitHub organisation "h2database" 
needs to connect Travis CI to the GitHub h2 repository. (I'd be happy to do 
this, but I don't seem to have the necessary permissions.)
 
You can do it here: https://travis-ci.org/

1. Connect to https://travis-ci.org/
2. On your Travis CI profile page, "flick" the repository switch on. 


That should be enough. Travis CI will automatically detect the config file 
I added to the repository and do the necessary building.

Note: Travis CI won't start for H2 until the first commit after doing all 
the above.

Cheers,

Steve


On Friday, 12 August 2016 09:48:00 UTC+2, Thomas Mueller Graf wrote:
>
> Hi,
>
> I agree, it would be great!
>
> Regards,
> Thomas
>
> On Thu, Aug 11, 2016 at 12:40 PM, Sergi Vladykin <sergi.v...@gmail.com 
> > wrote:
>
>> Very good idea! I wanted to do exactly that, but never got time. It will 
>> be a great contribution!
>>
>> Sergi
>>
>> 2016-08-11 13:18 GMT+03:00 Steve McLeod <steve@gmail.com 
>> >:
>>
>>> Travis CI is web-based continuous integration that works well with 
>>> GitHub and is free for open source.
>>>
>>> I propose adding H2 to Travis. This will make it clear almost 
>>> immediately after a commit to master that the build is broken. I think it 
>>> can also do the same to pull requests, before they get accepted.
>>>
>>> I volunteer to do the necessary work.
>>>
>>> A good idea? Or an unnecessary extra burden?
>>>
>>>
>>> -- 
>>> You received this message because you are subscribed 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 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...@googlegroups.com .
>> To post to this group, send email to h2-da...@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] Potential performance improvement

[Steve McLeod]

I've carefully reasoned my approach, and tested it. I've made a pull 
request, and I'd be grateful for a careful check of my pull request before 
it gets merged.



On Friday, 12 August 2016 10:09:52 UTC+2, Steve McLeod wrote:
>
> I'm using the latest master from GitHub.
>
> Your question triggered a thought in my mind. In December last year, I 
> ensured that the CACHED_CALENDAR is cleared each time it is retrieved, to 
> solve a particularly nasty bug affecting users of my H2-based product when 
> in the Moscow timezone and using Java 1.8.0_60 (and other Java versions 
> too). (See 
> https://github.com/h2database/h2database/commit/96b50c6f4690bf515ddec1b138d6f0136233a836
>  
> and 
> https://github.com/h2database/h2database/commit/9932e2d61d8f2af6662120a0ed2420fe2acf4431
> )
>
> So it was most likely my fix for that Moscow problem that introduced this 
> performance problem.
>
> I think a solution would be to make sure that getTimeLocalWithoutDst uses 
> its own Calendar object not used by any other method. Then there will be no 
> need to call Calendar.clear(), meaning that 
> calendar.get(Calendar.ZONE_OFFSET) should be fast. I'll keep working on 
> this to see what I can do.
>
> Regards,
>
> Steve
>
>
> On Thursday, 11 August 2016 21:37:43 UTC+2, Noel Grandin wrote:
>>
>> which version are you testing against? Because in recent versions we 
>> already cache the Calendar object in DateTimeUtils.CACHED_CALENDAR, at 
>> which point the ZONE_OFFSET should be cheap to calculate.
>>
>

-- 
You received this message because you are subscribed to the Google Groups "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] Potential performance improvement

[Steve McLeod]

I'm using the latest master from GitHub.

Your question triggered a thought in my mind. In December last year, I 
ensured that the CACHED_CALENDAR is cleared each time it is retrieved, to 
solve a particularly nasty bug affecting users of my H2-based product when 
in the Moscow timezone and using Java 1.8.0_60 (and other Java versions 
too). 
(See 
https://github.com/h2database/h2database/commit/96b50c6f4690bf515ddec1b138d6f0136233a836
 
and 
https://github.com/h2database/h2database/commit/9932e2d61d8f2af6662120a0ed2420fe2acf4431)

So it was most likely my fix for that Moscow problem that introduced this 
performance problem.

I think a solution would be to make sure that getTimeLocalWithoutDst uses 
its own Calendar object not used by any other method. Then there will be no 
need to call Calendar.clear(), meaning that 
calendar.get(Calendar.ZONE_OFFSET) should be fast. I'll keep working on 
this to see what I can do.

Regards,

Steve


On Thursday, 11 August 2016 21:37:43 UTC+2, Noel Grandin wrote:
>
> which version are you testing against? Because in recent versions we 
> already cache the Calendar object in DateTimeUtils.CACHED_CALENDAR, at 
> which point the ZONE_OFFSET should be cheap to calculate.
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: What is the last stable version of H2 for 1.4.x versions.

[Steve McLeod]

That is wrong information in Wikipedia. The H2 website is the authoritative 
source here.



On Friday, 12 August 2016 04:26:00 UTC+2, lakshman udayakantha wrote:
>
> Hi Steve,
>
> Anyway I see in wikipedia https://en.wikipedia.org/wiki/H2_(DBMS) , 
> 1.4.191 as a stable release. 
>
>
> On Thursday, August 11, 2016 at 11:36:03 AM UTC+5:30, lakshman udayakantha 
> wrote:
>>
>> Hi,
>>
>> H2Database site says that last stable version is 1.3.176. Is there any 
>> last stable version in 1.4.x versions?
>>
>> Thanks
>> Lakshman
>>
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Potential performance improvement

[Steve McLeod]

I'm seeking some advice on a performance improvement. 

I did some profiling on a query that returns 500K rows of data. I 
discovered that one method in org.h2.util.DateTimeUtils uses about 25% of 
the elapsed time:

public static long getTimeLocalWithoutDst(java.util.Date d) {
Calendar calendar = getCalendar();
return d.getTime() + calendar.get(Calendar.ZONE_OFFSET);
}

I made a modified version of H2 which only calculates 
calendar.get(Calendar.ZONE_OFFSET) once per session, and the performance of 
my query did improve dramatically.

Here's my code:

private static int zoneOffset = Integer.MIN_VALUE;

public static long getTimeLocalWithoutDst(java.util.Date d) {
if (zoneOffset == Integer.MIN_VALUE) {
Calendar calendar = getCalendar();
zoneOffset = calendar.get(Calendar.ZONE_OFFSET);
}

return d.getTime() + zoneOffset;
}


However I think that this introduces a bug. If the timezone of the computer 
changes while H2 is running, then with my 'improvement' H2 would continue 
to use the wrong zoneOffset value.

I think it would be acceptable to calculate the zoneOffset value once per 
query. The performance gains would be there, but the code would continue to 
be correct when the computer's timezone changes.

What do you think about that? Do you have a suggestion how I could do this?

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Shall I add H2 to Travis CI?

[Steve McLeod]

Travis CI is web-based continuous integration that works well with GitHub 
and is free for open source.

I propose adding H2 to Travis. This will make it clear almost immediately 
after a commit to master that the build is broken. I think it can also do 
the same to pull requests, before they get accepted.

I volunteer to do the necessary work.

A good idea? Or an unnecessary extra burden?


-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: load entire database in memory

[Steve McLeod]

This is the wrong list for your question.  Ask on stackoverflow.com or a 
general database design forum.


On Saturday, 6 August 2016 17:03:58 UTC+2, vicenrico wrote:
>
> Hello. I have a question.
> I'm developing a program and I would like how to improve my db scheme 
> desing.
> Currently I'm developing a movie manager program with 20 tables. The main 
> table is the movie table with 30 fields.I'd like to know whether I should 
> load the entire table into a movies object list or it would be better if a 
> loaded only the ids, and when the user scrolls down the movies list, go and 
> load the rest of the fields.
>
> In other words, should I go for a list of Movies Objects, with 30 fields 
> as members of Movies objects, or only a Movie object with the id field, and 
> do lazy loading as requiered.
>
> Thanks in advance.
>

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

[h2] Re: What is the last stable version of H2 for 1.4.x versions.

[Steve McLeod]

The website info is accurate. The latest stable is 1.3.176. There has not 
yet been a 1.4.x version marked as stable. I think it is likely that the 
next 1.4.x update will be marked as stable.

On Thursday, 11 August 2016 08:06:03 UTC+2, lakshman udayakantha wrote:
>
> Hi,
>
> H2Database site says that last stable version is 1.3.176. Is there any 
> last stable version in 1.4.x versions?
>
> Thanks
> Lakshman
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: The DB size increases from 100 MB to 1GB

[Steve McLeod]

> I'm using 1.4.161

Do you mean 1.3.161? If so, the first step would be to update to either 
1.3.176 (release, stable) or 1.4.192 (beta, latest).

Many bugs have been fixed since 1.3.161


On Friday, 29 July 2016 08:28:39 UTC+2, IntensiveH2 wrote:
>
> Hello,
>
> I'm using 1.4.161 in 
> mode 
> ;MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;CACHE_TYPE=LRU;MV_STORE=FALSE".
> Sometime, I have the error bellow:
> 2016-07-21 13:20:25 *pageStore: Transaction log could not be truncated; 
> size: 96 MB* because the h2 db size was increased just after.
> and after the size of the DB increases from 100 MB to 1GB in one day.
> The only way to decreases the size is stopping  the application server 
> (calling a "shutdown defrag") and restarting it just after.
>
> Maybe the trace "Transaction log could not be truncated" is due to a 
> transaction not closed (It's my understanding of the message and discussion 
> in this google group).
>
> *Qestion: *
> How to detect which transaction is the root cause of the issue? 
> It is possible to have latest SQL statements executed on this transaction?
> Something else helping me to debug this issue?
>
> Regards,
> Thierry. 
>

-- 
You received this message because you are subscribed to the Google Groups "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 - Connection Closes Abruptly

[Steve McLeod]

As a first step, update to the latest release H2 jar (1.3.176) or the 
latest beta jar (1.4.192).

As a second step, try to find reliable steps to reproduce the problem. This 
helps fix the problem.




On Friday, 24 June 2016 09:26:43 UTC+2, Ranjit K wrote:
>
> My question to you more specifically is “how to do we prevent H2 DB or 
> tables from getting corrupt?”
>
>  
>
> We think that since the connection to database is getting closed abruptly, 
> it might be causing the following SELECT query to fail (which can possibly 
> be due to database/table corruption).
>
> On Friday, 24 June 2016 01:34:56 UTC+5:30, Ranjit K wrote:
>>
>> What are the reasons H2 database can get corrupted? Is it possible that 
>> the 1st exception is the reason behind 2nd exception?
>>
>> On Thursday, 23 June 2016 21:52:10 UTC+5:30, Noel Grandin wrote:
>>>
>>> The first exception is occurring because somewhere your code is 
>>> forgetting to close a java.sql.Connection
>>>
>>> The second exception is occurring because your DB has become corrupted.
>>> ​
>>>
>>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: H2 Database support

[Steve McLeod]

> there is no response from Steve McLeod

For the record, I received an email from Aamir asking for commercial 
support AFTER he posted here. 



On Monday, 27 June 2016 14:24:19 UTC+2, Aamir Shahbaz wrote:
>
> I even tried to contact the "Commercial Support" listed on 
> http://www.h2database.com/ but there is no response from Steve McLeod 
> (steve dot mcleod at gmail dot com). 
>
> Please suggest if there are any other "Commercial Support" available for 
> H2 database?
>
> On Monday, 27 June 2016 17:02:52 UTC+5:30, Aamir Shahbaz wrote:
>>
>> May I know if some organization gives support for H2 database related 
>> issues. We are facing major problem due to h2 db corruption issue in 
>> production. 
>>
>>
>> Following is the log from trace.db 
>>
>> *06-20 00:00:19 jdbc[1404]: exception*
>>
>> *org.h2.jdbc.JdbcSQLException: File corrupted while reading record: 
>> "[41656] stream data key:8741 pos:11 remaining:0". Possible solution: use 
>> the recovery tool; SQL statement:*
>>
>> *select tran0_.TRAN_NO as TRAN1_229_, tran0_.ENTRY_DATE as ENTRY2_229_, 
>> tran0_.BUSINESS_DAY as BUSINESS3_229_, tran0_.POS_NO as POS4_229_, 
>> tran0_.RECEIPT_NO as RECEIPT5_229_, tran0_.RETURNED as RETURNED229_, 
>> tran0_.SERIES_FLAG as SERIES7_229_, tran0_.STAFF_NO as STAFF8_229_, 
>> tran0_.TRAN_DATA as TRAN9_229_, tran0_.TRAN_DATE as TRAN10_229_, 
>> tran0_.TRAN_DAY as TRAN11_229_, tran0_.TRAN_ID as TRAN12_229_, 
>> tran0_.TRAN_SEND_FLAG as TRAN13_229_, tran0_.TRAN_TIME as TRAN14_229_, 
>> tran0_.TRAN_VERSION as TRAN15_229_ from T_TRAN tran0_ where 
>> tran0_.TRAN_SEND_FLAG=0 [90030-174]*
>>
>>
>>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Trigger not found - I have read every other post

[Steve McLeod]

Note that an IntelliJ IDEA data source is not running in the same JVM that 
your project is running in. The data source is independent of your project 
code, and has no awareness of it.

I guess you'd have to compile your Trigger class, bundle it into a jar, and 
add that jar to the data source config's driver files section.




On Tuesday, 14 June 2016 05:35:17 UTC+2, Chronpwn wrote:
>
> I am using IntelliJ IDEA as my IDE, and they have an option where you can 
> specify the Driver URL. I tested this connection and am able to connect to 
> the database, but when I add a new trigger:
>
> public class VoteInsertTrigger extends TriggerAdapter {
>
> @Override
> public void fire(Connection conn, ResultSet oldRow, ResultSet newRow) 
> throws SQLException {
> Statement statement = conn.createStatement();
>
> boolean vote = newRow.getBoolean(newRow.findColumn("vote"));
> int deckId = newRow.getInt("deck_id");
> int value = vote ? 1 : -1;
>
> String sql = "UPDATE DECKS SET DECKS.RATING=DECKS.RATING + %d WHERE 
> DECKS.ID=%d";
> sql = String.format(sql, value, deckId);
>
> statement.executeUpdate(sql);
> }
> }
>
>
> With the command:
>
> CREATE TRIGGER vote_insert
> BEFORE INSERT
> ON USERS_VOTES
> FOR EACH ROW
> CALL "com.chronpwn.chronpwn.data.VoteInsertTrigger"
>
>
> it fails with: "cause: "org.h2.message.DbException: Class 
> ""VoteInsertTrigger"" not found"
>
> I rebuilt my entire project which puts the VoteInsertTrigger class in my 
> classpath.
>
> When you mention "The trigger class must be available in the classpath of 
> the database engine," my entire project + the H2 jar are on the 
> classpath, so I would appreciate some insight on why this is happening. 
> Thank you.
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To 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] Proposal: Change the docs for how to contribute

[Steve McLeod]

I've create a pull request with the doc changes:
https://github.com/h2database/h2database/pull/305


On Friday, 10 June 2016 07:45:06 UTC+2, Thomas Mueller Graf wrote:
>
> Hi,
>
> Yes, it would be great if you could update the documentation, if you have 
> time!
>
> Regards,
> Thomas
>
>
> On Thursday, June 9, 2016, Steve McLeod <steve@gmail.com > 
> wrote:
>
>> The H2 website has guidelines for contributing patches to H2:
>> http://h2database.com/html/build.html#providing_patches
>>
>> The guidelines include this:
>>
>> Submit patches as .patch files (compressed if big). To create a patch 
>>> using Eclipse, use Team / Create Patch.
>>
>>
>> However, ever since H2 moved from Subversion/Google Code to Git/Github, 
>> what's actually been mostly happening is that people are submitting GitHub 
>> pull requests. A conversation takes place on the pull request, and, if all 
>> doubts are resolved, then someone with appropriate authority merges the 
>> pull request.
>>
>> I'd like to update H2 "guidelines for contributing" to reflect this 
>> reality. This will also be helpful for people not familiar with the concept 
>> of pull requests on GitHub.
>>
>> Thomas, Noel, anyone else: Does this sound reasonable? If so, I'll go 
>> ahead and update the docs.
>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "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.

[h2] Re: H2 Corrupt database: Runtime Exception: rowCount expected 2593 got 2592, SQL Statement "INSERT [...]" [50000-172]

[Steve McLeod]

You should at the very least be using H2 1.3.176, the most recent stable 
version. I suggest doing that and seeing if you get less corruption 
problems.



On Thursday, 9 June 2016 16:30:17 UTC+2, Paul Erdos wrote:
>
> Hello,
> I get very often corrupted H2 databases, I'm able to recover them with the 
> recovery tool but this is getting annoying because while the database is 
> corrupt all insertions fail.
>
> I'm using one of the latest H2 stable releases "h2-1.3.172.jar" yet the 
> database gets corrupt, by analysing the database I realize somehow more 
> than one row with the same unique id is inserted in a table with several 
> thousands rows.
> Hence further insertions in the table fail.
>
> The database is shared between 2 windows machines running Java with the 
> connection String 
> "jdbc:h2:C:\HomeDir\db\mydb;create=true;AUTO_SERVER=TRUE;AUTO_RECONNECT=TRUE;TRACE_MAX_FILE_SIZE=3"
>
> The database would work well if it weren't for these sporadic corruptions 
> which happen regularly every month or so.
>
> Thank you for your help.
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Proposal: Change the docs for how to contribute

[Steve McLeod]

The H2 website has guidelines for contributing patches to H2:
http://h2database.com/html/build.html#providing_patches

The guidelines include this:

Submit patches as .patch files (compressed if big). To create a patch using 
> Eclipse, use Team / Create Patch.


However, ever since H2 moved from Subversion/Google Code to Git/Github, 
what's actually been mostly happening is that people are submitting GitHub 
pull requests. A conversation takes place on the pull request, and, if all 
doubts are resolved, then someone with appropriate authority merges the 
pull request.

I'd like to update H2 "guidelines for contributing" to reflect this 
reality. This will also be helpful for people not familiar with the concept 
of pull requests on GitHub.

Thomas, Noel, anyone else: Does this sound reasonable? If so, I'll go ahead 
and update the docs.


-- 
You received this message because you are subscribed to the Google Groups "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] When will stable version release?

[Steve McLeod]

Hi Thomas,

Thanks for investigating. I'm on the road for a couple of weeks; when I get 
a quiet evening soon, I'll check database size before and after compaction.

Do let me know if I can help further in profiling and analyzing this 
problem.

Regards,

Steve


On Tuesday, 10 May 2016 17:52:05 UTC+1, Thomas Mueller wrote:
>
> Hi,
>
> I found out, for your case, a lot of time is spend on garbage collection 
> of unused chunks. In your case this is about 40%, maybe this gets slower 
> and slower the larger the file.
>
> In the past, reference counting garbage collection was used, which is 
> fast, but it was not always accurate (the main problems are concurrency and 
> rollbacks). I need think how to best solve this. 
>
> To use reference counting GC, a simple way might be to use one immutable 
> tree, but that would reduce concurrency somewhat (not sure if that's 
> important). It would also solve another (unrelated) rollback problem. But 
> it's quite a big change.
>
> Just trying to make the existing GC faster is probably easier, but doesn't 
> scale quite as good. The same goes for doing GC only once in a while (at 
> most once every 10 seconds for example).
>
> Could you tell me what is the size of your database file (before and after 
> compaction)?
>
> Regards,
> Thomas
>
>
>
>
>
>
> On Wednesday, May 4, 2016, Steve McLeod <steve@gmail.com > 
> wrote:
>
>> Hi Thomas,
>>
>> I'll send you a private email with this info.
>>
>> Regards,
>>
>> Steve
>>
>>
>> On Tuesday, 3 May 2016 07:45:55 UTC+2, Thomas Mueller wrote:
>>>
>>> Hi Steve,
>>>
>>> I wonder, did you test with a spinning disk or a solid state disk? Also, 
>>> I would be interested to know how much the difference is.
>>>
>>> Of course it would be nice to have a reproducible test case, but if now, 
>>> then could you get full thread dumps (once per second for example) and heap 
>>> histograms (once every 20 seconds is enough)? I usually use the command 
>>> like way: "jstack -l  >> jstack.txt" and "jmap -histo  >> 
>>> jmap.txt". I would use an endless loop with "sleep 1" as a shell script.
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>>
>>> On Thursday, March 31, 2016, Steve McLeod <steve@gmail.com> wrote:
>>>
>>>> Hi Thomas,
>>>>
>>>> If keeping performance on par with the page store is an aim before 
>>>> removing the beta status, I'd say 1.4 is not ready.
>>>>
>>>> I did some performance tests today with my product against 1.4 with the 
>>>> MV Store and 1.4 with the page store. The page store is still much faster 
>>>> for a large number of inserts and updates.
>>>>
>>>> When building up a database that is 5 GB in size, inserts and updates 
>>>> seem to get progressively slower. 
>>>>
>>>> Let me know if any specific performance analysis would be useful.
>>>>
>>>> Regards,
>>>>
>>>> Steve
>>>>
>>>>
>>>>
>>>>
>>>> On Wednesday, 30 March 2016 19:34:27 UTC+2, Thomas Mueller wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> The MVStore I consider stable. What is relatively new is the table 
>>>>> engine that uses the MVStore, but even that one I would say is stable 
>>>>> now. 
>>>>> Unfortunately I didn't have time lately to do a release, I'm sorry about 
>>>>> that.
>>>>>
>>>>> Regards,
>>>>> Thomas
>>>>>
>>>>> On Mon, Mar 28, 2016 at 9:20 AM, yone <yone...@gmail.com> wrote:
>>>>>
>>>>>> Hi.
>>>>>>
>>>>>>
>>>>>> Now I'm measuring H2MV performance. And the result is very nice. 
>>>>>>
>>>>>> The benchmark of compaction ratio/read/write are better than LevelDB!!
>>>>>>
>>>>>> My product just use put/get/commit with synchronization.
>>>>>>
>>>>>> Is my simple operation stable or not?
>>>>>>
>>>>>>
>>>>>> If you decide the date when remove beta, please let us know.
>>>>>>
>>>>>> # There are already similar topic. I'm sorry if you feel bad
>>>>>>
>>>>>> Best regard,
>>>>>>
>>>>>

Re: [h2] When will stable version release?

[Steve McLeod]

Hi Thomas,

I'll send you a private email with this info.

Regards,

Steve


On Tuesday, 3 May 2016 07:45:55 UTC+2, Thomas Mueller wrote:
>
> Hi Steve,
>
> I wonder, did you test with a spinning disk or a solid state disk? Also, I 
> would be interested to know how much the difference is.
>
> Of course it would be nice to have a reproducible test case, but if now, 
> then could you get full thread dumps (once per second for example) and heap 
> histograms (once every 20 seconds is enough)? I usually use the command 
> like way: "jstack -l  >> jstack.txt" and "jmap -histo  >> 
> jmap.txt". I would use an endless loop with "sleep 1" as a shell script.
>
> Regards,
> Thomas
>
>
>
> On Thursday, March 31, 2016, Steve McLeod <steve@gmail.com 
> > wrote:
>
>> Hi Thomas,
>>
>> If keeping performance on par with the page store is an aim before 
>> removing the beta status, I'd say 1.4 is not ready.
>>
>> I did some performance tests today with my product against 1.4 with the 
>> MV Store and 1.4 with the page store. The page store is still much faster 
>> for a large number of inserts and updates.
>>
>> When building up a database that is 5 GB in size, inserts and updates 
>> seem to get progressively slower. 
>>
>> Let me know if any specific performance analysis would be useful.
>>
>> Regards,
>>
>> Steve
>>
>>
>>
>>
>> On Wednesday, 30 March 2016 19:34:27 UTC+2, Thomas Mueller wrote:
>>>
>>> Hi,
>>>
>>> The MVStore I consider stable. What is relatively new is the table 
>>> engine that uses the MVStore, but even that one I would say is stable now. 
>>> Unfortunately I didn't have time lately to do a release, I'm sorry about 
>>> that.
>>>
>>> Regards,
>>> Thomas
>>>
>>> On Mon, Mar 28, 2016 at 9:20 AM, yone <yone...@gmail.com> wrote:
>>>
>>>> Hi.
>>>>
>>>>
>>>> Now I'm measuring H2MV performance. And the result is very nice. 
>>>>
>>>> The benchmark of compaction ratio/read/write are better than LevelDB!!
>>>>
>>>> My product just use put/get/commit with synchronization.
>>>>
>>>> Is my simple operation stable or not?
>>>>
>>>>
>>>> If you decide the date when remove beta, please let us know.
>>>>
>>>> # There are already similar topic. I'm sorry if you feel bad
>>>>
>>>> Best regard,
>>>>
>>>> -- 
>>>> You received this message because you are subscribed 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 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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Calling H2 stored procedure results in 'JdbcSQLException: Function alias "PROCEDURE" not found'

[Steve McLeod]

I'm pretty certain that this is an EclipseLink bug. It is generating the 
wrong syntax to call a stored procedure (actually a user-defined function) 
in H2.

The syntax is
 CALL logProc() 
and not 
 EXECUTE PROCEDURE logProc()


On Sunday, 1 May 2016 19:47:02 UTC+2, Szymon Kuryło wrote:
>
> Table definition (src/test/resources/db.sql):
> create table if not exists users(
> user_id identity primary key,
> user_name varchar
> );
>
> insert into users(user_name) values('John Doe');
> insert into users(user_name) values('Jane Doe');
>
> create alias if not exists logProc as $$
> void loglog() {
>   System.out.println("Hello world!");
> }
> $$;
>
> Persistence.xml:
> 
> com.pany.User
> true
> 
>  value="org.h2.Driver" />
>
> value="jdbc:h2:mem:test;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;INIT=runscript
>  
> from 'src/test/resources/db.sql'" />
> 
>  value="org.eclipse.persistence.platform.database.H2Platform" />
> 
> 
>
> User class:
> package com.pany;
> import javax.persistence.*;
>
> @NamedStoredProcedureQuery(
> name = "log",
> procedureName = "logProc"
> )
> @Entity
> @Table(name = "users")
> public class User {
>
> @Id
> @GeneratedValue(strategy = GenerationType.IDENTITY)
> @Column(name = "user_id")
> public int userId;
>
> @Column(name = "user_name")
> public String userName;
> }
>
> Executing
> entityManager.createNamedStoredProcedureQuery("log").execute();
> or
> entityManager.createNamedQuery("log").getResultList();
> results in
>
> javax.persistence.PersistenceException: Exception [EclipseLink-4002] 
> (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): 
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: org.h2.jdbc.JdbcSQLException: Function alias 
> "PROCEDURE" not found; SQL statement:
> EXECUTE PROCEDURE logProc() [90077-191]
> Error Code: 90077
> Call: EXECUTE PROCEDURE logProc()
> Query: ResultSetMappingQuery(name="log" )
>
> at 
> org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377)
> at 
> org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
> at 
> org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:316)
> at com.pany.UserDao.log(UserDao.java:22)
> at com.pany.MainTest.test(MainTest.java:16)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at 
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at 
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at 
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at 
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at 
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at 
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at 
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at 
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
> at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
> Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 
> 2.5.0.v20130507-3faac2b): 
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: org.h2.jdbc.JdbcSQLException: Function alias 
> "PROCEDURE" not found; SQL statement:
> EXECUTE PROCEDURE logProc() [90077-191]
> Error Code: 90077
> Call: EXECUTE PROCEDURE logProc()
> Query: ResultSetMappingQuery(name="log" )
> at 
> org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
> at 
> 

[h2] Re: Having trouble increasing performance of a large table

[Steve McLeod]

* Do you need 3 million rows returned? Or, say, just the first 10 or 100 or 
1000 rows? Then use SELECT TOP 100 at the beginning of your query. This 
should give you an enormous gain in query speed.
* Can you somehow get rid of the OR in your where clause? The OR is going 
to cause H2 to scan every row with division = 2.


On Sunday, 24 April 2016 18:39:15 UTC+2, Brett Ryan wrote:
>
> I have a table with 12 million rows that isn't very complex, though the 
> table has a primary compound index of three fields. This is a 
> representation of an OpenEdge DB that we have in production that does seem 
> to perform quite well for the same sort of query.
>
> create table if not exists widget (
>   container_id integer not null default 0,
>   x integer default 0,
>   y integer default 0,
>   z integer default 0,
>   num integer default 0,
>   quantity integer default 0,
>   constraint pk_pocket_idx_prime primary key (container_id, x, y, z)
> );
> create index if not exists "ix_widget_1" on pocket (container_id, num, z);
>
> Any query that doesn't access the fields will perform quickly, such as a 
> count(*). 
>
> Ultimately I'm joining through using ix_widget_1 and the query being 
> performed will approximately return 3 million rows. using count(*) the 
> query performs pretty quickly, but as soon as I access any of the data 
> items the query takes around 20 minutes to return.
>
> Is there anything I can do to increase the performance of this table?
>
> Basically the query I'm trying to perform looks as follows.
>
>  select br.ACCOUNT_NUMBER
> r.CODE,
> r.DESCRIPTION,
> p.NUM,
> p.Z
>   from ACCOUNT br
>   left join ACCOUNT_PLAN rc on rc.ACCOUNT_PLAN_ID = br.TEMP_PLAN_ID
>   left join ACCOUNT_PLAN rf on rf.ACCOUNT_PLAN_ID = br.PRIME_PLAN_ID
>   join CONTAINER r on r.ACCOUNT_PLAN_ID = nvl(rc.ACCOUNT_PLAN_ID, 
> rf.ACCOUNT_PLAN_ID)
>   join WIDGET p on p.CONTAINER_ID = r.CONTAINER_ID
>  where br.DIVISION = 2
>and (rc.ACCOUNT_PLAN_ID is not null or rf.ACCOUNT_PLAN_ID is not null)
>   order by br.ACCOUNT_NUMBER,
>r.CODE,
>p.NUM,
>p.Z
>
> Executing the query with all tables except for WIDGET return without 
> issue, it's just when I add WIDGET I see the performance killed.
>
> NOTE: I have renamed objects and fields for the purpose of this post.
>

-- 
You received this message because you are subscribed to the Google Groups "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] Re: Timeout trying to lock table

[Steve McLeod]

Can MyBatis log all SQL statements it generates? If so, that'd be a good 
starting point for reproducible steps.


On Monday, 18 April 2016 23:02:58 UTC+2, Stuart Goldberg wrote:
>
> Steve.
>
> For full disclosure we use MyBatis as our ORM. I wrote a simple JDBC 
> program that mimics the table we use in our real environment. It has a 
> indexed timestamp field and we are trying to delete rows less than a 
> certain time. In my test code I cannot reproduce the problem, but with our 
> real application. It happens regularly even after I recreate the index.
>
> The code that deletes rows runs as a cleanup routine during the overnight. 
>
> This is a mystery.
>
> The database is corrupt after this happens. Is there some analysis I might 
> be able to do on my side to help figure out the source of the corruption?
>
>
>
> On Saturday, April 9, 2016 at 6:20:06 AM UTC-4, Steve McLeod wrote:
>>
>> Right, I was a bit vague. I meant to suggest that there might be another 
>> obscure bug related to this one. Over the last year I've found a couple of 
>> timestamp-related issues. If you are able to find a set of steps that 
>> reliably reproduce the problem, I'll investigate it. The repro steps might 
>> be "use version x to create db as follows, then try to update to version y".
>>
>>
>>
>> On Friday, 8 April 2016 16:30:18 UTC+2, Stuart Goldberg wrote:
>>>
>>> Steve:
>>>
>>> I'm going to answer my own question. I looked at your code change and 
>>> indeed the current version of H2 (1.4.191) has your changes, yet this is 
>>> the version that I am using!
>>>
>>> What's gripping about your posting is that the exception I am getting 
>>> involves an index based on time stamps!
>>>
>>> Could there be some other place in the H2 code that exhibits this bug 
>>> that might explain what I am seeing?
>>>
>>> - Stuart
>>>
>>> On Friday, April 8, 2016 at 9:08:32 AM UTC-4, Stuart Goldberg wrote:
>>>>
>>>> What release number is that? Because I think I'm using the latest 
>>>> release.
>>>>
>>>> Please clarify
>>>> On Apr 8, 2016 8:09 AM, "Steve McLeod" <steve@gmail.com> wrote:
>>>>
>>>>> This reminds me somewhat a bug I encountered in January. It affected 
>>>>> what was then the latest update, and the problem affected the old 
>>>>> PageStore. I fixed it, and the fix is in the current release of H2:
>>>>> https://github.com/h2database/h2database/pull/224
>>>>>
>>>>> The appearance of the bug was the same: searching in an index for the 
>>>>> row to delete sometimes failed.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Thursday, 7 April 2016 09:19:20 UTC+2, Noel Grandin wrote:
>>>>>>
>>>>>> Hmmm, It's vaguely possible that you're hitting a bug in H2, but 
>>>>>> you're using the old PageStore, which is pretty robust. 
>>>>>>
>>>>>> At a guess, I would say most likely you have dodgy hardware, and that 
>>>>>> index shows up as the corruption just because that 
>>>>>> index gets hit the most. 
>>>>>>
>>>>> -- 
>>>>> 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/wEdRn9bbwMM/unsubscribe.
>>>>> To unsubscribe from this group and all its topics, send an email to 
>>>>> h2-database...@googlegroups.com.
>>>>> To post to this group, send email to h2-da...@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] Re: Timeout trying to lock table

[Steve McLeod]

Right, I was a bit vague. I meant to suggest that there might be another 
obscure bug related to this one. Over the last year I've found a couple of 
timestamp-related issues. If you are able to find a set of steps that 
reliably reproduce the problem, I'll investigate it. The repro steps might 
be "use version x to create db as follows, then try to update to version y".



On Friday, 8 April 2016 16:30:18 UTC+2, Stuart Goldberg wrote:
>
> Steve:
>
> I'm going to answer my own question. I looked at your code change and 
> indeed the current version of H2 (1.4.191) has your changes, yet this is 
> the version that I am using!
>
> What's gripping about your posting is that the exception I am getting 
> involves an index based on time stamps!
>
> Could there be some other place in the H2 code that exhibits this bug that 
> might explain what I am seeing?
>
> - Stuart
>
> On Friday, April 8, 2016 at 9:08:32 AM UTC-4, Stuart Goldberg wrote:
>>
>> What release number is that? Because I think I'm using the latest release.
>>
>> Please clarify
>> On Apr 8, 2016 8:09 AM, "Steve McLeod" <steve@gmail.com > 
>> wrote:
>>
>>> This reminds me somewhat a bug I encountered in January. It affected 
>>> what was then the latest update, and the problem affected the old 
>>> PageStore. I fixed it, and the fix is in the current release of H2:
>>> https://github.com/h2database/h2database/pull/224
>>>
>>> The appearance of the bug was the same: searching in an index for the 
>>> row to delete sometimes failed.
>>>
>>>
>>>
>>>
>>> On Thursday, 7 April 2016 09:19:20 UTC+2, Noel Grandin wrote:
>>>>
>>>> Hmmm, It's vaguely possible that you're hitting a bug in H2, but you're 
>>>> using the old PageStore, which is pretty robust. 
>>>>
>>>> At a guess, I would say most likely you have dodgy hardware, and that 
>>>> index shows up as the corruption just because that 
>>>> index gets hit the most. 
>>>>
>>> -- 
>>> 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/wEdRn9bbwMM/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to 
>>> h2-database...@googlegroups.com .
>>> To post to this group, send email to h2-da...@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] Re: Timeout trying to lock table

[Steve McLeod]

This reminds me somewhat a bug I encountered in January. It affected what 
was then the latest update, and the problem affected the old PageStore. I 
fixed it, and the fix is in the current release of H2:
https://github.com/h2database/h2database/pull/224

The appearance of the bug was the same: searching in an index for the row 
to delete sometimes failed.




On Thursday, 7 April 2016 09:19:20 UTC+2, Noel Grandin wrote:
>
> Hmmm, It's vaguely possible that you're hitting a bug in H2, but you're 
> using the old PageStore, which is pretty robust. 
>
> At a guess, I would say most likely you have dodgy hardware, and that 
> index shows up as the corruption just because that 
> index gets hit the most. 
>

-- 
You received this message because you are subscribed to the Google Groups "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] When will stable version release?

[Steve McLeod]

Hi Thomas,

If keeping performance on par with the page store is an aim before removing 
the beta status, I'd say 1.4 is not ready.

I did some performance tests today with my product against 1.4 with the MV 
Store and 1.4 with the page store. The page store is still much faster for 
a large number of inserts and updates.

When building up a database that is 5 GB in size, inserts and updates seem 
to get progressively slower. 

Let me know if any specific performance analysis would be useful.

Regards,

Steve




On Wednesday, 30 March 2016 19:34:27 UTC+2, Thomas Mueller wrote:
>
> Hi,
>
> The MVStore I consider stable. What is relatively new is the table engine 
> that uses the MVStore, but even that one I would say is stable now. 
> Unfortunately I didn't have time lately to do a release, I'm sorry about 
> that.
>
> Regards,
> Thomas
>
> On Mon, Mar 28, 2016 at 9:20 AM, yone  
> wrote:
>
>> Hi.
>>
>>
>> Now I'm measuring H2MV performance. And the result is very nice. 
>>
>> The benchmark of compaction ratio/read/write are better than LevelDB!!
>>
>> My product just use put/get/commit with synchronization.
>>
>> Is my simple operation stable or not?
>>
>>
>> If you decide the date when remove beta, please let us know.
>>
>> # There are already similar topic. I'm sorry if you feel bad
>>
>> Best regard,
>>
>> -- 
>> You received this message because you are subscribed 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 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.

[h2] Re: To which H2 version should we update in productive environments

[Steve McLeod]

I develop a product that has extremely high use of H2, and uses the 
PageStore. We've tried the MVStore but still find some stability issues 
compared to the the PageStore.

I recommend the most recent stable version of 1.3, which is 1.3.176.

If you want to keep using the PageStore (which I recommend), and want the 
latest bug fixes and performance improvements, then use 1.4.190 but make 
sure to keep using the PageStore. Do this by adding ";MV_STORE=FALSE" to 
your JDBC URL. 




On Friday, 8 January 2016 05:32:31 UTC+1, Reinhold Bihler wrote:
>
> Hello,
>
> we are successfully using H2 in one of our products for more then 3 years 
> now. As customers are using our product with more load and parallel 
> requests to the H2 db we experienced the "H2 LOB bug" reported here 
> . We are 
> currently using H2 Version 1.3.164. For testing we upgraded to version 
> 1.4.187 and do no longer experience the "H2 LOB bug". Just now we saw 
> that 1.4 is still marked as beta.
>
> So, to what H2 version should we update?
>
> Note: We do have several customers with existing H2 1.3.164 databases 
> which we need to update as seamless as possible.
>
> Thanks in advance for your response and great work!
>
> Reinhold
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: Fix for problem affecting "Europe/Moscow" timezone

[Steve McLeod]

Hi Paul,

I guess I was unclear. I believe it to be an H2 bug, not a Java bug. H2 was 
reusing calendar instances, sometimes without first calling the 
"calendar.clear()" method. By chance, this was okay with the way H2 used 
it, until a Java update with a revised timezone database file was issued. 

The Moscow timezone changed in late 2014 from UTC+4 year-round to UTC+3 
year-round, I understand. This issue seems to be related to the bug.

I posted repro code and instructions in the pull request:
https://github.com/h2database/h2database/pull/224

Even with the appropriate combination of Java version, H2 version, and 
timezone, the bug only occurred if particular dates were present in the 
database. See my pull request for a specific instance of when this happened.

Regards,

Steve




On Wednesday, 30 December 2015 10:07:45 UTC+1, pwagland wrote:
>
> Hi Steve,
>
> Do you have more details as to what the bug is? And/or is there an 
> Oracle/OpenJDK issue reference for the bug?
>
> Cheers,
> Paul
>
>

-- 
You received this message because you are subscribed to the Google Groups "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] Poor performance

[Steve McLeod]

James, I think query optimizer hints would really help here. Unfortunately, 
no one has offered to implement them yet, I think.



On Wednesday, 23 December 2015 18:07:00 UTC+1, James Sheets wrote:
>
> Just to be clear, by "covering" I meant an index that covers the predicate 
> and the projection.
>

-- 
You received this message because you are subscribed to the Google Groups "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.

[h2] Re: 1.4 extrem slow

[Steve McLeod]

You'll have more chance of help if you make a self-contained reproducible 
test. The test should create the table, populate the table, run the SELECT 
+ UPDATE. Make sure to include your JDBC URL.

3 million rows shouldn't be necessary to reproduce the problem - it should 
become apparent with much fewer rows.



On Tuesday, 8 December 2015 07:37:04 UTC+1, Dave Anonymous wrote:
>
> i tried an upgrade to 1.4 yesterday and had several problems.
> one of them is the speed. i have an update function which convert a column 
> of ~3.000.000 rows. it took about 6 minutes to convert them with 1.3.176.
> with 1.4 after 14 hours 750.000 rows are converted (mv_store true or false 
> makes no difference).
>
> the function is really easy (id is primary key, alarmdate has an index):
> try(PreparedStatement selectPstmt = cnctn.prepareStatement("SELECT ID, 
> ALARMDATETEMP FROM ALARM_HISTORY;");){
> ResultSet result = selectPstmt.executeQuery();
> while(result.next()){
> byte[] alarmDate = result.getBytes("ALARMDATETEMP");
> Timestamp alarmDateStamp = 
> this.convertByteToTimeStamp(alarmDate);
> try(PreparedStatement updatePstmt = 
> cnctn.prepareStatement("UPDATE ALARM_HISTORY SET ALARMDATE=? WHERE ID=?;")){
> updatePstmt.setTimestamp(1, alarmDateStamp);
> updatePstmt.setInt(2, result.getInt("ID"));
> updatePstmt.executeUpdate();
> }
> }
> }.
>
>
>

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

[h2] Re: Database locked on long running DELETE query

[Steve McLeod]

My understanding is that H2 is single-threaded, unless you use 
MULTI_THREADED or possibly MVCC. The second query needs to wait until the 
first completes.

This is explained in the docs 
here: http://www.h2database.com/html/features.html#multiple_connections




On Saturday, 28 November 2015 06:44:50 UTC+1, Pierre N wrote:
>
> I have a long running query "delete from mytable" which last ~30 seconds 
> (deleting ~400 000 records).
>
> While this query is running, the database is totally locked, I can't 
> execute another query. 
>
> Well I can execute the query, but it will be executed only after the 
> "delete" query is done. Even if it is a simple query like "select 1" or 
> "select 1 from dual"
>
> However if I start a long running query like "select 
> hash('sha256','',2000);" I can execute in parallel "select 1" and 
> it will return immediatly.
>
> Why the "delete" statement is locking everything ?
>

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

[h2] Re: WebServlet container authentication

[Steve McLeod]

Hi Lukáš,

This is a generic WebServlet+database question, and not an H2-specific 
question, no? You should ask this question on stackoverflow.com. In fact, 
you'll probably find it is already answer there.


On Wednesday, 11 November 2015 09:15:02 UTC+1, Lukáš Vasek wrote:
>
> Hi,
> Is there any option how to configure WebServlet to authenticate to db 
> using container authentication and using jndi?
>
>
>

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

[h2] Re: What's your experience which huge databases (5M+)?

[Steve McLeod]

5M+ whats? rows? bytes?


On Tuesday, 27 October 2015 10:10:04 UTC+1, Benjamin Asbach wrote:
>
> Hi there,
>
> I'm looking for some experiences which tables with a huge amount of data 
> (5M+). To be a little bit more concrete what I'm interested in:
>
> * How much data do you store (lines per table (amount of columns) , total 
> lines)
> * Which hardware you use (CPU, RAM)
> * How you run H2
> * How do you query your database and how's the response time
>
> Looking forward for feedback
> Benjamin
>

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

[h2] Re: Issue between version 1.4.188 and 1.4.189

[Steve McLeod]

What's the username/password for this H2 database?

I ask because I did some work in the DateTimeUtils class that was merged 
into the latest release, and I'd like to double check with your database 
that my code is not at fault here.


On Thursday, 17 September 2015 08:03:30 UTC+2, IntensiveH2 wrote:
>
> Hello Thomas,
>
> I attached a zip file (please, remove the .aaa extension). This DB is 
> created with version 1.4.188.
>
> Thanks.
>
> On Wednesday, September 16, 2015 at 11:14:09 AM UTC+2, IntensiveH2 wrote:
>>
>> Hello,
>>
>> Since I use the version 1.4.189 with  an existing DB created with 
>> version 1.4.188 (option like 
>> MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;CACHE_TYPE=SOFT_LRU;MV_STORE=FALSE
>>  
>> ), I have a lot of issue with column of kind datetime
>>
>> INFO   | jvm 1| 2015/09/16 10:25:32 | org.h2.jdbc.JdbcSQLException: 
>> Cannot parse "TIMESTAMP" constant "8"; SQL statement:
>> INFO   | jvm 1| 2015/09/16 10:25:32 | SELECT 
>> A0.idmcprodinstance,A0.id FROM MCJOBHISTORY A0 LEFT OUTER JOIN 
>> MCPRODINSTANCE A1 ON A0.idmcprodinstance=A1.id WHERE ( (A0.processendstatus 
>> <> ? OR  (A0.jobreturncode <> ?)) AND A0.idmcprodinstance IS NOT NULL ) AND 
>>  ((A1.lastacknowledge IS NULL  OR  (A0.enddate > A1.lastacknowledge)) AND 
>> A0.enddate >= ?) [22007-189]
>> 
>> INFO   | jvm 1| 2015/09/16 10:25:34 | 2015/09/16 10:25:34 ERROR 
>> [pperListener_start_runner] mserver.H2Listener 
>> (com.infovista.mserver.H2Listener:H2Listener.java:exceptionThrown:57)
>> INFO   | jvm 1| 2015/09/16 10:25:34 |  [DATABASE] exceptionThrown 
>> sql=SELECT min(A0.startdate) FROM MCJOBHISTORY A0
>> INFO   | jvm 1| 2015/09/16 10:25:34 | org.h2.jdbc.JdbcSQLException: 
>> Cannot parse "TIMESTAMP" constant "0"; SQL statement:
>> INFO   | jvm 1| 2015/09/16 10:25:34 | SELECT min(A0.startdate) FROM 
>> MCJOBHISTORY A0 [22007-189]
>> INFO   | jvm 1| 2015/09/16 10:25:34 | at 
>> org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
>> .
>> INFO   | jvm 1| 2015/09/16 10:25:34 | Caused by: 
>> java.lang.IllegalArgumentException: 0
>> INFO   | jvm 1| 2015/09/16 10:25:34 | at 
>> org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:303)
>> INFO   | jvm 1| 2015/09/16 10:25:34 | at 
>> org.h2.value.ValueTimestamp.parseTry(ValueTimestamp.java:140)
>> INFO   | jvm 1| 2015/09/16 10:25:34 | at 
>> org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:120)
>> with 
>> create table MCJobHistory (
>>  id   intnot null,
>>  parametersString varchar(2) null,
>>  idMCProdInstance intnull,
>>  startDatedatetime   null,
>>  endDate  datetime   null,
>> ..
>>
>> Maybe it's due to recent change to h2.storeLocalTime.
>> Nevertheless this issue is very annoying for me because the only way is 
>> to migrate data.
>>
>> Do you have a workaround helping me?
>>
>> Regards
>>
>>

-- 
You received this message because you are subscribed to the Google Groups "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] alias in HAVING clause

[Steve McLeod]

To clarify my own post: I'd expect the SELECT to fail in both cases.

On Friday, 28 August 2015 12:41:10 UTC+2, Steve McLeod wrote:

 I agree with Taras here - I've been bitten by the same behaviour. But this 
 topic's title is misleading; it not because of the HAVING keyword and it is 
 not because of the column alias.

 Fundamentally, depending on the data in the table, sometimes you can 
 include a column in the select clause that is not in the group by clause. 
 Here is a simple repro :

 create table foobar (col1 int, col2 int);
 insert into foobar (col1, col2) values (1, 2);

 -- this time the select works
 select col1, col2 from foobar group by col1;

 insert into foobar (col1, col2) values (1, 3);

 -- this time the select fails
 select col1, col2 from foobar group by col1;

 Output:

 Column COL2 must be in the GROUP BY list; SQL statement:
 select col1, col2 from foobar group by col1 [90016-176] 


 On Friday, 28 August 2015 10:37:49 UTC+2, Taras Fedkiv wrote:

 Hi Thomas,
 Could you please answer the questions from previous email? 
 My API allows clients to enter SELECT queries which are executed on H2 
 db. Thats why logical H2 behaviur is very important. 

 I look forward to your reply,
 Taras



-- 
You received this message because you are subscribed to the Google Groups 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] alias in HAVING clause

[Steve McLeod]

I agree with Taras here - I've been bitten by the same behaviour. But this 
topic's title is misleading; it not because of the HAVING keyword and it is 
not because of the column alias.

Fundamentally, depending on the data in the table, sometimes you can 
include a column in the select clause that is not in the group by clause. 
Here is a simple repro :

create table foobar (col1 int, col2 int);
insert into foobar (col1, col2) values (1, 2);

-- this time the select works
select col1, col2 from foobar group by col1;

insert into foobar (col1, col2) values (1, 3);

-- this time the select fails
select col1, col2 from foobar group by col1;

Output:

Column COL2 must be in the GROUP BY list; SQL statement:
select col1, col2 from foobar group by col1 [90016-176] 


On Friday, 28 August 2015 10:37:49 UTC+2, Taras Fedkiv wrote:

 Hi Thomas,
 Could you please answer the questions from previous email? 
 My API allows clients to enter SELECT queries which are executed on H2 db. 
 Thats why logical H2 behaviur is very important. 

 I look forward to your reply,
 Taras


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

[h2] Re: Syntax error in SQL statement SELECT ...; expected identifier

[Steve McLeod]

You'll notice that immediately after the WITH keyword, there is [*]. This 
indicates where the syntax error was.

Without the WITH it should work as intended, no?

SELECT * 
  FROM policies 
  JOIN
 (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS 
mg 
  ON policies.id = mg.id AND policies.generationId = mg.maxgen


On Monday, 24 August 2015 19:43:15 UTC+2, etyr...@gmail.com wrote:

 Hi All,

 I have a table, policies, that has id and generationId columns.  I 
 want to get a row for id with the greatest generationId for that id. 
  My query is as follows:

 SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
 FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
 policies.generationId = mg.maxgen

 It seems that the database doesn't recognize maxgen as an identifier. 
 Below is the error I get.  Any suggestions would be appreciated.  I am 
 using 1.4.188 beta.

 Syntax error in SQL statement SELECT * FROM POLICIES JOIN WITH[*] (SELECT 
 ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES GROUP BY ID) AS MG ON 
 POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = MG.MAXGEN ; expected 
 identifier; SQL statement:
 SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
 FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
 policies.generationId = mg.maxgen [42001-188] 
 http://10.40.202.72:50548/query.do?jsessionid=66b99f60d78a7ce477313a8c97a5de99#
  42001/42001 (Help) 
 http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c42001
 org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement SELECT * FROM 
 POLICIES JOIN WITH[*] (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES 
 GROUP BY ID) AS MG ON POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = 
 MG.MAXGEN ; expected identifier; SQL statement:
 SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen 
 FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND 
 policies.generationId = mg.maxgen [42001-188]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:345 
 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=345build=188
 )
 at org.h2.message.DbException.getSyntaxError(DbException.java:205 
 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=205build=188
 )
 at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3060 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=3060build=188
 )
 at org.h2.command.Parser.readTableFilter(Parser.java:1191 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1191build=188
 )
 at org.h2.command.Parser.readJoin(Parser.java:1540 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1540build=188
 )
 at org.h2.command.Parser.parseJoinTableFilter(Parser.java:1879 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1879build=188
 )
 at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1874build=188
 )
 at org.h2.command.Parser.parseSelectSimple(Parser.java:1982 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1982build=188
 )
 at org.h2.command.Parser.parseSelectSub(Parser.java:1867 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1867build=188
 )
 at org.h2.command.Parser.parseSelectUnion(Parser.java:1688 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1688build=188
 )
 at org.h2.command.Parser.parseSelect(Parser.java:1676 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=1676build=188
 )
 at org.h2.command.Parser.parsePrepared(Parser.java:432 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=432build=188
 )
 at org.h2.command.Parser.parse(Parser.java:304 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=304build=188
 )
 at org.h2.command.Parser.parse(Parser.java:276 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=276build=188
 )
 at org.h2.command.Parser.prepareCommand(Parser.java:241 
 http://h2database.com/html/source.html?file=org/h2/command/Parser.javaline=241build=188
 )
 at org.h2.engine.Session.prepareLocal(Session.java:461 
 http://h2database.com/html/source.html?file=org/h2/engine/Session.javaline=461build=188
 )
 at org.h2.server.TcpServerThread.process(TcpServerThread.java:264 
 http://h2database.com/html/source.html?file=org/h2/server/TcpServerThread.javaline=264build=188
 )
 at org.h2.server.TcpServerThread.run(TcpServerThread.java:159 
 http://h2database.com/html/source.html?file=org/h2/server/TcpServerThread.javaline=159build=188
 )
 at java.lang.Thread.run(Thread.java:745)

 at org.h2.engine.SessionRemote.done(SessionRemote.java:624 
 

Re: [h2] Negative zero of DOUBLE type

[Steve McLeod]

Christopher,

You can use the latest H2 1.4 release with MV_STORE=false in the JDBC URL. This 
will, in effect, give you what could be considered an H2 1.3 database engine, 
but with the performance tweaks and bug fixes of the last year or so included.

-- 
You received this message because you are subscribed to the Google Groups 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 Engine: New version 1.4.188 available

[Steve McLeod]

Specifically, it is here: https://github.com/h2database

Thanks to GitHub, you can easily fork, watch, and issue pull requests.

On Monday, 10 August 2015 07:54:18 UTC+2, Noel Grandin wrote:

 It's on GitHub now. 
 On Mon, 10 Aug 2015 at 02:38, Kenton Garner kenton...@gmail.com 
 javascript: wrote:

 Thomas,

 I actually would like to get the latest source - the links you have 
 attached are only the compiled jars.  
 Is there a another way to get access to the source?  
 I have used the SVN link in the past - but as has been stated, the files 
 are not up to date - still version 187.

 I need the source because i still have mods I require so my application 
 instance can determine if it is the owner (embedded instance) of the shared 
 db connection.
 I believe we have spoken about this in the past and I do not think my mod 
 is a solution that is exceptable for all use cases so I need to compile my 
 own.



 On Tuesday, August 4, 2015 at 2:56:05 PM UTC-4, Thomas Mueller wrote:

 Hi,

 I see. I don't know what the problem is, but I have uploaded the Maven 
 files to SourceForge as usual. For some reason outside of my control, they 
 are not synchronized to the Maven repository. I will ask the Maven guys 
 what the problem is.

 The files are here:


 http://hsql.sourceforge.net/m2-repo/com/h2database/h2/1.4.188/h2-1.4.188.jar

 and also here:

 http://h2database.com/m2-repo/com/h2database/h2/1.4.188/h2-1.4.188.jar

 From time to time, synchronzation doesn't work, but the last time I got 
 a mail was quite a long time ago, see below.

 Regards,
 Thomas

 

 -- Forwarded message --
 From: ma...@repo1.maven.org ma...@repo1.maven.org
 Date: Mon, Jul 20, 2015 at 10:30 PM
 Subject: [Maven Central Nexus Repository Synchronization] FAILURE

 To: cen...@sonatype.com cen...@sonatype.com, Thomas Mueller 
 thomas.to...@gmail.com


 --- Some repositories were not synchronized ---
 groupId: com.h2database
 Error:
 ssh: connect to host web.sourceforge.net port 22: Connection refused
 rsync: connection unexpectedly closed (0 bytes received so far) 
 [receiver]
 rsync error: unexplained error (code 255) at io.c(600) [receiver=3.0.6]
 Error synchronizing metadata. Exit code: 255




 On Tuesday, August 4, 2015, Kenton Garner kenton...@gmail.com wrote:

 Thomas,
 I was updating from the svn repository.  I saw a lot of changes but the 
 version number in the jar file remained at 187 and when I grep'd for the 
 version number in the code - the src/main/org/h2/engine/Constants.java 
 file 
 has the public static final int BUILD_ID = 187;



 On Tue, Aug 4, 2015 at 1:45 AM, Thomas Mueller 
 thomas.tom.muel...@gmail.com wrote:

 Hi,
  

 I don't seem to have a new build ID in the constants file either... 
 it is still showing 187.  Is there something I am missing?


 Where exactly did you look?

 Regards,
 Thomas

 -- 
 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/1qk80-GcnV4/unsubscribe.
 To unsubscribe from this group and all its topics, send an email to 
 h2-database+unsubscr...@googlegroups.com.
 To post to this group, send email to h2-database@googlegroups.com.
 Visit this group at http://groups.google.com/group/h2-database.
 For more options, visit https://groups.google.com/d/optout.


 -- 
 You received this message because you are subscribed to the Google 
 Groups H2 Database group.
 To unsubscribe from 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...@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.
 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] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

@rado,

Thanks for the suggestion. I tried Tomcat's connection pool, and it is 
indeed exactly what I need.


On Saturday, 8 August 2015 10:04:46 UTC+2, rado wrote:

 I would recommend you to try the tomcat connection pool. I think it is 
 available as a separate distributable 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.
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] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

Hi Thomas,

Thanks for the tip. I spent time today trialling various connection pools 
with H2. Surprisingly, nothing I found compares with using H2's own query 
cache + connection pool for both speed and ease.


On Thursday, 6 August 2015 20:01:47 UTC+2, Thomas Mueller wrote:

 Hi,

 Did you try using a more advanced connection pool? One that re-uses 
 prepared statements. The H2 one is really simple and prevents that.

 Regards,
 Thomas



 On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote:

 Noel, I think you  are right. I use this pattern for each query:

 public void insertARow(int x) {
 String sql = insert into yada yada yada;
 try (Connection conn = getConnectionFromConnectionPool(); 
 PreparedStatement statement = conn.prepareStatement(sql)) {
 statement.setInt(1, x);
 statement.executeUpdate();
 }
 }

 It is based on keeping the database as unlocked as possible, in my 
 multi-threaded app. I may need to change the pattern a bit.

 I deduced that Parser.initialize, for an SQL statement with n characters
 * creates an array of n+1 ints
 * an array of n+1 chars
 * calls String.getChars() , which in turn calls System.arraycopy() for 
 n+1 characters
 * calls new String() , which in turn calls System.arraycopy() for n+1 
 characters

 All of these result in memory that escapes the method, so will be created 
 on the JVM's heap.

 Although this should all be blindingly fast, the fact that the rest of H2 
 is so fast, like you said,  makes this show up. I think I was seeing this, 
 because for an SQL statement with 3000 characters, being performed 10,000 
 times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being 
 allocated on the heap. And indeed, in my profiling, I noticed a lot of 
 churn on the heap. 

 Cheers,

 Steve


 On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:

 The thing is, I don't think there is a problem. I think that your code 
 is not caching PreparedStatement 's properly, and the rest of H2 is so 
 fast, that the only thing left in the profile is the parser initialisation 
 :)

 On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com wrote:

 Hi Noel,

 I've actually solved this problem of PreparedStatement caching for my 
 app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping 
 solve the bigger issue of why it seems to take a comparatively significant 
 time to create a PreparedStatement.

 Cheers,

 Steve


 On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:


 Thanks, I'll have a look tomorrow at them in detail. 

 Tell me, how often is JdbcConnection@preparedStatement called compared 
 to how many times you execute a query? 

 If it's every time, it means that your PreparedStatement caching is 
 not working, which would indicate a problem with 
 your connection pool, or something similar. 

 -- 
 You received this message because you are subscribed 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.
 For more options, visit https://groups.google.com/d/optout.

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



-- 
You received this message because you are subscribed to the Google Groups 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] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

Noel, I think you  are right. I use this pattern for each query:

public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool(); 
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}

It is based on keeping the database as unlocked as possible, in my 
multi-threaded app. I may need to change the pattern a bit.

I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for n+1 
characters
* calls new String() , which in turn calls System.arraycopy() for n+1 
characters

All of these result in memory that escapes the method, so will be created 
on the JVM's heap.

Although this should all be blindingly fast, the fact that the rest of H2 
is so fast, like you said,  makes this show up. I think I was seeing this, 
because for an SQL statement with 3000 characters, being performed 10,000 
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being 
allocated on the heap. And indeed, in my profiling, I noticed a lot of 
churn on the heap. 

Cheers,

Steve


On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:

 The thing is, I don't think there is a problem. I think that your code is 
 not caching PreparedStatement 's properly, and the rest of H2 is so fast, 
 that the only thing left in the profile is the parser initialisation :)

 On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com 
 javascript: wrote:

 Hi Noel,

 I've actually solved this problem of PreparedStatement caching for my app 
 by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve 
 the bigger issue of why it seems to take a comparatively significant time 
 to create a PreparedStatement.

 Cheers,

 Steve


 On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:


 Thanks, I'll have a look tomorrow at them in detail. 

 Tell me, how often is JdbcConnection@preparedStatement called compared 
 to how many times you execute a query? 

 If it's every time, it means that your PreparedStatement caching is not 
 working, which would indicate a problem with 
 your connection pool, or something similar. 

 -- 
 You received this message because you are subscribed 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.
 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] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

My latest findings:

I can reproduce (again, sporadically) the problem by exclusively using h2 
1.4.188 as follows:
1) set System.setProperty(h2.storeLocalTime, false), start the app, 
import some data; then
2) restart the app with System.setProperty(h2.storeLocalTime, true) and 
import some more data.

Once the error occurs, the database is in a corrupted state.

I've managed to create and save a corrupted database, which is zipped to 
only 5 MB. The zip file includes a trace.db file. Thomas, let me know if it 
is useful for you to see this corrupted database.

So far I've been doing this with MV_STORE=false. 

I'll continue posting more info as I narrow down the problem.


On Tuesday, 4 August 2015 18:48:06 UTC+2, Steve McLeod wrote:

 Hi Thomas,

 The database is created with 1.3.176. The error happens in 1.4.188. 

 I don't normally set h2.storeLocalTime - I just observed that it has a 
 different default in 1.4 to 1.3, and at one point my investigations led me 
 to code dependent on the value. To investigate further, I tried reloading 
 the database created in 1.3.176, this time running 
 System.setProperty(h2.storeLocalTime, true) immediately before loading 
 the H2 JDBC driver (which I do using Class.forName(org.h2.Driver)), and 
 was able to sporadically reproduce the problem.

 Here's a stack trace (right now it is happening with a simple INSERT 
 statement)

  sql = INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES 
 (?, ?, ?, ?)
  org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException; SQL statement:
  INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES (?, ?, 
 ?, ?) [5-188]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
   at org.h2.message.DbException.get(DbException.java:168)
   at org.h2.message.DbException.convert(DbException.java:295)
   at org.h2.table.RegularTable.addRow(RegularTable.java:137)
   at org.h2.command.dml.Insert.insertRows(Insert.java:156)
   at org.h2.command.dml.Insert.update(Insert.java:114)
   at org.h2.command.CommandContainer.update(CommandContainer.java:78)
   at org.h2.command.Command.executeUpdate(Command.java:254)
   at 
 org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
   at 
 org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
   at // non-h2 removed

 Caused by: java.lang.ArrayIndexOutOfBoundsException

 I'll keep investigating...

 I can also supply a database that has the problem, but it is 768 MB after 
 zipping...so I think I'll try to write some repro code instead.


 On Tuesday, 4 August 2015 18:01:59 UTC+2, Thomas Mueller wrote:

 Hi,

 Maybe you can reproduce the problem more easily if you set the system 
 property h2.check2 to true.

 I couldn't reproduce the problem so far, and the code looks correct to me 
 (I debugged it and added some trace output, both for storeLocalTime true 
 and false). Maybe it's a bug in an old version, or some strange combination.

 Could you post the exact stack trace including error message and error 
 code?

 Which version of H2 do you use exactly (1.3.x and 1.4.x), and what is the 
 create_build in the database file (select * from 
 information_schema.settings)? How do you set the system property (in which 
 version of H2, how, and when)? It would need to be set before loading the 
 H2 driver.

 Regards,
 Thomas


 On Tuesday, August 4, 2015, Steve McLeod steve@gmail.com wrote:

 After some more investigation, I think this happens when the database 
 was created with H2 1.3.x, it is opened with H2 1.4.x, a SELECT query is 
 performed that exceeds the maxmemoryrows value (a default of 10,000 
 rows), and their is a TIMESTAMP column in the result.

 It seems that the ResultDiskBuffer created to buffer the 
 org.h2.result.ResultSet is getting a wrong result for row length from 
 org.h2.store.Data.getValueLen(Value v, DataHandler handler) 
 when v.getType() is of Value.TIMESTAMP. So a call to 
 ByteArrayOutputStream.checkCapacity is given wrong input.

 That's what I think, but as the code is foreign to me, I could be very 
 wrong in my analysis.



 On Monday, 3 August 2015 12:06:35 UTC+2, Steve McLeod wrote:

 It seems the problem is essentially caused by this:

 In H2 1.3, h2.storeLocalTime=false
 In H2 1.4, h2.storeLocalTime=true

 1. Create a database with 1.3 that uses columns of type DATETIME
 2. Subsequently open it with 1.4
 3. You'll get sporadic exceptions when executing SQL select statements. 
 Here's a stack trace:

 org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException: 2048

   at 
 org.h2.message.DbException.getJdbcSQLException(DbException.java:344)   
  
   at org.h2.message.DbException.get(DbException.java:167) 

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

   at org.h2

Re: [h2] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

More info:

I've been unable to reproduce this using the MVStore.

I can make this problem happen when I only use h2 1.3.176 and I can make it 
happen when I only use h2 1.4.188.

I initially create the database and load some data with 
h2.storeLocalTime=false, then restart the app with h2.storeLocalTime=true 
and load some more data. Usually database corruption ensues.

 I've stripped almost every config option from the database url so that it 
just reads:

jdbc:h2:/Users/steve/Library/Application 
Support/com.barbarysoftware.pokercopilot/database/pokercopilot;MV_STORE=false

I believe this confirms it is not a new issue, but it has become apparent 
now because the default value of h2.storeLocalTime changes from 1.3 to 1.4, 
and I'm trying to move our app from 1.3 to 1.4.

Here's the most recent stack trace:

org.h2.jdbc.JdbcSQLException: General error: 
java.lang.ArrayIndexOutOfBoundsException: 2048 [5-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.convert(DbException.java:294)
at org.h2.table.RegularTable.addRow(RegularTable.java:138)
at org.h2.engine.UndoLogRecord.undo(UndoLogRecord.java:111)
at org.h2.engine.Session.rollbackTo(Session.java:595)
at org.h2.command.Command.executeUpdate(Command.java:278)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144)
at 
com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:311)
... 15 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
at org.h2.store.Data.writeByte(Data.java:379)
at org.h2.store.Data.writeValue(Data.java:441)
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
at org.h2.store.PageStore.writeBack(PageStore.java:1047)
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142)
at org.h2.util.CacheLRU.put(CacheLRU.java:116)
at org.h2.util.CacheLRU.update(CacheLRU.java:123)
at org.h2.store.PageStore.update(PageStore.java:1097)
at org.h2.index.PageDataLeaf.addRowTry(PageDataLeaf.java:209)
at org.h2.index.PageDataLeaf.split(PageDataLeaf.java:377)
at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:139)
at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:174)
at org.h2.index.PageDataIndex.add(PageDataIndex.java:137)
at org.h2.table.RegularTable.addRow(RegularTable.java:120)
... 21 more


On Wednesday, 5 August 2015 11:18:36 UTC+2, Steve McLeod wrote:

 My latest findings:

 I can reproduce (again, sporadically) the problem by exclusively using h2 
 1.4.188 as follows:
 1) set System.setProperty(h2.storeLocalTime, false), start the app, 
 import some data; then
 2) restart the app with System.setProperty(h2.storeLocalTime, true) 
 and import some more data.

 Once the error occurs, the database is in a corrupted state.

 I've managed to create and save a corrupted database, which is zipped to 
 only 5 MB. The zip file includes a trace.db file. Thomas, let me know if it 
 is useful for you to see this corrupted database.

 So far I've been doing this with MV_STORE=false. 

 I'll continue posting more info as I narrow down the problem.


 On Tuesday, 4 August 2015 18:48:06 UTC+2, Steve McLeod wrote:

 Hi Thomas,

 The database is created with 1.3.176. The error happens in 1.4.188. 

 I don't normally set h2.storeLocalTime - I just observed that it has a 
 different default in 1.4 to 1.3, and at one point my investigations led me 
 to code dependent on the value. To investigate further, I tried reloading 
 the database created in 1.3.176, this time running 
 System.setProperty(h2.storeLocalTime, true) immediately before loading 
 the H2 JDBC driver (which I do using Class.forName(org.h2.Driver)), and 
 was able to sporadically reproduce the problem.

 Here's a stack trace (right now it is happening with a simple INSERT 
 statement)

  sql = INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES 
 (?, ?, ?, ?)
  org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException; SQL statement:
  INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES (?, ?, 
 ?, ?) [5-188]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
   at org.h2.message.DbException.get(DbException.java:168)
   at org.h2.message.DbException.convert(DbException.java:295)
   at org.h2.table.RegularTable.addRow(RegularTable.java:137)
   at org.h2.command.dml.Insert.insertRows(Insert.java:156)
   at org.h2.command.dml.Insert.update(Insert.java:114)
   at org.h2.command.CommandContainer.update(CommandContainer.java:78)
   at org.h2.command.Command.executeUpdate(Command.java:254)
   at 
 org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal

Re: [h2] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

Here's a stack trace in the same scenario from h2 1.4.188 

org.h2.jdbc.JdbcSQLException: General error: 
java.lang.ArrayIndexOutOfBoundsException: 4096; SQL statement:
ROLLBACK [5-188]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:262)
at org.h2.jdbc.JdbcConnection.rollbackInternal(JdbcConnection.java:1500)
at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:488)
at 
org.h2.jdbcx.JdbcXAConnection$PooledJdbcConnection.close(JdbcXAConnection.java:465)
at 
com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:278)
...
Caused by: java.lang.ArrayIndexOutOfBoundsException: 4096
at org.h2.store.Data.writeVarLong(Data.java:1254)
at org.h2.store.Data.writeValue(Data.java:523)
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393)
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426)
at org.h2.store.PageStore.writeBack(PageStore.java:1046)
at org.h2.store.PageStore.writeBack(PageStore.java:418)
at org.h2.store.PageStore.checkpoint(PageStore.java:436)
at org.h2.store.PageStore.commit(PageStore.java:1480)
at org.h2.engine.Database.commit(Database.java:1995)
at org.h2.engine.Session.rollback(Session.java:614)
at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:49)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
... 17 more

On Wednesday, 5 August 2015 13:08:37 UTC+2, Steve McLeod wrote:

 More info:

 I've been unable to reproduce this using the MVStore.

 I can make this problem happen when I only use h2 1.3.176 and I can make 
 it happen when I only use h2 1.4.188.

 I initially create the database and load some data with 
 h2.storeLocalTime=false, then restart the app with h2.storeLocalTime=true 
 and load some more data. Usually database corruption ensues.

  I've stripped almost every config option from the database url so that it 
 just reads:

 jdbc:h2:/Users/steve/Library/Application 
 Support/com.barbarysoftware.pokercopilot/database/pokercopilot;MV_STORE=false

 I believe this confirms it is not a new issue, but it has become apparent 
 now because the default value of h2.storeLocalTime changes from 1.3 to 1.4, 
 and I'm trying to move our app from 1.3 to 1.4.

 Here's the most recent stack trace:

 org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException: 2048 [5-176]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
 at org.h2.message.DbException.get(DbException.java:167)
 at org.h2.message.DbException.convert(DbException.java:294)
 at org.h2.table.RegularTable.addRow(RegularTable.java:138)
 at org.h2.engine.UndoLogRecord.undo(UndoLogRecord.java:111)
 at org.h2.engine.Session.rollbackTo(Session.java:595)
 at org.h2.command.Command.executeUpdate(Command.java:278)
 at 
 org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158)
 at 
 org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144)
 at 
 com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:311)
 ... 15 more
 Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
 at org.h2.store.Data.writeByte(Data.java:379)
 at org.h2.store.Data.writeValue(Data.java:441)
 at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)
 at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
 at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
 at org.h2.store.PageStore.writeBack(PageStore.java:1047)
 at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)
 at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142)
 at org.h2.util.CacheLRU.put(CacheLRU.java:116)
 at org.h2.util.CacheLRU.update(CacheLRU.java:123)
 at org.h2.store.PageStore.update(PageStore.java:1097)
 at org.h2.index.PageDataLeaf.addRowTry(PageDataLeaf.java:209)
 at org.h2.index.PageDataLeaf.split(PageDataLeaf.java:377)
 at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:139)
 at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:174)
 at org.h2.index.PageDataIndex.add(PageDataIndex.java:137)
 at org.h2.table.RegularTable.addRow(RegularTable.java:120)
 ... 21 more


 On Wednesday, 5 August 2015 11:18:36 UTC+2, Steve McLeod wrote:

 My latest findings:

 I can reproduce (again, sporadically) the problem by exclusively using h2 
 1.4.188 as follows:
 1) set System.setProperty(h2.storeLocalTime, false), start the app, 
 import some data; then
 2) restart the app with System.setProperty(h2.storeLocalTime, true) 
 and import some more data.

 Once the error occurs, the database is in a corrupted state.

 I've managed to create and save a corrupted database, which is zipped to 
 only 5 MB. The zip file includes a trace.db file

Re: [h2] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

=?, betorraisedriver=?, flopCallFoldBetOrRaise=?, 
turnCallFoldBetOrRaise=?, riverFlopCallFoldBetOrRaise=?, donkbet_flop=?, 
donkbet_flop_opportunity=?, donkbet_turn=?, donkbet_turn_opportunity=?, 
donkbet_river=?, donkbet_river_opportunity=?, squeezeBetPreflop=?, 
squeezeBetPreflopOpportunity=?, threebet_bb=?, threebet_sb=?, 
threebet_btn=?, threebet_co=?, threebet_mp=?, threebet_ep=?, 
threebet_opportunity_bb=?, threebet_opportunity_sb=?, 
threebet_opportunity_btn=?, threebet_opportunity_co=?, 
threebet_opportunity_mp=?, threebet_opportunity_ep=?, fourbet_bb=?, 
fourbet_sb=?, fourbet_btn=?, fourbet_co=?, fourbet_mp=?, fourbet_ep=?, 
fourbet_opportunity_bb=?, fourbet_opportunity_sb=?, 
fourbet_opportunity_btn=?, fourbet_opportunity_co=?, 
fourbet_opportunity_mp=?, fourbet_opportunity_ep=?, checkRaisedFlop=?, 
checkRaisedFlopOpportunity=?, checkRaisedTurn=?, 
checkRaisedTurnOpportunity=?, checkRaisedRiver=?, 
checkRaisedRiverOpportunity=?where playerid=? and isplaymoney=? and 
iszoompoker=? and tablesize=? and gametypeid=?

Regards,

Steve

On Wednesday, 5 August 2015 14:40:28 UTC+2, Noel Grandin wrote:

 Is it possible you could share a couple of the queries that are taking 
 this long? 
 Perhaps privately with Thomas and myself? 

 On 2015-08-05 02:34 PM, Steve McLeod wrote: 
  I've attached some screenshots from Java VisualVM CPU sampling. 
  


-- 
You received this message because you are subscribed to the Google Groups 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] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

Hi Noel,

I've actually solved this problem of PreparedStatement caching for my app 
by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve 
the bigger issue of why it seems to take a comparatively significant time 
to create a PreparedStatement.

Cheers,

Steve


On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:


 Thanks, I'll have a look tomorrow at them in detail. 

 Tell me, how often is JdbcConnection@preparedStatement called compared to 
 how many times you execute a query? 

 If it's every time, it means that your PreparedStatement caching is not 
 working, which would indicate a problem with 
 your connection pool, or something similar. 



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

[h2] Re: Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

After some more investigation, I think this happens when the database was 
created with H2 1.3.x, it is opened with H2 1.4.x, a SELECT query is 
performed that exceeds the maxmemoryrows value (a default of 10,000 
rows), and their is a TIMESTAMP column in the result.

It seems that the ResultDiskBuffer created to buffer the 
org.h2.result.ResultSet is getting a wrong result for row length from 
org.h2.store.Data.getValueLen(Value v, DataHandler handler) 
when v.getType() is of Value.TIMESTAMP. So a call to 
ByteArrayOutputStream.checkCapacity is given wrong input.

That's what I think, but as the code is foreign to me, I could be very 
wrong in my analysis.



On Monday, 3 August 2015 12:06:35 UTC+2, Steve McLeod wrote:

 It seems the problem is essentially caused by this:

 In H2 1.3, h2.storeLocalTime=false
 In H2 1.4, h2.storeLocalTime=true

 1. Create a database with 1.3 that uses columns of type DATETIME
 2. Subsequently open it with 1.4
 3. You'll get sporadic exceptions when executing SQL select statements. 
 Here's a stack trace:

 org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException: 2048

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

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

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

   at org.h2.command.Command.executeQuery(Command.java:209)   
 
   at 
 org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109)
  
  

 Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048   
 at org.h2.store.Data.writeVarLong(Data.java:1259)  
 at org.h2.store.Data.writeValue(Data.java:526) 
 at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)   
 at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
 at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
 at org.h2.store.PageStore.writeBack(PageStore.java:1047)   
 at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)   
 at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142) 
 at org.h2.util.CacheLRU.put(CacheLRU.java:116) 
 at org.h2.store.PageStore.getPage(PageStore.java:858)  
 at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:234)  
 at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:233)
 at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:400)
 at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:95) 
 at org.h2.index.PageDataCursor.next(PageDataCursor.java:53)
 at org.h2.index.IndexCursor.next(IndexCursor.java:278) 
 at org.h2.table.TableFilter.next(TableFilter.java:361) 
 at org.h2.command.dml.Select.queryFlat(Select.java:533)
 at org.h2.command.dml.Select.queryWithoutCache(Select.java:646)
 at org.h2.command.dml.Query.query(Query.java:323)  
 at org.h2.command.dml.Query.query(Query.java:291)  
 at org.h2.index.ViewIndex.find(ViewIndex.java:283) 
 at org.h2.index.ViewIndex.find(ViewIndex.java:179) 
 at org.h2.index.BaseIndex.find(BaseIndex.java:128) 
 at org.h2.index.IndexCursor.find(IndexCursor.java:160) 
 at org.h2.table.TableFilter.next(TableFilter.java:330) 
 at org.h2.command.dml.Select.queryGroup(Select.java:327)   
 at org.h2.command.dml.Select.queryWithoutCache(Select.java:641)
 at org.h2.command.dml.Query.query(Query.java:323)  
 at org.h2.command.dml.Query.query(Query.java:291)  
 at org.h2.command.dml.Query.query(Query.java:37)   
 at org.h2.command.CommandContainer.query(CommandContainer.java:91) 
 at org.h2.command.Command.executeQuery(Command.java:197)   

 The same problem occurs if I override  h2.storeLocalTime to be true on 1.3 
 and then open an existing database.

 The problem is sporadic; I've been unable to create a short test case. But 
 my findings are feasible, right?

 I'd say this is a significant 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/d/optout.

Re: [h2] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

Hi Thomas,

The database is created with 1.3.176. The error happens in 1.4.188. 

I don't normally set h2.storeLocalTime - I just observed that it has a 
different default in 1.4 to 1.3, and at one point my investigations led me 
to code dependent on the value. To investigate further, I tried reloading 
the database created in 1.3.176, this time running 
System.setProperty(h2.storeLocalTime, true) immediately before loading 
the H2 JDBC driver (which I do using Class.forName(org.h2.Driver)), and 
was able to sporadically reproduce the problem.

Here's a stack trace (right now it is happening with a simple INSERT 
statement)

 sql = INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES 
(?, ?, ?, ?)
 org.h2.jdbc.JdbcSQLException: General error: 
java.lang.ArrayIndexOutOfBoundsException; SQL statement:
 INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES (?, ?, 
?, ?) [5-188]
  at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
  at org.h2.message.DbException.get(DbException.java:168)
  at org.h2.message.DbException.convert(DbException.java:295)
  at org.h2.table.RegularTable.addRow(RegularTable.java:137)
  at org.h2.command.dml.Insert.insertRows(Insert.java:156)
  at org.h2.command.dml.Insert.update(Insert.java:114)
  at org.h2.command.CommandContainer.update(CommandContainer.java:78)
  at org.h2.command.Command.executeUpdate(Command.java:254)
  at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
  at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
  at // non-h2 removed

Caused by: java.lang.ArrayIndexOutOfBoundsException

I'll keep investigating...

I can also supply a database that has the problem, but it is 768 MB after 
zipping...so I think I'll try to write some repro code instead.


On Tuesday, 4 August 2015 18:01:59 UTC+2, Thomas Mueller wrote:

 Hi,

 Maybe you can reproduce the problem more easily if you set the system 
 property h2.check2 to true.

 I couldn't reproduce the problem so far, and the code looks correct to me 
 (I debugged it and added some trace output, both for storeLocalTime true 
 and false). Maybe it's a bug in an old version, or some strange combination.

 Could you post the exact stack trace including error message and error 
 code?

 Which version of H2 do you use exactly (1.3.x and 1.4.x), and what is the 
 create_build in the database file (select * from 
 information_schema.settings)? How do you set the system property (in which 
 version of H2, how, and when)? It would need to be set before loading the 
 H2 driver.

 Regards,
 Thomas


 On Tuesday, August 4, 2015, Steve McLeod steve@gmail.com 
 javascript: wrote:

 After some more investigation, I think this happens when the database was 
 created with H2 1.3.x, it is opened with H2 1.4.x, a SELECT query is 
 performed that exceeds the maxmemoryrows value (a default of 10,000 
 rows), and their is a TIMESTAMP column in the result.

 It seems that the ResultDiskBuffer created to buffer the 
 org.h2.result.ResultSet is getting a wrong result for row length from 
 org.h2.store.Data.getValueLen(Value v, DataHandler handler) 
 when v.getType() is of Value.TIMESTAMP. So a call to 
 ByteArrayOutputStream.checkCapacity is given wrong input.

 That's what I think, but as the code is foreign to me, I could be very 
 wrong in my analysis.



 On Monday, 3 August 2015 12:06:35 UTC+2, Steve McLeod wrote:

 It seems the problem is essentially caused by this:

 In H2 1.3, h2.storeLocalTime=false
 In H2 1.4, h2.storeLocalTime=true

 1. Create a database with 1.3 that uses columns of type DATETIME
 2. Subsequently open it with 1.4
 3. You'll get sporadic exceptions when executing SQL select statements. 
 Here's a stack trace:

 org.h2.jdbc.JdbcSQLException: General error: 
 java.lang.ArrayIndexOutOfBoundsException: 2048

   at 
 org.h2.message.DbException.getJdbcSQLException(DbException.java:344)   
  
   at org.h2.message.DbException.get(DbException.java:167)   
  
   at org.h2.message.DbException.convert(DbException.java:294)   
  
   at org.h2.command.Command.executeQuery(Command.java:209) 
   
   at 
 org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109)
  
  

 Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048   
 at org.h2.store.Data.writeVarLong(Data.java:1259)  
 at org.h2.store.Data.writeValue(Data.java:526) 
 at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)   
 at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
 at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
 at org.h2.store.PageStore.writeBack(PageStore.java:1047)   
 at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216

[h2] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

[Steve McLeod]

It seems the problem is essentially caused by this:

In H2 1.3, h2.storeLocalTime=false
In H2 1.4, h2.storeLocalTime=true

1. Create a database with 1.3 that uses columns of type DATETIME
2. Subsequently open it with 1.4
3. You'll get sporadic exceptions when executing SQL select statements. 
Here's a stack trace:

org.h2.jdbc.JdbcSQLException: General error: 
java.lang.ArrayIndexOutOfBoundsException: 2048

  at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)   
 
  at org.h2.message.DbException.get(DbException.java:167)   
 
  at org.h2.message.DbException.convert(DbException.java:294)   
 
  at org.h2.command.Command.executeQuery(Command.java:209) 
  
  at 
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109) 
 

Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048   
at org.h2.store.Data.writeVarLong(Data.java:1259)  
at org.h2.store.Data.writeValue(Data.java:526) 
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)   
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
at org.h2.store.PageStore.writeBack(PageStore.java:1047)   
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)   
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142) 
at org.h2.util.CacheLRU.put(CacheLRU.java:116) 
at org.h2.store.PageStore.getPage(PageStore.java:858)  
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:234)  
at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:233)
at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:400)
at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:95) 
at org.h2.index.PageDataCursor.next(PageDataCursor.java:53)
at org.h2.index.IndexCursor.next(IndexCursor.java:278) 
at org.h2.table.TableFilter.next(TableFilter.java:361) 
at org.h2.command.dml.Select.queryFlat(Select.java:533)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:646)
at org.h2.command.dml.Query.query(Query.java:323)  
at org.h2.command.dml.Query.query(Query.java:291)  
at org.h2.index.ViewIndex.find(ViewIndex.java:283) 
at org.h2.index.ViewIndex.find(ViewIndex.java:179) 
at org.h2.index.BaseIndex.find(BaseIndex.java:128) 
at org.h2.index.IndexCursor.find(IndexCursor.java:160) 
at org.h2.table.TableFilter.next(TableFilter.java:330) 
at org.h2.command.dml.Select.queryGroup(Select.java:327)   
at org.h2.command.dml.Select.queryWithoutCache(Select.java:641)
at org.h2.command.dml.Query.query(Query.java:323)  
at org.h2.command.dml.Query.query(Query.java:291)  
at org.h2.command.dml.Query.query(Query.java:37)   
at org.h2.command.CommandContainer.query(CommandContainer.java:91) 
at org.h2.command.Command.executeQuery(Command.java:197)   

The same problem occurs if I override  h2.storeLocalTime to be true on 1.3 
and then open an existing database.

The problem is sporadic; I've been unable to create a short test case. But 
my findings are feasible, right?

I'd say this is a significant 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/d/optout.

Re: [h2] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

Hi Thomas,

Consider this issue solved for me - I wanted to make sure that for the 
longer term a bigger query cache was contemplated.

A related part of this issue is that 
  
   Parser.prepareCommand(String);

is costly for large SQL statements. In particular the 
Parser.initialize(String) method seems to be a CPU hog. When I say large 
SQL statement I mean  3000 characters, and 150 parameters.

I attempted to do some profiling to find if there were any simple 
optimisations available, but alas the Parser.initialize() method is complex 
and not easy to profile in more detail. 

Regards,
  
Steve McLeod


 

On Sunday, 2 August 2015 20:14:30 UTC+2, Thomas Mueller wrote:

 Hi,

 If you use PreparedStatement, and the re-use them, within the same 
 connection, you should be fine, even without the query cache.

 The cache query cache is only for those cases where you use Statement 
 instead of PreparedStatement, or if you re-create the PreparedStatement 
 each time. That includes the case if you use a simple connection pool.

 Could explain what you do, maybe with a simple code example?

 Regards,
 Thomas


 On Sun, Aug 2, 2015 at 2:43 PM, Steve McLeod steve@gmail.com 
 javascript: wrote:

 Hi all,

 I was doing some application profiling today. I found that in our 
 real-world scenario of loading a database using many different very long 
 SQL statements, a significant amount of H2's CPU time was being spent in 
 org.h2.command.Parser.initialise().

 Further investigation revealed that by default, H2 caches only 8 prepared 
 statements in a least recently used cache. Use 9 or more prepared 
 statements repeatedly - as our application does - and you lose all benefit 
 of the prepared statement cache. The effect is the same as if the query 
 cache was set to 0, except for the continual overhead of cache churn.

 I performed my profiling again, this time with QUERY_CACHE_SIZE set to 
 100. This time I managed to run 40% more queries in the same timeframe, 
 measured over several minutes of sustained inserts, updates, selects, and 
 merges.

 I proposed that the value for QUERY_CACHE_SIZE should by default be 
 significantly higher. 



 -- 
 You received this message because you are subscribed 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.
 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] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

I use  org.h2.jdbcx.ConnectionPool. However my software is a desktop app, 
with all JDBC activity taking place on one dedicated thread, so it might in 
effect work always use the same single connection.

And yes I do have a lot of complex queries.



On Monday, 3 August 2015 14:00:57 UTC+2, Noel Grandin wrote:



 On 2015-08-03 11:26 AM, Steve McLeod wrote: 
  Consider this issue solved for me - I wanted to make sure that for the 
 longer term a bigger query cache was contemplated. 
  
  A related part of this issue is that 
  Parser.prepareCommand(String); 
  
  is costly for large SQL statements. In particular the 
 Parser.initialize(String) method seems to be a CPU hog. When I say 
  large SQL statement I mean  3000 characters, and 150 parameters. 
  
  I attempted to do some profiling to find if there were any simple 
 optimisations available, but alas the 
  Parser.initialize() method is complex and not easy to profile in more 
 detail. 


 How many connections do you open? If you have a lot of very complex 
 queries, the right answer may be that we need to 
 share the PreparedStatement cache across sessions. 



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

[h2] QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

Hi all,

I was doing some application profiling today. I found that in our 
real-world scenario of loading a database using many different very long 
SQL statements, a significant amount of H2's CPU time was being spent in 
org.h2.command.Parser.initialise().

Further investigation revealed that by default, H2 caches only 8 prepared 
statements in a least recently used cache. Use 9 or more prepared 
statements repeatedly - as our application does - and you lose all benefit 
of the prepared statement cache. The effect is the same as if the query 
cache was set to 0, except for the continual overhead of cache churn.

I performed my profiling again, this time with QUERY_CACHE_SIZE set to 100. 
This time I managed to run 40% more queries in the same timeframe, measured 
over several minutes of sustained inserts, updates, selects, and merges.

I proposed that the value for QUERY_CACHE_SIZE should by default be 
significantly higher. 



-- 
You received this message because you are subscribed to the Google Groups 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] index for OR clause

[Steve McLeod]

Indexing for an OR clause that references two separate columns is a 
difficult task.

Does your use case tolerate an index on columns b and c only?

When I add this index to your example code:
create index IDX_TEST_4 on TEST(b,c);

I get this query plan:
SELECT
TEST.A,
TEST.A2,
TEST.B,
TEST.C
FROM PUBLIC.TEST
/* PUBLIC.IDX_TEST_4: C = 'PP'
AND B = '456'
 */
WHERE (C = 'PP')
AND ((B = '456')
AND ((A = '123')
OR (A2 = '1234')));

On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote:


 On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin noelg...@gmail.com 
 javascript: wrote:


 Try doing this:

 select * from TEST where (a = '123' and b = '456' and c='PP') or 
 (a2='1234' and b = '456' and c='PP');


 same result

 explain select * from TEST where (a = '123' and b = '456' and c='PP') or 
 (a2='1234' and b = '456' and c='PP');
 PLAN   
 http://192.168.5.85:8082/query.do?jsessionid=1bfd56e4866af5b58a02a0da0e930c6e#
 SELECT
 TEST.A,
 TEST.A2,
 TEST.B,
 TEST.C
 FROM PUBLIC.TEST
 /* PUBLIC.TEST.tableScan */
 WHERE ((C = 'PP')
 AND ((A = '123')
 AND (B = '456')))
 OR ((C = 'PP')
 AND ((A2 = '1234')
 AND (B = '456')))



-- 
You received this message because you are subscribed to the Google Groups 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] Moving from H2 1.3.176 to 1.4.187 results in sporadic ArrayIndexOutOfBoundsExceptions?

[Steve McLeod]

Hi Thomas,

 The default page size is 4096 bytes, did you set it manually to 2048 
bytes?

No, I didn't change it. Do note, that the databases were created with 
1.3.176 and the crashes happened with 1.4.187.

I've reverted to 1.3.176. That stopped the crashes almost immediately, 
although we are still getting a few. These newer crash messages refer to 
4096 bytes. eg:

Caused by: java.lang.ArrayIndexOutOfBoundsException: 4096
at org.h2.store.Data.writeVarInt(Data.java:1187)
at org.h2.store.Data.writeValue(Data.java:531)
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
at org.h2.store.PageStore.writeBack(PageStore.java:1047)
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142)
at org.h2.util.CacheLRU.put(CacheLRU.java:116)

On Saturday, 9 May 2015 02:12:07 UTC+10, Thomas Mueller wrote:

 Hi,

 I don't know what the problem could be. There were no changes in this area.

 What is strange is: The exception says it is trying to read past the end 
 of a page that is 2048 bytes long. The default page size is 4096 bytes, did 
 you set it manually to 2048 bytes? According to the database URL it doesn't 
 look like.

 Regards,
 Thomas


 On Wednesday, May 6, 2015, Steve McLeod steve@gmail.com javascript: 
 wrote:

 Today I released an update for my product, Poker Copilot. Until today, it 
 used H2 1.3.176. Today's update uses H2 1.4.187, with MV_STORE=false 
 appended to the database URL. After today's update, we are getting many H2 
 crash reports on both Windows and OS X. Poker Copilot is a mature product 
 that has been using H2 for six years, and is used by many people each day; 
 until this release we were getting infrequent crash reports. It does 
 initially look like this is a sporadic H2 problem, although I am, of 
 course, open to the idea that it is caused by my own programming mistakes.

 On OS X, the database URL is 
 jdbc:h2:/Users/steve/Library/Application 
 Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;MV_STORE=false;CACHE_SIZE=65536

 The most common crash reports are variants of:

  java.lang.ArrayIndexOutOfBoundsException: 2048
 at org.h2.store.Data.writeVarLong(Data.java:1254)
 at org.h2.store.Data.writeValue(Data.java:523)
 at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393)
 at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453)
 at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426)
 at org.h2.store.PageStore.writeBack(PageStore.java:1046)
 at org.h2.util.CacheLRU.removeOld(CacheLRU.java:215)
 at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:141)
 at org.h2.util.CacheLRU.put(CacheLRU.java:115)
 at org.h2.store.PageStore.getPage(PageStore.java:857)
 at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:233)
 at org.h2.index.PageDataNode.getRowWithKey(PageDataNode.java:279)
 at org.h2.index.PageDataIndex.getRowWithKey(PageDataIndex.
 java:426)
 at org.h2.index.PageDataIndex.getRow(PageDataIndex.java:415)
 at org.h2.table.RegularTable.getRow(RegularTable.java:106)
 at org.h2.index.PageBtreeIndex.getRow(PageBtreeIndex.java:301)
 at org.h2.index.PageBtreeCursor.get(PageBtreeCursor.java:45)
 at org.h2.index.IndexCursor.get(IndexCursor.java:260)
 at org.h2.table.TableFilter.getValue(TableFilter.java:913)
 at org.h2.expression.ExpressionColumn.getValue(
 ExpressionColumn.java:186)
 at org.h2.command.dml.Select.queryFlat(Select.java:580)
 at org.h2.command.dml.Select.queryWithoutCache(Select.java:685)
 at org.h2.command.dml.Query.query(Query.java:322)
 at org.h2.command.dml.Query.query(Query.java:290)
 at org.h2.command.dml.Query.query(Query.java:36)
 at org.h2.command.CommandContainer.query(
 CommandContainer.java:90)
 at org.h2.command.Command.executeQuery(Command.java:197)
 ... 18 more

 and 

 Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
 at org.h2.store.Data.writeStringWithoutLength(Data.java:263)
 at org.h2.store.Data.writeValue(Data.java:566)
 at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393)
 at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453)
 at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426)
 at org.h2.store.PageStore.writeBack(PageStore.java:1046)
 at org.h2.util.CacheLRU.removeOld(CacheLRU.java:215)
 at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:141)
 at org.h2.util.CacheLRU.put(CacheLRU.java:115

[h2] Moving from H2 1.3.176 to 1.4.187 results in sporadic ArrayIndexOutOfBoundsExceptions?

[Steve McLeod]

.engine.SessionRemote.connectEmbeddedOrServer(
SessionRemote.java:347)
at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:108)
at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:92)
at org.h2.Driver.connect(Driver.java:72)
at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(
JdbcDataSource.java:190)
at org.h2.jdbcx.JdbcDataSource.getXAConnection(
JdbcDataSource.java:353)
at org.h2.jdbcx.JdbcDataSource.getPooledConnection(
JdbcDataSource.java:385)
at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(
JdbcConnectionPool.java:227)
at org.h2.jdbcx.JdbcConnectionPool.getConnection(
JdbcConnectionPool.java:199)
at com.barbarysoftware.databaseframework.JdbcTemplateCore.
getConnection(JdbcTemplateCore.java:46)
... 10 more
Caused by: java.lang.ArrayIndexOutOfBoundsException



I'll need to rectify this pretty quickly. Does this sound like a problem 
introduced in a specific update of H2? If so, what's the version I should 
use to avoid this problem?

Regards,
  
Steve McLeod
Founder, Poker Copilot
http://www.pokercopilot.com




-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To 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] Timeline for Next Stable Release

[Steve McLeod]

There's an underscore missing, I think. The latest H2 jar didn't like 
MVSTORE=false.

It is MV_STORE=false

On Saturday, 25 April 2015 18:59:21 UTC+8, Noel Grandin wrote:

 As long as you are specifying MVSTORE=false on your database URL, you 
 are still using the stable engine. 
 I, for example, am shipping 1.4 in production like this. 

 On Sat, Apr 25, 2015 at 11:25 AM, Cecil Westerhof 
 cldwes...@gmail.com javascript: wrote: 
  2015-04-24 17:31 GMT+02:00 Rich Naylor rich.na...@gmail.com 
 javascript:: 
  
  Does anyone have the timeline for the next Stable Release. I would 
 like 
  to take advantage of several bug fixes made in the 1.4 branch but am 
 not 
  comfortable distributing a beta release with our product. Thanks 
  
  
  I was wondering this myself for quit some time. 
  
  
  -- 
  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...@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. 
  For more options, visit https://groups.google.com/d/optout. 


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

[h2] Re: Migrated to Github

[Steve McLeod]

Hi Thomas,

This is very good news. Some questions:

* Have you decided on a workflow for Git? That is, will we still submit 
patches via this Google Group? Or will we change to feature branches 
accompanied with requests to merge the branch into a 'master' branch or 
'develop' branch? Or will we make pull requests of personal forks?

* I noticed that the list of committers have been migrated to. Does that 
mean that the committers can push to GitHub? 

* As Git makes it much easier to keep two versions active, I'd be very 
happy if we could have an active 1.3.x branch, into which some fixes and 
improvements can be merged, until 1.4 is no longer in beta. For example, a 
couple of months ago I found the performance problem with converting column 
names into upper case, using Locale.ENGLISH, and you patched 1.4 with a 
fix. I don't think 1.4 is ready for our application, but I'd love to 
benefit from that performance improvement in 1.3. Would you consider having 
an active 1.3 branch?

Regards,

Steve


On Saturday, 11 April 2015 03:51:54 UTC+8, Thomas Mueller wrote:

 Hi,

 The source code of H2 is now migrated to Github: 
 https://github.com/h2database/h2database

 The list of issues could not be migrated, I hope I can still do that, but 
 if not I guess it's not a big issue.

 The links from the web page will need to be changed.

 Regards,
 Thomas





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

Re: [h2] org.h2.util.StringUtils.toUpperEnglish can be a bottleneck

[Steve McLeod]

The patch seems good. Here are my test results:

Typical query time after JVM warm-up

h2 1.3.176 - 716 ms
h2 1.4.185 (but not using MV_STORE) - 350 ms
h2 1.4.185 (but not using MV_STORE) with patch - 243 ms

Regards,

Steve


On Tuesday, 10 February 2015 23:36:34 UTC+6:30, Thomas Mueller wrote:

 Hi,

 I have committed my patch, could you please test it? You would need to 
 compile H2 from the trunk, see 
 https://code.google.com/p/support/source/checkout

 Regards,
 Thomas


 On Tue, Feb 10, 2015 at 7:45 AM, Thomas Mueller thomas.to...@gmail.com 
 javascript: wrote:

 Hi,

 It might make sense to use a concurrent and global cache in the 
 StringUtils class instead, similar to StringUtils.softCache 
 (StringUtils.fromCacheOrNew). That way, other components could benefit from 
 it as well, and small result sets would also benefit.

 I will write a patch.

 Regards,
 Thomas




 On Tuesday, February 10, 2015, Steve McLeod steve@gmail.com 
 javascript: wrote:

 Hi Thomas and Noel,

 I've created a patch that caches the results of toUpperEnglish on a per 
 JdbcResultSet basis. In my specific use case (fetching 95 columns by column 
 name over tens of thousands of rows), the query speed-up was extremely 
 significant, and utterly repeatable. Before: 510 milliseconds. After: 230 
 milliseconds.

 The patch is attached. I wrote the code, it's mine, and I'm 
 contributing it to H2 for distribution multiple-licensed under the MPL 2.0, 
 and the EPL 1.0 (http://h2database.com/html/license.html).

 Would either of you mind taking a look to see if you think it is worth 
 committing? In particular, I'm concerned that my use of HashMap may be 
 incorrect if multiple threads are sharing the result set. 

 Regards,

 Steve




 On Monday, 9 February 2015 22:45:12 UTC+6:30, Steve McLeod wrote:

 Hi Noel,



 On Monday, 9 February 2015 19:14:32 UTC+6:30, Noel Grandin wrote:

 Hi 


 On 2015-02-09 02:21 PM, Steve McLeod wrote: 
   final ResultSet resultSet = conn.prepareStatement(SELECT * 
 FROM foobar).executeQuery(); 
   int rowCount = 0; 
   while (resultSet.next()) { 
   rowCount++; 
   final int columnCount = 
  resultSet.getMetaData().getColumnCount(); 

   for (int column = 1; column = columnCount; column++) { 
   final String columnName = 
  resultSet.getMetaData().getColumnName(column); 

   final int anInt = resultSet.getInt(columnName); 
   } 
   } 

 You should rather be retrieving the column names once, and then 
 retrieving the result-set columns using the getXXX(int 
 columnIndex) methods. 


 I agree - that would help my (contrived) case. My actual code doesn't 
 use meta-data to get column names, but sources the column names from 
 elsewhere. I can certainly fix the problems on my end.

 But I think there is an opportunity to add a tiny performance 
 improvement in general here. Iterating over a result set, using 
 getXXX(String columnName) is very common use of JDBC. We're not talking 
 10% 
 speed improvement, but perhaps 1% in some cases? I don't know for sure - 
 I'm just guessing based on the profiling.
  

 The only other thing I can think of that might speed it up would be to 
 modify the current caching code to use a TreeMap 
 with a custom comparator and then set the comparator to 
 java.lang.String.CASE_INSENSITIVE_ORDER. 
 That would avoid the extra String object creation, at the very least. 


 Sounds good. Will java.lang.String.CASE_INSENSITIVE_ORDER be 
 satisfactory? The Javadocs state: Note that this Comparator does not take 
 locale into account, and will result in an unsatisfactory ordering for 
 certain locales. I think that means it is acceptable for H2's case, but 
 I'm not certain.


 Regards,

 Steve

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




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

Re: [h2] org.h2.util.StringUtils.toUpperEnglish can be a bottleneck

[Steve McLeod]

Hi Noel,



On Monday, 9 February 2015 19:14:32 UTC+6:30, Noel Grandin wrote:

 Hi 


 On 2015-02-09 02:21 PM, Steve McLeod wrote: 
   final ResultSet resultSet = conn.prepareStatement(SELECT * 
 FROM foobar).executeQuery(); 
   int rowCount = 0; 
   while (resultSet.next()) { 
   rowCount++; 
   final int columnCount = 
 resultSet.getMetaData().getColumnCount(); 
   for (int column = 1; column = columnCount; column++) { 
   final String columnName = 
 resultSet.getMetaData().getColumnName(column); 
   final int anInt = resultSet.getInt(columnName); 
   } 
   } 

 You should rather be retrieving the column names once, and then retrieving 
 the result-set columns using the getXXX(int 
 columnIndex) methods. 


I agree - that would help my (contrived) case. My actual code doesn't use 
meta-data to get column names, but sources the column names from elsewhere. 
I can certainly fix the problems on my end.

But I think there is an opportunity to add a tiny performance improvement 
in general here. Iterating over a result set, using getXXX(String 
columnName) is very common use of JDBC. We're not talking 10% speed 
improvement, but perhaps 1% in some cases? I don't know for sure - I'm just 
guessing based on the profiling.
 

 The only other thing I can think of that might speed it up would be to 
 modify the current caching code to use a TreeMap 
 with a custom comparator and then set the comparator to 
 java.lang.String.CASE_INSENSITIVE_ORDER. 
 That would avoid the extra String object creation, at the very least. 


Sounds good. Will java.lang.String.CASE_INSENSITIVE_ORDER be satisfactory? 
The Javadocs state: Note that this Comparator does not take locale into 
account, and will result in an unsatisfactory ordering for certain 
locales. I think that means it is acceptable for H2's case, but I'm not 
certain.


Regards,

Steve

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

Re: [h2] org.h2.util.StringUtils.toUpperEnglish can be a bottleneck

[Steve McLeod]

Hi Thomas and Noel,

I've created a patch that caches the results of toUpperEnglish on a per 
JdbcResultSet basis. In my specific use case (fetching 95 columns by column 
name over tens of thousands of rows), the query speed-up was extremely 
significant, and utterly repeatable. Before: 510 milliseconds. After: 230 
milliseconds.

The patch is attached. I wrote the code, it's mine, and I'm contributing it 
to H2 for distribution multiple-licensed under the MPL 2.0, and the EPL 1.0 
(http://h2database.com/html/license.html).

Would either of you mind taking a look to see if you think it is worth 
committing? In particular, I'm concerned that my use of HashMap may be 
incorrect if multiple threads are sharing the result set. 

Regards,

Steve




On Monday, 9 February 2015 22:45:12 UTC+6:30, Steve McLeod wrote:

 Hi Noel,



 On Monday, 9 February 2015 19:14:32 UTC+6:30, Noel Grandin wrote:

 Hi 


 On 2015-02-09 02:21 PM, Steve McLeod wrote: 
   final ResultSet resultSet = conn.prepareStatement(SELECT * 
 FROM foobar).executeQuery(); 
   int rowCount = 0; 
   while (resultSet.next()) { 
   rowCount++; 
   final int columnCount = 
 resultSet.getMetaData().getColumnCount(); 
   for (int column = 1; column = columnCount; column++) { 
   final String columnName = 
 resultSet.getMetaData().getColumnName(column); 
   final int anInt = resultSet.getInt(columnName); 
   } 
   } 

 You should rather be retrieving the column names once, and then 
 retrieving the result-set columns using the getXXX(int 
 columnIndex) methods. 


 I agree - that would help my (contrived) case. My actual code doesn't use 
 meta-data to get column names, but sources the column names from elsewhere. 
 I can certainly fix the problems on my end.

 But I think there is an opportunity to add a tiny performance improvement 
 in general here. Iterating over a result set, using getXXX(String 
 columnName) is very common use of JDBC. We're not talking 10% speed 
 improvement, but perhaps 1% in some cases? I don't know for sure - I'm just 
 guessing based on the profiling.
  

 The only other thing I can think of that might speed it up would be to 
 modify the current caching code to use a TreeMap 
 with a custom comparator and then set the comparator to 
 java.lang.String.CASE_INSENSITIVE_ORDER. 
 That would avoid the extra String object creation, at the very least. 


 Sounds good. Will java.lang.String.CASE_INSENSITIVE_ORDER be satisfactory? 
 The Javadocs state: Note that this Comparator does not take locale into 
 account, and will result in an unsatisfactory ordering for certain 
 locales. I think that means it is acceptable for H2's case, but I'm not 
 certain.


 Regards,

 Steve



-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Index: h2/src/main/org/h2/jdbc/JdbcResultSet.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
+UTF-8
===
--- h2/src/main/org/h2/jdbc/JdbcResultSet.java	(revision 6033)
+++ h2/src/main/org/h2/jdbc/JdbcResultSet.java	(revision )
@@ -83,9 +83,11 @@
 private Value[] insertRow;
 private Value[] updateRow;
 private HashMapString, Integer columnLabelMap;
+private final MapString, String toUpperMap = New.hashMap();
 private HashMapInteger, Value[] patchedRows;
 private JdbcPreparedStatement preparedStatement;
 
+
 JdbcResultSet(JdbcConnection conn, JdbcStatement stat,
 ResultInterface result, int id, boolean closeStatement,
 boolean scrollable, boolean updatable) {
@@ -3115,7 +3117,7 @@
 preparedStatement.setCachedColumnLabelMap(columnLabelMap);
 }
 }
-Integer index = columnLabelMap.get(StringUtils.toUpperEnglish(columnLabel));
+Integer index = columnLabelMap.get(toUpperEnglishCached(columnLabel));
 if (index == null) {
 throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, columnLabel);
 }
@@ -3144,6 +3146,21 @@
 }
 }
 throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, columnLabel);
+}
+
+/**
+ * This isn't thread-safe. Should we use a concurrent map instead? Or will an instance JdbcResultSet always be accessed from only one thread?
+ * @param columnLabel column name that can be lower, upper, or mixed case
+ * @return the column label in upper case according to the english locale, obtained from a cache if possible
+ */
+private String

[h2] org.h2.util.StringUtils.toUpperEnglish can be a bottleneck

[Steve McLeod]

Hi all,

I've got some H2 code that executes a query that returns possibly 100,000+ 
rows and 95 columns. I need to retrieve every column value by name, using 
JDBC. Using H2's built-in profiling tool, StringUtils.toUpperEnglish seems 
to be a bottleneck. It seems that JdbcResultSet.getColumnIndex(String 
columnLabel) is part of the problem.

I can see a couple of ways I could improve this in H2:
* caching the conversion from column-name to uppercase column name 
in JdbcResultSet.getColumnIndex(String columnLabel), on the assumption that 
if code calls, eg, rs.getInt(foobar) once it is rather likely to call it 
again.
* make StringUtils.toUpperEnglish cache recent invocations, on the 
assumption that the same strings within H2 probably get converted to upper 
case frequently.
* rework the logic in JdbcResultSet.getColumnIndex(String columnLabel) a 
little - there is already some code that tries to minimize the 
toUpperEnglish invocations, but I think it could be made smarter.
* I also suspect there is a Turkish-locale bug in in the case where the 
column count is less than 3, but I'd want to test before stating this 
incontrovertibly.

Of course, I could also implement a work-around in my code, but here is 
perhaps a chance for a tiny but worthwhile performance improvement for all 
users of H2.

Thoughts? 

I'm using H2-1.3.176, but I've checked SVN and the relevant code seems 
unchanged at the present.

Regards,

Steve


Contrived code to reproduce the problem follows:

import org.h2.util.Profiler;

import javax.swing.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ScratchSpace extends JFrame {
public static void main(String[] args) throws SQLException, 
ClassNotFoundException, IOException {


Class.forName(org.h2.Driver);
Connection conn = DriverManager.getConnection(jdbc:h2:~/test);

conn.prepareStatement(DROP TABLE IF EXISTS foobar).execute();
// create 100 column names, each of the form AA, AB, AC...etc.

System.out.println(Creating table with 100 columns);
String sql = create table foobar (;
for (int i = 0; i  100; i++) {
String col = getColumnName(i);
if (i  0) {
sql += , ;
}
sql += col +  INT;
}
sql += );
System.out.println(sql =  + sql);
conn.prepareStatement(sql).execute();

final int MAX_ROWS = 10;
System.out.println(Inserting  + MAX_ROWS +  rows);
for (int j = 0; j  MAX_ROWS; j++) {
conn.prepareStatement(INSERT INTO foobar (Column_AA) VALUES 
(1)).execute();
}

Profiler prof = new Profiler();
prof.startCollecting();


final ResultSet resultSet = conn.prepareStatement(SELECT * FROM 
foobar).executeQuery();
int rowCount = 0;
while (resultSet.next()) {
rowCount++;
final int columnCount = 
resultSet.getMetaData().getColumnCount();
for (int column = 1; column = columnCount; column++) {
final String columnName = 
resultSet.getMetaData().getColumnName(column);
final int anInt = resultSet.getInt(columnName);
}
}

System.out.println(Done);


prof.stopCollecting();
System.out.println(prof.getTop(3));
conn.close();


}

private static String getColumnName(int i) {
return Column_ + (char) ((65 + i / 26)) + (char) (65 + i % 26);
}
}

Profiler output:

Profiler: top 3 stack trace(s) of  of 2219 ms of 825 thread dumps:
108/825 (13%):
at org.h2.util.StringUtils.toUpperEnglish(StringUtils.java:93)
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3120)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3210)
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:340)
at com.barbarysoftware.pokercopilot.ScratchSpace.main(ScratchSpace.java:52)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
72/825 (8%):
at org.h2.store.fs.FileDisk.read(FilePathDisk.java:451)
at org.h2.store.fs.FileUtils.readFully(FileUtils.java:345)
at org.h2.store.FileStore.readFully(FileStore.java:274)
at org.h2.result.ResultDiskBuffer.readRow(ResultDiskBuffer.java:198)
at org.h2.result.ResultDiskBuffer.nextUnsorted(ResultDiskBuffer.java:221)
at org.h2.result.ResultDiskBuffer.next(ResultDiskBuffer.java:214)
at org.h2.result.LocalResult.next(LocalResult.java:234)
at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3233)
at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:124)
at com.barbarysoftware.pokercopilot.ScratchSpace.main(ScratchSpace.java:47)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
65/825 (7%):
at org.h2.engine.Session.isReconnectNeeded(Session.java:1336)
at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1470)
at 

Re: [h2] ALTER TABLE ADD uses too much memory

[Steve McLeod]

Hi Noel,

Once I investigated deeper, I discovered that I was wrong, that the culprit 
is not H2's data copying. The memory issue was, of course, completely in my 
own code.

Noel, you mentioned that you have some suggestions for making the data 
copying faster? I'm keen to hear them. The quicker the ALTER TABLE ADD ... 
statements run, the less chance my users experience power outage, or decide 
to force quit the process, etc.


On Tuesday, 6 January 2015 19:11:10 UTC+8, Noel Grandin wrote:


 On 2015-01-06 12:50 PM, Steve McLeod wrote: 
  When our users update to the latest version of our desktop software that 
 uses H2, we need to add several columns to a 
  table with many columns that may have millions of rows. Some people have 
 been getting an OutOfMemoryException during 
  this process. 
  

 That is rather odd, since H2 should be creating a temporary table if the 
 result-set gets too big. 
 I do this all the time, and I don't have OOM problems. It sure can take a 
 long time though, which I have some 
 suggestions for, but first lets sort out this bug. 

 What does your H2 URL look like, and what does your ALTER TABLE statement 
 look like? 

  I've analysed this problem, and checked the H2 source code for ALTER 
 TABLE ADD. It seems that the high RAM usage is 
  occurring during the data copying step. 
  

 Can you be more specific - what data-structure is using up memory and what 
 section of H2 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.

[h2] ALTER TABLE ADD uses too much memory

[Steve McLeod]

When our users update to the latest version of our desktop software that 
uses H2, we need to add several columns to a table with many columns that 
may have millions of rows. Some people have been getting an 
OutOfMemoryException during this process.

I've analysed this problem, and checked the H2 source code for ALTER TABLE 
ADD. It seems that the high RAM usage is occurring during the data copying 
step.

I'm looking for an alternative solution. Two I've thought of:
1) Manually create a replacement table, then using JDBC, fetch each 
existing row, inserting it into the new table. 
2) Dump the current table to CSV file, then create the new table with the 
additional columns, and import from the CSV file

Before I try these different approaches, do you have any thoughts on which 
might be better, or whether an alternative approach may 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/d/optout.

Re: [h2] 1.4 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

The database file size in 1.4.184 is much, much better than in earlier 
1.4.x releases.

I've done some trials and these are my findings:

1.3.176: Fully loaded database after shutdown is 317 Mb
1.4.184: Fully loaded database after shutdown is 380 Mb

This seems reasonable.


On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote:

 Hi,

 Version 1.4.184 should produce smaller database files than previous 
 version (1.4.x - 1.4.182), maybe half or a third of the old file size. It 
 would be great to get some real-world results!

 Regards,
 Thomas



 On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com 
 javascript: wrote:

 Hi,

 Some initial results: you can shrink the database by running shutdown 
 compact or shutdown defrag. Each time this is run, it shrinks a few MB 
 (up to some point, of course). This works, but it's relatively slow. Now 
 the task is to make it faster. There are two ways: shrink it fully to the 
 minimum size, and shrink it incrementally (like now) but faster. I'm 
 working on that now.

 Regards,
 Thomas



 On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com 
 javascript: wrote:

 Hi Thomas,

 I've sent you a private email with a link to the new database file, made 
 with H2 1.4.178

 Regards,

 Steve


 On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote:

 Hi,

 The database file should shrink if you run shutdown defrag.

 The current compact algorithm is quite inefficient, that means the 
 databases file is quite big on average. The highest priority is still to 
 ensure it always works correctly, and when that's done I will work on more 
 efficiently re-using disk space and specially compact the file faster when 
 closing the database.

 Could you send me the new database file? It would be nice to have a 
 real-world database file to test this. The last file you sent helped a 
 lot, 
 thanks to it I found some problems that completely prevented the file to 
 shrink.

 Regards,
 Thomas



 On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote:

 Hi Thomas,

 I tested the same large data import with H2 1.4.178, and there is no 
 improvement over H2 1.4.177.

 Here are the file sizes, in both cases after the app has stopped:

 H2 1.3.176: pokercopilot.h2.db  301,669,352  bytes
 H2 1.4.178: pokercopilot.mv.db 1,023,037,440  bytes

 Let me know what I can do to help.

 Regards,

 Steve


 On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote:

 Hi Thomas,

 Great! Glad I could help make your superb product even better.



 On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote:

 Hi,

 Thanks a lot for the database! I know what the problem is now, but I 
 couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 
 181 
 MB of live data, the rest (about 78%) is not used. The mechanism to get 
 rid of the unused space is not working as it should for this case (I 
 think 
 the problem is that b-tree nodes are not processed correctly). This will 
 be 
 fixed in the next release.

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com 
 wrote:

 Hi Thomas,

 I've sent a link to file privately to your email address.

 Regards,

 Steve



 On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote:

 Hi,

 Hm, that didn't help much. Could you send me the (compressed) database 
 files please? If it's too big, what is the compressed size of the files?

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com 
 wrote:

 Hi Thomas,

 Thanks for the suggestion. I tried adding ;retention_time=1000 to 
 the URL, and this resulted in a small improvement.

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes
 pokercopilot.mv.db with RETENTION_TIME=1000:  811,728,896 bytes

 These numbers all reflect a loading of data in a newly created 
 database that consisted of roughly 2,400,000 INSERTS and UPDATES with 
 plenty of SELECTS and almost no DELETES. After the loading was complete, 
 I 
 let the application keep running with the database open for a few 
 minutes, 
 then close the application and therefore the database.

 Here is the full JDBC url I'm using:
 jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.
 pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co
 m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_
 LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000

 Let me know if there is anything else I can do to help diagnose this.

 Regards,

 Steve




 On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel

  -- 
 You received this message because you are subscribed to the Google 
 Groups 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

Re: [h2] Re: Off-by-one bug for auto-incremented BIGINT columns?

[Steve McLeod]

It is not your use of Long.MIN_VALUE that is a problem. It seems any 
negative number causes the same problem. For instance, this fails with a 
syntax exception:

CREATE TABLE connection (id BIGINT AUTO_INCREMENT(-1, 1) PRIMARY KEY);

On Saturday, 30 August 2014 08:21:54 UTC+2, Gili wrote:

 Noel,

 Both the H2 documentation and java.lang.Long.MIN_VALUE indicate that 
 -9223372036854775808 is a legal value, yet H2 rejects it. Isn't this a bug?

 Gili

 On Saturday, August 30, 2014 1:36:15 AM UTC-4, Noel Grandin wrote:

 Our internal implementation of sequences uses longs for everything, so 
 you will need to use values for start/stop/cycle/increment that fit 
 into a long value. 

 On Fri, Aug 29, 2014 at 11:04 PM, Gili cow...@bbs.darktech.org wrote: 
  Two clarifications: 
  
  I am using version 1.3.176. 
  This behavior contradicts the documentation: 
  http://www.h2database.com/html/datatypes.html#bigint_type 
  
  Please also take this opportunity to clarify the difference between 
 IDENTITY 
  and BIGINT in the documentation. As far as I understand it, IDENTITY is 
  equivalent to BIGINT AUTO_INCREMENT PRIMARY KEY but it's not clear if 
  there are any other differences. For example, the documentation reads 
 Used 
  values are never re-used, even when the transaction is rolled back for 
  IDENTITY but not for BIGINT AUTO_INCREMENT so it's not clear if there 
 is 
  some magic under the hood. 
  
  Gili 
  
  On Friday, August 29, 2014 4:53:16 PM UTC-4, Gili wrote: 
  
  Hi, 
  
  If I run: 
  
CREATE TABLE connection (id BIGINT 
 AUTO_INCREMENT(-9223372036854775808, 
  1) PRIMARY KEY); 
  
  I get: 
  
Syntax error in SQL statement CREATE TABLE CONNECTION (ID BIGINT 
  AUTO_INCREMENT(-9223372036854775808[*], 1) PRIMARY KEY); expected 
 long 
  
  I've successfully initialized all other types at their minimum values. 
  BIGINT seems to be the only type that has a problem with this. 
  
  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...@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. 
  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: Off-by-one bug for auto-incremented BIGINT columns?

[Steve McLeod]

  It seems any negative number causes the same problem. .. CREATE TABLE 
 connection (id BIGINT AUTO_INCREMENT(-1, 1) PRIMARY KEY)

 I think that's not the problem. Your statement (with -1) works for me.


Indeed. My mistake. 

 

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

Re: [h2] 1.4 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

I tested the same large data import with H2 1.4.178, and there is no 
improvement over H2 1.4.177.

Here are the file sizes, in both cases after the app has stopped:

H2 1.3.176: pokercopilot.h2.db  301,669,352  bytes
H2 1.4.178: pokercopilot.mv.db 1,023,037,440  bytes

Let me know what I can do to help.

Regards,

Steve


On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote:

 Hi Thomas,

 Great! Glad I could help make your superb product even better.



 On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote:

 Hi,

 Thanks a lot for the database! I know what the problem is now, but I 
 couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 
 MB of live data, the rest (about 78%) is not used. The mechanism to get 
 rid of the unused space is not working as it should for this case (I think 
 the problem is that b-tree nodes are not processed correctly). This will be 
 fixed in the next release.

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 I've sent a link to file privately to your email address.

 Regards,

 Steve



 On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote:

 Hi,

 Hm, that didn't help much. Could you send me the (compressed) database 
 files please? If it's too big, what is the compressed size of the files?

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 Thanks for the suggestion. I tried adding ;retention_time=1000 to 
 the URL, and this resulted in a small improvement.

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes
 pokercopilot.mv.db with RETENTION_TIME=1000:  811,728,896 bytes

 These numbers all reflect a loading of data in a newly created 
 database that consisted of roughly 2,400,000 INSERTS and UPDATES with 
 plenty of SELECTS and almost no DELETES. After the loading was complete, 
 I 
 let the application keep running with the database open for a few 
 minutes, 
 then close the application and therefore the database.

 Here is the full JDBC url I'm using:
 jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.
 pokercopilot/database/pokercopilot;DATABASE_EVENT_
 LISTENER='com.barbarysoftware.pokercopilot.database.
 DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;
 RETENTION_TIME=1000

 Let me know if there is anything else I can do to help diagnose this.

 Regards,

 Steve




 On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote:

 Hi,

 Probably it's due to the long default retention time, see 
 http://h2database.com/html/grammar.html#set_retention_time - try 
 adding ;retention_time=1000 to the database URL, and tell us if 
 and how much this reduced the size.

 Regards,
 Thomas



 On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 I've tried my desktop app with the new MV store in 1.4.177. After 
 loading a significant amount of data, the database file is 3 times the 
 size, compared to H2 1.3.176

 Here are the file sizes, in both cases after the app has stopped:

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes

 Is that expected? If not, what can I do to help locate the problem?


 Regards,

 Steve


  -- 
 You received this message because you are subscribed to the Google 
 Groups H2 Database group.
 To unsubscribe from this group and stop receiving emails from it, 
 send an email to h2-database...@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.
 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...@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.
 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...@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.
 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 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

Great! Glad I could help make your superb product even better.



On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote:

 Hi,

 Thanks a lot for the database! I know what the problem is now, but I 
 couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 
 MB of live data, the rest (about 78%) is not used. The mechanism to get 
 rid of the unused space is not working as it should for this case (I think 
 the problem is that b-tree nodes are not processed correctly). This will be 
 fixed in the next release.

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod 
 steve@gmail.comjavascript:
  wrote:

 Hi Thomas,

 I've sent a link to file privately to your email address.

 Regards,

 Steve



 On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote:

 Hi,

 Hm, that didn't help much. Could you send me the (compressed) database 
 files please? If it's too big, what is the compressed size of the files?

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 Thanks for the suggestion. I tried adding ;retention_time=1000 to 
 the URL, and this resulted in a small improvement.

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes
 pokercopilot.mv.db with RETENTION_TIME=1000:  811,728,896 bytes

 These numbers all reflect a loading of data in a newly created database 
 that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of 
 SELECTS and almost no DELETES. After the loading was complete, I let the 
 application keep running with the database open for a few minutes, then 
 close the application and therefore the database.

 Here is the full JDBC url I'm using:
 jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.
 pokercopilot/database/pokercopilot;DATABASE_EVENT_
 LISTENER='com.barbarysoftware.pokercopilot.database.
 DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;
 RETENTION_TIME=1000

 Let me know if there is anything else I can do to help diagnose this.

 Regards,

 Steve




 On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote:

 Hi,

 Probably it's due to the long default retention time, see 
 http://h2database.com/html/grammar.html#set_retention_time - try 
 adding ;retention_time=1000 to the database URL, and tell us if and 
 how much this reduced the size.

 Regards,
 Thomas



 On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 I've tried my desktop app with the new MV store in 1.4.177. After 
 loading a significant amount of data, the database file is 3 times the 
 size, compared to H2 1.3.176

 Here are the file sizes, in both cases after the app has stopped:

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes

 Is that expected? If not, what can I do to help locate the problem?


 Regards,

 Steve


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




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

Re: [h2] 1.4 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

Thanks for the suggestion. I tried adding ;retention_time=1000 to the 
URL, and this resulted in a small improvement.

pokercopilot.h2.db  302,018,560  bytes
pokercopilot.mv.db 999,120,896  bytes
pokercopilot.mv.db with RETENTION_TIME=1000:  811,728,896 bytes

These numbers all reflect a loading of data in a newly created database 
that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of 
SELECTS and almost no DELETES. After the loading was complete, I let the 
application keep running with the database open for a few minutes, then 
close the application and therefore the database.

Here is the full JDBC url I'm using:
jdbc:h2:/Users/steve/Library/Application 
Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000

Let me know if there is anything else I can do to help diagnose this.

Regards,

Steve




On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote:

 Hi,

 Probably it's due to the long default retention time, see 
 http://h2database.com/html/grammar.html#set_retention_time - try adding 
 ;retention_time=1000 
 to the database URL, and tell us if and how much this reduced the size.

 Regards,
 Thomas



 On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod 
 steve@gmail.comjavascript:
  wrote:

 Hi Thomas,

 I've tried my desktop app with the new MV store in 1.4.177. After loading 
 a significant amount of data, the database file is 3 times the size, 
 compared to H2 1.3.176

 Here are the file sizes, in both cases after the app has stopped:

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes

 Is that expected? If not, what can I do to help locate the problem?


 Regards,

 Steve


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




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

Re: [h2] 1.4 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

I've sent a link to file privately to your email address.

Regards,

Steve



On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote:

 Hi,

 Hm, that didn't help much. Could you send me the (compressed) database 
 files please? If it's too big, what is the compressed size of the files?

 Regards,
 Thomas


 On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod 
 steve@gmail.comjavascript:
  wrote:

 Hi Thomas,

 Thanks for the suggestion. I tried adding ;retention_time=1000 to the 
 URL, and this resulted in a small improvement.

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes
 pokercopilot.mv.db with RETENTION_TIME=1000:  811,728,896 bytes

 These numbers all reflect a loading of data in a newly created database 
 that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of 
 SELECTS and almost no DELETES. After the loading was complete, I let the 
 application keep running with the database open for a few minutes, then 
 close the application and therefore the database.

 Here is the full JDBC url I'm using:
 jdbc:h2:/Users/steve/Library/Application 
 Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000

 Let me know if there is anything else I can do to help diagnose this.

 Regards,

 Steve




 On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote:

 Hi,

 Probably it's due to the long default retention time, see 
 http://h2database.com/html/grammar.html#set_retention_time - try adding 
 ;retention_time=1000 to the database URL, and tell us if and how much 
 this reduced the size.

 Regards,
 Thomas



 On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote:

 Hi Thomas,

 I've tried my desktop app with the new MV store in 1.4.177. After 
 loading a significant amount of data, the database file is 3 times the 
 size, compared to H2 1.3.176

 Here are the file sizes, in both cases after the app has stopped:

 pokercopilot.h2.db  302,018,560  bytes
 pokercopilot.mv.db 999,120,896  bytes

 Is that expected? If not, what can I do to help locate the problem?


 Regards,

 Steve


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




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

[h2] 1.4 beta creates much bigger database file

[Steve McLeod]

Hi Thomas,

I've tried my desktop app with the new MV store in 1.4.177. After loading a 
significant amount of data, the database file is 3 times the size, compared 
to H2 1.3.176

Here are the file sizes, in both cases after the app has stopped:

pokercopilot.h2.db  302,018,560  bytes
pokercopilot.mv.db 999,120,896  bytes

Is that expected? If not, what can I do to help locate the problem?


Regards,

Steve


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