unable to write: "the database is read only" after system crash

[Dieter Cailliau]

Using

jdbc:h2:${jboss.server.data.dir:/tmp}/bms;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=${com.x.ds.bms.lock.timeout.ms:5000}

h2 goes to readonly-mode on the attached database.

It may have to do with recovery, since we get into this situation (sometimes) by unpowering the machine (no clean shutdown).

It's not clear to me if this is intended behaviour (for a kind of db corruption h2 was unable to fix on the fly) or erroneous (h2 should have recovered on the fly, but there a h2 bug causing it to go into a kind of safety readonly mode).

[Dieter Cailliau]

I'm using h2-1.3.168 in jboss 7.2.0.Final on a Win7.

[Noel Grandin]

What exactly does the trace file say the problem is?

Also, this DB is working for me on the latest version.

Which may mean that the problem has been fixed, but might also just be
that something in your local environment, which is different to me, is
triggering 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...@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/groups/opt_out.

[Dieter Cailliau]

Every insert gives this in bms.trace.db

org.h2.jdbc.JdbcSQLException: The database is read only; SQL statement:
insert into Element (lastSeen, sources, name, options, tupple, host_string, assets2, DTYPE, string) values (?, ?, ?, ?, ?, ?, ?, 'DeviceCapabilityService', ?) [90097-168]^M
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)^M
        at org.h2.message.DbException.get(DbException.java:169)^M
        at org.h2.message.DbException.get(DbException.java:146)^M
        at org.h2.message.DbException.get(DbException.java:135)^M
        at org.h2.engine.Database.checkWritingAllowed(Database.java:1702)^M
        at org.h2.table.Table.checkWritingAllowed(Table.java:1060)^M
        at org.h2.engine.User.hasRight(User.java:111)^M
        at org.h2.engine.User.checkRight(User.java:97)^M
        at org.h2.command.dml.Insert.insertRows(Insert.java:93)^M
        at org.h2.command.dml.Insert.update(Insert.java:84)^M
        at org.h2.command.CommandContainer.update(CommandContainer.java:75)^M
        at org.h2.command.Command.executeUpdate(Command.java:230)^M
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)^M
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)^M
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)^M
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:125)^M
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)^M
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3058)^M
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3499)^M
        at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)^M
        at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)^M
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)^M
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)^M
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:328)^M
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)^M
        at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1212)^M
        at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:996)^M
        at org.jboss.as.jpa.container.AbstractEntityManager.flush(AbstractEntityManager.java:439)^M
        at ...my code

Note that the problem started off on a win7, next i copied the bms.h2.db file to my ubuntu laptop, restart my app, and i get stuck the same way. The file permissions on linux are set to allow everyone to read/write. The file permissions on Win7 were also such that the SYSTEM user can do anything. So i guess it must be something inside the file.

[Noel Grandin]

Hi

I'm sorry, at this I've reached the limit of my knowledge of that area
of the engine.
Hopefully Thomas will chime in.

Is it indeed a problem in database recovery.

I can tell is that it the DB is being set readonly by the following
logic in PageStore.recover()
if (log.getInDoubtTransactions().size() == 0) {
log.recoverEnd();
int firstUncommittedSection = getFirstUncommittedSection();
log.removeUntil(firstUncommittedSection);
} else {
setReadOnly = true;
}

But what exactly that piece of logic is trying to achieve I do not know.

Regards, Noel.

[Thomas Mueller]

Hi,

Well, do you use two-phase commit? Could you check if there are any in-doubt transactions, by running "select * from information_schema.in_doubt"? If yes, those transactions need to be either rolled back or committed first.

If no, then I wonder whether you explicitly open the database in read-only mode, or possibly don't have access rights to write to the file.

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+unsubscribe@googlegroups.com.

[Dieter Cailliau]

Yes I'm using 2-phase-commit because I use xa-datasource (this is an xml element in jboss' standalone-full-ha.xml), but I never really found out how to configure the thing (jboss as 7.2.0.Final with h2) to perform recovery. I've pasted the config xml below.

Note: having recovery is also not 'a must' for me actually, so would there be a way to proceed from this situation, somehow skipping recovery?

Note: i'm using xa because i need to send/rollback JMS messages transactionally along with the db transaction.

        <subsystem xmlns="urn:jboss:domain:datasources:1.1">
            <datasources>
                <xa-datasource jndi-name="java:jboss/datasources/H2-bms-DS" pool-name="H2-bms-DS" enabled="true" use-java-context="true">
                    <xa-datasource-property name="URL">
                        jdbc:h2:${jboss.server.data.dir:/tmp}/bms;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=${com.x.lock.timeout.ms:5000}
                    </xa-datasource-property>
                    <driver>h2</driver>
                    <security>
                        <user-name>bms</user-name>
                    </security>
                    <recovery>
                        <recover-credential>
                            <user-name>bms</user-name>
                        </recover-credential>
                    </recovery>
                </xa-datasource>
                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                </drivers>
            </datasources>
        </subsystem>

[Thomas Mueller]

Hi,

I'm sorry, but I'm not familiar with JBoss. I know that the 2-phase-commit implementation of H2 is currently incomplete, and probably this is the root cause of the problem. 

What H2 can't do currently is: after a crash, in the recovery phase, H2 doesn't allow to start a new transaction until all in-doubt transactions are either rolled back or committed.

I hope I can implement this missing features in the future, but demand for this feature is relatively low so far. Unfortunately with the current storage it's not possible to implement it (at least not easily); it will have to wait until the MVStore storage is ready.

Note: having recovery is also not 'a must' for me actually, so would there be a way to proceed from this situation, somehow skipping recovery?

Well, you could roll back all in-doubt transactions. This would be a manual step however:

    SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT ;

and then for each transaction, run:

    ROLLBACK TRANSACTION <name> 

(or COMMIT TRANSACTION <name>). Then re-open the database (close all connections or run "shutdown").

Regards,

Thomas

[Dieter Cailliau]

Thanks! That works fine.