Hi.
Recently I have observed that recovery tool strangely breaks LOBs (in
particular CLOB objects in my case, but same is applicable to BLOB).
After recovery CLOB values are not what they were in original H2 file
before recovery process is run.
More than a day of debugging and some understanding came.
It seems other improvements made for LOB types handling affected recovery
workflow. In particular, functions "READ_CLOB_DB" or "READ_BLOB_DB" do
not work as they worked long time ago.
In recovery script we can see this type of SQL in case when table has
LOB columns:
CREATE TABLE O_1460(C0 CLOB, C1 CLOB);
INSERT INTO O_1460 VALUES(READ_CLOB_DB(1, 1006), READ_CLOB_DB(2, 3692));
...
CREATE TABLE O_1462(C0 CLOB, C1 CLOB);
INSERT INTO O_1462 VALUES(READ_CLOB_DB(5, 1006), READ_CLOB_DB(6, 4152));
These are temporary tables populated during recovery. In my case the table
really looks like (CLOB, CLOB) - it does not matter, main idea is that
table has some LOB column.
let's continue.
READ_CLOB_DB(5, 1006) leads to Recover.readClobDb which creates
ValueLobDb object, but inserting this object into table O_1462 calls the
method LobStorageBackend.copyLob:
long lobId = getNextLobId();
String sql = "INSERT INTO " + LOB_MAP + "(LOB, SEQ,
POS, HASH, BLOCK) " +
"SELECT ?, SEQ, POS, HASH, BLOCK FROM " +
LOB_MAP + " WHERE LOB = ?";
PreparedStatement prep = prepare(sql);
prep.setLong(1, lobId);
prep.setLong(2, oldLobId);
prep.executeUpdate();
reuse(sql, prep);
sql = "INSERT INTO " + LOBS + "(ID, BYTE_COUNT,
TABLE) " +
"SELECT ?, BYTE_COUNT, ? FROM " + LOBS + "
WHERE ID = ?";
prep = prepare(sql);
prep.setLong(1, lobId);
prep.setLong(2, tableId);
prep.setLong(3, oldLobId);
prep.executeUpdate();
reuse(sql, prep);
v = ValueLobDb.create(type, database, tableId, lobId
, null, length);
At this moment tables INFORMATION_SCHEMA.LOBS, LOBS_MAP are completely
empty.
So, it means "getNextLobId()" returns always 1. So, instead of original
LOBID which is passed in READ_CLOB_DB(6, 4152) we get a LOB reference in
target table O_1462 that has LOBID=1.
If there are 400 CLOB values in H2 database then all of them gets LOBID=1.
When the whole recovery script is run including also logic below
...
DELETE FROM INFORMATION_SCHEMA.LOBS;
INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2;
UPDATE INFORMATION_SCHEMA.LOBS SET TABLE = -2;
DELETE FROM INFORMATION_SCHEMA.LOB_MAP;
INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6;
DELETE FROM INFORMATION_SCHEMA.LOB_DATA;
INSERT INTO INFORMATION_SCHEMA.LOB_DATA SELECT * FROM O_10;
...
INSERT INTO SYMDS.SYM_ON_I_FOR_SYM_TRNSFRM_TBL_FLD_CONFIG SELECT * FROM
O_1460;
...
DROP TABLE O_1460;
...
we get local database where all CLOB values instead of referencing to
correct INFORMATION_SCHEMA.LOB_DATA blocks as in original H2 file they
reference same LOB object, that has had LOBID=1.
-----------------------------
Problem is that LobStorageBackend.copyLob: should work differently at
recovery case because system tables with LOB information is empty.
I did a 1 minute fix that solves the problem:
ValueLobDb:
private boolean isRecoveryReference = false;
public void setRecoveryReference(boolean isRecoveryReference) {
this.isRecoveryReference = isRecoveryReference;
}
public boolean isRecoveryReference() {
return isRecoveryReference;
}
Recover:
/**
* INTERNAL
*/
public static Value.ValueClob readClobDb(Connection conn, long lobId,
long precision) {
DataHandler h = ((JdbcConnection) conn).getSession().getDataHandler
();
verifyPageStore(h);
ValueLobDb lob = ValueLobDb.create(Value.CLOB, h,
LobStorageFrontend.TABLE_TEMP,
lobId, null, precision);
lob.setRecoveryReference(true);
return lob;
}
/**
* INTERNAL
*/
public static Value.ValueBlob readBlobDb(Connection conn, long lobId,
long precision) {
DataHandler h = ((JdbcConnection) conn).getSession().getDataHandler
();
verifyPageStore(h);
ValueLobDb lob = ValueLobDb.create(Value.BLOB, h, LobStorageFrontend
.TABLE_TEMP,
lobId, null, precision);
lob.setRecoveryReference(true);
return lob;
}
And in LobStorageBackend:
@Override
public ValueLobDb copyLob(ValueLobDb old, int tableId, long length) {
int type = old.getType();
long oldLobId = old.getLobId();
assertNotHolds(conn.getSession());
// see locking discussion at the top
synchronized (database) {
synchronized (conn.getSession()) {
try {
init();
ValueLobDb v = null;
if(!old.isRecoveryReference()){
long lobId = getNextLobId();
String sql = "INSERT INTO " + LOB_MAP + "(LOB, SEQ,
POS, HASH, BLOCK) " +
"SELECT ?, SEQ, POS, HASH, BLOCK FROM " +
LOB_MAP + " WHERE LOB = ?";
PreparedStatement prep = prepare(sql);
prep.setLong(1, lobId);
prep.setLong(2, oldLobId);
prep.executeUpdate();
reuse(sql, prep);
sql = "INSERT INTO " + LOBS + "(ID, BYTE_COUNT,
TABLE) " +
"SELECT ?, BYTE_COUNT, ? FROM " + LOBS + "
WHERE ID = ?";
prep = prepare(sql);
prep.setLong(1, lobId);
prep.setLong(2, tableId);
prep.setLong(3, oldLobId);
prep.executeUpdate();
reuse(sql, prep);
v = ValueLobDb.create(type, database, tableId, lobId
, null, length);
}else{
//Recovery process, no need to copy LOB using
normal infrastructure
v = ValueLobDb.create(type, database, tableId,
oldLobId, null, length);
}
return v;
} catch (SQLException e) {
throw DbException.convert(e);
}
}
}
}
Must be tested more, of course, but seems as a simple solution - handle
copying of LOB reference at recovery as a reference with original LOBID.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
