Tomcat 4.1.36 comes with apache db common connection pooling. The reason
that hsqldb,db2, oracle and mysql don't not see it, is that the respective
jdbc drivers must be handling the error case and doing the switch from
readOnly == true --> readOnly == false.
commons-dbcp-1.2.1.jar
commons-pool-1.3.jar
jdbc pool configuration
------------------------------------------------------------------------------
<Resource name="jdbc/ods" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/ods">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>8</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>120000</value>
</parameter>
<parameter>
<name>username</name>
<value>postgres</value>
</parameter>
<parameter>
<name>password</name>
<value>postgres</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.postgresql.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:postgresql://localhost:5432/ods</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select count(*) from users</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>120</value>
</parameter>
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
<parameter>
<name>testOnReturn</name>
<value>true</value>
</parameter>
<parameter>
<name>numTestsPerEvictionRun</name>
<value>8</value>
</parameter>
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>500000</value>
</parameter>
<parameter>
<name>minEvictableIdleTimeMillis</name>
<value>500000</value>
</parameter>
</ResourceParams>
Steve
----- Original Message -----
From: "Werner Guttmann" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, May 14, 2008 3:45 AM
Subject: Re: [castor-user] read-only connection for generating keys for
postgres.
No problem. But let's think aloud a bit more: what connection pool are
you using, if I may ask ?
Werner
Stephen Ince wrote:
reset jdbc connection in
org.castor.persist.LocalTransactionContext.createConnection()
Hmm, trying to get my head around this .. ;-). I guess you are saying
that - once a connection is being re-used - it will have a wrong state
associated upon retrieval from the connection pool, correct ? Ideally,
it would be nice if such code could be added to
Yes but I do not think it is really a bug in castor. The connection pool
classes should reset the connections.
I did this in Castor because it was easier.
----- Original Message ----- From: "Werner Guttmann"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Tuesday, May 13, 2008 4:07 AM
Subject: Re: [castor-user] read-only connection for generating keys for
postgres.
Stephen Ince wrote:
FYI.
I was able to get my app working with castor/postgres and derby. I am
using castor 1.01. It seems to be a lot faster than the current
release.
FIXES and work arounds.
1) Changed to using IDENTITY key generator from HIGH/LOW. Over a
period
of time (week) the high/low seems to get out of sync.
2) ReadOnly transaction issue. This is not really a castor issue but
apache/db connection pooling issue. It is not resetting the
connections.
Work around.
reset jdbc connection in
org.castor.persist.LocalTransactionContext.createConnection()
Hmm, trying to get my head around this .. ;-). I guess you are saying
that - once a connection is being re-used - it will have a wrong state
associated upon retrieval from the connection pool, correct ? Ideally,
it would be nice if such code could be added to
Database.close()/TransactionContext.close(), but that would imply that
we woul dhave to force folks to close a Database instance (rather than
waiting for it to be 'auto-closed'.
conn.setReadOnly(false)
3) Derby identity generator bug.
In
org.exolab.castor.jdo.keygen.IdentityKeyGenerator
public void supportsSqlType( int sqlType )
throws MappingException
{
if (sqlType != Types.INTEGER &&
sqlType != Types.NUMERIC &&
sqlType != Types.DECIMAL &&
sqlType != Types.BIGINT) {
throw new MappingException(
Messages.format("mapping.keyGenSQLType",
getClass().getName(), new Integer(sqlType)));
}
if (sqlType != Types.INTEGER &&
fName.equals("hsql")) {
throw new MappingException(
Messages.format("mapping.keyGenSQLType",
getClass().getName(), new Integer(sqlType)));
}
// ODS: fix
if (sqlType == Types.NUMERIC &&
fName.equals("derby")) {
throw new MappingException(
Messages.format("mapping.keyGenSQLType",
getClass().getName(), new Integer(sqlType)));
}
}
Steve
----- Original Message ----- From: "Stephen Ince"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, January 30, 2008 1:19 PM
Subject: Re: [castor-user] read-only connection for generating keys for
postgres.
Werner,
I am having a hard time reproducing "read-only transaction" case
outside of my webapp. I am also getting the read-only error for
MAX-KEY key generation. Any suggestions on how I can reproduce it. I
am thinking about spawning a few threads and do not close the jdo
sessions. Sofar the single threaded case is not reproducible.
Here is a the stack trace for the webapp.
a.. This Database operation failed: : SQL exception in the key
generator org.exolab.castor.jdo.keygen.MaxKeyGenerator:
org.postgresql.util.PSQLException: ERROR: transaction is read-only.:
ERROR: transaction is read-only
Caused by: org.postgresql.util.PSQLException: ERROR: transaction is
read-only
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:255)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Dele
gatingPreparedStatement.java:92)
at
org.exolab.castor.jdo.keygen.MaxKeyGenerator.generateKey(MaxKeyGenera
tor.java:162)
at
org.exolab.castor.jdo.engine.SQLStatementCreate.generateKey(SQLStatem
entCreate.java:384)
at
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLS
tatementCreate.java:189)
at
org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313)
at
org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694)
at
org.exolab.castor.persist.LockEngine.create(LockEngine.java:532)
at
org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(
AbstractTransactionContext.java:817)
at
org.castor.persist.AbstractTransactionContext.create(AbstractTransact
ionContext.java:777)
at
org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractData
baseImpl.java:302)
Steve
----- Original Message ----- From: "Werner Guttmann"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, January 25, 2008 6:22 PM
Subject: Re: [castor-user] read-only connection for generating keys
for postgres.
I think I'll need a full test case so that I am able to run it
against e.g. Derby. Can you please supply us with one ?
Werner
Stephen Ince wrote:
Werner,
The read-only connection error showed up again on postgres. So
"<param name="same-connection" value="false"/>" didn't resolve the
problem. This problem only occurs with postgresql and derby.
Hsqldb, db2, oracle and sqlserver all work fine. I think it has
something to do with getting a connection from a pool that was
previously set to read-only.
Steve
A fatal error occurred while creating/updating
com.opendemand.security.UserProfi
le using SQL: UPDATE "users" SET
"user_name"=?,"title"=?,"fname"=?,"minitial"=?,
"lname"=?,"passwd"=?,"role"=?,"status"=?,"license"=?,"total_test_ran"=?,"test_ti
me"=?,"last_login_time"=?,"test_time_elapsed"=?,"group_id"=?,"company"=?,"accept
_license"=?,"email"=?,"url"=?,"referred_by"=?,"telephone"=?,"fax"=?,"address"=?,
"city"=?,"state"=?,"zipcode"=?,"country"=? WHERE "user_id"=?
org.postgresql.util.PSQLException: ERROR: transaction is read-only
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
Steve
----- Original Message ----- From: "Werner Guttmann"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, January 25, 2008 4:30 PM
Subject: Re: [castor-user] read-only connection for generating keys
for postgres.
Interesting, indeed. How did you come about this solution for
postgreSQL, if I may ask ?
Werner
Stephen Ince wrote:
Werner,
I think I solved the problem for postgres but not derby.
<mapping>
<key-generator name="HIGH-LOW" alias="keygen">
:
<param name="same-connection" value="false"/>
</key-generator>
</mapping>
This seemed to worked for postgres. With derby I get a similiar
error.
Nested error: java.sql.SQLException: An SQL data change is not
permitted for a read-only connection, user or database.: An SQL
data change is not permitted for a read-only connection, user or
database.
Steve
----- Original Message -----
From: "Werner Guttmann" <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>>
To: <[email protected] <mailto:[email protected]>>
Sent: Friday, January 25, 2008 3:48 PM
Subject: Re: [castor-user] read-only connection for generating
keys for postgres.
> Stephen,
>
> can you supply me with a test case (packaged as part of a Jira
issue) so
> that I could easily replay this problem ?
>
> Regards
> Werner
>
> Stephen Ince wrote:
>> I am getting the following error when I try to save an object
using a
>> key sequence.
>> This works fine for oracle, sqlserver, hsqldb and db2.
>>
>> Here is my database.xml. Is there something I can do it to
force castor
>> to use a different connection to get the keys or change
connection from
>> read-only to non read-only?
>>
>> org.exolab.castor.jdo.keygen.MaxKeyGenerator:
>> org.postgresql.util.PSQLException: ERROR: transaction is
read-only.:
>> ERROR: transaction is read-only
>>
>> <!DOCTYPE mapping PUBLIC "-//EXOLAB/Castor Mapping DTD Version
1.0//EN"
>> "http://castor.exolab.org/mapping.dtd";>
>> <mapping>
>> <key-generator name="HIGH-LOW" alias="keygen">
>> <param name="table" value="seq"/>
>> <param name="key-column" value="seq_table_name"/>
>> <param name="value-column" value="max_pk_value"/>
>> <param name="grab-size" value="50"/>
>> <param name="same-connection" value="true"/>
>> </key-generator>
>> </mapping>
>>
>> Steve
>>
>>
---------------------------------------------------------------------
>> To unsubscribe from this list please visit:
>>
>> http://xircles.codehaus.org/manage_email
>>
>>
>
>
>
---------------------------------------------------------------------
> To unsubscribe from this list please visit:
>
> http://xircles.codehaus.org/manage_email
>
>
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
