I will test it out and let you know.
----- Original Message -----
From: "Werner Guttmann" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, May 14, 2008 1:21 PM
Subject: Re: [castor-user] read-only connection for generating keys for
postgres.
Yes, driver-specific in that a few RDBMS do *not* support read-only
connections. But that should not be the case in your environment.
Let know whether using this property makes a difference for you ....
otherwise we'll have to add some code to how connections are being
obtained.
Werner
Stephen Ince wrote:
No. I did not. I see that it is driver specific. Hmmm. The error only
appeared when I used connection pooling.
Steve
----- Original Message ----- From: "Werner Guttmann"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, May 14, 2008 5:36 AM
Subject: Re: [castor-user] read-only connection for generating keys for
postgres.
Stephen,
have you had a look at
http://commons.apache.org/dbcp/configuration.html
and the 'defaultReadOnly' parameter in particular ?
Werner
Stephen Ince wrote:
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
---------------------------------------------------------------------
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
