Error 50200-192 if delete from table after upgrading from 1.3.176 to 1.4.192

[Dave Anonymous]

No special connection parameters:

DELETE FROM ALARM_HISTORY;

Timeout trying to lock table ; SQL statement:

DELETE FROM ALARM_HISTORY [50200-192] HYT00/50200 (Hilfe)

org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:

DELETE FROM ALARM_HISTORY [50200-192] 

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

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

    at org.h2.command.Command.filterConcurrentUpdate(Command.java:307) 

    at org.h2.command.Command.executeUpdate(Command.java:260) 

    at org.h2.server.TcpServerThread.process(TcpServerThread.java:344) 

    at org.h2.server.TcpServerThread.run(TcpServerThread.java:158) 

    at java.lang.Thread.run(Unknown Source) 

Caused by: org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index "PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE: ( /* key:23 */ 7653, 0, 83, 'SAB 1', 1, '1', 1, 1, 'External 1 Alarm ({NoUserText})', TIMESTAMP '2016-09-02 09:45:36.822')" [90112-192] 

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

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

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

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

    at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:336) 

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

    at org.h2.index.MultiVersionIndex.remove(MultiVersionIndex.java:192) 

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

    at org.h2.command.dml.Delete.update(Delete.java:95) 

    at org.h2.command.CommandContainer.update(CommandContainer.java:98) 

    at org.h2.command.Command.executeUpdate(Command.java:258) 

    ... 3 more 

    at org.h2.engine.SessionRemote.done(SessionRemote.java:624) 

    at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:191) 

    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184) 

    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) 

    at org.h2.server.web.WebApp.getResult(WebApp.java:1388) 

    at org.h2.server.web.WebApp.query(WebApp.java:1061) 

    at org.h2.server.web.WebApp$1.next(WebApp.java:1023) 

    at org.h2.server.web.WebApp$1.next(WebApp.java:1010) 

    at org.h2.server.web.WebThread.process(WebThread.java:164) 

    at org.h2.server.web.WebThread.run(WebThread.java:89) 

    at java.lang.Thread.run(Unknown Source) 

[Noel Grandin]

Sorry to say, but that looks like a corrupt DB

Suggest you try the recovery process and rebuild the DB

[Dave Anonymous]

i had this problem already with a few of our db's. recreating (drop/create) the indices fixed that problem.

and another strange thing related to this table happend:

CREATE CACHED TABLE ALARM_HISTORY(

    ACCOUNT INTEGER NOT NULL,

    UNIT TINYINT NOT NULL,

    UNITSTRING VARCHAR(1024) NOT NULL,

    ADDR INTEGER NOT NULL,

    ADDRSTRING VARCHAR(1024) NOT NULL,

    ALARMDATE TIMESTAMP NOT NULL,

    ALARMTYPE INTEGER NOT NULL,

    SEVERITY TINYINT NOT NULL,

    ALARMTEXT VARCHAR(1024) NOT NULL

);    
ALTER TABLE ALARM_HISTORY ADD CONSTRAINT FK_ALARM_HISTORY_USERS FOREIGN KEY (ACCOUNT) REFERENCES USERS (USERID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX IF NOT EXISTS IDX_ALARM_HISTORY_ALARMDATE ON ALARM_HISTORY(ALARMDATE);

CREATE INDEX IF NOT EXISTS IDX_ALARM_HISTORY_ACCOUNTALARMDATE ON ALARM_HISTORY(ACCOUNT,ALARMDATE);

I got two databases with this problem. with db1 i had just to recreate the indices but with the second i got this problem:

DROP INDEX PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE;

Index "IDX_ALARM_HISTORY_ACCOUNTALARMDATE" belongs to constraint "FK_ALARM_HISTORY_USERS"; SQL statement:

DROP INDEX PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE [90085-192] 90085/90085 (Hilfe)

org.h2.jdbc.JdbcSQLException: Index "IDX_ALARM_HISTORY_ACCOUNTALARMDATE" belongs to constraint "FK_ALARM_HISTORY_USERS"; SQL statement:

DROP INDEX PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE [90085-192] 

So i had to drop the fk as well and recreated the foreign key.

maybe it has sth to do that one of the index uses a fk column? 

but as i said recreating the indices/keys fixed that problem and all other tables seem to be fine and im not sure how to reproduce it.

[Dave Anonymous]

i could now reproduce it:

1) use 1.3.176 and execute users.sql and after that alarm_history.sql

2) shutdown and restart with 1.4.192 and try to execute "DELETE FROM ALARM_HISTORY"

[Dave Anonymous]

@Noel

could you reproduce the issue?

[Noel Grandin]

Sorry, but I'm not going to find time to track this down.

At a guess I would say it might have something to do with the Timestamp column

[Dave Anonymous]

Sorry, but I'm not going to find time to track this down.

At a guess I would say it might have something to do with the Timestamp column

isnt it possible to debug this with my attached sql script to reproduce the issue? 

it is very important for us to know which tables could be affected before we release our new version.

best regards

Dave

[Stuart Goldberg]

Please take a look at my post a number of months ago entitled "Timeout trying to lock table". This is the same problem!

There is a bug somewhere. This issue continues to plague us intermittently.

[Carl Hasselskog]

What's the status of this? We ran into the same issue when upgrading from 1.3.176 to 1.4.195 for our Android app. You can see more details here: http://crashes.to/s/97b4cacc8ab.

Anything we can do to assist? 

Regards

Carl

[Noel Grandin]

Short of debugging it itself, your best bet is to do dump before the upgrade, and a restore to a fresh db after.