[h2] When using h2 ver.1.3.176, db is double size! How to extract .mv.db programmatically?

[Kalcic Salcic]

Hi,

I noticed that when I use h2 db version 1.3.176 database is double size
than a photo I put in db. Why is that? With earlier version I put a photo
of 1.5 MB (as a blob) in db and h2 db is also about 1.5 MB, but when I
started using ver.1.3.176 with the same code, my h2 db with the same photo
of 1.5 MB is 3 MB!

Another question: how can I programmatically extract the .mv.db files?

Can you help me please? Hope you can! :)
Thanks in advance!

[Evgenij Ryazanov]

Hello.

Did you try the latest version? Can you provide a standalone test case?

With trivial test case I cannot reproduce such problem.

Connection c = DriverManager.getConnection("jdbc:h2:./tst" +
Constants.BUILD_ID);
Statement s = c.createStatement();
s.execute("CREATE TABLE TEST (ID IDENTITY PRIMARY KEY, B BLOB)");
PreparedStatement ps = c.prepareStatement("INSERT INTO TEST(B)
VALUES (?)");
byte[] b = new byte[1_500_000];
Random r = new Random();
r.setSeed(0L);
r.nextBytes(b);
ps.setBytes(1, b);
ps.executeUpdate();
c.close();

1566720 tst175.h2.db
1566720 tst176.h2.db
1511424 tst177.mv.db
1511424 tst196.mv.db
1511424 tst197.mv.db


1.3.176 does not use MVStore engine. It is used since 1.4.177 (Beta) and
old PageStore engine is still available too. What exactly do you mean
by “extract the .mv.db files”?

[Kalcic Salcic]

I forgot "dump" in my string "java -cp h2-1.4.196.jar
org.h2.mvstore.MVStoreTool game.h2.db game.zip true". Why it is not working?
Please help :)

Post by Evgenij Ryazanov
Hello.
Did you try the latest version? Can you provide a standalone test case?
With trivial test case I cannot reproduce such problem.
Connection c = DriverManager.getConnection("jdbc:h2:./tst" +
Constants.BUILD_ID);
Statement s = c.createStatement();
s.execute("CREATE TABLE TEST (ID IDENTITY PRIMARY KEY, B BLOB)");
PreparedStatement ps = c.prepareStatement("INSERT INTO TEST(B)
VALUES (?)");
byte[] b = new byte[1_500_000];
Random r = new Random();
r.setSeed(0L);
r.nextBytes(b);
ps.setBytes(1, b);
ps.executeUpdate();
c.close();
1566720 tst175.h2.db
1566720 tst176.h2.db
1511424 tst177.mv.db
1511424 tst196.mv.db
1511424 tst197.mv.db
1.3.176 does not use MVStore engine. It is used since 1.4.177 (Beta) and
old PageStore engine is still available too. What exactly do you mean
by “extract the .mv.db files”?

[Kalcic Salcic]

Hi,

I will try your code, thanks.

Second question was regarding extraction .mv.db files, because I tried
version 1.4.196. I used user "sa" and my password and put a zipped photo
into db. How can I get that zipped file from mv.db file? I should use the
same user and password and extract the zipped file, but how? I tried this
way in console "java -cp h2-1.4.196.jar org.h2.mvstore.MVStoreTool
game.h2.db game.zip true" My db file is game.h2.db and I want to put my
zipped file from db in game.zip. So, how?

Post by Kalcic Salcic
Hi,
I noticed that when I use h2 db version 1.3.176 database is double size
than a photo I put in db. Why is that? With earlier version I put a photo
of 1.5 MB (as a blob) in db and h2 db is also about 1.5 MB, but when I
started using ver.1.3.176 with the same code, my h2 db with the same photo
of 1.5 MB is 3 MB!
Another question: how can I programmatically extract the .mv.db files?
Can you help me please? Hope you can! :)
Thanks in advance!

[Evgenij Ryazanov]

Database is not an archive.

If you stored a BLOB or BINARY value in a database, you have to connect to
this database again and execute an appropriate SQL query. After it you can
read the data from the returned ResultSet and store it where you want.

[Kalcic Salcic]

Thanks, I extracted successfully data from .mv.db.

I used your code and modified it to test my h2 db version 1.3.176 so,
again, I got two times bigger db.
My code is:
Class.forName("org.h2.Driver");
String user = "sa";
String password = "mypass";
String url = String.format("jdbc:h2:%s/mydb" + ";CIPHER=AES", myPath);
Connection c = DriverManager.getConnection(url, user, password) ;
Statement s = c.createStatement();
s.execute("CREATE TABLE TEST (ID IDENTITY PRIMARY KEY, B BLOB)");
PreparedStatement ps = c.prepareStatement("INSERT INTO TEST(B) VALUES
(?)");
byte[] b = new byte [1500000];
Random r = new Random();
r.setSeed(0L);
r.nextBytes(b);
ps.setBytes(1, b);
ps.executeUpdate();
c.close();

So, I randomly generated file of 1.5MB and got mydb.h2.db file of 3.46MB!??
Why?

Post by Evgenij Ryazanov
Database is not an archive.
If you stored a BLOB or BINARY value in a database, you have to connect to
this database again and execute an appropriate SQL query. After it you can
read the data from the returned ResultSet and store it where you want.

[Evgenij Ryazanov]

I cannot reproduce such issue on my system without abnormal termination of
process.

Database needs some space for operation. Its size may be different with the
same data. I guess the database was shut down on your system too early
without a chance to collect and free disk space that is not needed any
more. You can try to execute
SHUTDOWN COMPACT
as last SQL command to reduce its size. However, even this command cannot
guarantee that database will have smallest possible size.

Databases and archives serve for different purposes. If you don't really
need SQL support and use database only as a file storage, it's better to
use some archiver library instead. If you need SQL support, indexes, and
other features of databases you should know that they have a cost, and
should not assume that size of database will be nearly the same as size of
persisted data.

[Kalcic Salcic]

Ok, thanks a lot my friend!)

Post by Evgenij Ryazanov
I cannot reproduce such issue on my system without abnormal termination of
process.
Database needs some space for operation. Its size may be different with
the same data. I guess the database was shut down on your system too early
without a chance to collect and free disk space that is not needed any
more. You can try to execute
SHUTDOWN COMPACT
as last SQL command to reduce its size. However, even this command cannot
guarantee that database will have smallest possible size.
Databases and archives serve for different purposes. If you don't really
need SQL support and use database only as a file storage, it's better to
use some archiver library instead. If you need SQL support, indexes, and
other features of databases you should know that they have a cost, and
should not assume that size of database will be nearly the same as size of
persisted data.