Re: [h2] TimeNanos out of range when using different version of jar - No check when DB upgraded

[Petr Holík | EgoPanda]

Hello Noel,

thanks for clarification.

> I suggest you revert to an earlier version, do a dump, and restore into
the new version, after fixing up the data.

That's exactly what I did - I am unwanted H2 recovery expert :( I have
recently recovered about 20-30 corrupted customer's databases :(

Sincerely Petr Holik


2016-11-28 19:19 GMT+01:00 Noel Grandin :

> That's not a format change, that's a "we prefer to detect corruption
> earlier rather than later" change​
>
> Obviously your DB become corrupted at some point in the past, but H2
> didn't notice back then because it was not checking the range of such
> things.
>
> I suggest you revert to an earlier version, do a dump, and restore into
> the new version, after fixing up the data.
>
> --
> 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/X3JDJIBjeHc/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 https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
S pozdravem Petr Holik
tel. 774 22 90 90

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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] TimeNanos out of range when using different version of jar - No check when DB upgraded

[Petr Holík]

Hello,

when we upgrade version of H2  we sometimes(sometimes not) got this 
exception:

IllegalArgumentException: timeNanos out of range 864003
at org.h2.value.ValueTimestamp.(ValueTimestamp.java:54)
at org.h2.value.ValueTimestamp.fromDateValueAndNanos(ValueTimestamp.java:68)
at org.h2.value.ValueTimestamp.fromMillisNanos(ValueTimestamp.java:95)
at org.h2.store.Data.readValue(Data.java:786)
at org.h2.index.PageDataLeaf.readRow(PageDataLeaf.java:624)


SQL statement:
ALTER TABLE BILL_ITEM ADD COLUMN LUCKY_HOUR BOOLEAN DEFAULT FALSE 



I know that on pages there is a recommendation of: create a backup of the 
database (in the form of a SQL script) but honestly who is reading manuals?

Why there is no check? I know that track incompatible differences in binary 
format is some work. But it can only check that the version match in very 
simple way as:
if ( getSingleInt(SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME 
= 'CREATE_BUILD') != getSingleInt(SELECT VALUE FROM 
INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'info.BUILD_ID') )  throw new 
IllegalStateException("Reimport database");


Sorry for being rude, but database which silently corrupts your data when 
you upgrade is not production ready.

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.

Re: [h2] Commit of rolled back transaction works

[Petr Holík]

Hi, thanks for reply.

>From the database trace file it looks like that rolled back and commited is 
another transaction that it should. I'll modify test I have created and 
exchange datasource implementation and than i'll exchange database and I 
will consult it with hikari authors or with Spring authors.

Thanks for help.

Sincerely Petr Holik

Dne pondělí 28. listopadu 2016 7:51:03 UTC+1 Noel Grandin napsal(a):
>
> Yes, that number is session ID.
>
> This might have something to do with XA stuff, which some connection pools 
> use, but I don't understand that stuff.​
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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] Commit of rolled back transaction works

[Petr Holík]

Hi Noel,

I've created repo which contains this isolated problem. But I can't 
reproduce it without Spring and Hikari Connection pool. So therefore I 
think it should not be bug in H2 but in Hikari or Spring or I have some 
missconfiguration.

I am unsure if this numbers in log does corelate with session number?

2016-11-27 20:35:34 database: connecting session #4 to 
C:/Users/User/test/strangeTransaction  <- This '4'
2016-11-27 20:35:34 jdbc[4]:  <- This number '4'
/*SQL */ROLLBACK;

Does it corelate? If yes, then Spring or Hikari is propably rolling back 
another connection that it should :(

PS: repo is available here: 
https://github.com/petrholik/strangeTransactionBehavior

Sincerely Petr Holik

Dne neděle 27. listopadu 2016 7:55:49 UTC+1 Noel Grandin napsal(a):
>
> that does sound dodgy, a commit directly after a rollback should have no 
> effect
>
> yes, a test case would be very useful
> ​
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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: Sudden database corruption

[Petr Holík]

Hello,

it looks like damaged metadata of database. There should be some blocks on 
end of file which there is not.
If you understand java code and data is important you can hack(fix) repair 
tool to not stop on exception about wrong datasize and dump all used 
chunks. And from dumped chunks you possibly can extract some of your data.
MV store structure is not MUCH complicated and is described 
here: http://h2database.com/html/mvstore.html

or 

Maybe someone from commiters will investigate it, which is not very likely 
to happen without test case how to corrupt database :(

I personally do not suggest running BETA MV_STORE on production servers 
with production data.
And ALWAYS(!) make backups.

Sincerely Petr Holik


Dne čtvrtek 24. listopadu 2016 14:19:58 UTC+1 Henky Prayoga napsal(a):
>
> Hi,
>
> Yesterday we got a very strange and serious problem. We run H2 on 5 
> servers and suddenly, in relatively same time, all the database gets 
> corrupted. I tried to recover it, but no luck. We are using the latest 
> 1.4.193 version. If anyone willing to see the database sample, you can 
> check below dropbox link. 
>
> Btw, we found that there is some error on the filesystem log related to 
> "Too many open files".
>
> Please help... Thank you very much.
>
> Best Regards,
>
>
> Henky Prayoga
>
> https://dl.dropboxusercontent.com/u/13081377/db-back01.rar
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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] Commit of rolled back transaction works

[Petr Holík]

Hello, 

recently I've run into strange issue. I've got unit tests which is after 
every test method rolled back - BUT because of wrong setting of datasource 
there was commit after rollback of transaction. Here is relevant part of 
trace of H2:

2016-11-26 23:56:14 jdbc[6]:
/*SQL */ROLLBACK;
2016-11-26 23:56:14 jdbc[6]:
/*SQL */COMMIT;

and data previously rolled back data was visible in new transaction. I 
don't know what does SQL spec says about that(because its non-public) but 
think that COMMIT after ROLLBACK and vice versa should not have any effect.

Should I investigate it more? Or create test case? (If my assumption that 
this should not work is correct)


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.

Re: [h2] SHUTDOWN COMPACT does not recover space SHUTDOWN DEFRAG take insanely long Opening DB takes 500Mb of space

[Petr Holík | EgoPanda]

Hello Thomas,

it makes a sense we are using CLOBs in our database extensively. And it
often ends with Missing LOB entry(which i've reported on github) or
database with wasted space (after deleting data mostly from table which
contains CLOBs). Propably is something broken in H2 in handling (C)LOB. We
have customers the that ends up with databases which contains multiple rows
with same unique constraint we have even databases with same(duplicate)
primary key(maybe it is fixed now, I don't know) but we can hardly make a
test case if customer is using this database daily for 3 years.
But missing LOB appears even in 1.4.192. Last month we have 3 issue on this.

I am sorry to say that, but we are now migrating to alternative embedded
java database. We are going to create some stress test and we will run it
against H2 too. If it ends with missing or non referenced LOB i'll let you
know.

Sincerely Petr Holik



2016-11-24 18:01 GMT+01:00 Thomas Mueller Graf :

> Hi,
>
> It could be due to LOG data (BLOB, CLOB) that are not referenced, and not
> correctly garbage collected. To analyze that, we would need a reproducible
> test case.
>
> To find out, you could use the Recover tool on the large database.
>
> Regards,
> Thomas
>
>
>
> On Tuesday, November 22, 2016, Petr Holík  wrote:
>
>> Hello,
>>
>>  1.4.192 db, Page Store  1.7GB -> SHUTDOWN COMPACT -> 1.4GB -> open ->
>> 1.9GB -> SHUTDOWN DEFRAG -> takes on cpu thread(3.5Ghz for maybe two three
>> minutes) -> SHUTDOWN COMPACT -> 1.4GB
>>
>> -> export to SQL ~60MB sql -> import -> ~60MB DB file
>>
>> Why compact does almost not compact and why opening db takes 500Mb of
>> space? I have not investigated ho much data process realy writes to db when
>> opening it.(on SSD it looks fast but on rotation disk it takes ages to open
>> DB)
>>
>> 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.
>>
> --
> 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/3P-4fvsgKss/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 https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
S pozdravem Petr Holik
tel. 774 22 90 90

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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] SHUTDOWN COMPACT does not recover space SHUTDOWN DEFRAG take insanely long Opening DB takes 500Mb of space

[Petr Holík]

Hello,

 1.4.192 db, Page Store  1.7GB -> SHUTDOWN COMPACT -> 1.4GB -> open -> 
1.9GB -> SHUTDOWN DEFRAG -> takes on cpu thread(3.5Ghz for maybe two three 
minutes) -> SHUTDOWN COMPACT -> 1.4GB

-> export to SQL ~60MB sql -> import -> ~60MB DB file

Why compact does almost not compact and why opening db takes 500Mb of 
space? I have not investigated ho much data process realy writes to db when 
opening it.(on SSD it looks fast but on rotation disk it takes ages to open 
DB)

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: Is there any use for indexes on memory database?

[Petr Holík]

Indexes normaly slow down inserts and speeds up selects(if indexes is 
usable). Can you post more details? Like structure of table, number of 
rows, select you performed?


Dne čtvrtek 17. listopadu 2016 16:04:39 UTC+1 Tommi Korhonen napsal(a):
>
> I did some testing and it seems my database is much faster without 
> indexes! As I wrote, the database is in memory, not in file. It seems that 
> indexes only make memory searches slower. Maybe the query plan takes less 
> time if table scan is the only option.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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: How to start a server and create memory database with H2

[Petr Holík]

Hello,
all that you ask /as you proved/ can be found on H2 site. Sometimes its 
little bit hidden ;-)

Just reminder that for importing csv is function CSVREAD.
http://www.h2database.com/html/functions.html#csvread



Dne úterý 15. listopadu 2016 15:23:09 UTC+1 Tommi Korhonen napsal(a):
>
> Hello H2 users!
>
> I'm a newbie with H2. I've been browsing the documentation, but haven't 
> been able to found a good example for what I need. Hopefully someone could 
> help me with this. I'm looking for a script that
>
>1. starts an H2 as a server 
>2. creates a memory database
>3. loads 'create table' -statements from a file and executes them
>4. loads data from csv-files and inputs data into tables (or links 
>tables from a Postgres database) 
>
>
>

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

[h2] Re: Is there any use for indexes on memory database?

[Petr Holík]

Generaly speaking, using index for search is almost always(except very 
small(say 1-10k row dataset or very low index selectivity) faster then 
fulltable scan search. 
It very depends on your datasize if this difference matters.
I can imagine that selecting from table with ~100k rows would be fast 
enough event without index.

Do a simple test and post result here ;-)



Dne středa 16. listopadu 2016 15:56:17 UTC+1 Tommi Korhonen napsal(a):
>
> If the database is completely in memory, does creating indexes give any 
> real speed advantage for searches?
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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: Monitor tool for H2?

[Petr Holík]

Is far as I know there is no such tool for this.


Dne středa 16. listopadu 2016 16:33:14 UTC+1 Tommi Korhonen napsal(a):
>
> Sorry to post so many questions at once but I'm a new H2 user and there 
> seems to be many things to find out before getting my setup up and running. 
> One of them is how to monitor the database server? Is there a tool that 
> shows current transactions, users, memory and CPU usage, etc.?
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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

[Petr Holík]

Hi,

 I was too supprised how much commits is above last release in branch 
and its not released.
  
 Is there any work(related to release) which can be delegated to 
volunteers(maybe I can help)?


Sicerely Petr Holik


Dne čtvrtek 20. října 2016 5:34:30 UTC+2 vraim...@gmail.com napsal(a):
>
> 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] Re: Main differeces between 1.3 and 1.4 versions

[Petr Holík]

Hi, 

thanks for some more info. About performance I've came from some opinions 
here in google group and in H2 website. But I do no have yet direct 
comparsion. But, when I have some idle time(if ever) i'll do some 
comparsion on my database (cca 60 tables) and mix about 500 SQL which we 
use in our app and post result here.


Sincerely Petr Holik



Dne čtvrtek 20. října 2016 9:49:19 UTC+2 Steve McLeod napsal(a):
>
> 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: Main differeces between 1.3 and 1.4 versions

[Petr Holík]

Hello,

I definevely agree with the latest version(1.4.192), but you have to decide 
if to use(or not to use MV_STORE). Which in my opinion depends at least on:
1) Which degree of risk is acceptable for you. MV_STORE is more riskious - 
is still in beta and many users including me, are afraid of using beta in 
production
2) Do you need the features provided by MV_STORE
3) Performance test - if performance is an issue for you. Make test on your 
typical HW (MV_STORE should be good for SSDs) and typical workload. 

I've using H2 for about 5 years and it's pretty good and fast (compared to 
Derby which I've used before). But its definitely not bug free :( I've had 
about 100 installation of H2 which varies in size from 10MB - 1GB and I've 
encountered some bugs(which are now probably fixed)
1) Non functional unique key constraits
2) Not using indexes on same simple SQL stament which as prepared statems 
was executes in about 2ms and as not prepared staments takes few seconds - 
when I have time I am going to test it on 1.4
3) Terrible performannce and size requirements (20x size of data of source 
h2 db) if using linked tables and performing large dataset SQLs against 
linked tables
4) Not using indexes for sorting in reverse order

So please do not take this as criticism of H2 its definitely awesome 
product. I've just want to say if you going to be used beta features or 
features which is not widely used/tested(linked tables/cluster mode/MVCC) 
be prepared to facing bugs.

I personally am slowly migrating my 100 installations to from various 
version 1.3.X (ranging from 152) to latest 1.4.192 with disabled MV_STORE. 
For me realibility is criticial. I have in DB some sort of financial data.

BTW.: I think if you are using pagestore via JDBC then it would be possible 
to use MV_STORE via the map api together(but not via JDBC in same JVM) (if 
you want to have rock solid store for JDBC data and feature full store for 
other data)

Sincerely Petr Holik

Dne čtvrtek 20. října 2016 9:49:39 UTC+2 Steve McLeod napsal(a):
>
> 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 &versioning 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] Main differeces between 1.3 and 1.4 versions

[Petr Holík]

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 &versioning 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] Probably bug in primary key index in older version of H2

[Petr Holík]

Hello, 
I am migrating some older version of H2(1.7.154) database into new last 
usable (and marked stable)(1.7.175) trough linked tables.
Migration stopped on constraint violation on new database which is caused 
by duplicate data in old one.
Was that bug fixed in newer version or should I send db with duplicate 
values to further investigation?
Following selects are all issued on old version of database:


select * from Stock_history  where element_id = 62 and history_date = 
'2016-07-19';
STORE_ID   

ELEMENT_ID   

CUR   

HISTORY_DATE   

1 62 79. 2016-07-19
1 62 79. 2016-07-19(2 rows, 0 ms)

select * from information_schema.indexes where table_name = 'STOCK_HISTORY';
TABLE_CATALOG   

TABLE_SCHEMA   

TABLE_NAME   

NON_UNIQUE   

INDEX_NAME   

ORDINAL_POSITION   

COLUMN_NAME   

CARDINALITY   

PRIMARY_KEY   

INDEX_TYPE_NAME   

IS_GENERATED   

INDEX_TYPE   

ASC_OR_DESC   

PAGES   

FILTER_CONDITION   

REMARKS   

SQL   

ID   

SORT_TYPE   

CONSTRAINT_NAME   

INDEX_CLASS   

DB PUBLIC STOCK_HISTORY TRUE CONSTRAINT_INDEX_1B 1 STORE_ID 0 FALSE INDEX 
TRUE 3 A 0 CREATE INDEX PUBLIC.CONSTRAINT_INDEX_1B ON 
PUBLIC.STOCK_HISTORY(STORE_ID) 100 0 CONSTRAINT_1B 
org.h2.index.PageBtreeIndex
DB
PUBLIC STOCK_HISTORY TRUE CONSTRAINT_INDEX_1B3 1 ELEMENT_ID 0 FALSE INDEX 
TRUE 3 A 0 CREATE INDEX PUBLIC.CONSTRAINT_INDEX_1B3 ON 
PUBLIC.STOCK_HISTORY(ELEMENT_ID) 102 0 CONSTRAINT_1B3 
org.h2.index.PageBtreeIndex
DB
PUBLIC STOCK_HISTORY FALSE PRIMARY_KEY_1B 1 STORE_ID 0 TRUE PRIMARY KEY TRUE 
3 A 0 CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_1B ON 
PUBLIC.STOCK_HISTORY(STORE_ID, ELEMENT_ID, HISTORY_DATE) 104 0 
CONSTRAINT_1B38 org.h2.index.PageBtreeIndex
DB
PUBLIC STOCK_HISTORY FALSE PRIMARY_KEY_1B 2 ELEMENT_ID 0 TRUE PRIMARY KEY 
TRUE 3 A 0 CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_1B ON 
PUBLIC.STOCK_HISTORY(STORE_ID, ELEMENT_ID, HISTORY_DATE) 104 0 
CONSTRAINT_1B38 org.h2.index.PageBtreeIndex
DB PUBLIC STOCK_HISTORY FALSE PRIMARY_KEY_1B 3 HISTORY_DATE 0 TRUE PRIMARY 
KEY TRUE 3 A 0 CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_1B ON 
PUBLIC.STOCK_HISTORY(STORE_ID, ELEMENT_ID, HISTORY_DATE) 104 0 
CONSTRAINT_1B38 org.h2.index.PageBtreeIndex
DB
PUBLIC STOCK_HISTORY TRUE STOCKHISTORY_HISTORYDATEINDEX 1 HISTORY_DATE 0 
FALSE INDEX FALSE 3 A 0 CREATE INDEX PUBLIC.STOCKHISTORY_HISTORYDATEINDEX 
ON PUBLIC.STOCK_HISTORY(HISTORY_DATE) 106 0 *null* 
org.h2.index.PageBtreeIndex(6 rows, 0 ms)


select * from INFORMATION_SCHEMA.settings;
NAME   

VALUE   

CREATE_BUILD 154
info.BUILD_ID 172
But when I issue insert constraints work correctly:

insert into stock_history values (1, 62, 79.,'2016-07-19');
Unique index or primary key violation: "PRIMARY_KEY_1B ON 
PUBLIC.STOCK_HISTORY(STORE_ID, ELEMENT_ID, HISTORY_DATE)"; SQL statement:

Sincerely Petr Holik

-- 
You received this message because you are subscri

Re: [h2] What version to pick for production

[Petr Holík]

Hello,

any progress or timeline in releasing new(non Beta) version? We are facing 
some bugs in 1.3.* version. I it is not(for us) politically possible to use 
BETA in production we are going to have 100+ installations. (We are using 
still 1.3. with bugs workarounds :( )

Kind Regard Petr Holik


Dne pondělí 12. září 2016 10:35:46 UTC+2 Dennis Wagelaar napsal(a):
>
> Hi Nicolas,
>
> I've seen that your pull request is merged by now, and the master build is 
> passing. Any chance a new 1.4.193 stable release can be built?
>
> Kind regards,
> Dennis Wagelaar
>
> Op donderdag 9 juni 2016 10:07:20 UTC+2 schreef Nicolas Fortin (OrbisGIS):
>>
>> Hi,
>>
>> The issue is fixed in the pull request. I've tried to run the whole h2 
>> unit tests, there is some issues but not related to my changes (fr 
>> localizations and ports already used)
>>
>> regards,
>>
>> Nicolas FORTIN
>>
>> Développeur d’applications - AI
>> Laboratoire d’Acoustique Environnementale (LAE)
>> Ifsttar Nantes, Route de Bouaye, CS 4, 44344 Bouguenais Cedex
>>
>> Activités du Laboratoire d’Acoustique Environnementale : 
>> www.lae.ifsttar.fr
>> Un logiciel open-source pour l’acoustique des salles : I-Simpa (
>> i-simpa.ifsttar.fr)
>> Un logiciel open-source pour des études d’impact en milieu extérieur : 
>> NoiseM@p (noisemap.orbisgis.org)
>>
>> Le jeudi 9 juin 2016 09:00:03 UTC+2, Nicolas Fortin (OrbisGIS) a écrit :
>>>
>>> Hi,
>>>
>>> I'm working on a critical fix caused by null values in geometries :
>>>
>>> https://github.com/h2database/h2database/pull/267
>>>
>>> Please give me some days before releasing a stable version of H2.
>>>
>>> Thanks
>>>
>>> Nicolas FORTIN
>>>
>>> Développeur d’applications - AI
>>> Laboratoire d’Acoustique Environnementale (LAE)
>>> Ifsttar Nantes, Route de Bouaye, CS 4, 44344 Bouguenais Cedex
>>>
>>> Activités du Laboratoire d’Acoustique Environnementale : 
>>> www.lae.ifsttar.fr
>>> Un logiciel open-source pour l’acoustique des salles : I-Simpa (
>>> i-simpa.ifsttar.fr)
>>> Un logiciel open-source pour des études d’impact en milieu extérieur : 
>>> NoiseM@p (noisemap.orbisgis.org)
>>>
>>>
>>> Le mercredi 8 juin 2016 19:59:53 UTC+2, Thomas Mueller Graf a écrit :

 Hi,

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

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

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

 Regards,
 Thomas



-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send 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: Digest for h2-database@googlegroups.com - 16 Messages in 11 Topics

[Petr Holík]

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.