H2 db getting corrupt on mac

[Kam]

I don't know what's causing it. We have a feature that backs up the H2 DB file and zips it via java. the DB is in use when we back it up (all it does is copy the h2 file) When we tried to use it, it's corrupted. This is the stack trace when I try to open it

ZipOutputStream append = new ZipOutputStream(new FileOutputStream(fileFullPath));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Caused by: java.lang.ArrayIndexOutOfBoundsException: 4

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

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

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

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

... 23 more

[Ryan How]

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

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

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

--
You received this message because you are subscribed 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.

[Thomas Mueller]

Hi,

If you copy the file while the database is open, then the backup can be corrupt in some cases (it's not so easy to reproduce it). Online backup is supported, but you have to use the "backup" SQL statement (see the documentation for details).

In theory the corruption can have other reasons. With which version of H2 was this database created? You can find it out using: "select * from information_schema.settings where name='CREATE_BUILD'" - or have a look in the SQL script created by the recover tool.

Regards,

Thomas

[Kam]

CREATE_BUILD | 173  (h2-1.3.173.jar)

Using a ConnectionPool. We are using the BACKUP nativeH2 Query command.  We're not using 'MULTI_THREADED' property either so everything defaultCIPHER=AES;TRACE_LEVEL_SYSTEM_OUT=0;ALLOW_LITERALS=NUMBERS

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

[Kam]

Here's the code:

 

 String sqlStatement = "BACKUP TO ?";

        try (Connection connection = getConnection())

        {

            try (PreparedStatement preparedStatement = connection.prepareStatement(sqlStatement))

            {

                preparedStatement.setString(1, fileName);

                preparedStatement.executeUpdate();

                if (isDebugEnabled)

                {

                    logger.debug("BACKUP TO result completed");

                }

            }

        }

[Ryan How]

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

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

--

You received this message because you are subscribed 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.

[Kam]

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

[witerat]

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

[Thomas Mueller]

Hi,

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

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

Regards,

Thomas

--

[Ryan How]

Pretty sure he is using the backup command.

> Here's the code:

 

 String sqlStatement = "BACKUP TO ?";

        try (Connection connection = getConnection())

        {

            try (PreparedStatement preparedStatement = connection.prepareStatement(sqlStatement))

            {

                preparedStatement.setString(1, fileName);

                preparedStatement.executeUpdate();

                if (isDebugEnabled)

                {

                    logger.debug("BACKUP TO result completed");

                }

            }

        }

[Thomas Mueller]

Hi,

OK, now I'm not sure if you use the "backup" statement or not, because at the beginning you wrote you don't ("We have a feature that backs up the H2 DB file and zips it via java. the DB is in use when we back it up (all it does is copy the h2 file)").

If you use the "backup" statement, then the database shouldn't get corrupt. If it does, then that would be a bug. I think we would need a reproducible test case. If you can reproduce the issue with a new database, could you try with H2 version 1.4.x? Version 1.4 should be more stable in this area, because recovery is much simpler (but version 1.4 is still beta).

Regards,

Thomas

[Thomas Mueller]

Hi,

OK, now I'm not sure if you use the "backup" statement or not, because at the beginning you wrote you don't ("We have a feature that backs up the H2 DB file and zips it via java. the DB is in use when we back it up (all it does is copy the h2 file)").

If you use the "backup" statement, then the database shouldn't get corrupt. If it does, then that would be a bug. I think we would need a reproducible test case. If you can reproduce the issue with a new database, could you try with H2 version 1.4.x? Version 1.4 should be more stable in this area, because recovery is much simpler (but version 1.4 is still beta).

Regards,

Thomas

On Sunday, September 7, 2014, Ryan How <rh...@exemail.com.au> wrote:

[Kam]

It was my mistake in describing. we do use the BACKUP command and then since it produces a zip file, we create a new zip file and copy inputStream to another outputStream to create a new zip file (with some configs related to the DB) and then give that zip file to the customers to use as their backup. 

Maybe the issue happens while writing the zip file to another file or maybe it's during backup. We've yet to encounter why.