Hi All,
Many thanks for the input. After a lot of head-scratching it seems that
Derby 10.3.2.1 (the *latest* version) gets fuddled with the SQL
statement
in the Recovery Log consistency check. The join below returns rows that
*do* have a 'commit' for the transaction!!
// Look for open transactions
stmt = getDatabaseConnection().prepareStatement(
"SELECT * FROM " + getLogTableName() + " WHERE "
+ getLogTableSqlColumnName() + " LIKE ? AND "
+ "transaction_id not in (SELECT transaction_id" + " FROM
"
+ getLogTableName() + " WHERE " +
getLogTableSqlColumnName()
+ " = ? OR " + getLogTableSqlColumnName() + " = ?) ");
stmt.setString(1, BEGIN + "%");
stmt.setString(2, COMMIT);
stmt.setString(3, ROLLBACK);
I've check this with a JDBC db viewer (jdbcnav) and the same rows are
returned (about 10% of all transactions with no disernable
similarities).
Is anyone out there using Derby 10.3.2.1 for the Recovery Log?
The XML config for the RL is :
<!-- Derby Recovery Log requires changes due to the SQL
implementation dialect -->
<RecoveryLog driver="org.apache.derby.jdbc.EmbeddedDriver"
idleConnectionTimeout="0" login="APP" password="APP"
recoveryBatchSize="10" requestTimeout="60"
url="jdbc:derby:/apps/rwds/rwds2.5.0.beta/xpe/clusterdb/recoverylog;databaseName=jaguar;create=true;>
<RecoveryLogTable
autoConnTranColumnType="CHAR(1) NOT NULL"
createTable="CREATE TABLE" execTimeColumnType="BIGINT"
extraStatementDefinition=",PRIMARY KEY (log_id)"
logIdColumnType="BIGINT NOT NULL"
requestIdColumnType="BIGINT"
sqlColumnName="sqlstr"
sqlColumnType="VARCHAR(8192) NOT NULL"
sqlParamColumnType="VARCHAR(8192)"
tableName="RECOVERY"
transactionIdColumnType="BIGINT NOT NULL"
updateCountColumnType="INT"
vloginColumnType="VARCHAR(32) NOT NULL"/>
<CheckpointTable
checkpointNameColumnType="VARCHAR(256) NOT NULL"
createTable="CREATE TABLE"
extraStatementDefinition=",PRIMARY KEY (name)"
logIdColumnType="BIGINT" tableName="CHECKPOINT"/>
<BackendTable
backendNameColumnType="VARCHAR(32) NOT NULL"
backendStateColumnType="INTEGER"
checkpointNameColumnType="VARCHAR(256) NOT NULL"
createTable="CREATE TABLE"
databaseNameColumnType="VARCHAR(32) NOT NULL"
extraStatementDefinition="" tableName="BACKEND"/>
<DumpTable
backendNameColumnType="VARCHAR(32) NOT NULL"
checkpointNameColumnType="VARCHAR(256) NOT NULL"
createTable="CREATE TABLE"
dumpDateColumnType="TIMESTAMP"
dumpFormatColumnType="VARCHAR(32) NOT NULL"
dumpNameColumnType="VARCHAR(256) NOT NULL"
dumpPathColumnType="VARCHAR(256) NOT NULL"
extraStatementDefinition=""
tableName="DUMP"
tablesColumnName="tables"
tablesColumnType="VARCHAR(256) NOT NULL"/>
</RecoveryLog>
Can anyone offer any suggestions (apart from not using Derby ;-) ??
Many Thanks in Advance,
Nick
On Fri, 2008-01-18 at 15:57, Emmanuel Cecchet wrote:
> Nick,
>
> Select statements are not inserted in the recovery log unless it's a
> 'select into' (which is equivalent as a create table) or you are using a
> construct that has a non-rollbackable side effect such as accessing a
> sequence.
> If a select statement is logged, it means it is broadcast and executed
> by all nodes in the cluster, so you might want to check that out.
> When I did the Derby config at ApacheCon, I did put config info in the
> presentation and there should also be config file samples in the example
> directory (especially with the fault tolerant recovery log config if I
> remember well).
>
> Hope this helps,
> Emmanuel
>
> > Dear Sequoia Users,
> >
> > We currently use Apache Derby for the backends in our distributed
> > Sequoia 2.10.9 cluster and Hypersonic for the Sequoia recovery log.
> >
> > Now we hear that their have been problems with HsqlDb in productive
> > environments and also of the suggestions to limit memory usage via the
> > URL settins (which we have included in our environment). Therefore we
> > would like to move the Recovery Log to Derby.
> >
> > My question is : does anyone use Derby for the Recovery Log. If so,
> > how do you configured the database columns and keys in the VDB XML?
> > I'm asking this as we have critical problem that Derby can take hours
> > to check a reasonably sized (200,000 rows) recovery log...
> >
> > Many Thanks in Advance,
> >
> > Nick
> >
> > p.s. Does anyone know if the upcoming 2.10.10 release will still insert
> > select statements into the recovery log? Our applications does 98%
> > selects and 2% inserts/updates...
> >
>
> --
> Emmanuel Cecchet - Research scientist
> EPFL - LABOS/DSLAB - IN.N 317
> Phone: +41-21-693-7558
>
> _______________________________________________
> Sequoia mailing list
> [email protected]
> https://forge.continuent.org/mailman/listinfo/sequoia
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
