Data loss with H2 1.4.186

[Daniele Renda]

Hi,
I experienced a data loss that is very strange: I loss some data (about a thousand) in the table COMUNICAZIONE of the db linked here. The db is used from my desktop application that uses Spring JPA + Hibernate + Tomcat pool.

The strange thing is that related tables as COMUNICAZIONE_TELEFONATA and COMUNICAZIONE_EVENTO still refer to missing rows in the table COMUNICAZIONE.

As you can see I've a  foreign key that links these table so I can't understand how is possible that data are lost!

The very very strange thing is also that opening a db with a sqlclient, I still able to change/save rows in COMUNICAZIONE_TELEFONATA doing a commit with an invalid COMUNICAZIONE_ID.

For example try this:

1. DELETE COMUNICAZIONE_TELEFONATA WHERE LISTATELEFONATE_ID=2
2. Now change the row in COMUNICAZIONE_TELEFONATA where COMUNICAZIONE_ID=1 AND set LISTATELEFONATE_ID=2 (UPDATE COMUNICAZIONE_TELEFONATA SET LISTATELEFONATE_ID=1 WHERE COMUNICAZIONE_ID=1;)

If you do the change manually (I'm using dbever) I can do it --> BREAK IN FOREIGN KEY; instead if I do it with the query I've an exception. I logged both cases:

First case in which I can break foreign key

/*SQL #:1*/SELECT COUNT(*) FROM PUBLIC.COMUNICAZIONE_TELEFONATA;
03-18 17:50:47 jdbc[3]:
/**/ResultSet rs22 = prep20.getResultSet();
03-18 17:50:47 jdbc[3]:
/**/rs22.next();
03-18 17:50:47 jdbc[3]:
/**/rs22.getObject(1);
03-18 17:50:47 jdbc[3]:
/**/rs22.close();
03-18 17:50:47 jdbc[3]:
/**/prep20.close();
03-18 17:50:54 jdbc[3]:
/**/PreparedStatement prep21 = conn2.prepareStatement("UPDATE PUBLIC.COMUNICAZIONE_TELEFONATA SET LISTATELEFONATE_ID=? WHERE LISTATELEFONATE_ID=?", 1003, 1007);
03-18 17:50:54 jdbc[3]:
/**/prep21.setLong(1, 2L);
03-18 17:50:54 jdbc[3]:
/**/prep21.setLong(1, 2L);
03-18 17:50:54 jdbc[3]:
/**/prep21.setLong(2, 1L);
03-18 17:50:54 jdbc[3]:
/**/prep21.setLong(2, 1L);
03-18 17:50:54 jdbc[3]:
/**/prep21.execute();
03-18 17:50:54 lock: 3 shared read lock requesting for COMUNICAZIONE_TELEFONATA
03-18 17:50:54 lock: 3 shared read lock ok COMUNICAZIONE_TELEFONATA
03-18 17:50:54 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F remove ( /* key:96 */ 1, 1)
03-18 17:50:54 pageStore: log undo 1185
03-18 17:50:54 pageStore: updateRecord page[1185] b-tree leaf table:312 entries:62
03-18 17:50:54 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F remove ( /* key:96 */ 1, 1)
03-18 17:50:54 pageStore: log undo 1125
03-18 17:50:54 pageStore: updateRecord page[1125] b-tree leaf table:196 entries:62
03-18 17:50:54 index: COMUNICAZIONE_TELEFONATA_DATA remove ( /* key:96 */ 1, 1)
03-18 17:50:54 pageStore: log undo 1058
03-18 17:50:54 pageStore: updateRecord page[1058] data leaf table:53 COMUNICAZIONE_TELEFONATA entries:62 parent:0 keys:[3, 4, 5, 6, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 96] offsets:[4094, 4092, 4090, 4088, 4085, 4083, 4080, 4077, 4074, 4070, 4066, 4062, 4058, 4054, 4050, 4046, 4042, 4038, 4034, 4030, 4026, 4022, 4018, 4014, 4010, 4006, 4002, 3998, 3994, 3990, 3986, 3982, 3978, 3974, 3970, 3966, 3962, 3957, 3953, 3949, 3945, 3941, 3937, 3933, 3929, 3925, 3921, 3916, 3911, 3906, 3901, 3896, 3891, 3886, 3881, 3876, 3871, 3866, 3861, 3856, 3851, 3846, 3844]
03-18 17:50:54 pageStore: log - s: 3 table: 53 row: ( /* key:96 deleted */ 1, 1)
03-18 17:50:54 index: COMUNICAZIONE_TELEFONATA_DATA add ( /* key:97 */ 1, 2)
03-18 17:50:54 pageStore: log + s: 3 table: 53 row: ( /* key:97 */ 1, 2)
03-18 17:50:54 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F add ( /* key:97 */ 1, 2)
03-18 17:50:54 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F add ( /* key:97 */ 1, 2)
03-18 17:50:54 jdbc[3]:
/*SQL l:90 #:1 t:9*/UPDATE PUBLIC.COMUNICAZIONE_TELEFONATA SET LISTATELEFONATE_ID=? WHERE LISTATELEFONATE_ID=? {1: 2, 2: 1};
03-18 17:50:54 pageStore: log commit s: 3
03-18 17:50:54 lock: 3 shared read lock unlock COMUNICAZIONE_TELEFONATA
03-18 17:50:54 jdbc[3]:
/**/prep21.getUpdateCount();
03-18 17:50:54 jdbc[3]:
/**/prep21.close();
03-18 17:50:55 pageStore: pageOut.storePage [1300] stream data key:54 pos:1330 remaining:2766
03-18 17:52:06 jdbc[3]:
/**/Statement stat2 = conn2.createStatement(1003, 1007);
03-18 17:52:06 jdbc[3]:
/**/stat2.setMaxRows(200);
03-18 17:52:06 jdbc[3]:

Second case doing the update with a query:

03-18 17:52:16 jdbc[3]:
/**/rs25.close();
03-18 17:52:16 jdbc[3]:
/**/stat3.close();
03-18 17:52:16 jdbc[3]:
/**/conn2.getWarnings();
03-18 17:52:16 jdbc[3]:
/**/conn2.clearWarnings();
03-18 17:52:22 jdbc[3]:
/**/Statement stat4 = conn2.createStatement(1003, 1007);
03-18 17:52:22 jdbc[3]:
/**/stat4.execute("UPDATE COMUNICAZIONE_TELEFONATA SET LISTATELEFONATE_ID=1 WHERE COMUNICAZIONE_ID=1");
03-18 17:52:22 lock: 3 shared read lock requesting for COMUNICAZIONE_TELEFONATA
03-18 17:52:22 lock: 3 shared read lock ok COMUNICAZIONE_TELEFONATA
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F remove ( /* key:3 */ 1, 3)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F remove ( /* key:3 */ 1, 3)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA remove ( /* key:3 */ 1, 3)
03-18 17:52:22 pageStore: log - s: 3 table: 53 row: ( /* key:3 deleted */ 1, 3)
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F remove ( /* key:97 */ 1, 2)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F remove ( /* key:97 */ 1, 2)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA remove ( /* key:97 */ 1, 2)
03-18 17:52:22 pageStore: log - s: 3 table: 53 row: ( /* key:97 deleted */ 1, 2)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA add ( /* key:98 */ 1, 1)
03-18 17:52:22 pageStore: log + s: 3 table: 53 row: ( /* key:98 */ 1, 1)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F add ( /* key:98 */ 1, 1)
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F add ( /* key:98 */ 1, 1)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA add ( /* key:99 */ 1, 1)
03-18 17:52:22 pageStore: log + s: 3 table: 53 row: ( /* key:99 */ 1, 1)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F add ( /* key:99 */ 1, 1)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA remove ( /* key:99 */ 1, 1)
03-18 17:52:22 pageStore: log - s: 3 table: 53 row: ( /* key:99 deleted */ 1, 1)
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F remove ( /* key:98 */ 1, 1)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F remove ( /* key:98 */ 1, 1)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA remove ( /* key:98 */ 1, 1)
03-18 17:52:22 pageStore: log - s: 3 table: 53 row: ( /* key:98 deleted */ 1, 1)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA add ( /* key:97 deleted */ 1, 2)
03-18 17:52:22 pageStore: log + s: 3 table: 53 row: ( /* key:97 */ 1, 2)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F add ( /* key:97 */ 1, 2)
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F add ( /* key:97 */ 1, 2)
03-18 17:52:22 index: COMUNICAZIONE_TELEFONATA_DATA add ( /* key:3 deleted */ 1, 3)
03-18 17:52:22 pageStore: log + s: 3 table: 53 row: ( /* key:3 */ 1, 3)
03-18 17:52:22 index: UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F add ( /* key:3 */ 1, 3)
03-18 17:52:22 index: FK_ERPYS2COX2RABQ2AMUIXY09GU_INDEX_F add ( /* key:3 */ 1, 3)
03-18 17:52:22 pageStore: log commit s: 3
03-18 17:52:22 lock: 3 shared read lock unlock COMUNICAZIONE_TELEFONATA
03-18 17:52:22 jdbc[3]: exception
org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "UK_SA780TK1V7LH4N75SD3H087XV_INDEX_F ON PUBLIC.COMUNICAZIONE_TELEFONATA(LISTATELEFONATE_ID) VALUES ( /* 99 */ 1 )"; SQL statement:
UPDATE COMUNICAZIONE_TELEFONATA SET LISTATELEFONATE_ID=1 WHERE COMUNICAZIONE_ID=1 [23505-186]
    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.BaseIndex.getDuplicateKeyException(BaseIndex.java:102)
    at org.h2.index.PageBtree.find(PageBtree.java:120)
    at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:150)
    at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:103)
    at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:96)
    at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:87)
    at org.h2.index.MultiVersionIndex.add(MultiVersionIndex.java:58)
    at org.h2.table.RegularTable.addRow(RegularTable.java:119)
    at org.h2.table.Table.updateRows(Table.java:478)
    at org.h2.command.dml.Update.update(Update.java:145)
    at org.h2.command.CommandContainer.update(CommandContainer.java:78)
    at org.h2.command.Command.executeUpdate(Command.java:254)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:369)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:155)
    at org.jkiss.dbeaver.runtime.sql.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:360)
    at org.jkiss.dbeaver.runtime.sql.SQLQueryJob.extractData(SQLQueryJob.java:628)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsProvider.readData(SQLEditor.java:1169)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataPumpJob.run(ResultSetDataPumpJob.java:132)
    at org.jkiss.dbeaver.runtime.AbstractJob.run(AbstractJob.java:91)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
03-18 17:52:22 jdbc[3]:
/**/stat4.close();
03-18 17:52:22 jdbc[3]:
/**/conn2.getWarnings();
03-18 17:52:22 jdbc[3]:
/**/conn2.clearWarnings();
03-18 17:52:22 pageStore: pageOut.storePage [1300] stream data key:54 pos:1380 remaining:2716
03-18 17:54:03 jdbc[3]:
/**/Statement stat5 = conn2.createStatement(1003, 1007);
03-18 17:54:03 jdbc[3]:
/**/stat5.setMaxRows(200);
03-18 17:54:03 jdbc[3]:

Seems strange to me that this is a big bug in h2, but I can't see how this behaviour is possible.

If you can take a look I would be grateful

Thanks

[Daniele Renda]

I forgot to write my connection url: jdbc:h2:C:\recovery\db;IFEXISTS=FALSE;IGNORECASE=TRUE;AUTO_RECONNECT=TRUE;MV_STORE=FALSE;MVCC=TRUE;LOCK_TIMEOUT=10000;TRACE_LEVEL_FILE=3

Thanks

...

[Daniele Renda]

The db url was disabled; here the new link to the file.

Thanks

[Noel Grandin]

This is probably an issue with mvcc mode. Unfortunately we are unlikely to fix it because mvcc mode is going to be replaced by the MVStore engine. 

For now I would suggest that you run without mvcc mode. 

On Wednesday, 18 March 2015, Daniele Renda <daniel...@gmail.com> wrote:

The db url was disabled; here the new link to the file.

Thanks

--
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.

[Daniele Renda]

Unfortunally in my application I've a good degree of concurrency and I need the mvcc mode. I turn back from Mvstore to PageStore due to the instability of Mvstore.

In short, if I need row level lock and a good reliability what version of H2 I should use and with which settings?

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/0NqMImiNIN0/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--

Daniele Renda

[Daniele Renda]

Just as info, also disabling MVCC and redoing tests I wrote I can break foreign key!

--

Daniele Renda

[Noel Grandin]

On Wed, Mar 18, 2015 at 8:16 PM, Daniele Renda <daniel...@gmail.com> wrote:
>
> In short, if I need row level lock and a good reliability what version of H2
> I should use and with which settings?

Are you really sure you need MVCC mode, because H2 is plenty fast and
concurrent in it's normal mode.

[Noel Grandin]

On Wed, Mar 18, 2015 at 8:21 PM, Daniele Renda <daniel...@gmail.com> wrote:
> Just as info, also disabling MVCC and redoing tests I wrote I can break
> foreign key!
>

If you can create a standalone test case I can look at fixing this, at
the moment I have no idea what could be wrong.

[Daniele Renda]

I'm not sure at 100%. I'll try without.

So MV_STORE=FALSE and MVCC=FALSE is the most reliable configuration for H2 for now? I want reduce the possibility to loss data (there wasn't power loss) again!

Thanks!

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/0NqMImiNIN0/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--

Daniele Renda

[Daniele Renda]

That case happend only 1-2 times for now and after a lot of operations. The sure thing is that if you pick up my db and try to do operations I wrote you'll se the problem.

you can not understand why I can break the foreign keys doing steps I wrote?

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/0NqMImiNIN0/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--

Daniele Renda

[Daniele Renda]

FInally I absoluty need MVCC in my case.

Please can you tell me the more reliable configuration for H2 in this case? I hoped what PageStore was stable. Maybe is more stable MvStore with MVCC?

Thanks

Il giorno mercoledì 18 marzo 2015 19:42:16 UTC+1, Daniele Renda ha scritto:

That case happend only 1-2 times for now and after a lot of operations. The sure thing is that if you pick up my db and try to do operations I wrote you'll se the problem.

you can not understand why I can break the foreign keys doing steps I wrote?

Thanks

2015-03-18 19:37 GMT+01:00 Noel Grandin <noelg...@gmail.com>:

On Wed, Mar 18, 2015 at 8:21 PM, Daniele Renda <daniel...@gmail.com> wrote:
> Just as info, also disabling MVCC and redoing tests I wrote I can break
> foreign key!
>

If you can create a standalone test case I can look at fixing this, at
the moment I have no idea what could be wrong.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/0NqMImiNIN0/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@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.

--

Daniele Renda

[Noel Grandin]

The only current stable configuration is PageStore without MVCC.

If you need more performance you will either need to
- do performance tuning of your queries
- upgrade your hardware
- look at other database options (like PostgreSQL)

[Daniele Renda]

Unfortunally I need mvcc in order to use row level locking that seems to be supported only with MVCC in H2. H2 now is very fast but unfortunally I've a lot of concurrency (I'm also using parallel stream) because the app manage realtime events and there are many cases in which events arrives at the same times. So is not facible using a table lock.

Thanks very much!

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/0NqMImiNIN0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@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.

--

Daniele Renda