org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: File is read-only";

[oxj]

I have a database with a single table containing about 90K clobs. I
create the database, load the table, shutdown the db and use the java
backup to create a zip version of it.

When I go to open a connection to it using jdbc:h2:zip:<path> I get
the following error:

org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: File
is read-only"; "zip:D:\SA\IBMPROD\Epackage_out\IBMPROD.AttachSA.dbc!/
IBMPROD.AttachSA.h2.db" [90031-126]
org.h2.message.Message.getSQLException(Message.java:110)
org.h2.message.Message.convertIOException(Message.java:325)
org.h2.store.FileStore.write(FileStore.java:341)
org.h2.store.FileStore.writeDirect(FileStore.java:318)
org.h2.store.FileStore.init(FileStore.java:187)
org.h2.engine.Database.openFile(Database.java:497)
org.h2.store.PageStore.openNew(PageStore.java:276)
org.h2.store.PageStore.open(PageStore.java:260)
org.h2.engine.Database.getPageStore(Database.java:2310)
org.h2.engine.Database.open(Database.java:623)
org.h2.engine.Database.openDatabase(Database.java:226)
org.h2.engine.Database.<init>(Database.java:221)
org.h2.engine.Engine.openSession(Engine.java:58)
org.h2.engine.Engine.openSession(Engine.java:142)
org.h2.engine.Engine.getSession(Engine.java:122)
org.h2.engine.SessionFactoryEmbedded.createSession
(SessionFactoryEmbedded.java:17)
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:
245)
org.h2.engine.SessionRemote.createSession(SessionRemote.java:223)
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
org.h2.Driver.connect(Driver.java:58)
java.sql.DriverManager.getConnection(DriverManager.java:582)
java.sql.DriverManager.getConnection(DriverManager.java:185)
com.sps.sa.services.H2Service.startRODB(H2Service.java:90)

[Thomas Mueller]

Hi,

It seems the file IBMPROD.AttachSA.h2.db within the zip file is very
small (not actually database). How exactly did you create the zip
file? Could you verify the zip file contains the correct files? If it
does, could you post it or upload it somewhere (for example
RapidShare)?

Regards,
Thomas

[oxj]

Hi,

It only contains one table of clob objects. There is the
IBMPROD.AttachSA.h2.db file and then a IBMPROD.AttachSA.lobs.db
directory. There are actually about 90K clob records, which seem to
get stored in the subdirectory (they are inside the zip as well). The
DB opens fine if I don't zip it or if I restore it first.

I currently restore the database from the zip to get around this
problem, but I have another issue now as well; it takes about six
minutes just to establish a connection to the database. I create a
text index on the database during db creation and I'm wondering if I'm
not storing it correctly and maybe it gets created each time the
connection is opened?

Here is the connection when I create the DB: connection =
DriverManager.getConnection("jdbc:h2:" + path +
";CACHE_SIZE=256000;LOCK_MODE=0;LOG=0;UNDO_LOG=0", "sa", "");

And when I open it : connection = DriverManager.getConnection
("jdbc:h2:" + path + ";CACHE_SIZE=128000", "sa", "");

[Thomas Mueller]

Hi,

You didn't answer my questions:

- How exactly did you create the zip file?
- Could you verify the zip file contains the correct files?
- Could you post it or upload it somewhere (for example RapidShare)?

> Here is the connection when I create the DB:          connection =
> DriverManager.getConnection("jdbc:h2:" + path +
> ";CACHE_SIZE=256000;LOCK_MODE=0;LOG=0;UNDO_LOG=0", "sa", "");

Why exactly do you use LOCK_MODE=0;LOG=0;UNDO_LOG=0 ? I suggest to not
use it and try again.

Regards,
Thomas

[oxj]

I created the zip file by issuing Backup.execute(targetPath,
dbDir, dbName, false);

The files contained in the zip file are:

IBMPROD.AttachSA.h2.db
IBMPROD.AttachSA.trace.db
and hundreds of lob.db files under a directory named
IBMPROD.AttachSA.lobs.db

Unfortunately the data is on a secured machine which does not allow
any outflow of data so I cannot upload the zip file.

I was using those parameters on the db creation to speed up the
process. I will remove and retry.

I was also going to test to see if it is the fulltext index that might
be causing an update somewhere. Currently I'm having a big issue with
the connection time being between 5-6 minutes and was going to see if
that is due to the index.

[Thomas Mueller]

Hi,

I really like to solve this problem, but I'm afraid I don't understand
what is going on so far.

> Backup.execute(targetPath, dbDir, dbName, false);

The database must be closed when you run this command. Unfortunately,
earlier versions of H2 didn't enforce this.

> The files contained in the zip file are:
> IBMPROD.AttachSA.h2.db
> IBMPROD.AttachSA.trace.db
> and hundreds of lob.db files under a directory named
> IBMPROD.AttachSA.lobs.db

That looks correct... Could you run the following commands and post the results?

jar -tf abc.zip
dir

Specially, I would like to know the file size of the *.h2.db file.

> Unfortunately the data is on a secured machine which does not allow
> any outflow of data so I cannot upload the zip file.

Could you test it on a different machine, with test data?

> I was also going to test to see if it is the fulltext index that might
> be causing an update somewhere. Currently I'm having a big issue with
> the connection time being between 5-6 minutes and was going to see if
> that is due to the index.

The newest version of H2 supports a profiling tool. To use it, run the
H2 Console, and then click on 'Test Connection'. Afterwards, click on
"Test successful" and you should see the most common stack traces,
which is why it took so long to connect.

Regards,
Thomas